Соглашение об именах - это набор правил для выбора последовательности символов, которая будет использоваться для идентификаторов, которые обозначают переменные, типы, функции и другие объекты в исходном коде и документации.
Причины использования соглашения об именах (в отличие от разрешения программистам выбирать любую последовательность символов) включают следующее:
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 |
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 | - |
SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines
SELECT *
, use explicit column names. More details here[]
and reserved words in object names and alias, use only Latin symbols [A-z]
and numeric [0-9]
;
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 hereGO
and line breakAvoid non-standard column aliases, use ,if required, double-quotes and always AS
keyword: SELECT p.LastName AS "Last Name" FROM dbo.Person AS p;
More details here.
All possible ways using aliases in SQL Server:
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;
/* Below recommended due to ANSI
SELECT Schema_Name(schema_id)+'.'+[name] AS "Tables" FROM sys.tables;
SELECT
expression should be on the same line with it: SELECT LastName …
Arguments are divided by line breaks, commas should be placed before an argument:
SELECT FirstName
, LastName
FETCH-OFFSET
instead TOP
. But if you use TOP
avoid use TOP
in a SELECT
statement without an ORDER BY
. More details hereTOP
function with brackets because TOP
has supports use of an expression, such as (@Rows*2)
, or a subquery: SELECT TOP(100) LastName …
.
More details here. Also TOP
without brackets does not work with UPDATE
and DELETE
statements.TOP(100)
or lower value because SQL Server SQL Server uses one sorting method for TOP 1-100 rows, and a different one for 101+ rows
More details hereFROM, WHERE, INTO, JOIN, GROUP BY, ORDER BY
expressions should be aligned so, that all their arguments are placed under each other (see Example below)FROM dbo.Table
. For stored procedure more details heremaster, sys.tables …
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'
ELSE NULL
END
More details here
INSERT INTO
a permanent table with ORDER BY
, more details herewk, yyyy, d
etc.) with date/time operations, use full names: month, day, year
. More details hereCAST('yyyymmdd' AS DATE)
formatWHERE
clause. More details hereOPTION(RECOMPILE)
if needed. More details hereSELECT…INTO
for production code, use instead CREATE TABLE
+ INSERT INTO …
approach. More details here=*
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 hereJOIN
condition, WHERE
search condition, or in a SELECT
list, unless the function is schema-bound.
More details hereEXISTS
or NOT EXISTS
if referencing a subquery, and IN
or NOT IN
when have a list of literal values
More details hereN
;NVARCHAR(MAX)
;NVARCHAR(MAX)
.
Example: SET @NVCmaxVariable = CONVERT(NVARCHAR(MAX), N'anything') + N'something else' + N'another';
More details hereNVARCHAR
or VARCHAR
: DECLARE @myGoodVariable VARCHAR(50);
and not DECLARE @myBadVariable VARCHAR;
.
More details hereExample:
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;
ALTER
statement and start with the object presence checkALTER
statement should be preceded by 2 line breaksALTER
statement and before AS keyword should be placed a comment with execution examplesp_
procedures only in master
database - SQL Server will always scan through the system catalog firstBEGIN TRY
and BEGIN CATCH
/* */
instead in-line comment --
SET NOCOUNT ON;
for stops the message that shows the count of the number of rows affected by a Transact-SQL statement. More details hereSET NOCOUNT OFF;
(because it is default behavior)RAISERROR
instead PRINT
if you want to give feedback about the state of the currently executing SQL batch without lags. More details here and hereTOP
expression with ()
:
-- Not working without ()
DECLARE @n int = 1;
SELECT TOP@n name FROM sys.objects;
summary: >
This procedure returns an object build script as a single-row, single column
result.
Revisions:
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