SQL Server Statement Usage specification

SQL Statement usage Specification specification content and precautions:

1, the query time using the top 10/top and where sentence control each execution of SQL returned result collection, Return the smallest results while meeting business needs.

2, using the method of data projection, select the data columns required by the front-end program, do not allow the use of the select * FROM dbo. access the columns in the database in a TableA way.

such as: Select UserID, username, sex from dbo. TableA

3. Use stored procedures to access data in the database as much as possible. Reduce access to database resources directly using SQL scripts.

4.all SQL statements are not allowed to use system tables and system fields as a basis for judging business logic or generating temporary data.

For example: select name from sys.sysobjects where xtype = ' U ' to query all user tables.

5, write long scripts need to pay attention to indentation and line wrapping, improve The reading of SQL script.

Such as:

 --  query orders   top  10   OrderID, UserID, ProductID  from   dbo. Orders  where   ...  --  insert orders  insert  into   dbo. Orders (Orderid,userid,productid)  values   ( 1 , 2 , 3 ) 

6. The statement inserting the data needs to write the inserted field in full.

Above

Inserting the Orders sample, not allowed to write:insertintoselect1,2,3

7,theSQL statement calls the object when you need to add the owner of the object, if it is the default owner, need to add dbo.

Such as:

Select  from Select  from TableA

8. Do not add the order by clause to theSQL script , if the demand does not require that the returned query result be ordered .

9,SQL statement If you design to a multi-table connection query, you need to use the table alias, instead of using the table name . column Name "in the way.

 such as: select   A.userid,a.username,b.sex  from   dbo. TableA a  join  dbo. TableB b on  a.userid  B.userid  where   .... Error:  select   Tablea.userid,tablea.username,tableb.sex  from   dbo. TableA  join  dbo. TableB on  tablea.userid  Tableb.userid  where  ... 

in the case of large data insertions, it can save a lot of resources by integrating the data that needs to be inserted into the database.

Such as: General wording

Insert  into dbo. TableA (id,name)Select1, ' AA 'Insert into dbo. TableA (id,name)Select2, ' BB '

Suggested wording :

Insert  into dbo. TableA (id,name)Select1, ' AA 'UnionallSelect2 , ' BB '

Suggested notation 2:

Insert  into Dbo.tablea (id,name) Values (1, ' AA '), (2, ' BBB ')

Such as: General wording :

UpdateaSetA.userno=A.userno+1 fromdbo. TableA aWhereA.username=' Zhang San 'GoUpdateaSetA.userno=A.userno+1 fromdbo. TableA aWhereA.username=' John Doe '

Suggested wording

Update a Set = A.userno+1 from dbo. TableA aWhere in (' Zhang San ', ' John Doe ')

One, multi-table queries need to strictly restrict the alias of the field, in order to prevent SQL parsing logic error.

Such as:

   Select from where in (  select  from dbo. UserInfo) If there is no orderid,sql in the subquery UserInfo to look for the OrderID field in the parent query, resulting in a logic error

Thereferenced object name or field name, which is called Strictly according to its original case

Such as: Query the Orders table

cannot be written as SELECT OrderID,.... From Dbo.orders ...

To write a select OrderID,.... FROM dbo. Orders ...

Prevents database encoding rules from changing, resulting in the inability to find objects.

SQL Server Statement Usage specification