This documentation is not maintained. Please refer to doc.castsoftware.com/technologies to find the latest updates.

PL/SQL Analyzer - SQL Analyzer

The comparison has been done between PL/SQL Analyzer 8.3.8 and SQL Analyzer 3.5.0-alpha1 (AIP 8.3.8 + datacolumnaccess 1.0.0-beta2).

Objects

Here is the list of the equivalence between PL/SQL Analyzer and SQL Analyzer objects:

  • Most of the objects have equivalents on both analyzers
  • Some objects exists only on SQL Analyzer, with no equivalent object on PL/SQL Analyzer.
  • Some objects which exists only on PL/SQL Analyzer, with no equivalent object on SQL Analyzer.


Please find the detailed list,

Object familyOriginal PL/SQL AnalyzerCounterpart SQL AnalyzerImpactRemarks
Files(139266) source file (subject to parsing)(1000007) File which contains source code(tick)
Structure
(1000003) Universal Directory(tick)No impact, just structure
(138000) Oracle Project(1101005) SQL Analyzer Project(tick)

(1000001) Universal Project(tick)
No impact, just technical types
(141813) The result project of a plugin
(139278) Oracle Database Subset(1101002) SQL Analyzer Subset(info)No impact, just structure
(138012) Oracle instance
(info)

No impact, just structure

(138014) Oracle schema(1101011) Schema(tick)
(140519) SQL unknown schema
(info)

No impact, just structure

Script
(1101010) SQL Script(plus)New feature
Package

(138510) Oracle package header

(1101015) Package(tick)

No impact, merged types

(138511) Oracle package body
(140285) Oracle package cursor
(info)

No impact on function points.

TQI is impacted only if (1564) Cursor naming convention - prefix control has been activated.

Normally the PL/SQL rule (1564) Cursor naming convention - prefix control is inactive by default and it make sense only when application contains Forms.

Table/View


(138017) Oracle table

(1101006) Table

(1101093) Unresolved Table

(tick)No impact, merged types
(140697) Oracle object table

(138183) Oracle view

(1101013) View(tick)No impact, merged types
(138791) Oracle materialized view

Column Like

(138832) Oracle table column

(1101007) Table Column(tick)No impact, merged types
(139223) Oracle view column
Constraint Like(138315) Oracle check table constraint
(info)No impact on function points or TQI
(36117) Oracle default table constraint
(info)No impact on function points or TQI
(138393) Oracle foreign key table constraint(1101016) Foreign Key(tick)
Index Like(138159) Oracle index(1101008) Index

(tick)No impact, merged types

(138774) Oracle bitmap index
(138743) Oracle function based index
(138389) Oracle primary key table constraint(1101020) Unique Constraint(tick)

Procedure Like(138245) Oracle function

(1101012) Function

(1101094) Unresolved Function

(tick)No impact, merged types
(140119) Oracle private stored function
(138244) Oracle procedure(1101009) Procedure(tick)No impact, merged types
(140118) Oracle private stored procedure
(138795) Oracle database event trigger(1101014) Trigger(tick)No impact, merged types
(138219) Oracle ddl trigger
(138220) Oracle dml trigger

(1101058) Method(plus)New feature
Type Like(138433) Oracle cursor datatype
(info)No impact on function points or TQI

(138591) Oracle collection nested table type

(1101049) Type(tick)No impact, merged types
(138590) Oracle collection varray type
(138592) Oracle object type
(138628) Oracle object type body
(138589) Oracle incomplete type
Synonym/Alias Like(140584) Oracle private synonym(1101040) Synonym(tick)No impact, merged types

(140583) Oracle public synonym
Database link Like(138732) Oracle database link(1101091) Database Link(tick)
Sequence Like(138207) Oracle sequence
(info)No impact on function points or TQI


Here is the list of the equivalence between PL/SQL Analyzer and SQL Analyzer links:

  • Most of the links have equivalents on both analyzers
  • Some links exists only on SQL Analyzer, with no equivalent links on PL/SQL Analyzer.
  • Some links which exists only on PL/SQL Analyzer, with no equivalent links on SQL Analyzer.

Please find the detailed list,

CallerCalleeLink typeOriginal PL/SQL AnalyzerCounterpart SQL AnalyzerRemarks
Table/ViewTable/View/Unresolved Table

useSelect(tick)(tick)The case of Table / View / Unresolved Table selected in a View
referLink(tick)(tick)
referDelete, referUpdate(minus)(plus)New feature
Procedure LikecallLink(tick)(tick)
Column LikeaccessRead, accessWrite(tick)(tick)We should install com.castsoftware.datacolumnaccess to have them.
TriggerFire: Before Insert/Update/Delete, After Insert/Update/Delete, For each row Insert/Update/Delete and For all row Insert/Update/Delete (warning)
Incorrect modelisation for function points
Column LikeColumn LikereferLink(tick)(tick)The case of Columns referenced in a Foreigner Key
Procedure Like


