SA0066B : Check all Columns for following specified naming convention

Inconsistent or unclear database column naming can significantly impact database readability and maintainability.

Description

Using standardized naming conventions for columns helps maintain the clarity and manageability of database schemas. However, it is common to encounter inconsistency due to ad-hoc naming practices. This inconsistency can lead to confusion, errors in understanding the data schema, and difficulties in future maintenance or expansion of the database.

For example:

SQL
Copy
1-- Example of inconsistent column naming
2CREATE TABLE Orders (
3    PK_OrderID INT PRIMARY KEY,
4    cust_Id INT FOREIGN KEY REFERENCES Customers(CustomerId),
5    orderDate DATETIME,
6    amt_due DECIMAL(10, 2)
7);

In the example above, varying naming styles—such as “PK_OrderID” versus “cust_Id”—make the schema less intuitive and may result in misunderstandings among team members or automated processes.

  • Difficulty in understanding the purpose or role of each column, especially for new team members or external stakeholders.

  • Increased risk of errors in writing or maintaining SQL queries due to unclear or misleading column names.

How to fix

This section provides a strategy to ensure consistent column naming conventions in your database schema, which is essential for clarity and maintainability.

Follow these steps to address the issue:

  1. Review the existing column names in your database schema to identify inconsistencies with the adopted naming convention.

  2. Establish a naming convention policy for column names if one is not already defined. Consider patterns such as using PascalCase for Primary Keys and Foreign Keys (e.g., OrderID, CustomerID).

  3. Rename columns using ALTER TABLE and sp_rename for existing tables to align with the defined naming convention, ensuring database integrity and no pending dependencies on those columns.

  4. Migrate any existing SQL queries, stored procedures, or applications to use the updated column names to avoid breaking changes.

  5. Implement code reviews and automated checks to ensure new development adheres to the established naming conventions.

For example:

SQL
Copy
1-- Correcting inconsistent column names
2EXEC sp_rename 'Orders.PK_OrderID', 'OrderID', 'COLUMN';
3EXEC sp_rename 'Orders.cust_Id', 'CustomerID', 'COLUMN';

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
GeneralColumnNamePattern

General column name pattern.

regexp:[a-z]+
PrimaryKeyColumnNamePattern

Primary key column name pattern.

{table_name}ID
ForeignKeyColumnNamePattern

Foreign key column name pattern.

{referenced_table}_{referenced_column}
BitColumnNamePattern

Bit column name pattern.

regexp:[A-Z][A-Za-z1-9]+
StringColumnNamePattern

String column name pattern.

regexp:[A-Z][A-Za-z1-9_]+
DateTimeColumnNamePattern

Datetime column name pattern.

regexp:[A-Z][A-Za-z1-9]+
NumericColumnNamePattern

Numeric column name pattern.

regexp:[A-Z][A-Za-z1-9]+
BinaryColumnNamePattern

Binary column name pattern.

regexp:[A-Z][A-Za-z1-9]+
GeographyColumnNamePattern

Geography column name pattern.

regexp:[A-Z][A-Za-z1-9]+
HierarchyidColumnNamePattern

Hierarchyid column name pattern.

regexp:[A-Z][A-Za-z1-9]+
UniqueidentifierColumnNamePattern

Uniqueidentifier column name pattern.

regexp:[A-Z][A-Za-z1-9]+
XmlColumnNamePattern

Xml column name pattern.

regexp:[A-Z][A-Za-z1-9]+
SqlVariantColumnNamePattern

Sql_Variant column name pattern.

regexp:[A-Z][A-Za-z1-9]+
TableColumnNamePattern

Table column name pattern.

regexp:[A-Z][A-Za-z1-9]+
TimestampColumnNamePattern

Timestamp column name pattern.

regexp:[A-Z][A-Za-z1-9]+
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 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 Persons (
 2    Id int NOT NULL,
 3    OrderId int,
 4    FOREIGN KEY (OrderId) REFERENCES Orders(Id),
 5    PRIMARY KEY (Id)
 6)
 7
 8CREATE TABLE OtherPersons (
 9    Id int NOT NULL,
10    OrderId int,
11    LastName varchar(255) NOT NULL,
12
13)
14
15ALTER TABLE OtherPersons
16ADD FOREIGN KEY (OrderId) REFERENCES Orders(Id);
17
18ALTER TABLE OtherPersons
19ADD CONSTRAINT FK_OrderPerson FOREIGN KEY (OrderId) REFERENCES Orders(Id);
20
21ALTER TABLE OtherPersons
22ADD CONSTRAINT PK_OtherPersons PRIMARY KEY (LastName);
Analysis Results
  Message Line Column
1 SA0066B : The primary key column [Persons].[Id] does not match the naming convention. The expected name is [PersonsID]. 3 4
2 SA0066B : The foreign key column [Persons].[OrderId] does not match the naming convention. The expected name is [Orders_Id]. 4 4
3 SA0066B : The foreign key column [OtherPersons].[OrderId] does not match the naming convention. The expected name is [Orders_Id]. 17 17
4 SA0066B : The foreign key column [OtherPersons].[OrderId] does not match the naming convention. The expected name is [Orders_Id]. 20 43
5 SA0066B : The primary key column [OtherPersons].[LastName] does not match the naming convention. The expected name is [OtherPersonsID]. 23 44
See Also

Other Resources

Analysis Rules