MSSQL SQL Server specification use method sharing

Transferred from: http://www.maomao365.com/?p=5586

Summary:
The following focuses on SQL Server table design and scripting, related specifications

———————————
Data table field type selection:
Character type Select the appropriate varchar nvarchar type based on length
Recommended money type for the amount involved
Numeric decimal type is recommended with decimal digits
Custom identity columns suggest using the bigint type
Time Type Select Date datetime smalldatetime as required
Try to avoid using the XML text ntext image type
————————————–
Table Design
The table name of the same business module is set to have the same identity prefix
Column Design
Fields of the same function should have the same name and type definition in all tables
All columns prohibit null values
Table related Design
Table as far as possible to set a reasonable primary key, try to use single field primary key
Index Design:
The fields used to join the table, try to set the index
The columns involved in order by, as far as possible to set as index
Index include does not include too many columns
Don't have too many indexes on a single sheet

——— Scripting ————— –
In scripting, it is forbidden to perform complex calculations
In scripting, avoid using SELECT *: Reduce the number of queries and unnecessary returns
In scripting, it is forbidden to perform calculations on indexed columns
Use fewer cursors
Use fewer triggers
In scripting, the parameter types and column types in the table need to be consistent
scripting, avoid splicing SQL, using parametric processing
In scripting, minimize the number of join tables
In scripting, limit the number of in, because unreasonable in causes the "DB estimate execution plan" error.
Avoiding the use of large transactions can lead to deadlocks
Try to use UNION all to replace the Union combination table
In a script query, the data is returned as much as you want on page

MSSQL SQL Server specification use method sharing