T-SQL Coding Standards and Best Practices

Establishing a consistent set of coding standards and best practices is crucial for any development team. In the world of T-SQL, this is especially important for ensuring code is readable, maintainable, and performant. This article outlines a comprehensive set of standards that can be adopted by your organization to improve the quality of your T-SQL code.

Introduction

In the world of database development, especially with SQL Server, having a solid style guide isn't just a nice-to-have—it's essential. Whether you're a solo developer tweaking queries or part of a team collaborating on complex databases, consistent coding practices make your T-SQL easier to read, debug, and scale. This guide draws from established resources to outline key principles for writing clean T-SQL. We'll cover everything from naming conventions to query formatting, helping you avoid common pitfalls and build databases that stand the test of time. Think of this as your roadmap to professional-grade SQL. By following these guidelines, you'll reduce errors, improve collaboration, and even boost performance in subtle ways. Let's dive in.

Prerequisites

Before adopting a coding standard, there are a few assumptions:

• You are working with Microsoft SQL Server
• You understand basic T-SQL syntax
• You are familiar with tables, views, stored procedures, and constraints
• You want consistent, production-quality code

Coding standards are not just for large teams. Even solo developers benefit. Six months from now, you are the next developer who has to read today’s code.

General

In the overall spectrum of writing code, like many other aspects of life, there are some “must do” actions and of course there some “avoid” actions. In this section we will briefly touch on some of the most significant dos and don’ts when writing SQL script.

Did you notice that play on words? The one where I wrote “SQL script” instead of “SQL code”. There’s a reason for that. In the world of programming, there are two classes of code, scripted and programing. Actually, it’s more along the lines of Compiled vs Interpreted.

Programming (compiled) languages are traditionally used to build standalone applications, systems, or services. Scripting (interpreted) languages are traditionally used to control, automate, or extend existing software.

Of course, this is just the surface of the difference between programming and scripting. You can read more about that at the University of Phoenix website blog: What are programming vs. scripting languages? among other places.

But I digress, let’s look at some of the common dos and don’ts for SQL scripting.

When it comes to writing high-quality T-SQL code, there are several key principles that you should always strive to follow. First and foremost, use consistent and descriptive identifiers and names for all of your database objects. This will make your code more self-documenting and easier to understand for anyone who needs to work with it. Additionally, make judicious use of white space and indentation to improve the readability of your queries. A well-formatted query is much easier to scan and debug than a block of unformatted text.

Another important practice is to store time and date information in the ISO 8601 compliant format (YYYY-MM-DDTHH:MM:SS.SSSSS). This will ensure that your date and time data is consistent and unambiguous, regardless of the regional settings of your server or client. Finally, always try to use standard SQL functions instead of vendor-specific ones whenever possible. This will make your code more portable and easier to migrate to other database platforms in the future.

Just as there are things you should do when writing T-SQL, there are also things you should avoid. One of the most common mistakes is using camelCase for object names. While this is a common convention in some programming languages, it can be difficult to read quickly in SQL. Instead, opt for PascalCase or snake_case for your object names. You should also avoid using descriptive prefixes or Hungarian notation, such as sp_ for stored procedures or tbl for tables. These prefixes are redundant and can make your code more cluttered. So, what are my alternatives? I’m glad you asked. Let’s say you want to create VIEW that returns some billing or inventory information. You should make use of your schema names for this desertion.

• Inventory.ProductStock
• Billing.InvoiceSummary
• HR.EmployeeHistory

Another thing to avoid is using plurals for table names. While it may seem natural to name a table of employees Employees, it’s generally better to use a collective term like Staff or Team. This can help to avoid confusion and make your database schema more consistent. Finally, you should avoid applying object-oriented design principles to your SQL or database structures. SQL is a declarative language, and trying to force object-oriented concepts onto it can lead to overly complex and inefficient designs.

Naming Conventions

Establishing a clear and consistent naming convention is one of the most important things you can do to improve the quality of your T-SQL code. A good naming convention makes your code more readable, understandable, and maintainable. When choosing names for your database objects, there are a few general rules you should always follow. First, ensure that the name is unique and does not conflict with any reserved keywords. You can find a list of reserved keywords in the SQL Server documentation.

