SA0066A : Check all Columns for following specified naming convention

Introduction

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

Description

A critical issue in T-SQL and SQL Server applications is the inconsistent naming of columns, which may lead to confusion and errors in database management and querying. Clear and consistent names facilitate easier understanding and maintenance of the database schema. Without adherence to naming conventions, developers and administrators may struggle to quickly identify a column’s purpose or type, leading to potential misinterpretations and inefficiencies.

For example:

-- Example of a poorly named column
SELECT CustomerName, CntctDt FROM Orders;

In this example, the column name CntctDt is unclear. It does not immediately convey that it refers to a ‘Contact Date’, making the query more difficult to decipher for someone unfamiliar with the schema. This ambiguity can lead to misunderstandings in column data use and can complicate maintenance and collaboration.

Unclear names increase the risk of misusing columns due to misunderstanding their stored content.
Lack of standardized naming conventions can slow down the development process as more time is spent deciphering column roles.

How to fix

Ensure database columns are named consistently and clearly to facilitate easier management and understanding.

Follow these steps to address the issue:

1.Review each column name in your database schema and identify those that do not adhere to established naming conventions or are unclear, such as CntctDt .

2.Consult your organization’s naming convention guidelines or refer to standardized practices, such as those found in the SQL Server Naming Conventions Guide .

3.Update the column names to align with the naming convention. Use clear and descriptive names that convey the column’s purpose, such as renaming CntctDt to ContactDate .

4.Make necessary changes in any dependent code or queries to reflect the updated column names to avoid breaking the functionality.

For example:

-- Corrected query with clear naming
SELECT CustomerName, ContactDate FROM Orders;

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Name	Description	Default Value
GeneralColumnNamePattern	General column name pattern.	regexp:[A-Z][A-Za-z1-9]+
PrimaryKeyColumnNamePattern	Primary key column name pattern.	{table_name}ID
ForeignKeyColumnNamePattern	Foreign key column name pattern.	{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 requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

8 minutes per issue.

Additional Information

There is no additional info for this rule.

Analysis Rules