Skip to content

Latest commit

 

History

History
764 lines (685 loc) · 62.6 KB

SQL Server Name Convention and T-SQL Programming Style.md

File metadata and controls

764 lines (685 loc) · 62.6 KB
Raw

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:

  • 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.

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

⬆ back to top

T-SQL Programming Style

SQL Server 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 hardcode server and database names in your code: 
    /* good */
CREATE TABLE dbo.MyTable (MyColumn int);

/* bad*/
CREATE TABLE PRODSERVER.PRODDB.dbo.MyTable (MyColumn int);
CREATE TABLE MyTable (MyColumn int);
    More details here, here, here, here, here, here.
  • Delimiters: spaces (not tabs).
  • Never use asterisk (*) in select statements SELECT * and INSERT statements, use explicit column names. Main problems are: traffic issues, Memory Grants issues, Index usage issues. Only one exception, see it below. More details here, here, here, here.
  • Use asterisk (*) only in an archiving situation, where rows are being moved to another table that must have the same structure. 
    INSERT INTO SalesOrderArchive  /* Note no column list */
SELECT *
FROM SalesOrder
WHERE OrderDate < @OneYearAgo;
 
DELETE FROM SalesOrder
WHERE OrderDate < @OneYearAgo;
    If a new column is added to SalesOrder table in the future, but not to SalesOrderArchive, the INSERT will fail. Which sounds bad, but it's actually a really good thing! Because the alternative is much worse. If all the columns were listed on the INSERT and the SELECT, then the INSERT would succeed, and so would the following DELETE (which is effectively DELETE *). Production code that succeeds doesn't get any attention, and it may be a long time before someone notices that the new column is not being archived, but being silently deleted altogether. 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 CONVERT, COALESE 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. From Transact-SQL Syntax Conventions (Transact-SQL):

    Although the semicolon isn't required for most statements in this version of SQL Server, it will be required in a future version. More details here, here, and here.

  • All script files should end with GO and line break. This is neccesary for batching scripts run throw sqlcmd or another tools.
  • Keywords should be in UPPERCASE: SELECT, FROM, GROUP BY etc. This increases the readability of the code.
  • Data types declaration should be in lowercase: varchar(30), int, real, nvarchar(max) etc. More details here.
  • All system database and tables must be in lowercase for properly working for Case Sensitive instance: master, sys.tables ….
  • Do not use nested transactions. The commit of a nested transaction has absolutely no effect – as the only transaction that really exists as far as SQL Server is concerned is the outer one. More details here.
  • Whenever you have data modification on non-temporary tables, is to use explicit transactions over autocommit.
    1. If you have a stored procedure which is simply running a SELECT statement, use autocommit.
    2. If you have a stored procedure which performs data modification on non-temporary tables, use an explicit transactions only over the area which modifies data.
    3. If you are working with non-global temporary tables beforehand, don’t include any modification of those inside the explicit transaction.
    4. In a loop, choose whether you want to put the explicit transactions around the loop or inside it. In most cases, prefer to put the transaction inside the loop to minimize the amount of time that blocking other users.
    5. Outside of a stored procedure use explicit transactions if you’re doing something potentially risky.
    6. Watch out for nested transactions. In SQL Server, there’s very little utility in them and their behavior is weird. Paul Randal explains in great detail just how broken they are. More details here.
  • Avoid using Cross-Database Queries because it increase backup/restore complexity (you restore one database, then realise you don’t have log backups to bring the other database to the same point in time). Also Azure SQL Database does not support cross-database queries and you can not migrate into in future.
  • Use temp tables to reduce network trafic, decrease query complexity and also to get better estimates for modification queries. More details here. INFORMATION_SCHEMA views only represent a subset of the metadata of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.
  • When more than one logical operator is used always use parentheses, even when they are not required. This can improve the readability of queries, and reduce the chance of making a subtle mistake because of operator precedence. There is no significant performance penalty in using parentheses. More details here. 
    SELECT
    ProductID
FROM Production.Product
WHERE (ProductModelID = 20 OR ProductModelID = 21)
    AND Color = 'Red';
  • Always use aliases for table names. More details here.
  • Avoid non-standard column and table aliases, use, if required, double-quotes for special characters and always AS keyword before alias:
