SQL Server performance Optimization (4) naming and writing specifications

Naming conventions is a commonplace issue, and good naming conventions play an important role in the development of team programs, in the location and processing of bugs, and in the continuation of projects.

First, list the current problems:

1. The initial letter of the name is not very written

2. Name in Chinese

3. Statement writing is not standardized, such as the following, the keyword base friend capital also has lowercase. The programming style is not unified.

Two. Common database object Naming basic specification (Network + self-organizing)

1. General naming conventions?

A. The length of the name does not exceed 32 characters.

B. Name in English word, English word abbreviation and number, the first letter of the word capital . Note: You must not violate this specification unless you provide documented industry standards (for example, GB or ministerial standards).

C. Do not use "_" as the starting letter and ending letter of the name.

D. The name must be understood by the text.

E. The name must not conflict with the database management system reserved word.?

2. Database name

A. The database name is defined as the system name + module name, or directly using the system name, such as the hot table acquisition platform user database Heatuser.

B. database names are all lowercase (? system comes with a database is lowercase).

3. Database file (to add database files when separating tables)

A. Data file naming takes database name +_+ file type +[file number]. file suffix, file number 1, 2, 3 ... A value of 9, when a file of a file type in the database has more than one file with a sequence number to differentiate. There is only one time that can not be added.

B. File suffix: The main data file is. mdf, the other data files are. ndf, and the log file is. ldf.

C. filenames are all lowercase.

4. Table naming follows these guidelines:?

A. The format of "system name +_+t_+ module name +_+ table name" is used. If the database contains only a single module, the name can be made up of the "system name +t_+ name" format. such as the user log table in the user library: User_t_log.?

B. The length of the entire table name should not exceed 30 characters.

C. The system name and module name are lowercase characters (the system comes with the database table name is lower case).

D. module or table names are named in their English words, and the characters are not separated, the first character of the words in the name of the word is capitalized, the other characters are lowercase, and there are no delimiters between multiple words, the words are all singular .
? E. In the database, both the table name and the field name are in English and no other languages are allowed; The first letter of each word must be capitalized, and if it is too long according to the actual situation, the real habit is abbreviated.

5. column or field naming follows these guidelines:?

A. Use meaningful column names as the actual meanings of the English words, and no delimiters between the characters.

B. Do not prefix the attribute name with the table name.

C. The attribute is not appended with any type identification as a suffix.

D. Do not use words such as "ID" that conflict with system reserved keywords as column names.

E. The primary key is "ID".

F. Foreign key named: "fk+_+ table name (or abbreviation) +_ primary table name (or abbreviation) +_+ primary Key ID" If user ID foreign key column in user log table: fk_user_id.

6. Index

A. The index is named: "Table name (or abbreviation) +_+ column name +_idx". The index of multiple columns only writes the first column.

7. Trigger?

A. After trigger: System name +tr_+< any combination of table name >_+<i,u,d, where i,u,d represents insert, update, and delete respectively.

B. INSTEAD of type trigger: Any combination of system name +ti_+< table name >+_+<i,u,d, where i,u,d represents insert, update, and delete respectively.

8. The view view name is prefixed with the system name +v_+ module name, and other naming rules are similar to the names of the tables.

9. Stored Procedures
A. Stored procedure naming consists of the system name +sp+_+ primary table name +_+ operation +_+ stored procedure identification (abbreviation). The operation is Insert,update,delete,select. such as the OA table to add the user's stored procedure oa_sp_user_insert, select User Oa_sp_user_select_byid

10. The function function name consists of the system name +f+_+ function identifier.

11. Variable name?

A. Parameter variable naming takes the form "I (o or IO) +_+ name", prefix I or O for input or output parameters.?

B. Process variable naming takes the form of "l+_+ name".

C. Variable names are lowercase, and if they are in the form of a phrase, separate each word with an underscore.

D. Role? All are named with lowercase characters. Consists of the system name +_+role+_+ noun (or abbreviation) or noun phrase (or abbreviation).

E. User? All are named with lowercase characters. Consists of the system name +_+user+_+ noun (or abbreviation) or noun phrase (or abbreviation).

III. Basic code for Transact-SQL programming

1. General notes

A. Comments can be included in the batch. Including descriptive comments in triggers and stored procedures will greatly increase the readability and maintainability of the text.

B. Comments as detailed and comprehensive as possible.

C. Before you create each data object, you should specifically describe the object's functionality and purpose.

D. The meaning of the incoming parameter should be described. If the range of values is determined, it should also be explained. Variables that have a specific meaning (such as a Boolean variable) should be given the meaning of each value.

E. Note syntax consists of two cases: single-line comment, multiline comment.

Single-line Comment: There are two hyphens (--) before the comment and end with a newline character. This type of comment can be used for variables, conditional clauses in general.

Multiline Comment: The content between the symbol/* and/* is the content of the comment. It is recommended to use this class comment for a complete operation.

E. Annotations should be concise and should be clearly described.

2. function text Comment?

A. When writing function text-such as views, functions, triggers, stored procedures, and other data objects-you must add appropriate annotations for each function. This comment is based on multiple lines of comments, the main structure is as follows:

--*

--output:--Output parameter--author:--author--createdate:--creation time--updatedate:--function change information (including author, time, change content, etc.)  

--***************************************************************** * CREATE [OR REPLACE] FUNCTION dfsp_xxx ...

B. The meaning of the incoming parameter should be described. If the range of values is determined, it should also be explained. Variables that have a specific meaning (such as a Boolean variable) should be given the meaning of each value.

C. Add a comment after each variable declaration. Describes the purpose of the variable, usually by simply using a single-line comment.

D. Add comments before each major part of the block, adding comments before each major part of the block, explaining the next-group statement purpose, preferably explaining the purpose of the paragraph and the algorithm and the results to be obtained, but do not describe its details too much.

E. You can also add information such as the database you want to access in the comments at the beginning of the block and procedure.

3. Basic specification for writing common SQL statements

A. CREATE statement

CREATE TABLEPublishers (pub_idChar(4) not NULL --Identification     CONSTRAINTUpkcl_pubindPRIMARY KEY CLUSTERED     CHECK(pub_idinch('1389','0736','0877','1622','1756')     ORpub_id like '99[0-9][0-9]'), pub_namevarchar( +)NULL,--nameCityvarchar( -)NULL,--CityStateChar(2)NULL,--StateCountryvarchar( -)NULL --National    DEFAULT('USA') )

B. SELECT statement

Query statements are written using the following guidelines (to maximize the reuse of SQL statements in a shared pool to improve application performance):?

Divide the SELECT statement into 5 parts: Select,from,where,group by, and ORDER by, each part of the branch is written to align the first keyword of each row with the SELECT tail of the first line, as

 SELECT from WHERE>GROUP by   ORDER by col1;

? All keywords in uppercase.?

Format the where and and sections in the statement, write a layout similar to the following, and leave a space on each side of the action

WHERE = 1  and = 2  and  c=3

? C. INSERT statement

INSERT  into <The name of the table to insert>  (<Column 1>,<Column 2>, ..,<Column n-1>,<Column n>)  VALUES(<Column 1 value>,<Column 2 value>, ..,<Column n-1 value>,<Column N Value>)

D. UPDATE statement

UPDATE < table names to update >set< columns to update >=< column values  >

F. DELETE statement

DELETE  from  WHERE=' admin'

SQL Server performance Optimization (4) naming and writing specifications