(4.9) SQL Server Database specification

SQL Server Database specification

First, naming norms

Common object naming conventions, using Pascal's nomenclature (Pascal, the first letter of the word capitalization), the uniform use of English.

1. Table. English singular noun, as far as possible to write the full word name generally not more than 3 English words can express the meaning of the table. Use Pascal to name the method.

such as: User,userrole,role,group,family,salesorderdetail

Error Example: users,usertable

Note: A table of special significance

Custom metadata tables, using the prefix sys start: sysdictionary,sysparameter,sysmodel,sysregion

Business Similar table: wechatcompany,wechatuser,wechatmember,smssend,smsreceive

Abbreviation table, universally recognized abbreviation: us_user,en_user,cn_user,wto_member,wto_ country

Table with too many words: user_ zgyh (Chinese abbreviation of Bank of China), User_ GSYH (ICBC Chinese abbreviation)

2. field. The name of the table is identical.

For primary key fields, uniform is: ID

Foreign key Reference field, unified as: foreign key table name +id

such as Userid,userroleid,salesorderdetailid,sysdictionaryid

3. View (do not use).

The name of the table is identical. Uppercase "V_" as prefix, format: v_ view name

such as: V_user,v_sysdictionary

4. Stored Procedures (business processing is not used, reports are available). The process of getting or executing data.

Requires a combination of nouns. Uppercase "usp_" as prefix, format: usp_ stored procedure name

such as: P_getuser,p_updateuserbyusername,p_cleandeleteduser,p_getmyrecord

5. Scalar functions (do not use the query to the table). Returns a single value.

Required verb noun combination, uppercase "fn_" as prefix, format: fn_ function name

such as: Fn_getusername,fn_getusernamebyuserid

6. Table-valued functions (do not use queries to tables). Returns the table.

Require verb combination, write "tf_" as prefix, format: tf_ function name

such as: Tf_ splitchar,tf_ splitcharbycomma,tf_getuserbycity

7. Trigger (do not use).

Uppercase "Tr_" as prefix, triggering action as suffix, Format: tr_ table/view name _insert/delete/update

such as: Tr_user_insert,tr_user_update,tr_user_delete

8. Index. Try to finish writing the fields involved

Uppercase "Ix_" as prefix, format: Ix_ table Name _ Field Name _ Field name _ ...

such as: Ix_user_id,ix_user_username_mobile

Unique index: Uix_ table name _ Field name

Include column index: Ix_ table name _ Field name _include

Filter index: Ix_ table name _ Field name _where

9. Constraints.

PRIMARY KEY constraint: Pk_ table name _ Field name

FOREIGN KEY constraint: Fk_ table name _ Field name _ Main Table name _ Main tables fields (disable FOREIGN KEY constraints!) ）

Unique constraint: Uix_ table name _ Field name (Unique constraint default is to create a unique index to constrain)

Default value constraint: Df_ table name _ Field name

Check constraint: Ck_ table name _ Field name

10. Temporary objects. Casing is not required, the other is the same as the table name specification. The declaration uses the session level.

Temp table: #user (do not use # #user)

(table) Variable: @name (do not use @ @name)

Second, the design code

Object-Level specification:

? database table design must meet the third paradigm (special case re-discussion)

? The business tables are set with the self-increment primary key id! (The primary key is not necessarily a clustered index)

? Prohibit FOREIGN KEY constraints! (Slow operation data; difficult to maintain data)

? Disable the use of triggers! (Control operation in the program)

? Prohibit the use of views! (Maintenance inconvenience, multiple table associations may have fields useless.) Report available)

? Do not use functions to access data such as tables, views, etc. (function is only used as a secondary calculation tool and does not participate in accessing data!) ）

? Prohibit the use of stored procedures to process business logic (before the program is calculated to read and write the database!) ）

? Disable cursor use! (Use a set concept to operate with less traversal)

? Disable the use of temporary tables! (business operations are frequently not controllable)

? Do not use synonyms! (Maintenance easy to be ignored)

? (Report available stored procedures, views, temporary tables, result sets in the view do not have a sort)

? (if necessary, views, stored procedures, functions, triggers, cursors, etc. do not call more than 2 times!) ）

? Do not use database keywords as table names, field names, etc. (especially system object names, such as: getdate, SUM, etc.)

? Create constraints, indexes, etc., manually set the name, not automatically generated by the system!

