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

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.

Reasons for using a naming convention (as opposed to allowing programmers to choose any character sequence) include the following:

Table of Contents

SQL Server Object Name Convention

Object Code Notation Length Plural Prefix Suffix Abbreviation Char Mask Example
Database UPPERCASE 30 No No No Yes [A-z] MYDATABASE
Schema lowercase 30 No No No Yes [a-z][0-9] myschema
Global Temporary Table PascalCase 117 No No No Yes ##[A-z][0-9] ##MyTable
Local Temporary Table PascalCase 116 No No No Yes #[A-z][0-9] #MyTable
File Table PascalCase 128 No FT_ No Yes [A-z][0-9] FT_MyTable
Memory-optimized SCHEMA_AND_DATA Table PascalCase 128 No MT_ _SD Yes [A-z][0-9] MT_MyTable_SD
Memory-optimized SCHEMA_ONLY Table PascalCase 128 No MT_ _SO Yes [A-z][0-9] MT_MyTable_SO
Temporal Table PascalCase 128 No No _TT Yes [A-z][0-9] MyTable_TT
Disk-Based Table U PascalCase 128 No No No Yes [A-z][0-9] MyTable
Disk-Based Wide Table - SPARSE Column U PascalCase 128 No No _SPR Yes [A-z][0-9] MyTable_SPR
Table Column PascalCase 128 No No No Yes [A-z][0-9] MyColumn
Table Column SPARSE PascalCase 128 No No _SPR Yes [A-z][0-9] MyColumn_SPR
Columns Check Constraint C PascalCase 128 No CTK_ No Yes [A-z][0-9] CTK_MyTable_MyColumn_AnotherColumn
Column Check Constraint C PascalCase 128 No CK_ No Yes [A-z][0-9] CK_MyTable_MyColumn
Column Default Values D PascalCase 128 No DF_ No Yes [A-z][0-9] DF_MyTable_MyColumn
Table Primary Key PK PascalCase 128 No PK_ No Yes [A-z][0-9] PK_MyTableID
Table Unique (Alternative) Key UQ PascalCase 128 No AK_ No Yes [A-z][0-9] AK_MyTable_MyColumn_AnotherColumn
Table Foreign Key F PascalCase 128 No FK_ No Yes [A-z][0-9] FK_MyTable_ForeignTableID
Table Clustered Index PascalCase 128 No IXC_ No Yes [A-z][0-9] IXC_MyTable_MyColumn_AnotherColumn
Table Non Clustered Index PascalCase 128 No IX_ No Yes [A-z][0-9] IX_MyTable_MyColumn_AnotherColumn
DDL Trigger TR PascalCase 128 No TR_ _DDL Yes [A-z][0-9] TR_LogicalName_DDL
DML Trigger TR PascalCase 128 No TR_ _DML Yes [A-z][0-9] TR_MyTable_LogicalName_DML
Logon Trigger TR PascalCase 128 No TR_ _LOG Yes [A-z][0-9] TR_LogicalName_LOG
View V PascalCase 128 No VI_ No No [A-z][0-9] VI_LogicalName
Indexed View V PascalCase 128 No VIX_ No No [A-z][0-9] VIX_LogicalName
Statistic PascalCase 128 No ST_ No No [A-z][0-9] ST_MyTable_MyColumn_AnotherColumn
Stored Procedure P PascalCase 128 No usp_ No No [A-z][0-9] usp_LogicalName
Scalar User-Defined Function FN PascalCase 128 No udf_ No No [A-z][0-9] udf_FunctionLogicalName
Table-Valued Function FN PascalCase 128 No tvf_ No No [A-z][0-9] tvf_FunctionLogicalName
Synonym SN camelCase 128 No sy_ No No [A-z][0-9] sy_logicalName
Sequence SO PascalCase 128 No sq_ No No [A-z][0-9] sq_TableName
CLR Assembly PascalCase 128 No CA No Yes [A-z][0-9] CALogicalName
CLR Stored Procedures PC PascalCase 128 No pc_ No Yes [A-z][0-9] pc_CAName_LogicalName
CLR Scalar User-Defined Function PascalCase 128 No cudf_ No No [A-z][0-9] cudf_CAName_LogicalName
CLR Table-Valued Function PascalCase 128 No ctvf_ No No [A-z][0-9] ctvf_CAName_LogicalName
CLR User-Defined Aggregates PascalCase 128 No ca_ No No [A-z][0-9] ca_CAName_LogicalName
CLR User-Defined Types PascalCase 128 No ct_ No No [A-z][0-9] ct_CAName_LogicalName
CLR Triggers PascalCase 128 No ctr_ No No [A-z][0-9] ctr_CAName_LogicalName
Linked Server PascalCase 128 No ls_ No No [A-z][0-9] ls_LogicalName