Second, keep your names concise but descriptive. A good name should give you a clear idea of what the object is and what it does, without being overly long or verbose. As a general rule, try to keep your names to a maximum of 30 characters. Finally, only use letters, numbers, and underscores in your names. Avoid using special characters or spaces, as these can cause problems when you try to reference the object in your code.

When it comes to naming tables, the most important thing is to be consistent. Choose a style and stick with it. One popular convention is to use PascalCase for table names, such as SalesOrder or ProductInventory. Another option is to use snake_case, like sales_order or product_inventory. Whichever style you choose, make sure you apply it consistently across your entire database.

In addition to choosing a consistent style, there are a few other best practices to keep in mind when naming tables. First, avoid using prefixes like tbl_ or table_. These prefixes are redundant and add unnecessary clutter to your code. Second, use singular nouns for your table names. For example, name your table Customer instead of Customers. This makes your SQL queries read more like natural language (e.g., SELECT * FROM Customer) as shown in the sample code below.

USE AdventureWorks2022;
GO

SELECT *
FROM Sales.Customer;
GO

Column names should be descriptive and follow a consistent naming convention. As with table names, PascalCase or snake_case are both good options. For example, you might name your columns FirstName, LastName, and EmailAddress, or first_name, last_name, and email_address. The key is to choose a style and use it consistently throughout your database. Yes I have mentioned this a few times previously, but it’s worth repeating and, for the sake of repetitive learning, it will be mentioned again throughout the remainder of this blog post and others that may come in the future.

When naming columns, it’s also a good idea to avoid using generic names like id or name. Instead, be more specific. For example, use CustomerId or ProductName. This will make your queries easier to read and understand, especially when you are joining multiple tables. Finally, avoid using reserved keywords as column names. If you must use a reserved keyword, you will need to enclose it in square brackets or double quotes every time you reference it. The square brackets option is the most commonly used. Here’s a sample of how to use the square brackets.

USE AdventureWorks2022;
GO

SELECT *
FROM Sales.[Customer];
GO

Aliasing is a powerful feature in T-SQL that allows you to assign a temporary, more descriptive name to a table or column in your query. This can be especially useful when you are working with long or complex queries, as it can make your code much easier to read and understand. When creating aliases, it’s important to choose names that are both short and meaningful.

For table aliases, a common convention is to use a short, two- or three-letter abbreviation of the table name. For example, you might alias the SalesOrderHeader table as soh and the SalesOrderDetail table as sod. For column aliases, you should choose a name that accurately describes the data in the column. For example, if you have a column that contains the total price of an order, you might alias it as TotalPrice. Below is a sample code block that demonstrates table aliasing using the Person.Person and Person.EmailAddress tables.

USE AdventureWorks2022;
GO

SELECT 
    p.FirstName,
    p.LastName,
    e.EmailAddress
FROM Person.Person AS p
INNER JOIN Person.EmailAddress AS e
    ON p.BusinessEntityID = e.BusinessEntityID;
GO

Correlations in SQL are used to associate a subquery with the outer query. When you use a correlated subquery, the subquery is executed once for each row that is processed by the outer query. This can be a powerful tool, but it can also be a source of performance problems if it is not used carefully. When you are writing correlated subqueries, it’s important to use clear and consistent aliases to make your code easier to read and understand.

As with table aliases, a common convention for correlation names is to use a short abbreviation of the table name. This will help you to keep track of which table you are referencing in your subquery. It’s also a good idea to use comments to explain the purpose of your correlated subquery, especially if it is complex or not immediately obvious.

Example:

-- Outer query:
-- Select employees whose vacation hours are greater than the company average
SELECT 
    e.BusinessEntityID,
    e.JobTitle,
    e.VacationHours
FROM HumanResources.Employee AS e
WHERE e.VacationHours >
(
    -- Subquery:
    -- Calculate the average vacation hours for all employees
    SELECT AVG(VacationHours)
    FROM HumanResources.Employee
);
GO

Stored procedures are a key component of any SQL Server database, and it’s important to have a consistent naming convention for them. A common convention is to use the prefix usp_ (for user-stored procedure) followed by a descriptive name that indicates what the procedure does. For example, you might have a stored procedure named usp_GetCustomerOrders or usp_UpdateProductInventory. But as we mentioned earlier, this is not a recommended naming convention. Instead use the schema name to distinguish it as a stored procedure.

Example:

USE AdventureWorks2022;
GO

CREATE SCHEMA usp;
GO

