SQL Server Database Design specification

Database design Specifications 1. Introduction

Database design refers to a given application environment, constructs the optimal database pattern, establishes the database and other application system, enables it to effectively store the data, satisfies the various user's demand. Naming conventions are important in the database design process, and a well-named design can save developers a lot of time to differentiate database entities.

Recently also because of the work need to sort out this Word document, I hope you correct.

2 Database Design

database planning → requirements analysis → database design → application design → implementation → test → run in maintenance

2.1 Database Planning

Define the main objectives of the database application system, define system-specific tasks, including workload estimates, use of resources, and requirements, define the scope and boundaries of the system.

2.2 Requirements Analysis 2.1.1 Requirements analysis steps and results

people involved : users and analysts

Task : The real world to deal with the object of the detailed investigation, the collection of basic data and processing methods, based on the user survey through analysis, and gradually clear the user's needs for the system, including information requirements and processing requirements.

methods and Steps : 1. Through the survey with the user, the user's information needs are collected.

2. While collecting the data, the designer will process and organize it, describe it in the form of data dictionary and flow chart, and tell the user the information from the designer's point of view, modify it according to the user's feedback and make sure (the process is iterative process).

results : Data flow Diagram, database dictionary, various explanatory forms, statistical output table and system function chart.

2.1.2 Streaming diagram basic elements and streaming diagram

external entity : A person or organization that exists outside the software system (represented by a square or cube).

processing : Data processing, which means that the inputs are transformed here, producing output data (large rounded or circular representations).

Data Flow : Represents the data flowing (indicated by an arrow line).

data storage : Used to represent the data to be stored (open-door rectangles or two parallel horizontal lines).

Order Processing System Top-level flowchart:

0 Layer Streaming Chart:

2.3 Database Design 2.3.1 Conceptual structure design

• A transaction is abstracted in the form of a e-r diagram.
• E-r Diagram (Entity contact map): Includes entities, contacts, attributes

entities : Things in the real world for example, students, teachers

Contact : Two relationships between entities, 1:1, 1:n, m:n three relationships

Properties : Attributes that an entity has, such as student's number, name, gender, etc.

For example: A student belongs to a class, a class has more than one student, e-r chart is as follows

Online shopping system E-r diagram, there are the following constraints between the system data

1. A customer (number unique) can have multiple orders, and each order belongs to only one customer.
2. An order (number unique) can contain multiple order lines, and each order breakdown belongs to only one order.
3. A product can appear in multiple ordering lines, and an ordering breakdown contains only a few items.
4. A product category can contain a variety of goods, a commodity belongs to only one commodity category.

Figure 2.2

2.3.2 Logical Structure design 2.3.2.1E-R diagram into relational mode

• Convert a e-r diagram into a relational schema

Each entity is converted into a relational schema, the attribute of the entity is the attribute of the relationship pattern, and the identity of the entity is the key of the relational pattern.

• Merge the links between the 1:1,1:n,m:n in the E-r diagram according to the rules

1. If the entity's contact is (1:1), then two entities can be converted to two relationship patterns, one of the attributes of any relationship pattern is added to another relationship pattern of the primary key (as a foreign key) and the properties of the contact itself
2. If the relationship between entities is one-to-many (1:n), then the entity type of the N-end is converted to the attribute of the primary key (as the foreign key) and the contact type of the 1-side entity type in the relational schema.
3. If the relationship between the entities is many-to-many (m:n), the contact type is also converted to the relational schema, whose property is the 2 entity type's primary key (as the foreign key) plus the property of the contact type itself, and the primary key of the relational pattern is the combination of the 2-end entity primary key.
4. If the relationship pattern is a 1:1:1 relationship, the conversion principle is the same as 1:1
5. If the relationship pattern is 1:1:n, the principle of transformation is the same as 1:n
6. If the relationship pattern is 1:n:m, then the contact type can also be converted to a relational pattern whose properties are the primary key (as foreign key) of the M-and N-end entity types, plus the properties of the contact type itself, while the primary key of the relational pattern is the combination of the N and M-end entity primary keys
7. If the relationship pattern is n:m:p, the transformation rules are the same as M:n