SELECT
       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:

     SELECT
     FirstName

  • Arguments are divided by line breaks, commas should be placed before an argument:

    SELECT
     FirstName
   , 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 wrapping functions around columns specified in the WHERE and JOIN clauses. Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • Use NULL or NOT NULL for each column in a temporary table. The [ANSI_NULL_DFLT_ON] option control the way the Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure. [ANSI_NULL_DFLT_ON]:https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-null-dflt-on-transact-sql

  • Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Be aware that in Transact-SQL, NULL is not an empty or "nothing" value. It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

  • 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.

  • Use LOOP JOIN and FAST 1 query hints for deleting huge number of rows with ON DELETE CASCADE foreign keys specification. More details here.

  • If you use [hints] always use it with WITH keyword because omitting the WITH keyword is a deprecated feature and will be removed from future Microsoft SQL Server versions. One benefit of using the WITH keyword is that you can specify multiple table hints using the WITH keyword against the same table. 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 syntaxes. 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 INNER, LEFT OUTER, RIGHT OUTER, FULL 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.

  • For scalar function use WITH SCHEMABINDING option to get a performance boost. More details here

  • Do not use INFORMATION_SCHEMA views to determine the schema of an object. INFORMATION_SCHEMA views only represent a subset of the metadata of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view. More details here.

  • Do not use explicit transactions for DML and DDL especially for reorganize index because the locking behavior of this statemnets becomes more restrictive. More details here.

  • In sqlcmd move the -Uand -P parameters after the -Q parameter so that the password is at the end of the command. More details here

    sqlcmd -S MSSQLSERVER$EXPRESS -Q "dbcc checkdb ('master') with DATA_PURITY, NO_INFOMSGS;" -U maintenanceUser -P ""weirdPassword

  • 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:
    /* good */
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 varchar, nvarchar, char, nchar:

     /* bad */
 DECLARE @myBadVarcharVariable  varchar;
 DECLARE @myBadNVarcharVariable nvarchar;
 DECLARE @myBadCharVariable     char;
 DECLARE @myBadNCharVariable    nchar;
 
 /* good */
 DECLARE @myGoodVarchareVariable  varchar(50);
 DECLARE @myGoodNVarchareVariable nvarchar(90);
 DECLARE @myGoodCharVariable      char(7);
 DECLARE @myGoodNCharVariable     nchar(10);

    More details here and 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.

  • Always use IF statement with BEGIN-END block to prevent errors with multi line statements:

    DECLARE @x int = 0;
DECLARE @y int = 1;

/* bad */
IF @y > @x
SET @x = @x + 1;
SET @y = @y - 1;
ELSE
PRINT(1);
/* Msg 156, Level 15, State 1, Line 8
   Incorrect syntax near the keyword 'ELSE'. */

/* good */
IF @y > @x
BEGIN
   SET @x = @x + 1;
   SET @y = @y - 1;
END;
ELSE
BEGIN
    PRINT(1);
END;

  • 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)

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

  • All stored procedures and functions should use ALTER statement and start with the object presence check (see example below) for saving GRANTs on your object. Also if you use Query Store and plan forcing and DROP and then CREATE new object you loosing plan forcing, more details here. For SQL Server 2016 and higher you can use new CREATE OR ALTER statement.
  • 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 and 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.
  • Call natively-compiled stored procedures without named parameters. More details here.
  • All code should be self documenting
  • T-SQL code, triggers, stored procedures, functions, scripts, should have a standard comment-documentation banner:
<documentation>
  <summary>Get all databases meta data using dynamic T-SQL</summary>
  <returns>1 data set: temp table #DatabaseInfo.</returns>
  <issues>No</issues>
  <author>Konstantin Taranov</author>
  <created>2018-03-01</created>
  <modified>2019-11-14 by Konstantin Taranov</modified>
  <version>1.2</version>
  <sourceLink>https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Databases_Report.sql</sourceLink>
</documentation>

⬆ 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.

  • 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 parameterizing 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' +
               @sep
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 +
               @sep
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;

⬆ back to top

Official Reference and useful links

⬆ back to top

[hints]https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql