You must know that Microsoft SQL Server one

Source: Internet
Author: User
Tags microsoft sql server

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.