Design

APX1129 – Index type not specified explicitly

Description:

This rule evaluates the T-SQL script for NOT specifying the Index types, CLUSTERED or NONCLUSTERED explicitly.

If not specified, the CREATE INDEX statement will be used to create non-clustered index.

It is recommended to specify the type of the index as Clustered or Non-clustered in the CREATE INDEX statement.

For more information visit

https://www.sqlshack.com/sql-server-indexes-series-intro/

Example script:

Message:

Index type, CLUSTERED or NONCLUSTERED, not specified

APX1165 – Unspecific DECIMAL or NUMERIC data types

Description:

This rule evaluates the T-SQL script for not specifying the precision and scale of DECIMAL or NUMERIC data types explicitly.

Consider specifying the precision and scale of DECIMAL or NUMERIC data types explicitly with values that fit your requirements.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017

Example script:

Message:

Column [DATA] has a NUMERIC or DECIMAL data type but is missing Precision and/or Scale

APX1171 – (N)VARCHAR data type size not specified

Description:

This rule evaluates the T-SQL script for not specifying the length of VARCHAR or NVARCHAR data types explicitly.

Consider specifying the length of VARCHAR or NVARCHAR data types explicitly instead of the need to alter it later.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017

Example script:

Message:

Column [DATA] has no length specified

APX1195 – Identity Column usage

Description:

This rule evaluates the T-SQL script for using Identity columns unless truly necessary, as overuse can be a problem.

Preferences and circumstances vary so you may explicitly ignore results for some objects, as necessary, or unselect this rule entirely.

Consider using identity columns when it is heavily required.

For more information visit

https://www.sqlshack.com/difference-between-identity-sequence-in-sql-server/

Example script:

Message:

Table has an identity column

APX1213 – Nullability not specified

Description:

This rule evaluates the T-SQL script for NOT setting explicitly the NULL or NOT NULL column property in the column definition.

It is considered a best practice to explicitly specify nullability of columns especially as the setting can be changed with ‘ANSI_NULL_DFLT_ON’

For more information visit

https://www.sqlshack.com/commonly-used-sql-server-constraints-not-null-unique-primary-key/

Example script:

Message:

The table, ‘*’FailedNULLTable, has column [‘*’Data] without nullability specified

APX1222 – Small (N)VARCHAR columns

Description:

This rule evaluates the T-SQL script for using very small columns (nvarchar, varchar) sizes, in favor of fixed length (nchar, char) fields.

Varchar takes a variable space, which means it will use only the number of bytes equal to the number of characters.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017

Example script:

Message:

Column, [DATA], in table, ‘*’FailedVARTable, (n)varchar columns that are too small. Consider (n)char instead

APX1223 – Small (N)VARCHAR variables

Description:

This rule evaluates the T-SQL script for using very small variable (nvarchar, varchar) sizes, in favor of fixed length (nchar, char) fields.

Varchar takes a variable space, which means it will use only the number of bytes equal to the number of characters.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017

Example script:

Message:

Variable, @’*’FailedVar has (n)varchar variables that are too small. Consider (n)char instead

APX1229 – Missing primary key

Description:

This rule evaluates the T-SQL script for NOT having a primary key in your tables.

Primary Key constraint helps enforcing the uniqueness of the inserted data and prevent inserting NULL values.

Consider defining a Primary Key constraint on each table.

For more information visit

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-2017

Example script:

Message:

Table, ‘*’Table, is missing a primary key

APX1303 – Disabled foreign key

Description:

This rule evaluates the T-SQL script for having a disabled foreign key in your tables.

Disable Foreign Key Constraints if you know that new data will not violate the existing constraint or if the constraint applies only to the data already in the database.

For more information visit

https://www.sqlshack.com/commonly-used-sql-server-constraints-not-null-unique-primary-key/

and

https://www.sqlshack.com/commonly-used-sql-server-constraints-foreign-key-check-default/

Example script:

Message:

Table [‘*’Table] has disabled foreign key

APX1304 – ON DELETE CASCADE action Usage

Description:

This rule evaluates the T-SQL script for using cascade deletes.

Implementing its functionality through other means (e.g. front-end application, triggers, stored procedures) gives you more control on what is deleted.

For more information visit

https://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

Example script:

Message:

ON DELETE CASCADE action is used in the foreign key

APX1305 – Identity column should not be used in the Primary Key

Description:

This rule evaluates the T-SQL script for using identity columns in the primary key, due to the overhead of managing these columns during replication.

Consider not using IDENTITY columns as Primary Key especially when the database replication is configured.

For more information visit

https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-identity-columns?view=sql-server-2017

Example script:

Message:

Identity column is used in the primary key

APX1307 – Use Integer data types for primary key when possible

Description:

This rule evaluates the T-SQL script for NOT using integer (number) data types for the primary key, followed by fixed-length character data types.

SQL Server processes number data type values faster than character data type values because it converts characters to ASCII equivalent values before processing, which is an extra step.

For more information visit

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-2017

Example script:

Message:

Primary key is not as integer or biginteger value

APX1309 – Enable cascading updates

Description:

This rule evaluates the T-SQL script for missing cascading updates, as it lets you cascade modifications in a table to related rows in another table.

Enforcing referential integrity using cascading updates saves you from writing a significant amount of code.

For more information visit

http://technet.microsoft.com/en-us/library/ms186973.aspx

Example script:

Message:

Foreign key has disabled cascading updates

SQLShack

SQLShack

SQL Shack was created as a community service from ApexSQL, with the goal to share SQL Server knowledge through articles written by SQL Server professionals and community members
SQLShack

Latest posts by SQLShack (see all)