SQL Server performance optimization included columns

Original: SQL Server performance optimization included columns

SQL Server performance optimization included columns

Guide: Data number optimization query has always been a relatively hot topic, niche in this area can only be regarded as an introductory student. This

Oh, God, we'll talk about it. The function of the database contains columns and the optimization effect

quote an explanation from the next MSDN section:

when all columns in a query are included as key or nonkey columns in the index, an index with an inclusive nonkey column can be explicitly

Improve query performance. This allows for performance gains because the query optimizer can find all the column values in the index;

Reduce disk I/O operations by accessing table or clustered index data

What does the paragraph above mean?

This means that the inclusion column is set up to improve query performance and reduce IO output.

So we all feel very abstract, let's give an example to illustrate the effect of this included column,

How do I create a include column index?

1. In Object Explorer, click the plus sign to expand the database that contains the table for which you want to create an index with nonkey columns.

2. Click the plus sign to expand the Tables folder.

3. Click the plus sign to expand the table where you want to create an index with nonkey columns.

4. Right-click the Indexes folder, point to New Index, and select Nonclustered indexes ....

5. In the New Index dialog box, on the General page, enter a name for the new index in the Index name box.

6. Under the Index Key Columns tab, click Add ....

7. In the Select Columns from table_name dialog box, select the check boxes for one or more table columns that you want to add to the index.

8. Click OK.

9. Under the Included Columns tab, click Add ....

10. In the Select Columns from table_name dialog box, select the check boxes for one or more table columns that you want to add to the index as non-key columns.

11. Click OK.

12. In the New Column dialog box, click OK.

Give an example to illustrate the effect of this included column

Sql:select Sex from User Where username= ' small Three '

Let's take a look at the above statement according to username to query the gender of small three, join our table did not build any index, if the data is relatively small, this query statement is not any problem, but when our user volume reached tens of millions, This query may not have been so fast, how to improve query efficiency at this time?

To build a nonclustered index

The first method: a nonclustered index is established for username, and the database can find the location of the data more quickly after the index is established.

build index include column

The second method: while creating a nonclustered index for username, it is recommended to include columns, including the sex column, so that you can quickly locate the username and quickly locate the physical location of sex. The Include column is a performance supplement to the query criteria.

Application scenario: This query is used very frequently. Select age from User Where username= ' small Three '

For example, when querying username and querying for a very high number of age, you can consider the inclusion of the username when creating a nonclustered index.

Attached: Creating an SQL statement containing columns

1 2 3 4 5 6 7 8 9

USE AdventureWorks2012; GO -- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.  -- index key column is PostalCode and the nonkey columns are -- AddressLine1, AddressLine2, City, and StateProvinceID. CREATENONCLUSTERED INDEXIX_Address_PostalCode ONPerson.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); GO

At the same time welcome to my personal blog guest: http://www.diyibk.com/post/140.html