SQL Server Соглашение по именованию и T-SQL стиль программирования

Соглашение об именах - это набор правил для выбора последовательности символов, которая будет использоваться для идентификаторов, которые обозначают переменные, типы, функции и другие объекты в исходном коде и документации.

Причины использования соглашения об именах (в отличие от разрешения программистам выбирать любую последовательность символов) включают следующее:

Содержание

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
Database Trigger PascalCase 50 No DTR_ No Yes [A-z] DTR_CheckLogin
Schema lowercase 30 No No No Yes [a-z][0-9] myschema
File Table PascalCase 128 No FT_ No Yes [A-z][0-9] FT_MyTable
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
Table U PascalCase 128 No No No Yes [A-z][0-9] MyTable
Table Column PascalCase 128 No No No Yes [A-z][0-9] MyColumn
Table Default Values D PascalCase 128 No DF_ No Yes [A-z][0-9] DF_MyTable_MyColumn
Table Check Column Constraint C PascalCase 128 No CK_ No Yes [A-z][0-9] CK_MyTable_MyColumn
Table Check Table Constraint C PascalCase 128 No CTK_ No Yes [A-z][0-9] CTK_MyTable_MyColumn_AnotherColumn
Table Primary Key PK PascalCase 128 No PK_ No Yes [A-z][0-9] PK_MyTableID
Table 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
Table Trigger TR PascalCase 128 No TR_ No Yes [A-z][0-9] TR_MyTable_LogicalName
View V PascalCase 128 No VI_ No No [A-z][0-9] VI_LogicalName
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

SQL Server Data Types Recommendation

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

General Type Type Recommended What use instead Why use or not
Exact Numerics bit Maybe tinyint
Exact Numerics tinyint Maybe int
Exact Numerics smallint Maybe int
Exact Numerics int Yes -
Exact Numerics bigint Yes int
Exact Numerics decimal Yes -
Exact Numerics smallmoney No decimal possibility to loss precision due to rounding errors
Exact Numerics money No decimal possibility to loss precision due to rounding errors
Approximate Numerics real Yes -
Approximate Numerics float Yes -
Date and Time date Yes -
Date and Time smalldatetime Maybe date
Date and Time time Yes -
Date and Time datetime2 Yes -
Date and Time datetime No datetime2
Date and time datetimeoffset Yes -
Character Strings char Maybe
Character Strings varchar Yes varchar
Character Strings varchar(max) Yes -
Character Strings nchar Maybe nvarchar
Character Strings nvarchar Yes -
Character Strings nvarchar(max) Yes -
Character Strings ntext Deprecated nvarchar(max)
Character Strings text Deprecated nvarchar(max)
Binary Strings image Deprecated nvarchar(max)
Binary Strings binary Deprecated nvarchar(max)
Binary Strings varbinary Yes -
Binary Strings varbinary(max) Yes -
Other Data Types cursor Maybe -
Other Data Types sql_variant No varchar?
Other Data Types hierarchyid Maybe -
Other Data Types rowversion Maybe -
Other Data Types timestamp Deprecated rowversion it is just synonym to rowversion data type
Other Data Types uniqueidentifier Yes -
Other Data Types xml Yes -
Other Data Types table Maybe -
Spatial Data Types geometry Yes -
Spatial Data Types geography Yes -

T-SQL Programming Style

SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines

General programming style

Example:

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;

Stored procedures and functions programming style

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
)
/*
EXECUTE dbo.usp_StoredProcedure
        @parameterValue1 = 0
      , @parameterValue2 = N'BULK'
*/
AS
SET NOCOUNT ON;

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

BEGIN CATCH
    -- Print error information. 
    PRINT 'Error: '       + CONVERT(varchar(50), ERROR_NUMBER())   +
          ', Severity: '  + CONVERT(varchar(5),  ERROR_SEVERITY()) +
          ', State: '     + CONVERT(varchar(5),  ERROR_STATE())    +
          ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-')         +
          ', Line: '      + CONVERT(varchar(5),  ERROR_LINE())     +
          ', User name: ' + CONVERT(sysname,     CURRENT_USER);
    PRINT ERROR_MESSAGE();
END CATCH;

GO

Official Reference and useful links