SQL coding standards

This topic describes SQL coding standards you may follow when working with databases and scripts.

SQL coding standards provide a consistent approach to writing clean code and scripts across SQL databases. These standards make your code readable, understandable, and organized. When working in teams, coding standards help reduce the risk of misinterpretation and ensure long-term maintainability in SQL environments.

Naming conventions

These are rules for naming database objects, such as tables, columns, stored procedures, indexes, or views. Proper naming improves code readability, reduces confusion, and supports ongoing development.

General rules

• Use PascalCase for object names, meaning that you should capitalize the first letter of each concatenated word, for example, CustomerOrder.
• Choose descriptive names that clearly indicate the object’s purpose.
• Start a name with a letter.
• Use only letters and numbers in names.
• Use singular nouns, for example, FirstName, OrderDate.
• Avoid using:
  • Spaces in database object names.
  • Reserved words for database object names.
  • Abbreviations in object names unless they are widely understood.
  • Underscores or special characters.

  Note

  Prefixes may include underscores.

Prefixes in object names

Follow a consistent prefix pattern.

Object type	Prefix	Naming convention	Example
Table		Contains a table name.	Customer
View	vw_	Followed by the table and view names.	vw_CustomerOrders
Stored procedure	usp_	Followed by a verb (Get, Delete, Update, Archive, Insert) and table name.	usp_GetCustomer
Function	ufn_	Followed by a key attribute that describes what the function returns or calculates.	ufn_CalcOrderTotal
Trigger	trg_	Followed by a verb (action) and table name.	trg_UpdateTimestamp
Primary key column		Contains a primary key name.	ID
Primary key constraint	PK_	Followed by a table name.	PK_Customer
Foreign key constraint	FK_	Followed by the child and parent table names.	FK_Order_CustomerId
Unique constraint	UQ_	Followed by the table and column names.	UQ_Customer_Email
Check constraint	CK_	Followed by a table name and a description.	CK_Order_Amount
Clustered index	IXC_	Followed by the table and column names.	IXC_Customer_LastName
Nonclustered index	IX_	Followed by the table and column names or multiple column names if the index is composite.	IX_Order_CustomerId_OrderDate
Unique nonclustered index	UQ_	Followed by the table and column names.	UQ_Product_SKU

Note

The prefixes usp_, vw_, ufn_, and trg_ are still very common in legacy enterprise environments, but in modern greenfield projects - especially those using EF Core, Dapper, or microservices - they are increasingly being dropped because most teams now consider them visual noise.

Tables

• Avoid using:
  • Prefixes, such as tbl, or any other descriptive prefix.
  • A table name in the ID column for the primary key.
  • The same name for both a table and any of its columns.
• Avoid naming a column after its table.
• For crosswalk tables, use descriptive column names for foreign keys.
• Avoid merging two table names to create a relationship table name when a clear, descriptive term for the relationship already exists.
• Create a PRIMARY KEY for each table unless there is a strong reason not to.

Columns

• Use ID as the primary key column name for identity columns.
• Avoid combining multiple values in one column.
• Use foreign keys to maintain referential integrity.
• Analyze business requirements to choose appropriate column sizes.

File and script naming

• Use descriptive names that reflect the action or object, for example, CreateCustomerTable.sql.
• Use underscores instead of spaces.

Temporary objects

• Use a consistent prefix for temporary tables, for example, #TempCustomer or ##GlobalTempCustomer.
• Avoid generic names, such as #Temp1 or #Data, that do not describe the purpose.

Design guidelines

Schema

• Organize database objects into schemas, such as Sales, Finance, or Audit, to separate them logically based on their purpose or business domain.

Nonclustered indexes

Use nonclustered indexes to improve query performance for frequently filtered, sorted, or joined columns.

