SA0042B : Avoid using special characters in object names

Using special characters in database object names can lead to readability and reference issues in T-SQL and SQL Server.

Description

Database objects such as tables, columns, and indexes should have simple and clear names. When special characters like spaces, brackets, or quotes are included in these names, it can create complications in T-SQL scripts. This practice often leads to challenges in understanding and maintaining the code.

For example:

SQL
Copy
1-- Example of problematic object naming
2CREATE TABLE [Order Details] (
3    [Order ID] INT,
4    [Product Name] NVARCHAR(50)
5);

The example above demonstrates how special characters, like spaces and brackets, make it cumbersome to reference objects in a query. Not only does it clutter the syntax, but it also increases the chances of errors when writing SQL scripts.

  • Increased complexity in query syntax, requiring additional escape characters or brackets.

  • Difficulty in maintaining and reading code, which can lead to misunderstandings among team members or when reviewing code later.

How to fix

Remove special characters from database object names to improve readability and maintainability in T-SQL scripts.

Follow these steps to address the issue:

  1. Identify the database objects with special characters in their names. This includes tables, columns, and indexes.

  2. Rename the problematic objects to remove special characters such as spaces, brackets, or quotes. Use sp_rename in SQL Server Management Studio (SSMS) for renaming.

  3. Update any references to the renamed objects in your T-SQL scripts to reflect the new names. Ensure that all parts of your application and queries use the updated names.

  4. Test the changes by running queries to ensure that all references are correct and the system operates as expected without errors.

For example:

SQL
Copy
1-- Example of renaming a table and columns
2EXEC sp_rename 'Order Details', 'OrderDetails';
3EXEC sp_rename 'OrderDetails.[Order ID]', 'OrderID', 'COLUMN';
4EXEC sp_rename 'OrderDetails.[Product Name]', 'ProductName', 'COLUMN';

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
3 minutes per issue.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE [dbo].[Table1]
 2( 
 3        [ID] INT NOT NULL IDENTITY(0, 1), 
 4        [Small'String] VARCHAR(10), -- contains special characters ( ' )
 5        [Small"String] VARCHAR(10), -- contains special characters ( " )
 6        [Small String] VARCHAR(10), -- contains special characters ( space )
 7        [Small        String] VARCHAR(10), -- contains special characters ( tab )
 8        [Small[String] VARCHAR(10), -- contains special characters ( square bracket )
 9    [Small]]String] VARCHAR(10) -- contains special characters ( square bracket )    
10)
11ON [PRIMARY]
12
13CREATE TABLE [dbo].[Table2]
14( 
15[ID] INT NOT NULL IDENTITY(0, 1), 
16[SmallString] VARCHAR(10)
17)
18ON [PRIMARY]
Analysis Results
  Message Line Column
1 SA0042B : The name of the column [Small’String] contains quote character. 4 1
2 SA0042B : The name of the column [Small”String] contains quote character. 5 1
3 SA0042B : The name of the column [Small String] contains whitespace character. 6 1
4 SA0042B : The name of the column [Small String] contains whitespace character. 7 1
5 SA0042B : The name of the column [Small[String] contains square bracket character. 8 1
6 SA0042B : The name of the column [Small]]String] contains square bracket character. 9 4
See Also

Other Resources

Analysis Rules