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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE TABLE FirstTable (ID INT, col1 VARCHAR(10), col2 VARCHAR(10) ); CREATE TABLE SecondTable (ID INT, col1 VARCHAR(10), col2 VARCHAR(10) ); CREATE TABLE ThirdTable (ID INT, col1 VARCHAR(10), col2 VARCHAR(10) ); CREATE TABLE FourthTable (ID INT, col1 VARCHAR(10), col2 VARCHAR(10) ); CREATE INDEX IX_FirstTable_Col1 ON FirstTable(col1); CREATE CLUSTERED INDEX IX_FirstTable_Col2 ON FirstTable(col2); CREATE UNIQUE INDEX IX_SecondTable_Col1_Col2 ON SecondTable(col1 DESC, col2 ASC); CREATE NONCLUSTERED INDEX IX_ThirdTable_Col1 ON ThirdTable(col1); CREATE CLUSTERED INDEX IX_FourthTable_Col2 ON FourthTable(col2); |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE FirstFailedDecNumTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] DECIMAL ); CREATE TABLE SecondFailedDecNumTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] NUMERIC ); CREATE TABLE ThirdFailedDecNumTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA1] NUMERIC, [DATA2] DECIMAL ); CREATE TABLE FirstGoodDecNumTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] DECIMAL(5, 2) ); CREATE TABLE SecondGoodDecNumTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] NUMERIC(10, 5) ); |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE FirstFailedVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] VARCHAR ); CREATE TABLE SecondFailedVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] NVARCHAR ); CREATE TABLE ThirdFailedVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA1] VARCHAR, [DATA2] NVARCHAR ); CREATE TABLE FirstGoodVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] VARCHAR(20) ); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE FirstFailedIdentityTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] DECIMAL ); CREATE TABLE SecondFailedIdentityTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] NUMERIC ); CREATE TABLE ThirdFailedIdentityTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA1] NUMERIC, [DATA2] DECIMAL ); CREATE TABLE FirstGoodIdentityTable ([ID] INT NOT NULL, [DATA] DECIMAL(5, 2) ); CREATE TABLE SecondGoodIdentityTable ([ID] INT NOT NULL, [DATA] NUMERIC(10, 5) ); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE [dbo].[FirstFailedNULLTable] ([ID] INT NOT NULL IDENTITY(0, 1), [someData] VARCHAR(30) ); CREATE TABLE [dbo].[ SecondFailedNULLTable] ([ID] INT NOT NULL IDENTITY(0, 1), [someData] VARCHAR(30), [otherData] VARCHAR(30) ); CREATE TABLE [dbo].[ FirstGoodNULLTable] ([ID] INT NOT NULL IDENTITY(0, 1), [someData] VARCHAR(30) NOT NULL, [otherData] VARCHAR(30) NOT NULL, ); |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE FirstFailedVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] VARCHAR(2) ); CREATE TABLE SecondFailedVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] NVARCHAR(1) ); CREATE TABLE ThirdFailedVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA1] VARCHAR(1), [DATA2] NVARCHAR(2) ); CREATE TABLE FirstGoodVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] VARCHAR(20) ); CREATE TABLE SecondGoodVARTable ([ID] INT NOT NULL IDENTITY(0, 1), [DATA] NVARCHAR(50) ); |
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
Example script:
|
1 2 3 4 5 6 |
DECLARE @FirstFailedVAR VARCHAR(1); DECLARE @SecondFailedVAR VARCHAR(2); DECLARE @ThirdFailedVAR NVARCHAR(1); DECLARE @FourthFailedVAR NVARCHAR(2); DECLARE @FirstGoodVAR VARCHAR(10); DECLARE @SecondGoodVAR NVARCHAR(10); |
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
Example script:
|
1 2 3 4 5 |
CREATE TABLE FirstTable (ID int, Name varchar(10)); CREATE TABLE SecondTable (ID int, Name varchar(10)); CREATE TABLE ThirdTable (ID int, Name varchar(10)); CREATE TABLE FourthTable (ID int, Name varchar(10), CONSTRAINT pk1 PRIMARY KEY CLUSTERED (ID)); CREATE TABLE FifthTable (ID int, Name varchar(10), CONSTRAINT pk2 PRIMARY KEY CLUSTERED (ID)); |
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:
|
1 2 3 4 5 6 |
ALTER TABLE SixthTable ALTER COLUMN Col2 varchar(50); ALTER TABLE FirstTable NOCHECK CONSTRAINT ALL; ALTER TABLE SecondTable NOCHECK CONSTRAINT ALL; ALTER TABLE ThirdTable NOCHECK CONSTRAINT ALL; ALTER TABLE FourthTable ALTER COLUMN Col2 varchar(50); ALTER TABLE FifthTable ALTER COLUMN Col2 varchar(50); |
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:
|
1 2 3 4 5 6 |
ALTER TABLE FirstTable NOCHECK CONSTRAINT ALL; ALTER TABLE SecondTable NOCHECK CONSTRAINT ALL; ALTER TABLE ThirdTable NOCHECK CONSTRAINT ALL; ALTER TABLE FourthTable ADD CONSTRAINT testCascade FOREIGN KEY (ID) REFERENCES SixthTable(ID) ON DELETE CASCADE; ALTER TABLE FifthTable ADD CONSTRAINT testCascade FOREIGN KEY (ID) REFERENCES SeventhTable(ID) ON DELETE CASCADE; |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE TABLE FirstTable (ID INT IDENTITY(1, 1) PRIMARY KEY, Col1 VARCHAR(100) NOT NULL, Col2 VARCHAR(255) ); CREATE TABLE SecondTable (ID INT IDENTITY(1, 1) PRIMARY KEY, Col1 VARCHAR(100) NOT NULL, Col2 VARCHAR(255) ); CREATE TABLE ThirdTable (ID INT IDENTITY(1, 1), Col1 VARCHAR(100) NOT NULL, Col2 VARCHAR(255) ); CREATE TABLE FourthTable (ID INT NOT NULL PRIMARY KEY, Col1 VARCHAR(100) NOT NULL, Col2 VARCHAR(255) ); CREATE TABLE FifthTable (ID INT NOT NULL, Col1 VARCHAR(100) NOT NULL, Col2 VARCHAR(255), PRIMARY KEY(ID) ); |
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
Example script:
|
1 2 3 4 |
CREATE TABLE FirstTable ( ID int IDENTITY(1,1) PRIMARY KEY, Col1 varchar(100) NOT NULL, Col2 varchar(255) ); CREATE TABLE SecondTable ( ID int IDENTITY(1,1) PRIMARY KEY, Col1 varchar(100) NOT NULL, Col2 varchar(255) ); CREATE TABLE ThirdTable ( ID varchar(10) PRIMARY KEY, Col1 varchar(100) NOT NULL, Col2 varchar(255) ); CREATE TABLE FourthTable ( ID varchar(10) PRIMARY KEY, Col1 varchar(100) NOT NULL, Col2 varchar(255) ); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE TABLE FirstBaseTable (ID INT PRIMARY KEY, Val VARCHAR(50) ); CREATE TABLE SecondBaseTable (ID INT PRIMARY KEY, Val VARCHAR(50), ExtID INT, ); CREATE TABLE FirstRefTable (ID INT PRIMARY KEY, Val VARCHAR(50), ExtID INT, ); CREATE TABLE SecondRefTable (ID INT PRIMARY KEY, Val VARCHAR(50), ExtID INT, ); CREATE TABLE ThirdRefTable (ID INT PRIMARY KEY, Val VARCHAR(50), ExtID INT, ); ALTER TABLE FirstRefTable ADD CONSTRAINT constrOne FOREIGN KEY(ExtID) REFERENCES FirstBaseTable(ID) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE SecondRefTable ADD CONSTRAINT constrTwo FOREIGN KEY(ExtID) REFERENCES FirstBaseTable(ID) ON DELETE SET NULL ; ALTER TABLE ThirdRefTable ADD CONSTRAINT constrThree FOREIGN KEY(ExtID) REFERENCES FirstBaseTable(ID) ON DELETE SET NULL; |
Message:
Foreign key has disabled cascading updates
- Best author award in 2021 - January 3, 2022
- Best author award in 2020 - January 5, 2021
- Best author award in 2019 - January 3, 2020