• Limit the number of indexes on tables with high update activity.
• Keep index definitions as compact as possible.
• Create multiple indexes on large, read-heavy tables to give the Query Optimizer more access paths.
• Create indexed views for queries that rely on JOINs or aggregate functions.
• Add nonclustered indexes to columns commonly used in search predicates.
• Merge data modifications into single statements that update multiple rows.
• Place the most selective or heavily filtered column first in a multi-column index.

Clustered indexes

Clustered indexes define the physical ordering of data in a table and can be used for operations that require sequential data access.

• Use a clustered index when queries frequently:
  • Retrieve ranges of values by using operators, such as BETWEEN, >, >=, <, or <=.
  • Return large sets of rows.
  • Perform JOINs, especially on foreign key columns.
  • Sort or group results by using ORDER BY or GROUP BY.

Because the clustered index key is automatically included in every nonclustered index, larger clustered keys increase the size and storage cost of all related indexes. Therefore, select clustered index columns that have one or more of the following characteristics:

• The values are unique or provide high selectivity.
• The values are inserted and accessed in sequential order.
• The column is defined as an IDENTITY.
• The column is commonly used to sort or organize query output.

Stored procedures

• Avoid using the RECOMPILE option unless there are specific requirements.
• Include a DECLARE statement followed by a SET statement at the beginning of the code before its execution.
• Add comments in stored procedures, triggers, and SQL batches for readability and maintainability.
• Use a single stored procedure to return multiple result sets instead of multiple calls.
• Use the SET NOCOUNT ON option at the beginning of SQL batches to improve the performance of stored procedures.
• Use transactions for multiple DML statements in a stored procedure, and keep them short to minimize locking.
• Declare variables at the beginning of the stored procedure and assign values immediately after.

SQL views

• Create a view to:
  • Minimize code duplication by adding specialized logic at the beginning of each distinct functionality.
  • Simplify complex queries.
  • Restrict data access for specific users.
  • Support computed columns.
• Avoid using data-changing commands, such as INSERT or UPDATE, inside views.

Note

Creating a view based on other views may slow down query performance.

If the structure of the underlying tables changes, the view must be updated accordingly.

Aliasing

Proper aliasing improves readability and makes complex queries easier to follow.

• Use short, descriptive aliases that clearly represent the table or column they reference.
• Use abbreviations or initials only when they are commonly understood and improve readability.
• Alias all tables in JOIN clauses to avoid ambiguity and improve query clarity.
• Avoid using reserved keywords as aliases.
• Use column aliases to clarify expressions, computed columns, or when column names are ambiguous.
• Use the AS keyword for clarity.
• Reference columns with the same name across multiple tables with either the table name or alias.

Spacing

Consistent spacing improves readability and helps developers quickly understand code structure.

• Start keywords, such as SELECT, FROM, WHERE, JOIN, GROUP BY, and ORDER BY, on their own lines.
• List each column on a new line, indented under the SELECT keyword.
• Add spaces before and after operators, such as =, >, <, +.
• Align AND/OR conditions vertically in WHERE or JOIN clauses.
• Remove extra spaces at the end of lines to keep code clean.
• Add commas at the end of lines in lists of columns or parameters.
• Avoid adding a space after the opening character ( or before the closing character ).
• Use blank lines to organize logical groups of statements for easier scanning.

Data types

Using correct data types improves performance, ensures data accuracy, and reduces storage requirements.

• Choose the most appropriate data type for each column.
• Avoid using deprecated or legacy data types, such as TEXT, NTEXT, and IMAGE. Instead, use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY, respectively.
• Avoid using vendor-specific data types, since they may not be compatible with older software versions.

The table provides a list of data types and their usage in SQL code.

Data type	Usage
INT	Integer.
BIGINT	Integer requiring a very large range.
SMALLINT	Integer requiring a small numeric range.
DATETIME2	Date and time values with higher precision.
VARCHAR	Variable-length character data.
CHAR	Fixed-length character data.
MONEY	Monetary values; use only when fixed precision is acceptable.
DECIMAL	Exact numeric values with fixed precision.
REAL or FLOAT	Approximate numeric values for scientific, statistical, or floating-point calculations.
NUMERIC	Fixed-point decimal numbers.
TIME	Time-only values (no date).
NCHAR or NVARCHAR	Unicode text for multilingual or international character sets.

