SA0043B : Avoid using reserved words for type names

Introduction

Avoid using reserved words as names for user-defined types in T-SQL.

Description

Using reserved words as names for user-defined types can lead to confusion and misunderstanding of the database code. Reserved words are terms that SQL Server uses for its own purposes, like SELECT , TABLE , or WHERE .

For example:

-- Example of problematic query: Using a reserved word as a type name
CREATE TYPE [Date] AS TABLE (col1 INT);

In this example, the use of Date , a reserved word, as the name for the user-defined type can make the code harder to interpret and maintain. While you can technically use reserved words by employing delimited identifiers, it is not a best practice.

Potential for confusion for developers reading the code, leading to increased difficulty in understanding the logic.
Complexity in debugging and maintenance due to the need to use delimited identifiers to differentiate reserved words from custom ones.

How to fix

Avoid using reserved words as type names to improve code clarity and maintainability.

Follow these steps to address the issue:

1.Identify user-defined types that use reserved keywords by reviewing your database schema and the analysis report from SQL Enlight rule sa0043b .

2.Choose an alternative name for the type that does not conflict with reserved keywords. Consider adding a prefix or suffix to the name, or using a synonym.

3.Update the database schema to use the new type name, replacing instances where the old name was used. Ensure that all references in your application code and scripts are also updated.

For example:

-- Example of a corrected query with a non-reserved type name
CREATE TYPE CustomDate AS TABLE (col1 INT);

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

5 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

--  keyword as a type name misuse
CREATE TYPE dbo.[Alter] FROM varchar(11) NOT NULL ; --  'Alter' is a reserved keyword
CREATE TYPE [Order] FROM varchar(11) NOT NULL ; --  'Order' is a reserved keyword



EXEC sp_addtype N'Alter', N'char(10)',N'not null' --  'Alter' is a reserved keyword
EXEC sp_addtype N'Table', N'char(10)',N'not null' --  'Table' is a reserved keyword
EXEC sp_addtype N'Order', N'char(10)',N'not null' --  'Order' is a reserved keyword


EXEC sp_addtype N'', N'char(10)',N'not null' --  'Order' is a reserved keyword


EXEC sys.sp_addtype [Alter], N'char(10)',N'not null' --  Alter is a reserved keyword
EXEC sp_addtype dbo.[Alter], N'char(10)',N'not null' --  Alter is a reserved keyword


EXEC sp_addtype N'AlterType', N'char(10)',N'not null'

EXEC sp_addtype N'TableType', N'char(10)',N'not null'

Analysis Results

	Message	Line	Column
1	SA0043B : Avoid using reserved words for type names.	3	16
2	SA0043B : Avoid using reserved words for type names.	4	12
3	SA0043B : Avoid using reserved words for type names.	8	16
4	SA0043B : Avoid using reserved words for type names.	9	16
5	SA0043B : Avoid using reserved words for type names.	10	16
6	SA0043B : Avoid using reserved words for type names.	16	20
7	SA0043B : Avoid using reserved words for type names.	17	20

Analysis Rules