SA0213 : The DBCC command is deprecated

Introduction

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:

-- Usage of a deprecated DBCC command
DBCC 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:

-- Example of replacing a deprecated DBCC command
ALTER 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.

Additional Information

DBCC DBREINDEX (Transact-SQL)

DBCC INDEXDEFRAG (Transact-SQL)

DBCC SHOWCONTIG (Transact-SQL)

DBCC UNPINTABLE (Transact-SQL)

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

DBCC DBREINDEX ('HumanResources.Employee', PK_Employee_BusinessEntityID,80);
DBCC DBREINDEX ('HumanResources.Employee', ' ', 70);
DBCC INDEXDEFRAG (AdventureWorks2012, 'Production.Product', PK_Product_ProductID);

DBCC SHOWCONTIG ('HumanResources.Employee');
DBCC PINTABLE ( 12 , 12 )
DBCC UNPINTABLE ( 12 , 33 )
DBCC 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

Analysis Rules