Default values

Specify default values for parameters or columns to ensure predictable behavior when inputs are missing.

• Assign the same type as the column to the default values.
• Add default values immediately after the data type declaration and before any NOT NULL statement.
• Use system functions, such as GETDATE(), for date and time defaults.

Constraints

• Define default constraints at the column level, and specify all other constraints at the table level.

NULL values

• Explicitly specify NULL and NOT NULL values for each column; avoid relying on implicit defaults.
• Avoid creating tables with many nullable columns because excessive NULL values impact performance, complicate the schema, and reduce index efficiency. Additionally, arithmetic expressions that involve NULL values, for example, NULL + 5 returns NULL, which can lead to unexpected results and errors in calculations.
• Document the purpose of the column that contains NULL values.
• Use ISNULL() or COALESCE() to process NULL values in calculations.
• Use IS NULL / IS NOT NULL instead of = NULL or <> NULL.
• Use NOT NULL + DEFAULT instead of NULL for date values whenever possible.
• Use NULL for numeric or text fields only when the absence of a value has a distinct semantic meaning. For example, Discount IS NULL indicates that a discount is not applicable or unknown, or Phone IS NULL indicates that no phone number is provided.
• Use NOT NULL + DEFAULT for numeric and string columns only when the default value has a clear and valid business meaning.
• Understand how NULL behaves in queries: In GROUP BY and DISTINCT clauses, all NULL values are considered equal, even though logical comparison (WHERE and ON) treats NULL = NULL as UNKNOWN.
• Keep it in mind that COUNT(column) ignores NULL values, while COUNT(*) counts all rows regardless of NULL values.
• Keep ternary logic in mind: A predicate like WHERE Active = 1 excludes rows where Active IS NULL. Use ISNULL(), COALESCE(), or explicit conditions, such as Active = 1 OR Active IS NULL, when appropriate.
• Avoid using NULL values in key columns, especially in foreign keys and columns involved in JOIN conditions.

SQL queries

Coding standards ensure queries, such as SELECT, INSERT, UPDATE, and DELETE, are written clearly, correctly, and securely.

Formatting and readability

• Write SQL keywords in uppercase, for example, SELECT, FROM, and WHERE.
• Start major clauses, such as SELECT, FROM, WHERE, GROUP BY, and ORDER BY, on a new line.
• Format queries for better readability.
• Use consistent indentation (2-4 spaces).
• Add comments for complex logic but avoid over-commenting obvious statements.
• Group related conditions and align logical operators (AND, OR) on new lines.
• Use meaningful, consistent aliases for all joined tables.
• Use the semicolon (;) in SQL code as a statement terminator.

SELECT statements

• Avoid SELECT * and list the required columns explicitly.
• Use column aliases to clarify calculated or ambiguous fields.
• Include only necessary columns in GROUP BY to avoid extra processing.
• Limit the use of functions in SELECT or WHERE clauses when alternative logic exists.
• Avoid functions on columns in WHERE clauses.
• Filter data by using a WHERE clause before a HAVING clause.
• When using ORDER BY with multiple columns, arrange them in a logical order of importance (for example, from the most to the least significant grouping).
• Use UNION ALL instead of UNION.

Joins

• Always specify JOIN conditions explicitly; avoid implicit JOINs.
• Choose the appropriate JOIN type (INNER, LEFT, RIGHT, and FULL).

Subqueries and CTEs

• Use Common Table Expressions (CTEs) with WITH clauses to improve readability over nested subqueries.
• A CTE must be preceded by a semicolon (;) in a batch.
• Keep subqueries simple and avoid unnecessary nesting.

Performance