Procedure Like


callLink(tick)

(tick)



TriggercallLink(minus)(plus)New feature : when table is INSERTED/ UPDATED / DELETED in a Procedure / Function we add a call link between the Procedure / Functions and the Trigger.
Table/ViewuseSelect, useInsert, useUpdate, useDelete(tick)(tick)

Between Triggers and Tables

monitorInsert, monitorUpdate, monitorDelete(minus)(plus)New feature : adding monitor link between the Trigger and the triggered Table/View
Column LikeaccessRead, accessWrite(tick)(tick)We should install com.castsoftware.datacolumnaccess to have them.
Constraint LikeTable/ViewrelyonLink(tick)(tick)
Column LikerelyonLink(tick)(tick)
Index LikeTable/ViewrelyonLink(tick)(tick)
Column LikerelyonLink(tick)(tick)
Type LikeType LikeinheritLink(minus)(plus)New feature, new link from Type to super Type
Package

Column LikeaccessRead, accessWrite(tick)(minus)When Columns are accessed directly from packages.
Procedure LikecallLink(tick)(minus)

When Procedure Like are called directly from packages, TQI is impacted via Unreferenced rules.

Table/ViewuseSelect, useInsert, useUpdate, useDelete(tick)(minus)

When Table / View are accessed directly from packages, TQI is impacted via Unreferenced rules.

Synonym / Alias Like

Table/ViewrelyonLink(tick)(tick)
Procedure LikerelyonLink(tick)(tick)
PackagerelyonLink(minus)(plus)New feature
ScriptTable/ViewuseSelect, useInsert, useUpdate, useDelete(minus)(plus)New feature
Procedure LikecallLink(minus)(plus)New feature
Client Code

Table/View

useSelect, useInsert, useUpdate, useDelete(tick)(tick)We add use links when dependency is added between the client code and SQL code.
Procedure LikecallLink(tick)(tick)We add use links when dependency is added between the client code and SQL code.
TriggercallLink(minus)(plus)New feature : when table is INSERTED/ UPDATED / DELETED in a Client Code we add a call link between the Client Code and the Trigger.
Column LikeaccessRead, accessWrite(minus)(plus)New feature. We add access links when dependency is added between the client code and SQL code and com.castsoftware.datacolumnaccess is installed.
Oracle Forms and Reports Analyzer objectsPL/SQL Analyzer objects
(tick)(minus)Not supported

Rules

Here is the list of the equivalence between PL/SQL Analyzer and SQL Analyzer rules:

  • Some quality rules exists only on SQL Analyzer, with no equivalent quality rule on PL/SQL Analyzer.
  • Some quality rules which exists only on PL/SQL Analyzer, with no equivalent quality rule on SQL Analyzer.
  • Most of the rules have equivalents on both analyzers


Please find the detailed list,

Original PL/SQL AnalyzerCounterpart SQL AnalyzerImpactRemarks
(7902) Avoid SQL queries that no index can support
(7418) Avoid SQL queries using functions on indexed columns in the WHERE clause
(7428) Avoid SQL queries not using the first column of a composite index in the WHERE clause
(1101004) Avoid non-indexed SQL queries(tick)

(7904) Avoid SQL queries on XXL tables that no index can support 

(7658) Avoid SQL queries on XXL Tables using Functions on indexed Columns in the WHERE clause

(7642) Avoid SQL queries on XXL tables not using the first column of a composite index in the WHERE clause

