You must know that Microsoft SQL Server one

Do not know why I WIN10 environment installed Sqlserver2012, the function of smart tips is basically no use, no way, I still choose to install the plug-in SQL Prompt 5 bar. : http://www.uzzf.com/soft/95310.html

Some people reflect the double hints (Prompt and Transact-SQL IntelliSense) of SQL Prompt 5 when writing SQL code, and find it awkward to turn off Transact-SQL IntelliSense in Settings.

Specific setup steps: Tools, options, find text editor, Transact-SQL, "IntelliSense"----"Enable IntelliSense (E)" On the right, in the Options window , re-open Ms SQL Manage Studio to

Here is the configuration diagram:

Take the Northwind database as an example.

The principle of dynamic index finding in SQL Server database--Index finding principle

The so-called Dynamic index lookup is when SQL Server executes the statement, only to format the query condition, and then according to the different query conditions automatically to match all items, so as to achieve performance improvement purposes.

Suppose we execute the following SQL statement:

Select *  from [dbo]. [Orders] where inch ('90110','01307');

Let's look at the SQL execution plan here in two ways.

One way is to enter the following steps directly before the SQL statement:

Set  on GO

Mode two, query--Query options--Advanced

Press F5 to execute the SQL statement, and the result is as follows.

The text reads as follows:

  | -- Clustered Index Scan (OBJECT: ([northwind].[ DBO]. [Orders]. [Pk_orders]), Where: ([Northwind]. [dbo]. [Orders]. [Shippostalcode]=n ' 01307 ' OR [Northwind]. [dbo]. [Orders]. [Shippostalcode]=n ' 90110 '))

The "Include Actual execution Plan" button in the toolbar of the point can be viewed as shown in the execution plan.

We will see that the execution plan is indexed and the index scan is the best way to query SQL Server. Actually, SQL Server automatically modifies the in operation in the SQL statement that we wrote earlier to the or query operation.

Let's look at the following sql:

 UseNorthwindGODECLARE @p1 NVARCHAR( -),@p2 NVARCHAR( -)SELECT @p1=N'90110',@p2=N'01307'SELECT *  fromDbo. OrdersWHEREShippostalcodeinch(@p1,@p2)

View the execution plan its execution results are identical to the previous SQL statement.

  | -- Clustered Index Scan (OBJECT: ([northwind].[ DBO]. [Orders]. [Pk_orders]), Where: ([Northwind]. [dbo]. [Orders]. [shippostalcode]=[@p2] OR [Northwind]. [dbo]. [Orders]. [shippostalcode]=[@p1]))

Before Sqlserver2012, its execution plan was different, because Sqlserver2012 automatically helped us optimize.

So sometimes we find out that even if our SQL statement sucks, but we find out how to do it, it's because SQL Server's execution plan is automatically optimized, but we still have to know its rationale and try to write high-performance SQL statements.

You must know that Microsoft SQL Server one