SQLServerCentral Article

SQL Server Name Convention and T-SQL Programming Style


There are only two hard things in Computer Science: cache invalidation and naming things -- Phil Karlton

The recommendations in this article are not the ultimate truth. Please consider this article as a rule template that you can adapt to your needs.

A naming convention is a set of rules for choosing the character sequence to be used for identifiers which denote variables, types, functions, and other entities in source code and documentation. As opposed to allowing programmers to choose any character sequence, the reasons for using a naming convention include the following:

    • To reduce the effort needed to read and understand source code.
    • To enable code reviews to focus on more important issues than arguing over syntax and naming standards.
  • To enable code quality review tools to focus their reporting mainly on significant issues other than syntax and style preferences.

SQL Server Object Name Conventions

ObjectCodeNotationLengthPluralPrefixSuffixAbbreviationChar MaskExample
Global Temporary TablePascalCase117NoNoNoYes##[A-z][0-9]##MyTable
Local Temporary TablePascalCase116NoNoNoYes#[A-z][0-9]#MyTable
File TablePascalCase128NoFT_NoYes[A-z][0-9]FT_MyTable
Memory-optimized SCHEMA_AND_DATA TablePascalCase128NoMT__SDYes[A-z][0-9]MT_MyTable_SD
Memory-optimized SCHEMA_ONLY TablePascalCase128NoMT__SOYes[A-z][0-9]MT_MyTable_SO
Temporal TablePascalCase128NoNo_TTYes[A-z][0-9]MyTable_TT
Disk-Based TableUPascalCase128NoNoNoYes[A-z][0-9]MyTable
Disk-Based Wide Table - SPARSE ColumnUPascalCase128NoNo_SPRYes[A-z][0-9]MyTable_SPR
Table ColumnPascalCase128NoNoNoYes[A-z][0-9]MyColumn
Table Column SPARSEPascalCase128NoNo_SPRYes[A-z][0-9]MyColumn_SPR
Columns Check ConstraintCPascalCase128NoCTK_NoYes[A-z][0-9]CTK_MyTable_MyColumn_AnotherColumn
Column Check ConstraintCPascalCase128NoCK_NoYes[A-z][0-9]CK_MyTable_MyColumn
Column Default ValuesDPascalCase128NoDF_NoYes[A-z][0-9]DF_MyTable_MyColumn
Table Primary KeyPKPascalCase128NoPK_NoYes[A-z][0-9]PK_MyTableID
Table Unique (Alternative) KeyUQPascalCase128NoAK_NoYes[A-z][0-9]AK_MyTable_MyColumn_AnotherColumn
Table Foreign KeyFPascalCase128NoFK_NoYes[A-z][0-9]FK_MyTable_ForeignTableID
Table Clustered IndexPascalCase128NoIXCNoYes[A-z][0-9]IXC_MyTable_MyColumn_AnotherColumn
Table Non Clustered IndexPascalCase128NoIX_NoYes[A-z][0-9]IX_MyTable_MyColumn_AnotherColumn
DDL TriggerTRPascalCase128NoTR__DDLYes[A-z][0-9]TR_LogicalName_DDL
DML TriggerTRPascalCase128NoTR__DMLYes[A-z][0-9]TR_MyTable_LogicalName_DML
Logon TriggerTRPascalCase128NoTR__LOGYes[A-z][0-9]TR_LogicalName_LOG
Indexed ViewVPascalCase128NoVIX_NoNo[A-z][0-9]VIx_LogicalName
Stored ProcedurePPascalCase128Nousp_NoNo[A-z][0-9]usp_LogicalName
Scalar User-Defined FunctionFNPascalCase128Noudf_NoNo[A-z][0-9]udf_FunctionLogicalName
Table-Valued FunctionFNPascalCase128Notvf_NoNo[A-z][0-9]tvf_FunctionLogicalName
CLR AssemblyPascalCase128NoCANoYes[A-z][0-9]CALogicalName
CLR Stored ProceduresPCPascalCase128Nopc_NoYes[A-z][0-9]pc_CAName_LogicalName
CLR Scalar User-Defined FunctionPascalCase128Nocudf_NoNo[A-z][0-9]cudf_CAName_LogicalName
CLR Table-Valued FunctionPascalCase128Noctvf_NoNo[A-z][0-9]ctvf_CAName_LogicalName
CLR User-Defined AggregatesPascalCase128Noca_NoNo[A-z][0-9]ca_CAName_LogicalName
CLR User-Defined TypesPascalCase128Noct_NoNo[A-z][0-9]ct_CAName_LogicalName
CLR TriggersPascalCase128Noctr_NoNo[A-z][0-9]ctr_CAName_LogicalName