according to E-r the links between the graph entities can be converted into the following relational patterns :

Customer (customer number, name, telephone, e-mail). Primary key of the relationship: customer number; foreign key: None

Order (order number, order time, customer number). Primary key of the relationship: order number; foreign key: customer number

Order Details (Order detail number, order quantity, payment amount, order number). Relationship PRIMARY KEY: Order detail number; foreign key: order number.

appears (Order detail number, item number, type). Primary key of the relationship: Order detail number, product number, FOREIGN key: Order detail number, product number.

Product : (product number, product name, unit price, production date, product class number, product category name). Primary key of the relationship: product number; foreign key: None

The following problems may occur in the design of relational schemas: data redundancy, inconsistent data modification, data insertion exception, data deletion exception, so the requirement of the paradigm is to minimize redundancy and avoid inserting, deleting and modifying exceptions.

2.3.2.2 Paradigm

Main property: All properties that contain the key.

• Relational mode requires 4NF (reduced redundancy, elimination of operational anomalies)

The first paradigm (1NF): If each component of the relational pattern R is an irreducible data item, then the relational pattern belongs to the first paradigm. That is, each property is non-detachable.

Second Normal (2NF): R is 1NF, and each non-primary property is completely dependent on the key (no partial dependency), R is 2NF

For example: Elective relationship (school number, course number, score, credits)

The primary key of the relationship is (school number, course number), but the course number → credits, so the credit attribute part depends on the primary key, that is, the relationship department satisfies the second normal form, can be divided into (school number, course number, score), (course number, credit) two relations

The third paradigm (3NF): R is 2NF, and each non-principal attribute is not partially dependent on the code, and does not pass the code-dependent

For example: Student relations (school number, name, affiliation, Department address)

The primary key of the relationship is: study number

Study number → Affiliated department, affiliated department → Study Number, affiliated Department → Department address; According to the function of the axiom of dependency, the address transfer function depends on the number, that is, the relationship does not meet the third normal form, you can split the relationship between (school number, name, affiliation), (affiliation, Department address)

If there is a data modification exception, such as the student changed the system, modify the affiliation, but the system address is not modified, which caused the modification of the exception

Bcnf:r belongs to 3NF, and there is no partial and transitive function dependency of the main attribute pair code

For example: Relationship R (Part number, part name, manufacturer name), if you set each part number with only one part name, but different part numbers can have the same part name, each part can have more than one manufacturer, but each manufacturer's parts should have a different part name. This can be achieved by:

Part number → part name, (manufacturer name, part name) → Part number

So the main attributes include (part number, manufacturer name, part name), but "part name" pass depends on the code "manufacturer name, part name", so the relationship R does not meet the BCNF, when a part is produced by multiple manufacturers, because the part number only one and the part name according to the manufacturer of different and more, The link between the part name and the part number repeats multiple times, resulting in data redundancy and operational anomalies

The relationship can be broken down into (part number, manufacturer name), (part number, part name)

4NF: The relationship mode R is 1NF, if each non-trivial multi-value of R dependent X→→y and Y is not included in X, x must contain code, R belongs to 4NF

5NF: Projection of relationships, eliminating connection dependencies that are not contained in a candidate code in a relationship

For the above commodity relationship, because the primary key of the relationship is the product number, and the product category number → product category name

Therefore, the commodity relations department satisfies the third normal form, the non-main attribute commodity class name passing depends on the commodity number, there will be data redundancy, data modification anomaly problem. Break down the commodity relationship into:

Product (product code, product name, unit price, production date, product class number)

Product Category (Product class number, product category name)

2.3.3 Physical Structure Design

The process of designing a physical structure for a given logical data model that is most suitable for application requirements

