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

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:

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.

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:

1-- Example of corrected query
2DROP 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.

Design Rules, Deprecated Features, Bugs

There is no additional info for this rule.

Other Resources