SQL Server Data Types Recommendation

More details about SQL Server data types and mapping it with another databases and program languages you can find here

General TypeTypeANSIRecommendedWhat use insteadWhy use or not
Exact NumericsbitNoMaybetinyintbit convert any number (except 0) to 1, 0 converted to 0
Exact NumericstinyintNoMaybeintfor saving 3 bytes compare to int data type or for replacing bit data type
Exact NumericssmallintYesMaybeintfor saving 2 bytes compare to int data type
Exact NumericsintYesYes-
Exact NumericsbigintNoYesintif you work more than
Exact NumericsdecimalYesYes-
Exact NumericssmallmoneyNoMaybedecimalpossibility to lose precision due to rounding errors
Exact NumericsmoneyNoMaybedecimalpossibility to lose precision due to rounding errors
Approximate NumericsrealYesYes-
Approximate Numericsfloat(1-24)YesNorealSQL Server automatically converts float(1-24) to real data type
Approximate Numericsfloat(24-53)YesYes-
Date and TimedateYesYes-
Date and TimesmalldatetimeNoMaybedate
Date and TimetimeYesYes-
Date and Timedatetime2NoYes-
Date and TimedatetimeYesMaybedatetime2On the Advantages of DateTime2(n) over DateTime
Date and timedatetimeoffsetNoYes-
Character StringscharYesMaybevarcharSave 1 byte from varchar, but be ready for trailing spaces
Character StringsvarcharYesYes-
Character Stringsvarchar(max)YesYes-
Character StringsncharYesMaybenvarchar
Character StringsnvarcharYesYes-
Character Stringsnvarchar(max)YesYes-
Character StringsntextNoDeprecatednvarchar(max)NVARCHAR(MAX) VS NTEXT in SQL Server
Character StringstextNoDeprecatedvarchar(max)Differences Between Sql Server TEXT and VARCHAR(MAX) Data Type
Binary StringsimageNoDeprecatedvarbinary(max)VARBINARY(MAX) Tames the BLOB
Binary StringsbinaryYesDeprecatedvarbinaryConversions between any data type and the binary data types are not guaranteed
Binary StringsvarbinaryYesYes-
Binary Stringsvarbinary(max)YesYes-
Other Data TypescursorNoYes-
Other Data Typessql_variantNoYes-
Other Data TypeshierarchyidNoYes-
Other Data TypesrowversionNoMaybe-
Other Data TypestimestampNoDeprecatedrowversionit is just synonym to rowversion data type and must be removed
Other Data TypesuniqueidentifierNoYes-
Other Data TypesxmlYesYes-
Other Data TypestableNoMaybe-
Spatial Data TypesgeometryNoYes-
Spatial Data TypesgeographyNoYes-

T-SQL Programming Style

This sections includes some T-SQL coding conventions, best practices, and programming guidelines.