CREATE PROCEDURE usp.GetOrders
AS
BEGIN
    SELECT TOP 10 *
    FROM Sales.SalesOrderHeader;
END;

When naming stored procedures, it’s also a good idea to use a verb-noun combination to describe the action that the procedure performs. This will make your code more readable and easier to understand. For example, GetCustomerOrders is a better name than CustomerOrders. Finally, be sure to use a consistent case for your stored procedure names. PascalCase is a popular choice.

Using consistent suffixes for your database objects can be a great way to improve the readability and maintainability of your code. A suffix is a short, descriptive word or abbreviation that you add to the end of an object name to indicate its type or purpose. For example, you might use the suffix _ID for primary key columns, or _Date for columns that store date values.

Some other common suffixes include _Status for columns that store status information, _Total for columns that store a total value, and _Name for columns that store a name. By using consistent suffixes, you can make your code more self-documenting and easier to understand for anyone who needs to work with it.

Here's an example of how to use a suffix when creating a stored procedure.

USE AdventureWorks2022;
GO

-- Create a stored procedure with a descriptive suffix
-- "_rpt" indicates this procedure is intended for reporting
CREATE OR ALTER PROCEDURE Sales.SalesSummary_rpt
AS
BEGIN
    SET NOCOUNT ON;

    -- Main query:
    -- Summarizes total sales by year
    SELECT 
        YEAR(OrderDate) AS OrderYear,
        SUM(TotalDue) AS TotalSales
    FROM Sales.SalesOrderHeader
    GROUP BY YEAR(OrderDate)
    ORDER BY OrderYear;
END;
GO

-- Execute the reporting procedure
EXEC Sales.SalesSummary_rpt;

Notice the continued use of comments. You really need to get into the habit of marking up your code.

Okay, how many of you caught the use of the underscore in this naming convention? sp_ is discouraged because of SQL Server’s special lookup behavior, not because underscores or prefixes are inherently evil.
The engine treats sp_ TableName differently than it does TableName_rpt. That’s a technical rule, not a naming philosophy rule.
So: _rpt has no engine penalty and in this case, underscores are perfectly valid. The problem is not the underscore, the problem is semantic clutter.

Query Syntax

Understanding query syntax is the foundation of writing reliable, readable, and high-performance SQL. Every query you write is a structured conversation with the database engine, and syntax is the grammar that ensures your intent is interpreted correctly. In this section, we’ll break down how SQL statements are constructed, why clause order matters, and how each keyword contributes to the final result set. A strong grasp of query syntax not only prevents errors, it also improves maintainability, collaboration, and long-term scalability of your code. Whether you’re selecting data, joining tables, or filtering results, mastering syntax transforms SQL from trial-and-error into a precise and predictable tool for data retrieval and analysis.

Reserved words are keywords that have a special meaning in T-SQL. These words are used to define the structure and logic of your queries, and they cannot be used as object names unless they are delimited. It’s a good practice to always write reserved words in uppercase. This will make your code easier to read and help you to distinguish between keywords and object names.

Some common reserved words include SELECT, FROM, WHERE, GROUP BY, ORDER BY, INSERT, UPDATE, and DELETE. For a complete list of reserved words in T-SQL, you can refer to the SQL Server documentation. By consistently using uppercase for reserved words, you can improve the readability of your code and make it easier for others to understand.

You can see a full list of SQL Server keywords in my previous blog here.

Proper use of white space is essential for writing readable and maintainable T-SQL code. White space includes spaces, tabs, and line breaks, and it can be used to separate different parts of your query and make it easier to read. When formatting your queries, you should use white space to align your keywords and clauses in a consistent and logical way.

For example, you should always put each clause of your SELECT statement on a new line, and you should indent your JOIN and WHERE clauses to show their relationship to the FROM clause. You should also use spaces around operators like = and + to make your code easier to read. By using white space effectively, you can transform a dense block of code into a well-structured and easy-to-understand query.

Here’s an example of what NOT to do followed by the preferred standard.

The Bad

USE AdventureWorks2022;
GO

CREATE OR ALTER PROCEDURE Sales.SalesSummary_rpt AS BEGIN SET NOCOUNT ON; SELECT 
YEAR(OrderDate) AS OrderYear, SUM(TotalDue) AS TotalSales FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate) ORDER BY OrderYear;
END; GO EXEC Sales.SalesSummary_rpt; GO

