Database naming and comment specifications

Code annotation Specification

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.

2. Transaction Annotation

Note the beginning of each transaction to describe the functions of the transaction.

-- <Modifier, date, description>

Begin transaction;

 

I. Database naming rules

Database naming rules depend on the database compatibility level.

Ms SQL database version and compatibility level table:

Ms SQL database version	Compatibility Level
Unknown	60
Unknown	65
Ms SQL 7.0	70
Ms SQL 2000	80
Ms SQL 2005	90
Ms SQL 2008	100

Note: You can use the stored procedure "sp_dbcmptlevel" to modify the database level.

The following uses compatibility level 90 as an example to describe database naming rules:
The name of step 1 cannot exceed 128 characters, and the name of the local temporary table cannot exceed 116 characters.

Step 2 consists of English letters and uses the Pascal naming method. For example, northwind.

Step 3 names cannot contain spaces or other special characters.

Step 4 names cannot be reserved words or symbols of variables or local objects (For details, refer to the variable naming section ).

 

Ii. T-SQL writing specifications

1. "@" Indicates a local variable; "@" indicates a global variable; "#" indicates a global temporary object;
2. Custom stored procedure: "Pr _" + English characters named by Pascal. system stored procedure: "SP _" + all lowercase English characters
3. View: "vw _" + English characters named by Pascal
4. Table: English characters named after "Tb _" + Pascal
5. Trigger: "Trg _" + English characters named by Pascal + "_" + "function description ". For example, trg_product_insert
6. Cursor: English character in the name of "CRS _" + Pascal

 

Good naming habits are a virtue. Below are the naming rules for databases compiled by individuals:

 

Database naming rules: 1. database Name: 1.1) Name of a product or project; 1.2) Pascal Case, such as AdventureWork; 1.3) avoid special characters, such as numbers, underscores, spaces, etc.; 1.4) avoid using abbreviations 2. table Name 2.1) use the plural value, Pascal Case, and the plural value can only be added to the last word, for example, Products, Users, UserRoles.
2.2) avoid special characters, such as numbers, underscores, and spaces. 2.3) avoid abbreviations 3. column name 3.1) Use Pascal Case 3.2) to avoid duplication with the table name and avoid data type prefix such as: Int
3.3) avoid abbreviations or special characters 4. Stored Procedure 4.1) use the verb and table name to describe the Operation Type
4.2) prefix: sp + {"Insert", "Update", "Delete", "Get", "Validate ",...} 5. view 5.1) reference table name rules
5.2) prefix with "vw"
6. Trigger 6.1) use "trg" prefix 6.2) Use Operation Type + Table name, such as: trg_ProductsInsert
7. Index 7.1) format used for example: idx _ {table name }_{ index column name }_{ Unique/NonUnique }_{ Cluster/NonCluster}
8. primary Key 8.1) The format is as follows: pk _ {table name }_{ primary key column name} 9. foreign key 9.1) Format: fk _ {name of the primary table }_{ name of the column of the primary table }_{ name of the referenced table }_{ name of the column of the referenced table} 10. default 10.1) Format: df _ {table name }_{ column name} 11. constraint 11.1) use format: ck _ {table name }_{ column name}
12. Variables

12.1) refer to column name rules