General programming T-SQL style

  • For database objects names in code use only schema plus object name, do not hard code server and database names in your code: dbo.MyTable is good and bad PRODSERVER.PRODDB.dbo.MyTable. More details herehere and here.
  • Delimiters: spaces (not tabs)
  • Avoid using asterisk in select statements SELECT *, use explicit column names. More details here.
  • No square brackets [] and reserved words in object names and alias, use only Latin symbols [A-z] and numeric [0-9].
  • Prefer ANSI syntax and functions (CAST instead CONVERTCOALESE instead ISNULL, etc.).
  • All finished expressions should have semicolon ; at the end. This is ANSI standard and Microsoft announced with the SQL Server 2008 release that semicolon statement terminators will become mandatory in a future version so statement terminators other than semicolons (whitespace) are currently deprecated. This deprecation announcement means that you should always use semicolon terminators in new development. More details here.
  • All script files should end with GO and line break.
  • Keywords should be in UPPERCASESELECTFROMGROUP BY etc.
  • Data types declaration should be in lowercasevarchar(30)intrealnvarchar(max) etc. More details here.
  • All system database and tables must be in lowercase for properly working for Case Sensitive instance: master, sys.tables ….
  • Avoid non-standard column aliases, use, if required, double-quotes for special characters and always AS keyword before alias:
           p.LastName AS "Last Name"
      FROM dbo.Person AS p;

    More details here. All possible ways using aliases in SQL Server:

     /* Recommended due to ANSI */ SELECT SCHEMA_NAME(schema_id) + '.' + "name" AS "Tables" FROM sys.tables;
     /* Not recommended but possible */ SELECT SCHEMA_NAME(schema_id) + '.' + [name] AS "Tables" FROM sys.tables;
     SELECT Tables   = SCHEMA_NAME(schema_id) + '.' + [name]  FROM sys.tables;
     SELECT "Tables" = SCHEMA_NAME(schema_id) + '.' + [name]  FROM sys.tables;
     SELECT [Tables] = SCHEMA_NAME(schema_id) + '.' + [name]  FROM sys.tables;
     SELECT 'Tables' = SCHEMA_NAME(schema_id) + '.' + [name]  FROM sys.tables;
     SELECT SCHEMA_NAME(schema_id) + '.' + [name] [Tables]    FROM sys.tables;
     SELECT SCHEMA_NAME(schema_id) + '.' + [name] 'Tables'    FROM sys.tables;
     SELECT SCHEMA_NAME(schema_id) + '.' + [name] "Tables"    FROM sys.tables;
     SELECT SCHEMA_NAME(schema_id) + '.' + [name] Tables      FROM sys.tables;
     SELECT SCHEMA_NAME(schema_id) + '.' + [name] AS [Tables] FROM sys.tables;
     SELECT SCHEMA_NAME(schema_id) + '.' + [name] AS 'Tables' FROM sys.tables;
     SELECT SCHEMA_NAME(schema_id) + '.' + [name] AS Tables   FROM sys.tables;
  • The first argument in SELECT expression should be on the next line:
  • Arguments are divided by line breaks, commas should be placed before an argument:
         , LastName
  • For SQL Server >= 2012 use FETCH-OFFSET instead TOP. More details here. But if you use TOP avoid use TOP in a SELECT statement without an ORDER BY. More details here.
  • If you using TOP (instead recommended FETCH-OFFSET) function with round brackets because TOP has supports use of an expression, such as (@Rows*2), or a sub query: SELECT TOP(100) LastName …. More details here. Also TOP without brackets does not work with UPDATE and DELETE statements.
    /* Not working without brackets () */DECLARE @n int = 1;
    SELECT TOP@n name FROM sys.objects;
  • For demo queries use TOP(100) or lower value because SQL Server uses one sorting method for TOP 1-100 rows, and a different one for 101+ rows. More details here.
  • Avoid specifying integers in the ORDER BY clause as positional representations of the columns in the select list. The statement with integers is not as easily understood by others compared with specifying the actual column name. In addition, changes to the select list, such as changing the column order or adding new columns, requires modifying the ORDER BY clause in order to avoid unexpected results. More details here.
    /* bad */SELECT ProductID, Name FROM Production.Production ORDER BY 2;
    /* good */SELECT ProductID, Name FROM Production.Production ORDER BY Name;
  • Avoid using ISNUMERIC function. Use for SQL Server >= 2012 TRY_CONVERT function and for SQL Server < 2012 LIKE expression:
    CASE WHEN STUFF(LTRIM(TapAngle),1,1,'') NOT LIKE '%[^-+.ED0123456789]%' /* is it a float? */          AND LEFT(LTRIM(TapAngle),1) LIKE '[-.+0123456789]'
              AND TapAngle LIKE '%[0123456789][ED][-+0123456789]%'
              AND RIGHT(TapAngle ,1) LIKE N'[0123456789]'
         THEN 'float'
         WHEN STUFF(LTRIM(TapAngle),1,1,'') NOT LIKE '%[^.0123456789]%' /* is it numeric? */          AND LEFT(LTRIM(TapAngle),1) LIKE '[-.+0123456789]'
              AND TapAngle LIKE '%.%' AND TapAngle NOT LIKE '%.%.%'
              AND TapAngle LIKE '%[0123456789]%'
         THEN 'float'

    More details here.

  • Avoid using INSERT INTO a permanent table with ORDER BY. More details here.
  • Avoid using shorthand (wk, yyyy, d etc.) with date/time operations, use full names: month, day, year. More details here.
  • Avoid ambiguous formats for date-only literals, use CAST('yyyymmdd' AS DATE) format.
  • Avoid treating dates like strings and avoid calculations on the left-hand side of the WHERE clause. More details here.
  • Avoid using hints except RECOMPILE if needed and NOEXPAND (see next tip). More details here.
  • Use NOEXPAND hint for indexed views on non enterprise editions and Prior to SQL Server 2016 (13.x) SP1 to let the query optimizer know that we have indexes. More details here.
  • Avoid use of SELECT…INTO for production code, use instead CREATE TABLE + INSERT INTO … approach. More details here.
  • Use only ISO standard JOINS syntax. The old style Microsoft/Sybase JOIN style for SQL, which uses the =* and *= syntax, has been deprecated and is no longer used. Queries that use this syntax will fail when the database engine level is 10 (SQL Server 2008) or later (compatibility level 100). The ANSI-89 table citation list (FROM tableA, tableB) is still ISO standard for INNER JOINs only. Neither of these styles are worth using. It is always better to specify the type of join you require INNERLEFT OUTERRIGHT OUTERFULL OUTER and CROSS, which has been standard since ANSI SQL-92 was published. While you can choose any supported JOIN style, without affecting the query plan used by SQL Server, using the ANSI-standard syntax will make your code easier to understand, more consistent, and portable to other relational database systems. More details here.
  • Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. More details here.
  • Use EXISTS or NOT EXISTS if referencing a subquery, and IN or NOT IN when have a list of literal values. More details here.
  • For concatenate Unicode strings:
    • always using the upper-case N;
    • always store into a variable of type nvarchar(max);
    • avoid truncation of string literals, simply ensure that one piece is converted to nvarchar(max). Example:
    DECLARE @nvcmaxVariable nvarchar(max);
    SET @nvcmaxVariable = CAST(N'? russian anomaly ??? ? ' AS nvarchar(max)) + N'something else' + N'another';
    SELECT @nvcmaxVariable;

    More details here.

  • Always specify a length to any text-based data type such as varcharnvarcharcharnchar:
     /* Correct */ DECLARE @myGoodVarchareVariable  varchar(50);
     DECLARE @myGoodNVarchareVariable nvarchar(90);
     DECLARE @myGoodCharVariable      char(7);
     DECLARE @myGoodNCharVariable     nchar(10);
     /* Not correct */ DECLARE @myBadVarcharVariable  varchar;
     DECLARE @myBadNVarcharVariable nvarchar;
     DECLARE @myBadCharVariable     char;
     DECLARE @myBadNCharVariable    nchar;

    More details here.

  • Use only ORIGINAL_LOGIN() function because is the only function that consistently returns the actual login name that we started with regardless of impersonation. More details here.
  • FROM, WHERE, INTO, JOIN, GROUP BY, ORDER BY expressions should be aligned so, that all their arguments are placed under each other (see Example below)

