INCLUDE Clause in Non-clustered Indexes

Summary: improve performance of you queries by including hot data into index

If you have a big table, let’s say with 100 columns and million rows, and some of those 100 columns, say 5 of them are being used in queries very often, you may significantly improve performance by building a special non-clustered index with INCLUDE clause. People often overlook this opportunity, but it’s amazing.

Something like this: table “bigTable” contains plenty of columns rare01…rare85 which are rarely required. Users very often search by columns “oftenSearchBy01” and “oftenSearchBy02”.

IF EXISTS 
(
SELECT 1 FROM sys.objects WHERE name='bigTable' AND type='U'
)
DROP TABLE bigTable
GO

CREATE TABLE bigTable 
(
uniqueMeanenglessId INT IDENTITY (1,1),
rare01	INT, rare02 INT, rare03 INT, rare04 INT, rare05 INT, 
rare11	INT, rare12 INT, rare13 INT, rare14 INT, rare15 INT, 
rare21	INT, rare22 INT, rare23 INT, rare24 INT, rare25 INT, 
rare31	INT, rare32 INT, rare33 INT, rare34 INT, rare35 INT, 
rare41	INT, rare42 INT, rare43 INT, rare44 INT, rare45 INT, 
rare51	INT, rare52 INT, rare53 INT, rare54 INT, rare55 INT, 
rare61	INT, rare62 INT, rare63 INT, rare64 INT, rare65 INT, 
rare71	INT, rare72 INT, rare73 INT, rare74 INT, rare75 INT, 
rare81	INT, rare82 INT, rare83 INT, rare84 INT, rare85 INT,
oftenSearchBy01 INT, oftenSearchBy02 INT,
often01 INT, often02 FLOAT, often03 VARCHAR(1), often04 INT,
CONSTRAINT PK_bigTable PRIMARY KEY (uniqueMeanenglessId)
)
GO

Obviously, it make sense to build a non-clustered index on these two columns, and you already figured that much:

CREATE NONCLUSTERED INDEX [IX_bigTable_often] ON [bigTable]
 (
 oftenSearchBy01, oftenSearchBy02
 )

But there is another thing: 95% of the times, when you call the table, you actually need data only from columns “often01…often04”. Did you know that you actually can avoid using your big table in that 95% of cases at all, and pull out all required data right from the index in one quick shot? All you have to do is include all often used columns into index with “INCLUDE” clause:

CREATE NONCLUSTERED INDEX [IX_bigTable_often] ON [bigTable]
 (
 oftenSearchBy01, oftenSearchBy02
 )
 INCLUDE
 (
 often01, often02, often03, often04
 )

Basically, you get yourself a small table (your non-clustered index) sitting next to your monster and having everything you need. Only in case when you query for something “rare”, SQL will go to the actual bigTable.

SQL example can be downloaded from Dropbox location, no need to copy/paste it.