(1101006) Avoid non-indexed XXL SQL queries(tick)
(7950) Avoid definition of synonym as PUBLIC in PL/SQL context(1101078) Always prefer PRIVATE to PUBLIC synonym(tick)
(7958) Avoid orphaned synonyms in PL/SQL context(1101080) Avoid orphaned synonyms(tick)
(7946) Avoid queries using old style join convention  instead of ANSI-Standard joins(1101014) Avoid queries using old style join convention instead of ANSI-Standard joins(tick)
(1576) Use varchar2 instead of char and varchar(1101086) Always use VARCHAR2 and NVARCHAR2 instead of CHAR, VARCHAR or NCHAR(tick)
(7806) Avoid Artifacts with Group By(1101018) Avoid using the GROUP BY clause(tick)
(7790) Avoid Cursors inside a loop(1101084) Avoid Cursors inside a loop (SQL)(tick)
(1558) Package naming convention - prefix control(1101046) Package naming convention - prefix control (SQL)(tick)
(1560) Package Function naming convention - prefix control (1101048) Package Function naming convention - prefix control (SQL)(tick)
(1562) Package Stored Procedure naming convention - prefix control(1101050) Package Stored Procedure naming convention - prefix control (SQL)(tick)
(1574) Use at most one statement per line(1101062) Avoid using multiple statements per line (SQL)(tick)
(1578) Avoid using LONG & LONG RAW datatype for Table Columns (1101088) Avoid using LONG and LONG RAW datatypes(tick)
(1580) Avoid using execute immediate(1101024) Avoid using dynamic SQL in SQL Artifacts(tick)
(1582) Avoid large Tables - too many columns(1101056) Avoid large Tables - too many columns (SQL)(tick)
(1588) Use WHEN OTHERS in exception management(1101052) Avoid using WHEN OTHERS without exception management (SQL)(tick)
(1608) Avoid cascading Triggers(1101064) Avoid cascading Triggers (SQL)(tick)
(7660) Never use SQL queries with a cartesian product on XXL Tables(1101002) Never use SQL queries with a cartesian product on XXL Tables (SQL)(tick)
(7820) Never use SQL queries with a cartesian product(1101000) Never use SQL queries with a cartesian product(tick)
(7770) Avoid Artifacts with too many parameters(1101016) Avoid Artifacts with too many parameters (SQL)(tick)
(7822) Avoid Artifacts with queries on more than 4 Tables (1101030) Avoid Artifacts with queries on too many Tables and/or Views(tick)
(7948) Do not mix Ansi joins syntax  with Oracle proprietary joins syntax in the same query(1101058) Avoid mixing ANSI and non-ANSI JOIN syntax in the same query(tick)
(7952) Avoid synonym with both private & PUBLIC Definition in PL/SQL context (1101068) Avoid synonym with both private and public definition(tick)
(7960) Avoid looping chain of synonyms in PL/SQL context (1101082) Avoid looping chain of synonyms(tick)
(8036) Avoid improperly written triangular joins with XXL tables in PL/SQL code (1101066) Avoid improperly written triangular joins with XXL tables(tick)
(8080) Avoid exists independent clauses(1101032) Avoid exists and not exists independent clauses (SQL)(tick)
(8082) Avoid Tables without Primary Key(1101022) Avoid Tables without Primary Key (SQL)(warning)TQI : the rule changed from CRITICAL to generic
(1596) Avoid using "nullable" Columns except in the last position in a Table
(warning)

TQI

Replaced by (1101008) Avoid non-SARGable queries.

(1564) Cursor naming convention - prefix control 
(warning)
TQI
(1598) Avoid Rule HINT /*+ rule */ or --+ rule in PL/SQL code 
(warning)

TQI

RBO is no more supported since Oracle 10g.

(7420) Avoid SQL queries with implicit conversions in the WHERE clause 
(warning)
TQI
(7662) Avoid SQL queries on XXL Tables with implicit conversions in the WHERE clause
(warning)
TQI
(7810) Avoid Artifacts with a Complex SELECT Clause(1101098) Avoid Artifacts with a Complex SELECT Clause (SQL)(tick)



(1101026) Always define column names when inserting values(plus)
New feature

(1101034) Avoid using DISTINCT in SQL SELECT statements(plus)New feature

(1101038) Avoid OR conditions testing equality on the same identifier in SQL WHERE clauses(plus)New feature

(1101040) Avoid empty catch blocks(plus)
New feature

(1101008) Avoid non-SARGable queries(plus)
New feature

(1101060) Avoid using LIKE conditions starting with a wildcard character(plus)New feature

(1101070) Avoid explicit comparison with NULL(plus)New feature

(1101072) Avoid not aliased Tables(plus)New feature

(1101076) Avoid unqualified column references(plus)
New feature

(1101074) Avoid Tables aliases ending with a numeric suffix(plus)
New feature

(1101054) Never use WHEN OTHER THEN NULL(plus)
New feature

(1101044) View naming convention - character set control (SQL)(plus)
New feature

(1101042) Table naming convention - character set control (SQL)(plus)
New feature

(1101036) Use ANSI standard operators in SQL WHERE clauses(plus)
New feature for the client code

(1101028) Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries(plus)
New feature

(1101020) Avoid using quoted identifiers(plus)
New feature

(1101012) Avoid specifying column numbers instead of column names in ORDER BY clauses(plus)
New feature

(1101010) Avoid NATURAL JOIN queries(plus)
New feature

(1101092) Avoid Procedures using an Insert, Update, Delete, Create Table or Select without including error management (SQL)(plus)
New feature

(1101096) Avoid SQL injection in dynamic SQL for Oracle(plus)
New feature

(1101102) Avoid using LIKE conditions without wildcards(plus)New feature

(1101104) Avoid XXL tables without primary key / unique key constraint / unique index(plus)New feature

(1101106) Avoid tables without primary key / unique key constraint / unique index(plus)New feature

(1101108) Avoid Cobol SQL Cursors without FOR READ ONLY or FOR FETCH ONLY or FOR UPDATE clauses(plus)New feature, only for SQL in Cobol