T-SQL Example with formatting:

                t1.Value1  AS Val1
              , t1.Value2  AS Val2
              , t2.Value3  AS Val3
     INNER JOIN dbo.Table3 AS t2
             ON t1.Value1 = t2.Value1
     WHERE      t1.Value1 > 1
       AND      t2.Value2 >= 101
         t1.Value1 AS Val1
       , t1.Value2 AS Val2
       , t2.Value3 AS Val3
INTO     #Table3
ORDER BY t2.Value2;

Stored procedures and functions programming style

  • All stored procedures and functions should use ALTER statement and start with the object presence check (see example below)
  • ALTER statement should be preceded by 2 line breaks
  • Parameters name should be in camelCase
  • Parameters should be placed under procedure name divided by line breaks
  • After the ALTER statement and before AS keyword should be placed a comment with execution example
  • The procedure or function should begin with parameters checks (see example below)
  • Create sp_ procedures only in master database - SQL Server will always scan through the system catalog first
  • Always use BEGIN TRY and BEGIN CATCH for error handling
  • Always use multi-line comment /* */ instead in-line comment --
  • Use SET NOCOUNT ON; for stops the message that shows the count of the number of rows affected by a Transact-SQL statement and decreasing network traffic. More details here.
  • Do not use SET NOCOUNT OFF; because it is default behavior
  • Use RAISERROR instead PRINT if you want to give feedback about the state of the currently executing SQL batch without lags. More details here and here.
  • All code should be self documenting
  • T-SQL code, triggers, stored procedures, functions, scripts, should have a standard comment-documentation banner:
  <summary>Get all databases meta data using dynamic T-SQL</summary>
  <returns>1 data set: temp table #DatabaseInfo.</returns>
  <author>Konstantin Taranov</author>
  <modified>2019-11-14 by Konstantin Taranov</modified>