⬆ back to top

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 Type Type ANSI Recommended What use instead Why use or not
Exact Numerics bit No Maybe tinyint bit convert any number (except 0) to 1, 0 converted to 0
Exact Numerics tinyint No Maybe int for saving 3 bytes compare to int data type or for replacing bit data type
Exact Numerics smallint Yes Maybe int for saving 2 bytes compare to int data type
Exact Numerics int Yes Yes -
Exact Numerics bigint No Yes int if you work more than 2^31 numbers.
Exact Numerics decimal Yes Yes -
Exact Numerics smallmoney No Maybe decimal possibility to loss precision due to rounding errors
Exact Numerics money No Maybe decimal possibility to loss precision due to rounding errors
Approximate Numerics real Yes Yes -
Approximate Numerics float(1-24) Yes No real SQL Server automatically converts float(1-24) to real data type
Approximate Numerics float(24-53) Yes Yes -
Date and Time date Yes Yes -
Date and Time smalldatetime No Maybe date
Date and Time time Yes Yes -
Date and Time datetime2 No Yes -
Date and Time datetime Yes Maybe datetime2 On the Advantages of DateTime2(n) over DateTime
Date and time datetimeoffset No Yes -
Character Strings char Yes Maybe varchar Save 1 byte from varchar, but be ready for trailing spaces
Character Strings varchar Yes Yes -
Character Strings varchar(max) Yes Yes -
Character Strings nchar Yes Maybe nvarchar
Character Strings nvarchar Yes Yes -
Character Strings nvarchar(max) Yes Yes -
Character Strings ntext No Deprecated nvarchar(max) NVARCHAR(MAX) VS NTEXT in SQL Server
Character Strings text No Deprecated varchar(max) Differences Between Sql Server TEXT and VARCHAR(MAX) Data Type
Binary Strings image No Deprecated varbinary(max) VARBINARY(MAX) Tames the BLOB
Binary Strings binary Yes Deprecated varbinary Conversions between any data type and the binary data types are not guaranteed
Binary Strings varbinary Yes Yes -
Binary Strings varbinary(max) Yes Yes -
Other Data Types cursor No Yes -
Other Data Types sql_variant No Yes -
Other Data Types hierarchyid No Yes -
Other Data Types rowversion No Maybe -
Other Data Types timestamp No Deprecated rowversion it is just synonym to rowversion data type and must be removed
Other Data Types uniqueidentifier No Yes -
Other Data Types xml Yes Yes -
Other Data Types table No Maybe -
Spatial Data Types geometry No Yes -
Spatial Data Types geography No Yes -

⬆ back to top

SQL Server Function Recommendations

This is only recommendations! But it is consistent for choosing only 1 function from possibles alterntives and use only it.

Not Recommended Recommended When and Why More details
!= <> <> is ANSI , != not ANSI, <> and != are identical 13
CONVERT CAST CAST is ANSI 14,15
ISNULL COALECSE COALECSE is ANSI and supports more than two arguments, ISNULL has dangerous behaviour with possibility to implicit triming string 16,17
DATEDIFF DATEADD The predicate MyDateTime < DATEADD(SECOND, -1, GETUTCDATE()) syntax is SARGable 18,19
SELECT SET Using SET (is ANSI ) instead of SELECT when assigning variables due to properly work with Msg 501 Subquery returned more than 1 value 20,21,22
STR CAST STR is not ANSI , extremly slow, don't use more than 15 digits, and has rounding problem - use CAST plus concatenate instead STR 23
ISNUMERIC TRY_CONVERT ISNUMERIC can often lead to data type conversion errors, when importing data. For SQL Server below 2012 use WHERE with LIKE. 24
GETDATE [SYSUTCDATETIME] Daylight Saving Time and other factors can play havoc with our dates and times, rounding to the nearest 3 milliseconds. 25
GETUTCDATE [SYSUTCDATETIME] Daylight Saving Time and other factors can play havoc with our dates and times, rounding to the nearest 3 milliseconds. 25
SYSDATETIME [SYSUTCDATETIME] Daylight Saving Time and other factors can play havoc with our dates and times, rounding to the nearest 3 milliseconds. 25

⬆ back to top

T-SQL Programming Style

SQL Server T-SQL Coding Conventions, Best Practices, and Programming Guidelines.

General programming T-SQL style

TSQL Example with formating:

WITH CTE_MyCTE AS (
    SELECT
        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
)
SELECT
    t1.Value1 AS Val1
    , t1.Value2 AS Val2
    , t2.Value3 AS Val3
INTO #Table3
FROM CTE_MyCTE AS t1
ORDER BY t2.Value2;

⬆ back to top

Stored procedures and functions programming style

Recommendations from Microsoft: Stored procedure Best practice

⬆ back to top

Stored Procedure Example:

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

ALTER PROCEDURE dbo.usp_StoredProcedure(
      @parameterValue1 smallint
    , @parameterValue2 nvarchar(300)
    , @debug           bit = 0
)
/*
<documentation>
    <summary>Simple example of tsql procedure</summary>
    <returns>nothing</returns>
    <issues>No</issues>
    <author>Konstantin Taranov</author>
    <created>2019-01-01</created>
    <modified>2019-11-25 by Konstantin Taranov</modified>
    <version>1.2</version>
    <sourceLink>-</sourceLink>
    <example1>EXECUTE dbo.usp_StoredProcedure
          @parameterValue1 = 0
        , @parameterValue2 = N'BULK'</example1>
</documentation>
*/
AS
SET NOCOUNT ON;

BEGIN TRY
    IF (@parameterValue1 < 0 OR @parameterValue2 NOT IN ('SIMPLE', 'BULK', 'FULL'))
    RAISERROR('Not valid data parameter!', 16, 1);
    IF (@debug) PRINT @parameterValue2;
END TRY

BEGIN CATCH
    /* 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);
    PRINT ERROR_MESSAGE();
END CATCH;
GO

⬆ back to top

Dynamic T-SQL Recommendation

Highly recommended to read 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.

⬆ back to top

Official Reference and useful links

⬆ back to top