SA0196 : Deprecated use of DROP INDEX with two-part index name syntax

Using outdated syntax in DROP INDEX statements for removing indexes can cause compatibility issues and hinder proper execution in SQL Server.

Description

Using old syntax patterns in T-SQL code can lead to confusion or operational issues in SQL Server environments. It is crucial to update legacy code to leverage more recent, reliable script structures.

For example:

SQL
1-- Example of problematic query using outdated syntax
2DROP INDEX IndexName ON TableName;

The above example uses a two-part index name, which is considered backward-compatible syntax. This may result in ambiguity or misinterpretation in modern SQL Server settings, where explicitly defined index and table relationships should be used.

  • The use of deprecated syntax can complicate script maintenance and readability.

  • Future updates or migrations to new SQL Server versions may render such scripts ineffective, requiring costly rewrites.

How to fix

Replace the deprecated backward-compatible syntax in `DROP INDEX` statements with the more modern and explicit syntax.

Follow these steps to address the issue:

  1. Identify all `DROP INDEX` statements in your T-SQL scripts that use the outdated two-part index name syntax.

  2. Modify the queries to adopt the modern syntax by specifying `index_name ON table_name` directly within the `DROP INDEX` statement.

  3. Test the updated queries in a development environment to ensure that they execute correctly and deliver the expected outcomes.

For example:

SQL
1-- Example of corrected query
2DROP INDEX IndexName ON TableName;

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
5 minutes per issue.
Categories

Design Rules, Deprecated Features, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
1DROP INDEX Test.Greeting.IX_Greeting_GreetingId  
2
3DROP INDEX IX_Greeting_GreetingId ON Test.Greeting,  IX_Greeting_GreetingId ON Test.Greeting

Example Test SQL with Automatic Fix
SQL
1DROP INDEX IX_Greeting_GreetingId ON [Test].[Greeting];  
2
3DROP INDEX IX_Greeting_GreetingId ON Test.Greeting,  IX_Greeting_GreetingId ON Test.Greeting

Analysis Results
  Message Line Column
1 SA0196 : Deprecated use of DROP INDEX with two-part index name syntax. 2 0
See Also

Other Resources