SA0214 : The CREATE TABLE, ALTER TABLE, or CREATE INDEX syntax without parentheses around the options is deprecated

The topic describes the SA0214 analysis rule.

Message

The CREATE TABLE, ALTER TABLE, or CREATE INDEX syntax without parentheses around the options is deprecated

Description

The rule checks CREATE TABLE, ALTER TABLE, or CREATE INDEX statements for use of deprecated syntax without parentheses around WITH index options.

How to fix

Rewrite the statement to use the current WITH (<index_options>) syntax.

Example:

CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)

WITH ( DROP_EXISTING = ON, PAD_INDEX = ON);

CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)

WITH DROP_EXISTING = ON, PAD_INDEX = ON

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)  
 2WITH ( DROP_EXISTING = ON );  
 3
 4CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)  
 5WITH FILLFACTOR = 75 ,STATISTICS_NORECOMPUTE ,PAD_INDEX, DROP_EXISTING
 6
 7CREATE TABLE dbo.Employee (
 8        EmployeeID int  
 9        PRIMARY KEY CLUSTERED WITH FILLFACTOR = 75,
10        FirstName nvarchar(50) NOT NULL,
11        LastName nvarchar(50) NOT NULL
12);  
13
14ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK   
15ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  
16WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON); 
17
18ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK   
19ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  
20WITH  FILLFACTOR = 75
Example Test SQL with Automatic Fix
SQL
Copy
 1CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)  
 2WITH ( DROP_EXISTING = ON );  
 3
 4CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)  
 5WITH (FILLFACTOR = 75 ,STATISTICS_NORECOMPUTE ,PAD_INDEX, DROP_EXISTING)
 6
 7CREATE TABLE dbo.Employee (
 8        EmployeeID int  
 9        PRIMARY KEY CLUSTERED WITH (FILLFACTOR = 75),
10        FirstName nvarchar(50) NOT NULL,
11        LastName nvarchar(50) NOT NULL
12);  
13
14ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK   
15ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  
16WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON); 
17
18ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK   
19ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)  
20WITH  (FILLFACTOR = 75)
Analysis Results
  Message Line Column
1 SA0214 : The WITH clause syntax without parentheses around the options is deprecated. 5 0
2 SA0214 : The WITH clause syntax without parentheses around the options is deprecated. 9 23
3 SA0214 : The WITH clause syntax without parentheses around the options is deprecated. 20 0
See Also

Other Resources

Analysis Rules