Shulou(Shulou.com)05/31 Report--

This article gives you an introduction to SQL Server database design specifications, the content is very detailed, interested partners can refer to, I hope to help you.

What is SQL Server Database Design Specification?

1. Object annotation rules:

Comment header: The comment header should indicate what the object does, what objects it is related to, and what the parameters are, author, and when it was last modified.

Stored Procedure: CalLnIncome

Relevant data:

LnLineInfo,lnStation: Provides information about routes and route splitting.

pbTransportBill,pbTrnasInfo: Provides data of transportation volume bill.

lnInComeList: The calculation results are saved in this table.

Execution success flag: lnCostList table has the latest time period total score component data Last modified: Byczyk, November 20, 2003

Note: System operation classes are generally written at the beginning and end of statements, which are separated from specific business statements by separators!

SETQUOTED_IDENTIFIERONSETANSI_NULLSONSETANSI_WARNINGSONSETXACT_ABORTONdeclare@ServerNamevarchar(50),@UserNamevarchar (20),@ PasswordVarkar (20),@DBNamevarchar(30),@ TableQueryVarkar (4000)--Parameter definition select@ServerName=vcComputer,@UserName=vcUserName,@Password = vcPassword,@DBName=vcDataBasefromsmDataParamset@TableQuery='select*into##QJGroupInfosource from opendatas ('+'''SQLOLEDB'','+'''DataSource='+rtrim(@ServerName)+';UserID='+rtrim(@UserName)+';Password='+rtrim(@Password)+'''). '+rtrim(@DBName)+'.dbo.QJGroupInfo'--Export data to global temporary table of current connection server for standby. Later, just operate ##QJGroupInfo table to exec(@TableQuery)updatesmDeptsetvcDeptName=t.GroupNamefrom(selectm.vcTransDeptNo,g.GroupNamefrom##QJGroupInfoginnerjoinsmDeptRelatemonm.vcSourceDeptNo=g.GroupIDwherem.RelateKind=0) lastwherecDeptNo =t.vcTransDeptNo

What is SQL Server Database Design Specification?

2. Database design specification Database design is divided into logical design and physical design

It is generally recommended to logically model the database before mapping the logical design to the physical design. In logical design, please pay attention to the correlation between objects and the redundancy of data. Table design principles: Three useful fields to consider adding to each table.

RecordedID, a unique number for the record. Business data is not recommended as a unique number for the record.

CreationDate, which defaults to GETDATE() under SQL Server.

RecordCreator, which defaults to NOTNULLDEFAULTUSER under SQL Server.

RecordVersion, the version mark of the record; helps to accurately indicate the reason for null data or missing data in the record Data type: char is generally not recommended and varchar data type is used, except when the length of the column is particularly fixed.

char Insufficient numeric type exists

If the amount of money is expressed, it is recommended to use money-type data. If the scientific notation is expressed, it is recommended to use numeric data type. The int type is generally used to identify a unique row of records. Whether this column is automatically incremented depends on the circumstances. If you need to get this tag order before saving, you cannot use auto-increment types if you want to strictly control the order in which records are added.

All tables should have a primary key index, which helps improve database performance. If you can use several fields in the table to determine a record, you can consider establishing a unique index, but after establishing a unique index, there is a constraint, such as, if a table has too many records, and you are not familiar with how to establish an index, you can consider SQL Server in the Query Analyzer Index Optimization Wizard. You can execute the query execution plan for a given statement in the parser and adjust the query statement according to the query cost shown in the execution plan.

Ensuring data consistency and integrity:

Primary Foreign Key Association

Establishing primary and foreign key associations can reduce data redundancy and improve query efficiency. Although we can usually use the join statement to associate between different tables, we can uniformly establish the association graph through the relationship graph of Enterprise Manager after designing the tables. After all the associations are established, we can record the current price of goods in the view creation table.

Establishing Constraints to Realize Data Validity Detection

You can create constraints for a column of values that are particularly important. For example, you need to determine the sales category based on the SaleKind column data in the database. You have a value of 0 for store sales and 1 for online sales. Since your system only has these two sales channels, you should create constraints for it that can only have values between 0 and 1. SaleKind>0andSaleKind

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.