Skip to content

SA0066B : Check all Columns for following specified naming convention

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

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:

-- Example of inconsistent column naming
CREATE TABLE Orders (
PK_OrderID INT PRIMARY KEY,
cust_Id INT FOREIGN KEY REFERENCES Customers(CustomerId),
orderDate DATETIME,
amt_due DECIMAL(10, 2)
);

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.

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:

-- Correcting inconsistent column names
EXEC sp_rename 'Orders.PK_OrderID', 'OrderID', 'COLUMN';
EXEC sp_rename 'Orders.cust_Id', 'CustomerID', 'COLUMN';

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

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. {referencedtable}{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]+

The rule does not need Analysis Context or SQL Connection.

8 minutes per issue.

Naming Rules, Code Smells

There is no additional info for this rule.

CREATE TABLE Persons (
Id int NOT NULL,
OrderId int,
FOREIGN KEY (OrderId) REFERENCES Orders(Id),
PRIMARY KEY (Id)
)
CREATE TABLE OtherPersons (
Id int NOT NULL,
OrderId int,
LastName varchar(255) NOT NULL,
)
ALTER TABLE OtherPersons
ADD FOREIGN KEY (OrderId) REFERENCES Orders(Id);
ALTER TABLE OtherPersons
ADD CONSTRAINT FK_OrderPerson FOREIGN KEY (OrderId) REFERENCES Orders(Id);
ALTER TABLE OtherPersons
ADD CONSTRAINT PK_OtherPersons PRIMARY KEY (LastName);
  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

Analysis Rules