(1101112) Avoid LOCK TABLE statements in SQL code for COBOL Programs(plus)New feature, only for SQL in Cobol

DB2 UDB Analyzer and DB2 z/OS Analyzer - SQL Analyzer

The comparison has been done between DB2 UDB Analyzer 8.2.1 / DB2 zOS Analyzer 8.2.1 and SQL Analyzer 3.1.0-funcrel (AIP 8.3.8 + datacolumnaccess 1.0.0-beta2).

Objects

Here is the list of the equivalence between DB2 UDB Analyzer, DB2 z/OS Analyzer and SQL Analyzer objects:

  • Most of the objects have equivalents on both analyzers
  • Some objects exists only on SQL Analyzer, with no equivalent object on DB2 UDB Analyzer, DB2 z/OS Analyzer.
  • Some objects which exists only on DB2 UDB Analyzer, DB2 z/OS Analyzer, with no equivalent object on SQL Analyzer.

Object familyOriginal DB2 UDB Analyzer, DB2 z/OS AnalyzerCounterpart SQL AnalyzerImpactRemarks
Files
(1000007) File which contains source code(plus)New feature
Structure
(1000003) Universal Directory(tick)

No impact, just structure

(299) DB2 System

(1101005) SQL Analyzer Project(tick)No impact, just structure, merged types
(300) DB2 Instance

(1000001) Universal Project

(tick)No impact, just technical types
(141813) The result project of a plugin
(107001) DB2 Subset(1101002) SQL Analyzer Subset(info)No impact, just structure
(313) DB2 Database(1101061) Database(tick)
(301) Schema(1101011) Schema(tick)
Script
(1101010) SQL Script(plus)New feature
Table/View(1) SQL Table(1101006) Table(tick)
(9) SQL View(1101013) View(tick)

Column Like
(1101007) Table Column(plus)New feature
Constraint Like
(1101016) Foreign Key(plus)New feature
Index Like(6) SQL Index(1101008) Index(tick)

(1101020) Unique Constraint(plus)New feature
Procedure Like(3) SQL Procedure(1101009) Procedure(tick)
(10) SQL Function(1101012) Function(tick)
(4) SQL Trigger(1101014) Trigger(tick)
UDT Like(358) DB2 Distinct UDT
(warning)Impact on TQI, when UDT is linked to a DB2 object covered by an Unreferenced rule
(302) DB2 Structured UDT
(warning)Impact on TQI, when UDT is linked to a DB2 object covered by an Unreferenced rule
Type Like(16) TYPE
(info)No impact on function points or TQI
Synonym/Alias Like(303) DB2 Alias(1101040) Synonym(tick)No impact, merged types
(304) DB2 Nickname

Links

Here is the list of the equivalence between DB2 UDB Analyzer, DB2 z/OS Analyzer and SQL Analyzer links:

  • Most of the links have equivalents on both analyzers
  • Some links exists only on SQL Analyzer, with no equivalent links on DB2 UDB Analyzer, DB2 z/OS Analyzer.
  • Some links which exists only on DB2 UDB Analyzer, DB2 z/OS Analyzer, with no equivalent links on SQL Analyzer.

Please find the detailed list,

CallerCalleeLink typeOriginal DB2 UDB Analyzer, DB2 z/OS AnalyzerCounterpart SQL AnalyzerRemarks
Table/ViewTable/ViewuseSelect(tick)(tick)
referLink, referDelete, referUpdate

(tick)

(tick)
inheritLink(tick)(minus)

Impact on TQI related to Unreferenced rules

Procedure LikecallLink(tick)(tick)
Column LikeaccessRead, accessWrite(minus)(plus)New feature : we should install com.castsoftware.datacolumnaccess to have them.
TriggerFire: Before Insert/Update/Delete, After Insert/Update/Delete, For each row Insert/Update/Delete and For all row Insert/Update/Delete (warning)
Incorrect modelisation for function points
UDT LikerelyonLink(tick)(minus)Impact on TQI related to Unreferenced rules
Column LikeColumn LikereferLink(minus)(plus)New feature for the case of Columns referenced in a Foreigner Key
Procedure Like





Procedure LikecallLink(tick)

(tick)



TriggercallLink(minus)(plus)New feature : when table is INSERTED/ UPDATED / DELETED in a Procedure / Function we add a call link between the Procedure / Functions and the Trigger
Table/ViewuseSelect, useInsert, useUpdate, useDelete(tick)(tick)


monitorInsert, monitorUpdate, monitorDelete(minus)(plus)New feature : adding monitor link between the Trigger and the triggered Table/View

ddlLinkCreate, ddlLinkDrop, lockLink

