SA0213 : The DBCC command is deprecated

The misuse of deprecated DBCC commands can lead to efficiency and compatibility issues in SQL Server environments.

Description

Deprecated DBCC commands in T-SQL code can introduce significant drawbacks to SQL Server performance and maintainability. These commands have been replaced by more efficient and powerful alternatives that better support current SQL Server features and enhancements.

For example:

SQL
Copy
1-- Usage of a deprecated DBCC command
2DBCC DBREINDEX('TableName');

Using deprecated commands like DBCC DBREINDEX can lead to plans that do not take advantage of new optimizations and future SQL Server enhancements. These commands are no longer actively supported and can be removed in later SQL Server versions, affecting upgrade paths and backward compatibility.

  • Decreased query performance due to exclusion from new SQL Server optimizations.

  • Increased maintenance burden owing to compatibility issues with future SQL Server versions.

How to fix

Replace deprecated DBCC commands with supported alternatives to ensure efficiency and compatibility in SQL Server environments.

Follow these steps to address the issue:

  1. Identify the deprecated DBCC commands in your SQL scripts. For example, DBCC DBREINDEX(‘TableName’).

  2. Consult the SQL Server documentation to find the modern alternatives that replace deprecated commands. Refer to the SQL Server Documentation on Deprecated Database Engine Features for guidance.

  3. Replace the deprecated command with its alternative. For instance, use ALTER INDEX for index-related operations instead of DBCC DBREINDEX.

  4. Test the modified queries to ensure functionality and performance improvements.

For example:

SQL
Copy
1-- Example of replacing a deprecated DBCC command
2ALTER INDEX ALL ON TableName REBUILD;

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

Deprecated Features, Bugs

Additional Information
Example Test SQL
SQL
Copy
1DBCC DBREINDEX ('HumanResources.Employee', PK_Employee_BusinessEntityID,80);  
2DBCC DBREINDEX ('HumanResources.Employee', ' ', 70);  
3DBCC INDEXDEFRAG (AdventureWorks2012, 'Production.Product', PK_Product_ProductID);  
4
5DBCC SHOWCONTIG ('HumanResources.Employee');  
6DBCC PINTABLE ( 12 , 12 )
7DBCC UNPINTABLE ( 12 , 33 )
8DBCC HELP ('CHECKDB')
Analysis Results
  Message Line Column
1 SA0213 : The DBCC DBREINDEX command is deprecated. Use ALTER INDEX instead. 1 5
2 SA0213 : The DBCC DBREINDEX command is deprecated. Use ALTER INDEX instead. 2 5
3 SA0213 : The DBCC INDEXDEFRAG command is deprecated. Use ALTER INDEX instead. 3 5
4 SA0213 : The DBCC SHOWCONTIG command is deprecated. Use sys.dm_db_index_physical_stats instead. 5 5
5 SA0213 : The DBCC PINTABLE command is deprecated. The command has no effect and its usages should be removed. 6 5
6 SA0213 : The DBCC UNPINTABLE command is deprecated. The command has no effect and its usages should be removed. 7 5
See Also

Other Resources

Analysis Rules