{"id":1783,"date":"2019-04-21T11:00:05","date_gmt":"2019-04-21T04:00:05","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1783"},"modified":"2020-04-11T20:12:22","modified_gmt":"2020-04-11T13:12:22","slug":"sql-server-indexes-with-included-columns","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-indexes\/sql-server-indexes-with-included-columns\/","title":{"rendered":"SQL Server Indexes with Included Columns"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use indexes with included columns to improve the speed of queries.<\/p>\n\n\n\n

Introduction to SQL Server indexes with included columns #<\/a><\/h2>\n\n\n\n

We will use the sales.customers<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n

\"customers\"<\/figure>\n\n\n\n

The following statement creates a unique index<\/a> for the email<\/code> column:<\/p>\n\n\n

CREATE<\/span> UNIQUE<\/span> INDEX<\/span> ix_cust_email \nON<\/span> sales.customers(email);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement finds the customer whose email is 'aide.franco@msn.com'<\/code>:<\/p>\n\n\n
SELECT<\/span>    \n    customer_id, \n    email\nFROM<\/span>    \n    sales.customers\nWHERE<\/span> \n    email = 'aide.franco@msn.com'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you display the estimated execution plan for the above query, you will find that the query optimizer uses the index seek operation on the non-clustered index.<\/p>\n\n\n\n
\"index<\/figure>\n\n\n\n
However, consider the following example:<\/p>\n\n\n
SELECT<\/span>    \n\tfirst_name,\n\tlast_name, \n\temail\nFROM<\/span>    \n\tsales.customers\nWHERE<\/span> email = 'aide.franco@msn.com'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the execution plan:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this execution plan:<\/p>\n\n\n\n
First, the query optimizer uses the index seek on the non-clustered index ix_cust_email<\/code> to find the email<\/code> and customer_id<\/code>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Second, the query optimizer uses the key lookup on the clustered index of the sales.customers<\/code> table to find the first name and last name of the customer by customer id.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Third, for each row found in the non-clustered index, it matches with rows found in the clustered index using nested loops.<\/p>\n\n\n\n
As you can see the cost for key lookup is about 50% of the query, which is quite expensive.<\/p>\n\n\n\n
To help reduce this key lookup cost, SQL Server allows you to extend the functionality of a non-clustered index by including non-key columns.<\/p>\n\n\n\n
By including non-key columns in non-clustered indexes, you can create nonclustered indexes that cover more queries.<\/p>\n\n\n\n
Note that when an index contains all the columns referenced by a query, the index is typically referred to as covering the query.<\/p>\n\n\n\n
First, drop the index ix_cust_email<\/code> from the sales.customers<\/code> table:<\/p>\n\n\n
DROP<\/span> INDEX<\/span> ix_cust_email \nON<\/span> sales.customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> UNIQUE<\/span> INDEX<\/span> ix_cust_email_inc\nON<\/span> sales.customers(email)\nINCLUDE<\/span>(first_name,last_name);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, the query optimizer will solely use the non-clustered index to return the requested data of the query:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
An index with included columns can greatly improve query performance because all columns in the query are included in the index; The query optimizer can locate all columns values within the index without accessing table or clustered index resulting in fewer disk I\/O operations.<\/p>\n\n\n\n
The syntax for creating an index with included columns #<\/a><\/h2>\n\n\n\n
The following illustrates the syntax for creating a non-clustered index with included columns:<\/p>\n\n\n
CREATE<\/span> [UNIQUE<\/span>] INDEX<\/span> index_name\nON<\/span> table_name(key_column_list)\nINCLUDE<\/span>(included_column_list);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
Then, create a new index<\/a> ix_cust_email_inc<\/code> that includes two columns first name and last name:<\/p>\n\n\n