(tick)(minus)Impact on TQI related to Unreferenced rules
UDT LikerelyonLink(tick)(minus)Impact on TQI related to Unreferenced rules
Column LikeaccessRead, accessWrite(minus)(tick)We should install com.castsoftware.datacolumnaccess to have them.
Cobol ProgramcallLink(minus)(plus)New feature, when the LANGUAGE parameter of the procedure is Cobol and external name is matched with an existing Cobol Program and dependency is added between client code and SQL code.
C/C++ FunctioncallLink(minus)(plus)New feature, when the LANGUAGE of the procedure is C and external name is matched with an existing C/C++ Function and dependency is added between client code and SQL code.
Java MethodcallLink(minus)(plus)New feature, when the LANGUAGE of the procedure is JAVA and external name is matched with an existing Java Method and dependency is added between client code and SQL code.
Constraint LikeTable/ViewrelyonLink(tick)(tick)
Column LikerelyonLink(minus)(plus)New feature
Index LikeTable/ViewrelyonLink(tick)(tick)
Column LikerelyonLink(minus)(plus)New feature
Synonym / Alias Like

Table/View

DB2 : prototypeLink

SQL : relyonLink

(tick)(tick)
Procedure LikerelyonLink(minus)(plus)New feature
UDT LikerelyonLink(tick)(info)No impact
UDT LikeUDT LikeinheritLink(tick)(info)No impact
ScriptTable/ViewuseSelect, useInsert, useUpdate, useDelete(minus)(plus)New feature
Procedure LikecallLink(minus)(plus)New feature
Client Code

Table/View

useSelect, useInsert, useUpdate, useDelete(tick)(tick)We add use links when dependency is added between the client code and SQL code.
Procedure LikecallLink(tick)(tick)We add call links when dependency is added between the client code and SQL code.
TriggercallLink(minus)(plus)New feature : when table is INSERTED/ UPDATED / DELETED in a Client Code we add a call link between the Client Code and the Trigger.
Column LikeaccessRead, accessWrite(minus)(plus)

New feature. We add access links when dependency is added between the client code and SQL code and com.castsoftware.datacolumnaccess is installed.

Rules

Here is the list of the equivalence between DB2 UDB Analyzer, DB2 z/OS Analyzer and SQL Analyzer rules:

  • Some quality rules exists only on SQL Analyzer, with no equivalent quality rule on DB2 UDB Analyzer, DB2 z/OS Analyzer.
  • Some quality rules which exists only on DB2 UDB Analyzer, DB2 z/OS Analyzer, with no equivalent quality rule on SQL Analyzer.
  • Most of the rules have equivalents on both analyzers

Original DB2 UDB Analyzer, DB2 z/OS AnalyzerCounterpart SQL AnalyzerImpactRemarks
(7902) Avoid SQL queries that no index can support
(7418) Avoid SQL queries using functions on indexed columns in the WHERE clause
(7428) Avoid SQL queries not using the first column of a composite index in the WHERE clause
(1101004) Avoid non-indexed SQL queries(tick)

Avoid Artifacts with too many parameters(7904) Avoid SQL queries on XXL tables that no index can support 

(7658) Avoid SQL queries on XXL Tables using Functions on indexed Columns in the WHERE clause

(7642) Avoid SQL queries on XXL tables not using the first column of a composite index in the WHERE clause

(1101006) Avoid non-indexed XXL SQL queries(tick)

(1101078) Always prefer PRIVATE to PUBLIC synonym(plus)New feature

(1101080) Avoid orphaned synonyms(plus)New feature

(1101014) Avoid queries using old style join convention instead of ANSI-Standard joins(plus)New feature
(7806) Avoid Artifacts with Group By(1101018) Avoid using the GROUP BY clause(tick)
(7420) Avoid SQL queries with implicit conversions in the WHERE clause 
(warning)TQI
(7662) Avoid SQL queries on XXL Tables with implicit conversions in the WHERE clause
(warning)TQI
(7810) Avoid Artifacts with a Complex SELECT Clause(1101098) Avoid Artifacts with a Complex SELECT Clause (SQL)(tick)



(1101084) Avoid Cursors inside a loop (SQL)(plus)New feature

(1101062) Avoid using multiple statements per line (SQL)(plus)New feature

(1101024) Avoid using dynamic SQL in SQL Artifacts(plus)New feature

(1101056) Avoid large Tables - too many columns (SQL)(plus)New feature

(1101064) Avoid cascading Triggers (SQL)(plus)New feature
(7660) Never use SQL queries with a cartesian product on XXL Tables(1101002) Never use SQL queries with a cartesian product on XXL Tables (SQL)(tick)
(7820) Never use SQL queries with a cartesian product(1101000) Never use SQL queries with a cartesian product(tick)
(7770) Avoid Artifacts with too many parameters(1101016) Avoid Artifacts with too many parameters (SQL)(tick)
(7822) Avoid Artifacts with queries on more than 4 Tables(1101030) Avoid Artifacts with queries on too many Tables and/or Views(tick)New feature : the number of accessed Tables / Views could be changed, default value is 4.

