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

Introduction

Using deprecated syntax in SQL Server to specify index options without parentheses can lead to compatibility issues and errors, especially in future versions of SQL Server.

Description

In SQL Server, certain syntax practices have become outdated and are discouraged in favor of using newer, more robust methods. One such issue occurs when index options in CREATE TABLE , ALTER TABLE , or CREATE INDEX statements are specified without enclosing them in parentheses.

For example:

-- Example of problematic syntax
CREATE INDEX IX_IndexName ON TableName(ColumnName)
WITH FILLFACTOR = 90;

This syntax is problematic because it does not use parentheses around the index options, which SQL Server has marked as deprecated. Using deprecated syntax can lead to:

Potential future incompatibility with new SQL Server versions as deprecated features may be removed in upcoming releases.
Difficulty in maintaining the code since relying on older practices can hinder adopting new performance improvements and features.

How to fix

To fix the deprecated syntax issue in SQL Server with index options, use the current WITH () syntax format.

Follow these steps to address the issue:

1.Identify the SQL statements that are using deprecated syntax for index options. Deprecated syntax occurs when index options are specified without parentheses in CREATE INDEX , CREATE TABLE , or ALTER TABLE statements.

2.Rewrite the SQL statement to include parentheses around the index options to ensure forward compatibility with future SQL Server releases. This means enclosing the options in a WITH () clause.

3.Verify the changes by running the updated SQL statement to ensure it executes as expected and adheres to best practices.

For example:

-- Example of corrected query with parenthesis around index options
CREATE INDEX IX_IndexName ON TableName(ColumnName)
WITH (FILLFACTOR = 90);

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.

Additional Information

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)
WITH ( DROP_EXISTING = ON );

CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)
WITH FILLFACTOR = 75 ,STATISTICS_NORECOMPUTE ,PAD_INDEX, DROP_EXISTING

CREATE TABLE dbo.Employee (
  EmployeeID int
  PRIMARY KEY CLUSTERED WITH FILLFACTOR = 75,
  FirstName nvarchar(50) NOT NULL,
  LastName nvarchar(50) NOT NULL
);

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH  FILLFACTOR = 75

Example Test SQL with Automatic Fix

CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)
WITH ( DROP_EXISTING = ON );

CREATE INDEX IX_FF ON dbo.FactFinance ( FinanceKey, DateKey, OrganizationKey DESC)
WITH (FILLFACTOR = 75 ,STATISTICS_NORECOMPUTE ,PAD_INDEX, DROP_EXISTING)

CREATE TABLE dbo.Employee (
  EmployeeID int
  PRIMARY KEY CLUSTERED WITH (FILLFACTOR = 75),
  FirstName nvarchar(50) NOT NULL,
  LastName nvarchar(50) NOT NULL
);

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH  (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

Analysis Rules