Skip to content

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.

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:

-- Example of problematic query using outdated syntax
DROP 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.

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:

-- Example of corrected query
DROP INDEX IndexName ON TableName;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

5 minutes per issue.

Design Rules, Deprecated Features, Bugs

There is no additional info for this rule.

DROP INDEX Test.Greeting.IX_Greeting_GreetingId
DROP INDEX IX_Greeting_GreetingId ON Test.Greeting, IX_Greeting_GreetingId ON Test.Greeting
DROP INDEX IX_Greeting_GreetingId ON [Test].[Greeting];
DROP INDEX IX_Greeting_GreetingId ON Test.Greeting, IX_Greeting_GreetingId ON Test.Greeting
  Message Line Column
1 SA0196 : Deprecated use of DROP INDEX with two-part index name syntax. 2 0

Analysis Rules