SQL Server has been using and often overlooked knowledge points in a

There have been no update blog for nearly 2 months, it is because of the recent occurrence of too many things, quit the job, in Xiangya Hospital for one months, and then a new job ...

In peacetime, many of SQL Server's knowledge points are often used, but sometimes we tend to ignore them, in the past year, has been using MySQL, and now began to contact SQL Server, So let's summarize some of the commonly used and easily overlooked SQL Server knowledge points for the purpose of memo.

All operations are based on the Northwind database.

SET NOCOUNT on Introduction

In the stored procedure, the set NOCOUNT on is often used;

Effect: Prevents the return of row count information in the result set that is displayed by T-SQL statements or by USP.
When set NOCOUNT on, the count is not returned, when set NOCOUNT off, the count is returned.

When set NOCOUNT on, the @ @RowCount is updated, but the DONE_IN_PROC message for each statement of the stored procedure is not sent to the client.

If the stored procedure contains statements that do not return actual data, the network traffic will be significantly reduced with set NOCOUNT on, which can dramatically improve application performance.

The settings specified by set NOCOUNT only take effect at execution time or runtime and do not take effect when parsing.

Example:

 Use Northwind GO SET OFF ; SELECT TOP 5  from Orders GO

The results of the implementation are as follows:

 Use Northwind GO SET  on ; SELECT TOP 5  from Orders GO

The results of the implementation are as follows:

Go Introduction

If you just execute a statement, there is no go all the same.

It is not the same if you separate multiple statements with go.

Each statement that is delimited by Go is a separate transaction, and a statement execution failure does not affect the execution of other statements.

GO is not a Transact-SQL statement, but a command that can be identified for SQL Server Query Analyzer.

If your SQL is too long, you have to write go, or some statements, it can only be the first sentence operation, before you have to write go, go means batch processing statements, add this go, execute the Go line code, execute the next code after execution.

In this case, go is used to achieve the purpose of batch processing data, otherwise it will be an error.

IF EXISTS(SELECT 1  fromSys.viewsWHEREName='View_orderinfo')DROP VIEWView_orderinfoCreate ViewView_orderinfo asSelectC.contactname,c.companyname,o.orderdate,o.shipname,o.orderid,o.freight from [Orders]OInner JoinCustomers C onO.customerid=C.customerid

Will error

Must be:

IF EXISTS(SELECT 1  fromSys.viewsWHEREName='View_orderinfo')DROP VIEWView_orderinfoGOCreate ViewView_orderinfo asSelectC.contactname,c.companyname,o.orderdate,o.shipname,o.orderid,o.freight from [Orders]OInner JoinCustomers C onO.customerid=C.customerid

Select COUNT (*) count (1) The difference between count (2) Count (' a ')

COUNT (*): Find the shortest column in the table to count the rows

Count (1) count (2) Count (' a '): counts the number of rows in a constant sequence. They are executed in the same way, without any distinction.

It is clear that the count (1) count (2) Count (' a ') is more efficient, because count (*) calculates the shortest column first and then the statistics. While SQL Server Query Analyzer now automatically does some optimizations for us, we must know how they are implemented.

With (NOLOCK)

Disadvantages:

1. Dirty reads are generated

2. Only applicable with SELECT query statements

Advantages:

1. Some articles say that the efficiency of SQL queries added with (NOLOCK) can be increased by 33%.

2. Can be used for statements such as INNER join or left join

Dirty Read : A user made a change to a resource, at this time another user just read the modified record, and then, the first user discard the changes, the data back to the changes, these two different results are dirty read.

Detailed content:

To improve the query performance of SQL, we will consider indexing (index) first. In fact, in addition to the establishment of the index, when we are under the SQL command, in the syntax of adding a section with (NOLOCK) can improve the environment of large online query data set by lock, thereby improving the performance of the query.

However, it is important to note that the SQL select with (NOLOCK) may cause dirty read (dirty Read).

For example:

 select   O.orderid,o.orderdate,o.freight, D.quantity,d.unitprice  from  [ Span style= "color: #ff0000;" >dbo  ] . [  ]  o with   (NOLOCK)  join  [ dbo  ] . [ order Details    D  (NOLOCK)  on  o.orderid=  D.orderid 

DELETE, INSERT, update these instructions that require transaction cannot use with (NOLOCK).

Add with (NOLOCK) to tell SQL Server, our select instruction does not need to consider the current table transaction lock state, so the performance will be significantly improved, and the database system lock phenomenon will be significantly reduced ( Contains dead Lock).

When using NOLOCK, it allows you to read data that has been modified but not yet completed. So if there is a need to consider the real-time integrity of transaction transactional data, using with (NOLOCK) takes a good look.
If you do not need to consider transaction,with (NOLOCK) may be a useful reference.

SQL Server has been using and often overlooked knowledge points in a