SA0215 : The CREATE RULE and DROP RULE statements are deprecated

Introduction

The usage of deprecated CREATE RULE and DROP RULE statements in T-SQL code can cause compatibility issues in future versions of SQL Server, as these features are being phased out and may no longer be supported.

Description

In modern SQL Server, using CREATE RULE and DROP RULE is discouraged because these statements are deprecated. They were once used to enforce domain integrity by defining rules that a column’s data must satisfy. However, SQL Server now recommends using CHECK constraints as a more robust and flexible alternative.

For example:

-- Using deprecated statements
CREATE RULE Positive AS @value > 0;
DROP RULE Positive;

This is considered problematic because:

Rules offer less flexibility compared to CHECK constraints, which can be added or modified directly within the table design, enhancing clarity and maintainability.
Reliance on deprecated features can lead to issues during SQL Server upgrades, as support for these features might be removed in future versions.

How to fix

Replace deprecated CREATE RULE and DROP RULE statements with CHECK constraints in your SQL code to enhance flexibility and maintainability.

Follow these steps to address the issue:

1.Identify all instances of CREATE RULE and DROP RULE statements in your SQL scripts.

2.For each identified rule, determine the table and column(s) it is associated with.

3.Replace the rule logic with a CHECK constraint directly defined within the table using CREATE TABLE or ALTER TABLE .

4.Remove the deprecated CREATE RULE and DROP RULE statements from your scripts.

For example:

-- Deprecated usage
-- CREATE RULE Positive AS @value > 0;
-- DROP RULE Positive;

-- Recommended replacement with CHECK constraint
ALTER TABLE TableName
ADD CONSTRAINT CK_Positive CHECK (Column1 > 0);

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

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

CREATE RULE list_rule
AS
@list IN ('1389', '0736', '0877');

Analysis Results

	Message	Line	Column
1	SA0215 : The CREATE RULE and DROP RULE statements are deprecated.	1	0

Analysis Rules