T-SQL coding standard

1. Overview

1.1. Basic Principles
Write SQL statements in both upper and lower case.

Use the Unicode data type whenever possible.

Use connections instead of subqueries or nested queries.

Use parameterized SQL queries instead of statements to splice SQL queries.

You cannot use [pinyin] + [English] To name SQL objects or variables.

Use stored procedures instead of SQL statements as much as possible.

1.2. basic specifications
We recommend that you use the Pascal style or camel style to name database objects.

All keywords, predicates, and system functions of the capital T-SQL language.

2. Naming rules
Generally, database objects are named using Pascal or camel styles to access data generated by ORM tools during database application development.CodeIt complies with the naming rules of the program development language (such as C #) without adjustment. In addition, relational databases are increasingly closely integrated with XML, and standardized naming is becoming increasingly important.

In the actual database development process, if the demand side has already provided a database design scheme, it is recommended that the provided scheme prevail. During the upgrade and development of the original database, where feasible, appropriate design adjustments can be made to comply with programming specifications.

1.3. object naming
1.3.1. Database
The first method is to use the Pascal style name in the format of [project name in English].

Example: adventureworks

The second method is to use the Pascal style name. The naming format is [project English name] + dB.

Example: adventureworksdb

Biztalkruleenginedb

The first method is recommended.

1.3.2. Database Files
Data File: [database name] + _ data. MDF

Log File: [database name] + _ log. LDF

Example: adventureworks_data.mdf

Adventureworks_log.ldf

1.3.3. Relational Data Warehouse
Use the Pascal style name. The name format is [project English name] + DW.

Example: adventureworksdw

1.3.4. Data Architecture
In addition to the data architecture defined by the SQL server system, the new architecture is named in Pascal format, in the format of [architecture name].

Example: HumanResources

Production

Classify database objects such as table, view, procedure, and function using the data architecture. In SQL Server 2000, DBO is the default architecture.

1.3.5. Data Table
The name is in the [Table name] format.

Example: Employee

Product

The table name is named in English singular. It is mainly referred to the SQL Server 2005 sample database. In my personal understanding, it is better to use the ORM tool to generate code that complies with programming specifications (such as C #).

Example: Use Product

Instead of Products

1.3.6. Data View
The view name is named in Pascal format. The format is V + [view name].

Example: vemployee

Vsalesperson

1.3.7. Data Column
The column name is abbreviated to an English word. An English word only comes from a specific business definition and should be clearly expressed as much as possible. The name is in the [column name] format.

Example: addressid

Postalcode

Do not use Pinyin for naming. If it is unavoidable, use Pinyin for short column names. If the pinyin column name is complex, use the first word in full spelling, other words are capitalized.

Example: Ningbo

Business Method jingyfs

1.3.8. Stored Procedure
We recommend that you use the Pascal style name in the format of [stored procedure name].

Example: getuser

Adduser

Note: Use the camel style name in the SQL Server 2005 sample database.

1.3.9. Functions
The user-defined function is named in Pascal format. The name format is [function name], and all system functions are capitalized.

Example: Select isnull (@ lastname, 'unknown last name ');

Getdate ()

1.3.10. User-Defined Data Types
Use the Pascal style name. The name format is [custom data type name].

Example: Flag

Namestyle

1.3.11. DML triggers
DML triggers are operations performed when a data operation language (DML) event occurs on the database server. DML events include update, insert, or delete statements sent to tables or views. Prefix is used for distinguishing based on different naming rules of events. The format is [u | I | D] + [Table Name | view name].

Example: uemployee

Iemployee

Demployee

Another method is,

After trigger: TR _ TABLE name_[ insert I after, modify and add U, delete and add D].

Instead of trigger: TR _ table name or view name _ of [insert I, modify and add U, delete and add D]

1.3.12. DDL triggers
Triggered by responding to various Data Definition Language (DDL) events. These events mainly correspond to the transact-SQL statements starting with "CREATE", "alter", and "Drop. System stored procedures that execute DDL operations can also trigger DDL triggers.

The name follows the camel style and the name word can describe the DDL trigger function.

Example:

Create trigger safety

On database

For drop_table, alter_table

As

Print 'you must disable trigger "safety" to drop or alter tables! '

Rollback;

Add a DDL prefix,

Example:

Create trigger [ddldatabasetriggerlog]

On database

For ddl_database_level_events

As

1.3.13. Primary Key, foreign key relationship, and index
Primary Key: pK _ [Table name] _ [primary key]; if it is a combination of primary keys, use pK _ [Table name] _ [primary key 1] _ [primary key 2].

Example: pk_store_customerid

Pk_storecontact_customerid_contactid

Foreign key relationship: FK _ [slave table name] _ [master table name] _ [foreign key column name].

Example: fk_storecontact_store_customerid

Clustered index: pK _ [Table name] _ [primary key]; if it is a combination of primary keys, use pK _ [Table name] _ [primary key 1] _ [primary key 2].

Example: pk_store_customerid

Pk_storecontact_customerid_contactid

Unique non-clustered index: AK _ [Table name] _ [column name].

Example: ak_store_rowguid

Non-unique non-clustered index: pK _ [Table name] _ [column name].

Example: ix_store_salespersonid

Primary XML index: pxml _ [Table name] _ [XML type column name].

Example: pxml_store_demographics

NOTE: For the above naming conventions, refer to the SQL Server 2005 sample database. Generally, you only need the designer to automatically generate the database without any additional modifications.

1.4. Parameter naming
1.4.1. Data Column Parameters
The name format is @ + [column name].

Example: @ employeeid

When the column name does not conform to the Pascal style (legacy systems), for example, use all uppercase column names, or use "_" to connect field names, the parameter name is defined using @ + [column name]. The column name here should be in the Pascal style as much as possible.

1.4.2. Non-data Column Parameters
When a parameter cannot be associated with a column name, use a combination of English words or words that reflect the function of this parameter, and use the Pascal style name.

Example: @ errorid

@ Flag

1.5. common naming
1.5.1. Names of common fields
Common fields here refer to the names or column names frequently used during table creation. The following table defines frequently used fields,

Column name data type description

Createddate datetime records the creation date, which is generally automatically generated using getdate ()

Modifieddate datetime records the Last modified Date, the first use of getdate ()

Deleteddate datetime record Delete (Mark delete) Date

Startdate datetime start date

Enddate datetime end date

Starttime datetime Start Time

Endtime datetime End Time

Rowguid uniqueidentifier: The rowguidcol number that uniquely identifies a row. It is used to support merging and copying.

Id int uses ID instead of ID or ID. Generally, it is an auto-increment primary key column.

Parentid int parent ID

Status int status

3. Write SQL statements
3.1. Case
All keywords, predicates, and system functions of the capital T-SQL language. The variable name and cursor name use the Pascal style. Data types are defined in lower case.

Example: declare @ lastname nvarchar (32 );

3.2. Use ";"
Use ";" as the terminator of the transact-SQL statement. Although a semicolon is not required, it is a good habit to use it.

Example:

Use adventureworks;

Go

Declare @ find varchar (30 );

Set @ find = 'man % ';

Select lastname, firstname, phone

From person. Contact

Where lastname like @ find;

3.3. Storage Format
Use Unicode data storage formats to improve portability and compatibility. In actual applications, use nchar, nvarchar, and ntext instead of char, varchar, and text.

3.4. Type Selection
If the character has a clear length, use nchar instead of nvarchar; char instead of varchar.

When there are only two possible values, use bit instead of Int or smallint.

In SQL Server 2005, nvarchar (max) is used to replace ntext; varchar (max) is used to replace text; varbinary (max) is used to replace image.

In a special data table structure, you can consider the XML data type to achieve half-work.

3.5. Default Value
When creating a data table, use the default value instead of null. For example, set the default value of the createddate column to getdate (). When feasible, the field cannot be blank.

3.6. Field Length
Always specify the length of the character data type, and ensure that the maximum number of characters that a user may need is allowed to avoid character loss when the maximum length is exceeded. For balanced data, we recommend that you use the Npower of 2 to define the data length.

Example: nvarchar (32)

Varchar (64)

3.7. Use "'"
Use single quotes for character constants in the T-SQL code to avoid double quotes.

3.8. Statement indent
The statement in a nested code block uses four spaces for indentation. Use Microsoft SQL Server Management studio, select the "Tools" menu, open the "options" menu, select the text editor-> plain text-> tab in the Options dialog box, select "insert Space single-digit", set "tab size" to 4, and indent size to "4 ".

3.9. line feed
We recommend that each line of SQL code start with a keyword or.

Example:

Select [shiftid]

, [Name]

, [Starttime]

, [Endtime]

, [Modifieddate]

From [adventureworks]. [HumanResources]. [shift]

3.10. Statement Segmentation
A logical block that separates T-SQL code with one (instead of two) Empty lines.

3.11. Use "*"
Avoid using "select *" in any code whenever possible *".

3.12. Table Name alias
The table name alias should be brief, but the meaning should be as clear as possible. Generally, an uppercase table name is used as an alias and the as keyword is used to specify the alias of a table or field.

3.13. type conversion
Do not rely on any implicit data type conversion, and do not assume that the T-SQL performs the necessary conversion. For example, assign a numeric variable to a character value. Instead, use the appropriate convert function to match the data type before assigning or comparing values to the variable.

3.14. Numerical Comparison
Do not directly compare empty variable values with comparison operators (symbols. If the variable may be null, use is null or is not null for comparison, or use the isnull function.

3.15. Sort
The SELECT statement must not rely on any particular order to return rows, unless the order is specified in the order by clause. Generally, the order by clause and
Select
Statement. Predictable Order (even if not the most convenient) is stronger than unpredictable order, especially during development or debugging. Order can be ignored when the order of returned rows is irrelevant.
By to reduce resource overhead.

3.16. Unicode string
Use the N prefix before Unicode characters to avoid data inconsistency.

Example:

-- Assumes the default code page is not Greek

Create Table # T1 (C1 nchar (1 ))

Insert # T1 values (n'Ω ')

Insert # T1 values ('Ω ')

Select * from # T1

Output result:

C1

----

Ω

O

3.17. Begin... End Block
Use the begin... end statement block as much as possible in the fast SQL code to improve code readability.

3.18. Try Block
Try to use try blocks for statements that may fail to be executed in SQL Server 2005. The Transact-SQL statement group can be included in the try block. If an error occurs in the try block, the control is passed to another statement group contained in the Catch Block.

Example:

Begin try

SQL statement Group 1

End try

Begin catch

SQL statement Group 2

End catch;

3.19. Top clause
The use of top is enhanced in SQL Server 2005, and top (variable) is used as much as possible to reduce the SQL spelling phenomenon.

3.20. Write transaction
As long as you use multiple database modification statements in the routine, including executing one statement multiple times in a loop, you should consider acoustic explicit transactions. In SQL Server 2005, try blocks are added for good application.

Instance:

Begin try

Begin transaction;

Update [HumanResources]. [employee]

Set [title] = @ title

, [Hiredate] = @ hiredate

, [Currentflag] = @ currentflag

Where [employeeid] = @ employeeid;

Insert into [HumanResources]. [employeepayhistory]

([Employeeid]

, [Ratechangedate]

, [Rate]

, [Payfrequency])

Values (@ employeeid, @ ratechangedate, @ rate, @ payfrequency );

Commit transaction;

End try

Begin catch

-- Rollback any active or uncommittable transactions before

-- Inserting information in the errorlog

If @ trancount> 0

Begin

Rollback transaction;

End

Execute [DBO]. [usplogerror];

End catch;

3.21. Stored Procedure
When writing a stored procedure, use procedure instead of Proc.

Example: Create procedure [DBO]. [stored procedure name]

4. Code comments
4.1. Code header comments
In the header of an SQL code block (SQL file or stored procedure), annotate the author (author), create date, and modify information (modify [N]).

Format:

-- ===================================================== ======

-- Author: <author, Name>

-- Create Date: <create date,>

-- Description: <description,>

-- Modify [N]: <modifier, date, description>

-- ===================================================== ======

Example:

-- ===================================================== ==========

-- Author: zhanghaifeng

-- Create Date: 2006-12-25

-- Description: h2000 return ticket handling

-- Modify [1]: Zheng Zuo, 2006-12-31, simplifying the logical judgment process

-- Modify [2]: Zheng Zuo, updated condition judgment

-- ===================================================== ==========

Note: The date format is yyyy-mm-dd. Modify [N] n indicates the number of changes. Starting from 1, add 1 to each change.

4.2. Transaction Annotation
Note the beginning of each transaction to describe the functions of the transaction.

-- <Modifier, date, description>

Begin transaction;

5. Appendix A naming rules
There are four naming rules: uppercase, lowercase, Pascal, and camel.

5.1. Pascal case
The upper-case letters of each word that comprise an identifier, and the lower-case letters of other letters. Uppercase letters are required for abbreviations.

Example: applicationexception

ID

5.2. Camel case
The first letter of the identifier is lowercase, the first letter of each connected word is uppercase, And the other letters are written in lowercase. For the abbreviations of double-letter words, it is required that they appear in lowercase when the first part of the identifier, otherwise all are in upper case.

Example: applicationexception

ID

5.3. Hungarian naming law
The Hungarian naming method was invented by a Hungary programmer who has been working at Microsoft for many years. It is passed through various products and documents of Microsoft. Most experienced programmers, no matter which language they use, are more or less using it.

Basic principle: variable name = property + Type + Object Description

That is to say, a variable name is composed of three parts of information, so that programmers can easily understand the type and purpose of the variable and make it easier to remember.

6. Appendix B reference resources
6.1. Microsoft SQL Server 2005 books online
Http://www.microsoft.com/downloads/details.aspx? Displaylang = ZH-CN & familyid = BE6A2C5D-00DF-4220-B133-29C1E0B6585F

6.2. SQL Server 2005 Sample Database
Adventureworks

Adventureworksdw

6.3. Compile portable Transact-SQL code
Http://www.microsoft.com/china/msdn/library/data/sqlserver/USsqldnsqldevdev_06112004L.mspx

6.4. T-SQL coding standards
Http://www.microsoft.com/china/msdn/library/data/sqlserver/sp04l9.mspx