• Avoid placing wildcard characters at the start of strings in LIKE searches to prevent index scans.
• Avoid using non-equal operators (<> or NOT), as they often cause table or index scans.
• Minimize the usage of dynamic SQL.
• Use parameters instead of concatenating values into SQL strings.
• Use IF EXISTS instead of SELECT COUNT(*) to check record existence.
• Use EXISTS instead of IN to check for specific values.
• Prefer CAST over CONVERT for simple type conversions.
• Use CREATE TABLE instead of SELECT INTO whenever possible, since SELECT INTO can lock system objects in tempdb and is slower as it copies the table structure along with data.
• Check query execution plans when performance issues arise.
• Avoid using NOLOCK unless the specific use case and risks are understood.
• Use SET NOCOUNT ON at the beginning of SQL batches, stored procedures, and triggers.

Data modification

• Always specify the column list in INSERT statements.

Note

To verify compliance with best practices, design guidelines and naming conventions, use T-SQL Analyzer.

Temporary objects

SQL coding standards help define when to use a type of the temporary object, how to name them, and how to manage their lifecycle for clarity and performance.

The table describes temporary objects used in SQL queries.

Temporary object type	Visibility	Lifetime	Example
Table variables	Visible only within the session that creates the table variable.	Deleted when the batch ends.	DECLARE @t TABLE (...)
Local temporary tables	Visible only within the session that creates the local temporary table.	Deleted when the object created the local temporary table ends. Used commonly for stored procedures.	CREATE TABLE #t (...)
Global temporary tables	Visible to all connections.	Deleted after all referencing connections are closed.	CREATE TABLE ##t (...)
Permanent tempdb tables	Visible to all users.	Exists until SQL Server restarts.	USE tempdb; CREATE TABLE t (...)
Common table expressions (CTEs)	Visible only within the statement.	Exists only within a single statement.	WITH cte AS (...) SELECT * FROM cte

General rules

• Use temporary tables when working with large data sets, complex JOINs, or when the data needs indexing.
• Use table variables for smaller datasets or when the number of rows is low.
• Avoid temporary objects when a single query or CTE can produce the same result.
• Add indexes to temporary tables when needed to improve query performance.
• Drop temporary tables explicitly by using DROP TABLE when they are no longer required.
• Avoid excessive use of temporary tables in frequently executed stored procedures.
• Use table variables with caution – large table variables may not generate optimal execution plans.

CTEs vs. TempTable

Aspect	Common Table Expressions (CTEs)	Temporary tables
Purpose	Improve readability and support recursion.	Re-use data or perform multiple processing passes.
Execution	Re-executed each time they are referenced.	Data is stored once; faster and more reliable re-use.
Performance	Used for small datasets or recursive queries.	Preferred for large datasets and repeated processing.
Indexes	Do not support indexes. However, indexes may be created on the underlying base tables.	Support indexes, improving query performance.
Lifetime and cleanup	Exist only during query execution and are automatically cleaned up.	Must be explicitly dropped after use.

Note

When working with stored procedures, use table variables instead of temporary tables, since the latter ones can cause stored procedures to recompile.

Error handling

Here are techniques and patterns to manage runtime errors:

• Include potentially error-prone code in BEGIN TRY and BEGIN CATCH blocks.
• Combine TRY...CATCH with explicit transactions (BEGIN TRANSACTION, COMMIT, and ROLLBACK) to ensure data consistency on failure.
• Create error handling and logging tables.
• Regularly test how your procedures and scripts respond to different error conditions.

Error severity

• TRY-CATCH blocks handle errors with severity levels between 11 and 19 that do not close the database connection.
• Errors with severity 20 or higher cause the Database Engine to close the connection and can’t be caught by TRY-CATCH blocks.
• Errors with severity 10 or lower are warnings or informational messages and are not handled by TRY-CATCH blocks.

Reserved keywords

View a list of reserved keywords that shouldn’t be used as names for tables, columns, or other database objects unless you enclose them in quoted identifiers or delimited identifiers.