? Index as few as 5, especially those frequently changed and inserted tables!

? Tables and fields must be added in the database with detailed comments! (Reference: Sp_addextendedproperty)

? Views, stored procedures, functions, triggers, etc., must fill in the creation information and the description of each operation!

? Prevents files from being stored in the database.

? When inserting character data, you should remove the left and right spaces!

? All fields must be listed when inserting a table! (Example: INSERT into tab (name,phone) select name, mobile from user)

? Stored procedures, functions, etc., module statements plus BEGIN ... END; Remove the extra empty lines and spaces.

? Permissions on the Program action table allow only SELECT, INSERT, UPDATE, delete!

? For the business data that may be deleted, the database does not actually delete the data, add the field IsDeleted to determine whether to delete!

? Operation logs, system logs and other tables, only allowed to insert, can not be modified!

? For the more restrictive enumeration options, the program should set options instead of manually filling out the user!

? Add a Time field to the Business table: Addtime datetime NOT NULL constraint df_ table name _datetime default (GETDATE ())

Field Specification:

? Try to set the use of "NOT NULL" constraint, the value default 0, the word defaults think empty '. Otherwise, NULL is not involved in the comparison!

? Disable ntext, text, and image, substituting nvarchar (max), varchar (max), and varbinary (max), and try not to max!

? Non-English important data are saved using Unicode type, such as nvarchar, nchar, others can be varchar;

? DateTime fields are uniformly used for the time period, accurate to milliseconds;

? Amount, decimal type using decimal, without int, float, double (int can store 2.1 billion, float, double precision is not allowed)

? The state field is unified with the States (enumerable) without the status

? Do not allow plaintext to store passwords!

Third, the query specification

You define parameters, variable types to refer to the same field type and length.

Declare @name varchar (50)--type to be the same as User (name)

Select * from User where Name = @name

You use a set concept to manipulate data by using no cursor traversal.

U operation use in inside the constant not too hundred, as little as possible! Apply the other way to table join.

Example ERROR: SELECT * FROM [User] wherename in (' AA ', ' BB ', ' CC ', ' DD ', ' EE ', ' FF ',.........)

If you need to use "in", no more than 3 nested subqueries.

Select * from User

where Name in (

Select Name

From Manager

where Sex in (Select sex from User where name= ' KK ')

)

U sub-query "in" is changed to "Innerjoin" mode, note whether there is a one-to-many or many-to-many cases, the first Connection key group and then connect.

The in query above can be changed to the following:

Select distinct t1.*

From User T1

Inner JOIN Manager T2 on T1. Name=t2. Name

INNER join User T3 on T2. Sex=t3. Sex

where T3. Name= ' KK '

U can use "EXISTS" without "in", EXISTS can avoid possible errors.

--table TestRole does not exist field Mobile, but the query will not error!

SELECT * from User where name in (select Mobile fromtestrole)

SELECT * from User where name ' Not in ' (select Mobile from TestRole)

Switch

SELECT * from User a where exists (select 1 Fromtestrole b where a.name=b.rolename)

--Table testrole field RoleName The query has no result as long as there is a Null

SELECT * from User where name ' Not in ' (select RoleName from TestRole)

Switch

SELECT * from User where name ' Not in ' (select RoleName from TestRole whererolename is Notnull)

SELECT * from User a where NOT exists (select 1 from testrole b wherea.name=b.name)

The first line in the U-stored procedure is added: SET NOCOUNT ON, which is mainly to display the execution results of the printed information does not return to the client, reducing network IO.

CREATE PROC dbo. Testpro

As

BEGIN

SET NOCOUNT on--Add here

............

END

GO

Do not print (print) information in the U-stored procedure for the same reason.

U queries do not use asterisks, even if the fields are more or less complete, not all fields are used. And the asterisk cannot establish an appropriate index.

u where filtering, do not use functions on fields, otherwise the whole table is scanned for function processing.

Select * from User where Left (Name) = ' Yellow '

Switch

Select * from User where Name like ' yellow '

Select * from User where convert (varchar (), addtime,120) = ' 2018-01-01 '

Switch

Select * from User where addtime>= ' 2018-01-01 ' andaddtime< ' 2018-01-02 '

U fuzzy match like do not speak% put in the first place. Ibid.

U time comparison note, do not have to hand-written so detailed

Select * from User where addtime<= ' 2017-12-31 23:59:59 '

Switch