The Good

USE AdventureWorks2022;
GO

-- Create a stored procedure with a descriptive suffix
-- "_rpt" indicates this procedure is intended for reporting
CREATE OR ALTER PROCEDURE Sales.SalesSummary_rpt
AS
BEGIN
    SET NOCOUNT ON;

    -- Main query:
    -- Summarizes total sales by year
    SELECT 
        YEAR(OrderDate) AS OrderYear,
        SUM(TotalDue) AS TotalSales
    FROM Sales.SalesOrderHeader
    GROUP BY YEAR(OrderDate)
    ORDER BY OrderYear;
END;
GO

-- Execute the reporting procedure
EXEC Sales.SalesSummary_rpt;
GO

Indention is a key aspect of T-SQL formatting that can greatly improve the readability of your code. By indenting your code in a consistent and logical way, you can make it easier to see the structure of your queries and understand how the different parts of your code are related. A common convention is to use four spaces for each level of indentation.

When formatting your queries, you should indent your JOIN clauses, subqueries, and CASE statements to show their relationship to the main query. You should also indent the code within your stored procedures, functions, and triggers to make it easier to read. By consistently applying a clear indentation style, you can make your code more professional and easier to maintain.

In the following sample, the query will function as intended but readability is questionable. In other words, don’t write your code like this.

USE AdventureWorks2022;
GO

-- Outer query:
-- Return product cost records where a product’s standard cost
-- is higher than the average cost of all products in the same category
SELECT
pc.Name AS CategoryName,
p.Name AS ProductName,
pch.StandardCost,
pch.StartDate
FROM Production.ProductCostHistory AS pch

-- Join to Product to identify the product
INNER JOIN Production.Product AS p
ON pch.ProductID = p.ProductID

-- Join to ProductSubcategory to reach the category level
INNER JOIN Production.ProductSubcategory AS ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID

-- Join to ProductCategory to get the category name
INNER JOIN Production.ProductCategory AS pc
ON ps.ProductCategoryID = pc.ProductCategoryID

WHERE pch.StandardCost >
(
-- Subquery:
-- Calculate the average standard cost for the SAME category
-- as the outer row (correlated subquery)
SELECT AVG(pch2.StandardCost)
FROM Production.ProductCostHistory AS pch2
INNER JOIN Production.Product AS p2
ON pch2.ProductID = p2.ProductID
INNER JOIN Production.ProductSubcategory AS ps2
ON p2.ProductSubcategoryID = ps2.ProductSubcategoryID
WHERE ps2.ProductCategoryID = ps.ProductCategoryID
)

-- Sort results for easier review
ORDER BY pc.Name, p.Name;

In T-SQL, there are often multiple ways to achieve the same result. While this can be a good thing, it can also lead to inconsistencies in your code if you don’t establish a set of preferred formalisms. A formalism is simply a preferred way of writing a particular piece of code. For example, you might prefer to use INNER JOIN instead of the older comma-style join syntax.

Old Method

USE AdventureWorks2022;
GO

-- Old comma-style join syntax
-- Tables are separated by commas
-- Join condition is written in the WHERE clause
SELECT 
    p.FirstName,
    p.LastName,
    e.EmailAddress
FROM Person.Person p, Person.EmailAddress e
WHERE p.BusinessEntityID = e.BusinessEntityID;

Prefered Method

SELECT 
    p.FirstName,
    p.LastName,
    e.EmailAddress
FROM Person.Person AS p
INNER JOIN Person.EmailAddress AS e
    ON p.BusinessEntityID = e.BusinessEntityID;

Another example of a preferred formalism is to always use the AS keyword when creating aliases. While the AS keyword is optional in many cases, using it consistently can make your code more explicit and easier to read. By establishing a set of preferred formalisms and applying them consistently, you can improve the quality and consistency of your T-SQL code.

USE AdventureWorks2022;
GO

-- Demonstrates explicit aliasing with AS for clarity and consistency
SELECT
    p.BusinessEntityID AS EmployeeID,   -- Column alias using AS
    p.FirstName AS FirstName,
    p.LastName AS LastName,
    e.EmailAddress AS Email
FROM Person.Person AS p                -- Table alias using AS
INNER JOIN Person.EmailAddress AS e    -- Table alias using AS
    ON p.BusinessEntityID = e.BusinessEntityID