Stored Procedure Example:

IF OBJECT_ID('dbo.usp_StoredProcedure', 'P') IS NULL
EXECUTE('CREATE PROCEDURE dbo.usp_StoredProcedure as SELECT 1');

ALTER PROCEDURE dbo.usp_StoredProcedure(
                @parameterValue1 smallint
              , @parameterValue2 nvarchar(300)
              , @debug           bit = 0
  <summary>Simple example of tsql procedure</summary>
  <author>Konstantin Taranov</author>
  <modified>2019-11-25 by Konstantin Taranov</modified>
  <example1>EXECUTE dbo.usp_StoredProcedure
        @parameterValue1 = 0
      , @parameterValue2 = N'BULK'</example1>
    IF (@parameterValue1 < 0 OR @parameterValue2 NOT IN ('SIMPLE', 'BULK', 'FULL'))
    RAISERROR('Not valid data parameter!', 16, 1);
    IF (@debug) PRINT @parameterValue2;
    /* Print error information. */    PRINT 'Error: '       + CAST(ERROR_NUMBER()) AS varchar(50)) +
          ', Severity: '  + CAST(ERROR_SEVERITY(), varchar(5))   +
          ', State: '     + CAST(ERROR_STATE(), varchar(5) )     +
          ', Procedure: ' + COALESCE(ERROR_PROCEDURE(), '-')     +
          ', Line: '      + CAST(ERROR_LINE(), varchar(5))       +
          ', User name: ' + CAST(ORIGINAL_LOGIN(), sysname);


Dynamic T-SQL Recommendation

It is highly recommended that you read an awesome detailed article about dynamic T-SQL by Erland Sommarskog: The Curse and Blessings of Dynamic SQL

Dynamic SQL is a programming technique that allows you to construct SQL statements dynamically at runtime. It allows you to create more general purpose and flexible SQL statement because the full text of the SQL statements may be unknown at compilation. For example, you can use the dynamic SQL to create a stored procedure that queries data against a table whose name is not known until runtime.

