SA0156 : Statements CREATE/DROP DEFAULT are deprecated. Use DEFAULT keyword in CREATE/ALTER TABLE

Using deprecated SQL statements, such as CREATE DEFAULT and DROP DEFAULT, poses problems for database maintenance and future-proofing. These statements are set to be removed in future SQL Server versions, meaning applications relying on them may fail to run or require costly updates to remain operational.

For example:

1-- Example of deprecated statements
2CREATE DEFAULT myDefault AS 'N/A';
3EXEC sp_bindefault 'myDefault', 'myTable.myColumn';
4DROP DEFAULT myDefault;

These examples showcase the use of defaults created with deprecated statements, which should be replaced with DEFAULT constraints directly within the table schema. Such updates not only ensure better compatibility with modern SQL Server versions but also enhance the clarity and efficiency of database design.

• Increases the risk of application failure after SQL Server upgrades if not updated in time.

• Promotes inefficient schema designs that are harder to maintain and troubleshoot.

To ensure future compatibility and maintainability, replace deprecated SQL statements like CREATE DEFAULT and DROP DEFAULT with SQL Server’s modern default constraint options.

Follow these steps to address the issue:

1. Identify all instances of deprecated default definitions, such as CREATE DEFAULT and sp_bindefault, in your database schema.

2. For each identified instance, remove the deprecated default definition by using DROP DEFAULT as needed.

3. Create or modify the table schema using the DEFAULT keyword either in the ALTER TABLE or CREATE TABLE statements to define default values directly on columns.

4. Update any application code that relies on the old default definitions to ensure it aligns with the new schema design.

5. Test thoroughly to verify that the new defaults are functioning as intended across all application workflows.

For example:

1-- Example of corrected default constraint in a table schema
2ALTER TABLE myTable
3ADD CONSTRAINT DF_myColumn DEFAULT 'N/A' FOR myColumn;

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, Code Smells

Deprecated Database Engine Features

Discontinued Database Engine Functionality

Other Resources