WHERE p.LastName LIKE 'A%'
ORDER BY p.LastName AS SortOrder;      -- Column alias used for readability

Create Syntax

The CREATE syntax is where database design becomes concrete. Every table, view, procedure, and function begins with a well-formed CREATE statement, making this command the blueprint language of SQL Server. Understanding how CREATE works is essential for building objects that are structured, predictable, and easy to maintain over time. In this section, we’ll walk through the anatomy of CREATE statements, explain how options and clauses affect behavior, and highlight patterns that promote consistency and long-term stability. Mastering create syntax ensures that new database objects aren’t just functional — they’re designed with clarity, scalability, and best practices in mind from the start.

Choosing the right data types for your columns is a critical aspect of database design. The data types you choose will affect the performance of your queries, the amount of storage space your database requires, and the integrity of your data. When choosing data types, you should always choose the smallest data type that can accommodate all of the possible values for that column.

For example, if you have a column that will only ever store integer values between 0 and 255, you should use the tinyint data type instead of int. This will save you 3 bytes of storage space for each row in your table. Similarly, if you have a column that will store fixed-length character data, you should use the char data type instead of varchar. By choosing your data types carefully, you can optimize the performance and storage of your database.

You can read more about data types in the "First Steps" section of this website here.

Default values are a great way to ensure data integrity and simplify your INSERT statements. A default value is a value that is automatically inserted into a column if no other value is specified. For example, you might set the default value of a CreateDate column to the current date and time.

When defining default values, it’s important to choose a value that makes sense for the column. For example, you wouldn’t want to set the default value of a Price column to 0 if that’s not a valid price. It’s also a good idea to give your default constraints a descriptive name, so that you can easily identify them later.

Here's an example of setting "default values" in a CREATE TABLE query.

USE master;
GO

CREATE DATABASE myTestDB;
GO

USE myTestDB;
GO

-- Create a Staff table
-- HireDate has a default value of the current date
CREATE TABLE dbo.Staff
(
    StaffID INT IDENTITY(1,1) PRIMARY KEY, -- Here is where we create our User-defined default value to create an ID value with an incriment of 1.
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL 
        DEFAULT (GETDATE())  -- Here is where we create our User-defined default value to assign todays date in the HireDate column.
);
GO

-- Insert a new employee WITHOUT specifying HireDate
-- SQL Server will automatically apply the default
INSERT INTO dbo.Staff (FirstName, LastName)
VALUES ('Maria', 'Lopez'),
('John', 'Sanchez'),
('Mike', 'Smith');
GO

-- Verify the result
SELECT *
FROM dbo.Staff;

Constraints are rules that you can apply to your columns to enforce data integrity. There are several different types of constraints in SQL Server, including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT. By using constraints effectively, you can ensure that the data in your database is accurate and consistent.

When creating constraints, it’s important to give them descriptive names. This will make it easier to identify and manage your constraints later on. For example, you might name a primary key constraint PK_Customers or a foreign key constraint FK_Orders_Customers. By using a consistent naming convention for your constraints, you can make your database schema more self-documenting and easier to understand.

Keys are a fundamental concept in relational database design. A key is a column or a set of columns that can be used to uniquely identify a row in a table. There are two main types of keys in SQL Server: primary keys and foreign keys. A primary key is a column or a set of columns that uniquely identifies each row in a table. A foreign key is a column or a set of columns in one table that refers to the primary key of another table.

When designing your tables, it’s important to choose your keys carefully. Your primary key should be a column that is guaranteed to be unique and not null. An identity column is often a good choice for a primary key. Your foreign keys should be used to establish relationships between your tables and enforce referential integrity.

*** We'll discuss Constraints and Keys in a future blog post, so stay tuned.

While there are many good design patterns in SQL Server, there are also some designs that you should try to avoid. One common anti-pattern is the Entity-Attribute-Value (EAV) model. In this model, you have a single table that stores all of your data in three columns: Entity, Attribute, and Value. While this model can be very flexible, it can also be very difficult to query and maintain.

Another design to avoid is storing multiple values in a single column. For example, you might be tempted to store a comma-separated list of values in a single varchar column. However, this can make it very difficult to query and update your data. A better approach is to create a separate table to store the multiple values, with a foreign key relationship back to the main table.