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

Introduction

Deprecated statements can lead to future compatibility issues in SQL Server applications.

Description

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:

-- Example of deprecated statements
CREATE DEFAULT myDefault AS 'N/A';
EXEC sp_bindefault 'myDefault', 'myTable.myColumn';
DROP 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.

How to fix

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:

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

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

13 minutes per issue.

Additional Information

Deprecated Database Engine Features

Discontinued Database Engine Functionality

Example Test SQL

 --DROP DEFAULT  phonedflt;
 CREATE DEFAULT phonedflt AS 'unknown';

Analysis Results

	Message	Line	Column
1	SA0156 : Deprecated CREATE/DROP DEFAULT statement. Use DEFAULT keyword in CREATE/ALTER TABLE.	2	1

Analysis Rules