• Establishment of the database
• Establishment of data tables
• Establishment of indexes
• Creation of views
• Creation of triggers
• Stored Procedure Design
• User-defined function design
• Constrain data items in relational schemas, such as check constraints, PRIMARY KEY constraints, referential integrity constraints, to ensure data correctness

2.4 Application Design

Design with a structured design approach or object-oriented approach in high-level languages

2.5 System implementation

3. Optimization Strategy 3.1. Query optimization strategy

1. Minimize multiple table queries or build materialized views as much as possible
2. Retrieve only the columns you need
3. Replace or sentence with conditional sentence level with in
4. Commit commits frequently to release locks as early as possible

3.2 Table Design

1. If frequent access involves connecting to two related tables, consider merging them

2. If frequent access is made only on a portion of a field in a table, consider a decomposition table and separate that part as a table

3. Introducing materialized views for tables that are rarely updated

4. When there are some small, recurring values in the system, use a dictionary table to conserve storage space and optimize queries. such as the name of the user type in the region and system. Such values do not change during the program's run time, but need to be stored in the database.

As far as the region is concerned, if we want to query a region's records, the database needs to be queried by string matching, and if the code for a region is saved in a table and queried by the code of the region, the efficiency of the query will be greatly improved.

It is advisable to use a dictionary table to represent such values in a program. The dictionary table holds the names of such values and the collection of entities, in a way that is associated to a table that uses such values. However, in the coding stage, the programmer does not use the dictionary table, because first querying the dictionary table entity's code name, violates the original intention of improving the query efficiency. The programmer, with the help of a data dictionary, directly uses the code name to represent the entity, thus improving efficiency.

Although the dictionary tables are not actually used in practice, they should remain in the database (at least during the development period). The dictionary table appears as another form of "Data dictionary document" to illustrate which of the tables in the database are using the dictionary table.

In order to improve the data integrity of the database, the complete dictionary table and the foreign key constraints of the normal table can be preserved during the development phase. However, in the run phase of the database, the foreign keys of the normal table and the dictionary table should be deleted to improve the efficiency of operation, especially when some tables use many dictionary tables.

Case: There is millions user information in a database, the application system often needs to query the user's information according to the region. The User information table was previously saved according to the specific region name, and now the specific name is changed to the region code in the dictionary table, the query efficiency greatly improved.

3.3 Index

1. If the query is a bottleneck, an appropriate index is established on the relationship, and in general, indexing on attributes that are query criteria can improve query efficiency.
2. If the update is a bottleneck, because each update rebuilds the indexes on the table, causing inefficiencies, consider deleting some indexes.
3. Select an appropriate index, and if you frequently use a range query, the B-tree index is more efficient than a hash index
4. The index that facilitates most queries and updates is set to a clustered index.

3.4 Improve IO efficiency

1. index files and data files are stored separately and transaction log files are stored on high-speed devices
2. Frequently modify the page size of data files and index files
3. Sort data on a regular basis
4. Add the necessary index entries

4. Database naming Conventions 4.1 database objects

Object	Prefix
Database	No
Table	No
View	VI
Index	Ix
Stored Procedures	Sp
Function	Fn
Trigger	Tr
Custom data types	Ud
Default	Df
Primary key	Pk
FOREIGN key	Fk
Rule	Ru
Sequence	Sq
UNIQUE	Uq

The database objects are made up of 26 letters (case sensitive) and 0-9 of these 10 natural numbers, plus the underscore _, a total of 63 characters. No other characters (except annotations) can appear.

These object names in the same database cannot be duplicated

C Check_constraint

D Default_constraint

F Foreign_key_constraint

IT internal_table

P sql_stored_procedure

PK Primary_key_constraint

S system_table

SQ Service_queue

TR Sql_trigger

U user_table

UQ Unique_constraint

V VIEW

4.2 Naming Convention provisions

1. Table names using single-digit names

For example, do not use customers for tables that store guest information (customer)

2. Avoid unnecessary table suffixes