(1101058) Avoid mixing ANSI and non-ANSI JOIN syntax in the same query


(1101068) Avoid synonym with both private and public definition(plus)New feature

(1101082) Avoid looping chain of synonyms(plus)New feature

(1101066) Avoid improperly written triangular joins with XXL tables(plus)New feature

(1101032) Avoid exists and not exists independent clauses (SQL)(plus)New feature

(1101022) Avoid Tables without Primary Key (SQL)(plus)New feature

(1101026) Always define column names when inserting values(plus)New feature

(1101034) Avoid using DISTINCT in SQL SELECT statements(plus)New feature

(1101038) Avoid OR conditions testing equality on the same identifier in SQL WHERE clauses(plus)New feature

(1101040) Avoid empty catch blocks(plus)New feature

(1101008) Avoid non-SARGable queries(plus)New feature

(1101060) Avoid using LIKE conditions starting with a wildcard character(plus)New feature

(1101070) Avoid explicit comparison with NULL(plus)New feature

(1101072) Avoid not aliased Tables(plus)New feature

(1101076) Avoid unqualified column references(plus)New feature

(1101074) Avoid Tables aliases ending with a numeric suffix(plus)New feature
(6588) View naming convention - character set control(1101044) View naming convention - character set control (SQL)(tick)
(6590) Table naming convention - character set control(1101042) Table naming convention - character set control (SQL)(tick)

(1101036) Use ANSI standard operators in SQL WHERE clauses(plus)New feature for the client code

(1101028) Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries(plus)New feature

(1101020) Avoid using quoted identifiers(plus)New feature

(1101012) Avoid specifying column numbers instead of column names in ORDER BY clauses(plus)New feature

(1101010) Avoid NATURAL JOIN queries(plus)New feature

(1101090) Avoid Tables without a clustered Index (SQL)(plus)New feature

(1101102) Avoid using LIKE conditions without wildcards(plus)New feature

(1101104) Avoid XXL tables without primary key / unique key constraint / unique index(plus)New feature

(1101106) Avoid tables without primary key / unique key constraint / unique index(plus)New feature

(1101108) Avoid Cobol SQL Cursors without FOR READ ONLY or FOR FETCH ONLY or FOR UPDATE clauses(plus)New feature, only for SQL in Cobol

(1101112) Avoid LOCK TABLE statements in SQL code for COBOL Programs(plus)New feature, only for SQL in Cobol

T-SQL Analyzer - SQL Analyzer

The comparison has been done between T-SQL Analyzer 8.3.8 and SQL Analyzer 3.2.0-funcrel (AIP 8.3.8 + datacolumnaccess 1.0.0-beta2).

Objects

Here is the list of the equivalence between T-SQL Analyzer and SQL Analyzer objects:

  • Most of the objects have equivalents on both analyzers
  • Some objects exists only on SQL Analyzer, with no equivalent object on T-SQL Analyzer.
  • Some objects which exists only on T-SQL Analyzer, with no equivalent object on SQL Analyzer.


Please find the detailed list:

Object familyOriginal T-SQL AnalyzerCounterpart SQL AnalyzerImpactRemarks
Files(139266) source file (subject to parsing)(1000007) File which contains source code(tick)
Structure





(1000003) Universal Directory(tick)No impact, just structure

(138003) Sybase Project

(1101005) SQL Analyzer Project(tick)No impact, just structure, merged types
(138069) Microsoft Project

(1000001) Universal Project(tick)No impact, just technical types
(141813) The result project of a plugin

(141014) Sybase Database Subset

(1101002) SQL Analyzer Subset(tick)No impact, just structure
(141003) Microsoft Database Subset
(140982) Sybase instance
(info)

No impact, just structure

(140952) Microsoft instance

(140981) Sybase database


(1101061) Database(tick)No impact, merged types
(140949) Microsoft database
(140980) Sybase schema(1101011) Schema(tick)No impact, merged types
(140950) Microsoft schema
(141783) Sybase unknown instance
(info)

No impact, just structure

(141784) Microsoft unknown instance
(141751) Sybase unknown database
(info)No impact, just structure
(141752) Microsoft unknown database
(140979) Sybase unknown schema
(info)No impact, just structure
(140951) Microsoft unknown schema
Script(1101010) SQL Script(plus)New feature
Table/View


(140978) Sybase table


(1101006) Table

(tick)No impact, merged types
(138871) Microsoft table
(140976) Sybase view(1101013) View(tick)No impact, merged types

(138873) Microsoft view

Column Like

(140977) Sybase table column

(1101007) Table Column(tick)No impact, merged types
(138872) Microsoft table column
(140975) Sybase view column
(139224) Microsoft view column
Constraint Like(141498) Sybase foreign key table constraint(1101016) Foreign Key(tick)No impact, merged types
(141495) Microsoft foreign key table constraint
Index Like(140971) Sybase index(1101008) Index