Select * from User where addtime< ' 2018-01-01 '

U consistency is not strong or need rough statistics of large table data or report, can add nolock allow dirty read.

Select * from User with (NOLOCK)

Select * from User as U with (NOLOCK)

The u count is not counted as null. Counting uses count (*), and the number of MSSQL (*) defaults to the smallest of index lengths to count.

COUNT (*) = count (1) = count (' A ')

Select Count (*) from User--Total row count

Select count (mobile) from User--count the total number of rows that the phone is not null

In U SQL Server, the default null plus any characters are null, so be careful!

SELECT ' A ' +null--the result is NULL

SELECT 100+null--the result is NULL

-Normal notation, if the field has a NULL value, use ISNULL to determine the change.

Select Name + ISNULL (Mobile, ') from User

Note: The aggregate function sum, AVG, max, Min are ignored by NULL.

U In addition to the data need a large number of exports, any query must use a paged query.

U do not have to read table data or manipulate data frequently in a transaction

U query as far as possible parameterization, that is, declare parameters, then assign values, and then bring the parameters into the execution script (for example: where name = @name)

Select name,mobile from User where Name = ' AA '

Select name,mobile from User where Name = ' BB '

Switch

Declare @name varchar (50)

Set @name = ' AA '

Select name,mobile from User where Name [email protected]

Go

Declare @name varchar (50)

Set @name = ' BB '

Select name,mobile from User where Name [email protected]

Go

U Multi-Table Association and if the result set is satisfied, use the left join instead of the INNER join (inner join) as far as possible

Select t1. Name

From User T1

Inner JOIN Manager T2 on T1. Managerid=t2.id

where T1. Mobile= ' 13000000000 '

Switch

Select t1. Name

From User T1

Left join Manager T2 on T1. Managerid=t2.id

where T1. Mobile= ' 13000000000 '

Because Mobile is unique, the second case only queries the User table. So when the condition is uncertain and does not affect the result, use the second.

When you sort by add time, poor performance can be sorted by the primary key clustered index ID, because the ID is also incremented.

Select Top (Name,mobile) from the User order by addtime Desc

Switch

Select Top (Name,mobile) from the User order by ID Desc

U multi-table connection query, use short aliases, fields are alias and alias to unify!

Select t1. Name from User t1 INNER join Manager t2on t1. Managerid=t2.id

Select A.name from User a inner join Manager B on a.managerid=b.id

Select u.name from the User u inner join Manager m on u.managerid=m.id

U script do not write on one line, note indentation, each line length as far as 120 characters.

The system keywords in the U script remain uniform and can be capitalized in all uppercase or all lowercase or initials.

You do not use a linked server in the database to query across servers, and the program should read the data from one node to another node.

U multi-table connection, if there is "or" after "on", then the Union fetch connection is changed:

Select A.name,b.name from User a Inner Join Manager B on A.managerid=b.id or a.mobile=b.mobile

Switch

Select A.name,b.name from User a Inner Join Manager B on a.managerid=b.id

Union

Select A.name,b.name from User a Inner Join Manager B on A.mobile=b.mobile

You do not query large amounts of data in the production library, consuming the IO and memory of valid data. If enquiry is required, add nolock.

Select * from User with (NOLOCK)

Select * from User as U with (NOLOCK)

U for complex paging queries, you can find the primary table ID by criteria (find the ID in the nonclustered index), and find out all the paging data by ID Re-association (find all the data through the clustered index)

IV. Maintenance Specifications

Production libraries use full mode, test, development library available in simple mode

Operation log table, ID usually only the key, no use. You can use the field "Addtime" as a clustered index field and a partition field.

Each time you modify a function, stored procedure, and so on, back up the old copy and record the modification information.

Database-level changes, back up the database first

Back up the transaction log when you are updating and deleting data in large quantities

When deleting and updating data, store the data that needs to be manipulated in another dedicated database for temporary backup.

Small batch operations when data is deleted and updated. It is recommended that you do not exceed 5000 rows at a time, reducing the granularity of transactions and preventing locks from being promoted to table locks.

Rebuild indexes on a regular basis and do not rebuild them all at once.

Other: Disk io, CPU, memery, NetWork io, migration, scaling and other performance issues, find dba! ~

Maximum capacity specification for SQL Server: https://msdn.microsoft.com/zh-cn/library/ms143432 (v=sql.100). aspx

(4.9) SQL Server Database specification