SA0196 : Deprecated use of DROP INDEX with two-part index name syntax
Introduction
Section titled “Introduction”Using outdated syntax in DROP INDEX statements for removing indexes can cause compatibility issues and hinder proper execution in SQL Server.
Description
Section titled “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:
-- Example of problematic query using outdated syntaxDROP 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
Section titled “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:
-- Example of corrected queryDROP INDEX IndexName ON TableName;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”5 minutes per issue.
Categories
Section titled “Categories”Design Rules, Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”DROP INDEX Test.Greeting.IX_Greeting_GreetingId
DROP INDEX IX_Greeting_GreetingId ON Test.Greeting, IX_Greeting_GreetingId ON Test.GreetingExample Test SQL with Automatic Fix
Section titled “Example Test SQL with Automatic Fix”DROP INDEX IX_Greeting_GreetingId ON [Test].[Greeting];
DROP INDEX IX_Greeting_GreetingId ON Test.Greeting, IX_Greeting_GreetingId ON Test.GreetingAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0196 : Deprecated use of DROP INDEX with two-part index name syntax. | 2 | 0 |