(tick)

No impact, merged types

(138877) Microsoft index
(141497) Sybase primary key table constraint(1101020) Unique Constraint(tick)No impact, merged types
(141494) Microsoft primary key table constraint
Procedure Like
(1101012) Function(plus)New feature
(138876) Microsoft function(1101012) Function(tick)
(140973) Sybase procedure(1101009) Procedure(tick)No impact, merged types
(138875) Microsoft procedure
(140967) Sybase DML trigger(1101014) Trigger(tick)No impact, merged types

(138891) Microsoft DML trigger

Synonym/Alias Like
(1101040) Synonym(plus)


New feature

Links

Here is the list of the equivalence between T-SQL Analyzer and SQL Analyzer links:

  • Most of the links have equivalents on both analyzers
  • Some links exists only on SQL Analyzer, with no equivalent links on T-SQL Analyzer.
  • Some links which exists only on T-SQL Analyzer, with no equivalent links on SQL Analyzer.


Please find the detailed list,

CallerCalleeLink typeOriginal T-SQL AnalyzerCounterpart SQL AnalyzerRemarks
Table/ViewTable/View

useSelect(tick)(tick)The case of Table / View selected in a View
referLink(tick)(tick)
referDelete, referUpdate(minus)(plus)New feature
Procedure LikecallLink(tick)(tick)
Column LikeaccessRead, accessWrite(tick)(tick)We should install com.castsoftware.datacolumnaccess to have them.
TriggerFire: Before Insert/Update/Delete, After Insert/Update/Delete, For each row Insert/Update/Delete and For all row Insert/Update/Delete (warning)
Incorrect modelisation for function points
Column LikeColumn LikereferLink(tick)(tick)The case of Columns referenced in a Foreigner Key
Procedure Like


Procedure Like


callLink(tick)

(tick)



TriggercallLink(minus)(plus)New feature : when table is INSERTED/ UPDATED / DELETED in a Procedure / Function we add a call link between the Procedure / Functions and the Trigger.
Table/ViewuseSelect, useInsert, useUpdate, useDelete(tick)(tick)

Between Triggers and Tables

monitorInsert, monitorUpdate, monitorDelete(minus)(plus)New feature : adding monitor link between the Trigger and the triggered Table/View
Column LikeaccessRead, accessWrite(tick)(tick)We should install com.castsoftware.datacolumnaccess to have them.
Constraint LikeTable/ViewrelyonLink(tick)(tick)
Column LikerelyonLink(tick)(tick)
Index LikeTable/ViewrelyonLink(tick)(tick)
Column LikerelyonLink(tick)(tick)
Synonym / Alias Like

Table/ViewrelyonLink(minus)(plus)New feature

Procedure LikerelyonLink(minus)(plus)
ScriptTable/ViewuseSelect, useInsert, useUpdate, useDelete(minus)(plus)New feature
Procedure LikecallLink(minus)(plus)New feature
Client Code

Table/View

useSelect, useInsert, useUpdate, useDelete(tick)(tick)We add use links when dependency is added between the client code and SQL code.
Procedure LikecallLink(tick)(tick)We add use links when dependency is added between the client code and SQL code.
TriggercallLink(minus)(plus)New feature : when table is INSERTED/ UPDATED / DELETED in a Client Code we add a call link between the Client Code and the Trigger.
Column LikeaccessRead, accessWrite(minus)(plus)New feature. We add access links when dependency is added between the client code and SQL code and com.castsoftware.datacolumnaccess is installed.

Rules

Here is the list of the equivalence between T-SQL Analyzer and SQL Analyzer rules:

  • Some quality rules exists only on SQL Analyzer, with no equivalent quality rule on T-SQL Analyzer.
  • Some quality rules which exists only on T-SQL Analyzer, with no equivalent quality rule on SQL Analyzer.
  • Most of the rules have equivalents on both analyzers


Please find the detailed list,

Original T-SQL AnalyzerCounterpart SQL AnalyzerImpactRemarks
(7902) Avoid SQL queries that no index can support
(7418) Avoid SQL queries using functions on indexed columns in the WHERE clause
(7428) Avoid SQL queries not using the first column of a composite index in the WHERE clause
(1101004) Avoid non-indexed SQL queries(tick)

(7904) Avoid SQL queries on XXL tables that no index can support 

(7658) Avoid SQL queries on XXL Tables using Functions on indexed Columns in the WHERE clause

(7642) Avoid SQL queries on XXL tables not using the first column of a composite index in the WHERE clause