1, table is used to store data information, the table is a collection of rows. Then if the table name is already good enough to describe the data it contains, there is no need to add a suffix that reflects the above two points.

2, Guestinfo (storage of customer information) should be written guest,flightlist (Storage flight information table) should be written flight

3. All fields that represent time, uniformly ending with date (instead of some using date, and some using times)

For everyone familiar with the forum, it is necessary to record the last time the member logged in, the average person will be named this field Logintime or Logindate. At this time, there has been a ambiguity; if you look only at the field names of the table and don't look at the contents of the table, it is easy to interpret logintime as the number of logins, because time has a very common meaning, that is, the number of times

4. All fields that represent a number should end with count

5. All the fields that represent the links are the end of the URL

6. All names have a range of characters: A-Z, A-Z, 0-9, and _ (underscore). Other characters are not allowed to be used as names.

7. Use English words or English phrases (including abbreviations) as names, not meaningless characters or Hanyu Pinyin.

8. The name should be clear, can accurately express the meaning of things, it is best to read, to follow the "see the name of understanding" principle.

4.3 Database naming conventions

The database name does not need to be abbreviated and is named according to the actual meaning. Example: ReportServer

Database name: ReportServer

Logical data name: ReportServer; logical log name: Reportserver_log

Physical data name: reportserver.mdf; physical log name: Reportserver_log. LDF

CREATE DATABASE [ReportServer] on PRIMARY

(NAME = n ' reportserver ', FILENAME = N ' D:\Microsoft SQL server\mssql10. Mssqlserver\mssql\usedata\reportserver.mdf ', SIZE = 3328KB, MAXSIZE = UNLIMITED, filegrowth = 1024KB)

LOG on

(NAME = n ' reportserver_log ', FILENAME = N ' D:\Microsoft SQL server\mssql10. Mssqlserver\mssql\usedata\reportserver_log. LDF ', SIZE = 6400KB, MAXSIZE = 2048GB, filegrowth = 10%)

GO

Note: Avoid using the same name for all database logical names.

4.4 Table Design Naming conventions

Note Field names cannot use reserved keywords: such as ACTION,AVG, etc.

1, do not use Tab or TBL as a table prefix (originally a table, why also explain)

2. The table name consists of one and more nouns representing the contents of the table, separated by an underscore, and capitalized with the first letter of each noun, for example: User, userlogin,usergrouprelation, etc.

3. Use the table's content classification as the prefix for the table name: For example, the table associated with user information uses the prefix user, and content-related information uses the prefix contents.

4. After the table prefix, it is the description of the specific contents of the table. For example: The user login information table name is: Userlogin, the user in the Forum information table name is: Userbbsinfo

5, some as a multi-to-many connection table, you can use the prefix of two tables as the table name:

such as: User Login table userlogin, user Group table GroupInfo, these two tables establish a many-to-many relationship table named: usergrouprelation

4.4.1 Field Naming conventions

