Click or drag to resize

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.

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.

Categories

New Rules, Deprecated Features

Additional Information
Example Test Script
SQL
 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

 MessageLineColumn
1SA0214 : The WITH clause syntax without parentheses around the options is deprecated.50
2SA0214 : The WITH clause syntax without parentheses around the options is deprecated.923
3SA0214 : The WITH clause syntax without parentheses around the options is deprecated.200
See Also

Other Resources