More details here.

  • Do not use nvarchar(max) for your object’s name parameter, use sysname instead (synonym for nvarchar(128) except that, by default, sysname is NOT NULL).
    /* Bad */DECLARE @tableName nvarchar(max) = N'MyTableName';
    /* Good */DECLARE @tableName sysname = N'MyTableName';
  • Do quote the names of your objects properly.
    /* Bad */DECLARE @tsql      nvarchar(max);
    DECLARE @tableName sysname = N'My badly named table!';
    SET @tsql = N'SELECT object_id FROM ' + @tableName;
    /* Good */DECLARE @tsql      nvarchar(max);
    DECLARE @tableName sysname = N'My badly named table 111!';
    SET @tsql = N'SELECT object_id FROM ' + QUOTENAME(@tableName);
  • Always use sp_executesql instead EXEC to prevent sql injection. Also, sp_executesql can parameterize your dynamic statement that means plans can be reused as well (when the value of the dynamic object is the same). Also sp_executesql can even be used to output values as well (see example below).
    /* Bad EXEC example with sql injection*/DECLARE @tsql      nvarchar(max);
    DECLARE @tableName sysname = N'master.sys.tables; SELECT * FROM master.sys.server_principals;';
    SET @tsql = N'SELECT "name" FROM ' + @tableName + N';';
    EXEC (@tsql);
    /* Good sp_executesql example*/DECLARE @tsql      nvarchar(max);
    DECLARE @tableName sysname = N'master.sys.tables';
    DECLARE @id        int     = 2107154552;
    SET @tsql = N'SELECT name FROM '   + @tableName +
                N' WHERE object_id = ' + CONVERT(nvarchar(max), @id);
    EXEC sp_executesql @tsql, N'@ID int', @ID = @id;
    /* Good sp_executesql example with OUTPUT */DECLARE @tsql      nvarchar(max);
    DECLARE @tableName sysname = N'master.sys.tables';
    DECLARE @count     bigint;
    SET @tsql = N'SELECT @countOUT = COUNT(*) FROM ' + @tableName + N';';
    EXEC sp_executesql @tsql, N'@countOUT bigint OUTPUT', @countOUT = @count OUTPUT;
    PRINT('@count = ' + CASE WHEN @count IS NULL THEN 'NULL' ELSE CAST(@count AS varchar(30)) END);
  • Do not use dynamic T-SQL if your statement is not dynamic.
    /* Bad */DECLARE @tsql nvarchar(max);
    DECLARE @id   int = 2107154552;
    SET @tsql = N'SELECT object_id, "name" FROM master.sys.tables WHERE object_id = ' + CAST(@id AS nvarchar(max));
    EXEC sp_executesql @tsql;
    /* Good */DECLARE @id int = 2107154552;
    SELECT object_id, "name" FROM master.sys.tables WHERE object_id = @id;
  • Do not debug the code that creates the dynamic T-SQL first, debug the generated T-SQL statement instead. Use @debug variable to print (or a SELECT statement if your dynamic T-SQL is over 4000 characters) dynamic statement instead executing it. See example below.
  • Do take the time to format your dynamic T-SQL.
    /* Bad @tsql formating */DECLARE @tsql  nvarchar(max);
    DECLARE @sep   nvarchar(30) = ' UNION ALL ';
    DECLARE @debug bit          = 1;
    SELECT @tsql = COALESCE(@tsql, N'') +
                   N'SELECT N' + QUOTENAME(name,'''') +
                   N' AS DBName, (SELECT COUNT(*) FROM ' +
                   QUOTENAME(name) + N'.sys.tables) AS TableCount' +
    FROM sys.databases
    ORDER BY name;
    SET @tsql = LEFT(@tsql, LEN(@tsql) - LEN(@sep));
    IF @debug = 1 SELECT @tsql AS "tsql" ELSE EXEC sp_executesql @tsql;
    /* Good @tsql formating */DECLARE @tsql  nvarchar(max);
    DECLARE @sep   nvarchar(30) = ' UNION ALL ';
    DECLARE @debug bit          = 1;
    DECLARE @crlf  nvarchar(10) = NCHAR(13) + NCHAR(10);
    SELECT @tsql = COALESCE(@tsql, N'') + @crlf +
                   N'SELECT N' + QUOTENAME(name,'''') + N' AS DBName' + @crlf +
                   N'     , (SELECT COUNT(*) FROM ' + QUOTENAME(name) + N'.sys.tables) AS TableCount' + @crlf +
    FROM sys.databases
    ORDER BY name;
    SET @tsql = LEFT(@tsql, LEN(@tsql) - LEN(@sep)) + N';';
    IF @debug = 1 SELECT @tsql AS "tsql" ELSE EXEC sp_executesql @tsql;

Official Reference and useful links


4.67 (9)

You rated this post out of 5. Change rating




4.67 (9)

You rated this post out of 5. Change rating