(1101006) Avoid non-indexed XXL SQL queries(tick)
(7946) Avoid queries using old style join convention  instead of ANSI-Standard joins(1101014) Avoid queries using old style join convention instead of ANSI-Standard joins(tick)
(7806) Avoid Artifacts with Group By(1101018) Avoid using the GROUP BY clause(tick)
(7790) Avoid Cursors inside a loop(1101084) Avoid Cursors inside a loop (SQL)(tick)
(1574) Use at most one statement per line(1101062) Avoid using multiple statements per line (SQL)(tick)
(1582) Avoid large Tables - too many columns(1101056) Avoid large Tables - too many columns (SQL)(tick)
(7660) Never use SQL queries with a cartesian product on XXL Tables(1101002) Never use SQL queries with a cartesian product on XXL Tables (SQL)(tick)
(7820) Never use SQL queries with a cartesian product(1101000) Never use SQL queries with a cartesian product(tick)
(7770) Avoid Artifacts with too many parameters(1101016) Avoid Artifacts with too many parameters (SQL)(tick)
(7822) Avoid Artifacts with queries on more than 4 Tables (1101030) Avoid Artifacts with queries on too many Tables and/or Views(tick)
(8080) Avoid exists independent clauses(1101032) Avoid exists and not exists independent clauses (SQL)(tick)
(8082) Avoid Tables without Primary Key(1101022) Avoid Tables without Primary Key (SQL)(warning)TQI : the rule changed from CRITICAL to generic
(7420) Avoid SQL queries with implicit conversions in the WHERE clause 
(warning)TQI
(7662) Avoid SQL queries on XXL Tables with implicit conversions in the WHERE clause
(warning)TQI
(4062) Avoid Functions and Procedures doing an Insert, Update or Delete without managing a transaction(1101100) Avoid Functions and Procedures doing an Insert, Update or Delete without managing a transaction (SQL)(tick)
(4064) Avoid Procedures using an Insert, Update, Delete, Create Table or Select without including error management(1101092) Avoid Procedures using an Insert, Update, Delete, Create Table or Select without including error management (SQL)(tick)
(4066) Avoid Stored Procedures not returning a status value(1101094) Avoid Stored Procedures not returning a status value (SQL)(tick)
(4070) Avoid use of "truncate table"
(warning)TQI
(4076) Avoid using temporary Objects
(warning)TQI
(4084) Avoid nested Stored Procedures using temporary Tables
(warning)TQI
(7386) Avoid Tables without a clustered Index(1101090) Avoid Tables without a clustered Index (SQL)(tick)
(7810) Avoid Artifacts with a Complex SELECT Clause(1101098) Avoid Artifacts with a Complex SELECT Clause (SQL)(tick)



(1101026) Always define column names when inserting values(plus)New feature

(1101034) Avoid using DISTINCT in SQL SELECT statements(plus)New feature

(1101038) Avoid OR conditions testing equality on the same identifier in SQL WHERE clauses(plus)New feature

(1101066) Avoid improperly written triangular joins with XXL tables(plus)New feature

(1101082) Avoid looping chain of synonyms(plus)New feature

(1101080) Avoid orphaned synonyms(plus)New feature

(1101058) Avoid mixing ANSI and non-ANSI JOIN syntax in the same query(plus)New feature

(1101064) Avoid cascading Triggers (SQL)(plus)New feature

(1101024) Avoid using dynamic SQL in SQL Artifacts(plus)New feature

(1101040) Avoid empty catch blocks(plus)New feature

(1101008) Avoid non-SARGable queries(plus)New feature

(1101060) Avoid using LIKE conditions starting with a wildcard character(plus)New feature

(1101070) Avoid explicit comparison with NULL(plus)New feature

(1101072) Avoid not aliased Tables(plus)New feature

(1101076) Avoid unqualified column references(plus)New feature

(1101074) Avoid Tables aliases ending with a numeric suffix(plus)New feature

(1101054) Never use WHEN OTHER THEN NULL(plus)New feature

(1101044) View naming convention - character set control (SQL)(plus)New feature

(1101042) Table naming convention - character set control (SQL)(plus)New feature

(1101036) Use ANSI standard operators in SQL WHERE clauses(plus)New feature for the client code

(1101028) Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries(plus)New feature

(1101020) Avoid using quoted identifiers(plus)New feature

(1101012) Avoid specifying column numbers instead of column names in ORDER BY clauses(plus)New feature

(1101010) Avoid NATURAL JOIN queries(plus)New feature

(1101102) Avoid using LIKE conditions without wildcards(plus)New feature

(1101104) Avoid XXL tables without primary key / unique key constraint / unique index(plus)New feature

(1101106) Avoid XXL tables without primary key / unique key constraint / unique index(plus)New feature

(1101108) Avoid Cobol SQL Cursors without FOR READ ONLY or FOR FETCH ONLY or FOR UPDATE clauses(plus)New feature, only for SQL in Cobol

(1101112) Avoid LOCK TABLE statements in SQL code for COBOL Programs(plus)New feature, only for SQL in Cobol