1. Field names do not have useless prefixes, such as table ' Weixinconfig ', since I already know this table is about the table, inside the name field can be used to use name, do not need to add useless prefixes like ' weixinname ', ' weixinguanzhumsg ', ' Weixinupimgmsg ' et
2. field using the actual English translation as a named field, see the name of the meaning, do not use to let people see a half-day do not know what the meaning of the field (similar: Lev1,lev2 ... ）

4.5 Stored Procedure naming

Stored Procedure name =[sp_]+[table name]+[operation name]

[Operation name]=[insert|delete|update|calculate|confirm]

Example: Sp_community_update

4.5.1 only allows applications to access the database through stored procedures

Allows the application to access the database only through stored procedures, without allowing direct SQL statements to be accessed directly from the code.

In a database development project, there are many benefits of using stored procedures in large numbers, first looking at Microsoft for information:

The advantages of using a stored procedure in SQL Server without using a Transact-SQL program stored locally on the client computer are: Allows modular programming: You simply create the procedure once and store it in the database, and you can call the procedure any time in the program. Stored procedures can be created by people with expertise in database programming and can be individually modified independently of the program source code. Allow Faster execution: If an operation requires large amounts of Transact-SQL code or needs to be repeated, the stored procedure will be faster than the Transact-SQL batch code execution. It will be parsed and optimized when the stored procedure is created, and the in-memory version of the procedure can be used after the first execution of the procedure. Each time a Transact-SQL statement is run, it is sent repeatedly from the client and compiled and optimized every time SQL Server executes the statements. Reduce network traffic: An operation that requires hundreds of lines of Transact-SQL code is implemented by a separate statement that executes the procedure code without the need to send hundreds of lines of code across the network. Can be used as a security mechanism: Even users who do not have permissions to directly execute statements in stored procedures can be granted permission to execute the stored procedure.

In addition, the benefits of using stored procedures include:

1. Logically, the stored procedure separates the application layer from the physical structure of the database. A stored procedure forms an interface between an application and a database. Such interfaces abstract complex database structures and conform to the idea of "interface-based programming" in extreme programming.

2. Encapsulating the main business logic in a stored procedure avoids writing large amounts of code at the application layer (SQL statements that insert too long into an application are inefficient and difficult to maintain). Helps improve the efficiency of development and debug stored procedures directly in Query Analyzer, enabling earlier discovery of logical problems in the system, thus improving the quality of the code.

3, in the site-class application system, SQL injection type of vulnerability has been difficult to completely eliminate the vulnerability. This type of security can be greatly reduced if the database is accessed only through stored procedures. (therefore, even a simple SQL statement should be written as a stored procedure.) ）

4, because of the use of stored procedures, the level of the application can not be concerned about the specific database structure, but only concerned about the stored procedure interface calls. Therefore, the advantages of a stored procedure are obvious in the following situations:

• Changes in requirements, the structure of the table must be changed. With stored procedures, as long as the parameters are the same, we only need to modify the corresponding stored procedures without modifying the application's code. Such a design would reduce the impact of changes in requirements on the project.

• For increased efficiency, partial field redundancy: Some frequently accessed fields, we can make redundant storage in the related tables. This improves efficiency and masks redundant details through stored procedures.

• To improve efficiency, use redundant tables (split tables): Some large tables, in order to improve query efficiency, you may need to save records to more than one table. Using stored procedures, there are stored procedures that determine which of the split tables to get or insert data from. This improves efficiency without having to care about specific split rules at the application level.

5, the use of stored procedures, to facilitate the project later or in the operation of centralized optimization of system performance. In the project development process, due to various reasons, often can not write efficient code, this problem is often in the late phase of the project or during the run time to reflect. Through the storage procedure to encapsulate the access to the database, after the project integration, through the trial operation to observe the efficiency of the system, so it is easy to identify the bottleneck of the system, and can optimize the code of the stored procedure to improve the system's operational efficiency. This optimization is more efficient and easier than optimizing in the application process.

At the same time, excessive use of stored procedures, there are some of the following concerns:

problem one : After a stored procedure is compiled, it is saved as a global object of the database, and too many stored procedures consume a large amount of memory on the database server.

Issue two : Implementing a large amount of logic in a stored procedure will allow a large number of operations to be done on the database server, rather than on the application server. When the traffic is large, the CPU usage of the database server is greatly consumed.

There is also this case: there is a site with a huge amount of traffic, and multiple Web servers make up a load-balanced server cluster, but only one central database server. When the traffic continues to increase, access to more Web servers to meet the high concurrency of access, but the database server has not been able to increase. Therefore, it is necessary to complete the business logic on the Web server as far as possible to avoid consuming the resources of the database server.

For these two worries, my idea is:

problem one : stored procedures are compiled SQL statements that are binary code in memory and do not consume too much memory. Also, stored procedures are significantly more efficient than directly using SQL statements. In other words, this is a "space-for-time" scheme, it is worthwhile to consume a bit more memory for efficiency improvement.

problem two : first, it is easier to implement the business logic in the stored procedure than in the application, and secondly, from the development efficiency, the development of the stored procedure is simpler than the application (in the same logic). In high-traffic system, the problem of resource allocation of application server and database server should be open from the angle of cost: The cost of software development, the cost of labor expenditure is much higher than that of hardware expenditure. We can easily afford to buy better servers, but it's hard to spend money on developers making the program a lot more expensive.

Using stored procedures to encapsulate business logic first saves a lot of development time and debugging time, and can greatly improve the quality of your code. Therefore, in terms of cost, stored procedures should be used.

For large traffic, the simplest way is to invest more hardware: faster hard drives, larger memory and more CPUs, and better network cards .....---yes. Wait a minute.

Second, at the application level, you can use a large number of static file caching methods to alleviate the pressure on the database. such as: Infrequently changing information, can be read from the database server, save as an XML static file on the application server, and so on.

If not, the system should be designed in the first place, considering the possible traffic, and the system should be distributed. This can fundamentally solve the problem of large traffic.

4.5.2 Naming conventions

1. The prefix of the stored procedure is similar to the prefix of the table name: Consider a series of tables as an object, a field as an object, and a stored procedure as a method of accessing an object. For example: Add a user's stored procedure named: User_adduser

2. The stored procedure is named using the prefix of the module. For example, user-managed stored procedures use the prefix user_.

3, the stored procedure prefix, is the verb + noun form of the stored procedure name (also can be a verb phrase).

4.5.3 parameter naming of stored procedures

1, the parameter name uses the Hungarian name law, uses the type prefix

2, each stored procedure has: @errno int and @errmsg varchar (255) Two output parameters. In the application, the stored procedure can be executed according to these two parameters. (These two parameters use default values, which can be ignored)

errno is an integer error message code that executes successfully returns 0. The specific meaning of the errno value is explained by the ErrMsg parameter, or through comments or documents in the code.

ErrMsg is a string description of the error message, which is used primarily as a description for the debug period and avoids using the value in the application. At the same time, pay attention to the English version of the system and the Chinese version of the system, the language selection of information on the impact of the program.

4.5.4 the recordset returned by the stored procedure

1, the output recordset of the stored procedure: for the structure of the program is clear, the stored procedure is best to return only one recordset. However, in some cases to improve performance, it is possible to output multiple recordsets

2, the record set, each output field finally specifies the alias of the field, to the real field name information loss to the client, thereby increasing the potential for hackers to find a system vulnerability.

4.5.5 Format Conventions

1. Capitalize all SQL keywords

2, the use of good variable naming norms

3, maintain a good structure, including blank lines, indentation and space, and so on.

4, the block statement, must be written on the begin ... END

5. Add a detailed comment at the beginning of each stored procedure: including the stored procedure name, parameter description, feature description, return data set description, and author and copyright notice.

6, the code inside each stored procedure must add set NOCOUNT on and set NOCOUNT OFF before and after.

7. Examples of stored procedure formats are as follows:

CREATE PROCEDURE Sp_user_update

(

@Options VarChar (100),

@strUserName varchar (20),

@strPwd varchar (50),

@errno int = 0 OUTPUT,

@errmsg varchar (255) =null OUTPUT

)

As

BEGIN

IF @Options = ' UP1 '

BEGIN

SET NOCOUNT on

/* The following is the code for the stored procedure */

SET NOCOUNT OFF

END

IF @Options = ' UP2 '

BEGIN

SET NOCOUNT on

/* The following is the code for the stored procedure */

SET NOCOUNT OFF

END

END

4.6 View naming

View names in a database cannot be duplicated

View name =vi (prefix) +[table name]. [Table name]+[description]

4.7 Primary Key naming

The primary key name in a database cannot be duplicated

Primary Key name =pk_ (prefix) +[table name]

Example: pk_community

4.8 Foreign key naming

Foreign key names in a database cannot be duplicated

FOREIGN key Name =fk_ (prefix) +[primary table name]+[]+[field name from table name]

Consider such a relationship, table hotel, field ID, Name, Cityid. Table City, Field Id,name. Because a city may have a lot of hotels, so is a one-to-many relationship, cities is the main table (1 square), hotel is from the table (multi-party). In the hotel table, Cityid is used as a foreign key.

We can write this when we implement the foreign key:

ALTER TABLE Hotelinfo
ADD CONSTRAINT Fk_hotel_city_cityid FOREIGN KEY (Cityid) REFERENCES City (ID)

4.9 Trigger naming

1. A prefix (TR) that describes the type of database object.
2. The basic section, which describes the tables added by the trigger.
3. Suffixes (_i, _u, _d), showing modification statements (Insert, update, and delete)

Trigger name =tr_ (prefix) +[table name]+[_i, _u, _d]+[field \ Description]

For example: TR _communtiy_u_name (update the field name for table community)

4.10 Default Constraint

Use format such as: df_[table name]_[column Name]

Example: DF _community_age

4.11CHECK constraints

Format: ck_[table name]_[column Name]

Example: Ck_community_number

4.12UNIQUE constraints

Format: uq_[table name]_[column Name]

Example: Uq_community_name

4.13 Field Naming conventions

1, the field does not use any prefix (the table name represents a namespace, the field preceded by a prefix appears wordy)

2, the dictionary name also avoids too common too simple name: For example, user table, User Name field username is better than name.

3, the Boolean field, with some auxiliary verbs to start, more directly vivid: for example, whether the user has a message hasmessage, whether the user through check ischecked and so on.

4, the field name is English phrases, adjectives + nouns or auxiliary verbs + verb tenses in the form of expression, mixed case, follow the "see the name of understanding" principle.

4.14 SQL Statement Specification

1. Do not allow to write select * from ..., you must indicate the specific fields that need to be read.

2. Do not allow direct write SQL statements in application code to access the database.

3. Avoid writing SQL statements that are too long in a row, and dividing the SQL statements into multiple rows is clearer where the SQL keyword is.

such as: SELECT userid,username,userpwd from User_login WHERE areaid=20

Modified to:

SELECT userid,username,userpwd

From User_login

WHERE areaid=20

More intuitive

4, in some block form of SQL statements, even if there is only one line of code, but also to add the begin ... End block.

such as: If EXISTS (...)

SET @nVar = 100

Should be written as:

IF EXISTS (...)

BEGIN

SET @nVar = 100

END

5. The empty lines and indents of SQL batch statements are consistent with the general structured programming language and should be kept in good code format.

6. All SQL keyword capitalization

4.15 Cursor Usage Conventions

1, if not necessary, do not use cursors

2. A stored procedure containing cursors must be carefully tested for performance.

4.16 Index Naming conventions

It is common for databases to be indexed for maintenance, but without a normalized naming, we may need to take some time to understand the many indexes of a table.

1. If a primary key exists in the table by default, the clustered index of the table is the primary key column, the name of the primary key has already been mentioned before, and the index name is the same as the primary key name (pk_ table name)
2. For nonclustered indexes on a table, it is recommended to use (ix0_ table name, ix1_ table name) ...., so go on, so also very clearly expressed the index, for many named articles mentioned in the need for detailed expression of specific columns, I personally feel no need, first clustered index often involves multiple columns, it is difficult to list all the columns And it has a beautiful effect.

When you execute the Select NAME from SYS. COLUMNS when querying an index, you quickly know based on the name name that the index is from that table, whether it is a nonclustered index, rather than associating it with the object table based on the Objectid column.

4.17 Function Naming conventions

Function naming is divided into two categories: 1. Functions for objects, 2. Functions that are used as accessibility actions (not specific database objects)

1. First class naming: fn_+[user]+_+[object name] For example: fn_user_student (Operation function for Student)
2. Second class naming: fn_[specific function explanation] Example: fn_spit (splitting function on a field)

SQL Server Database Design specification