WebProfIT Consulting
  • Home
  • Cloud
  • SQL
  • Big Data
  • Contact Us
  • RSS

Categories

  • AWS (1)
  • Big Data (1)
  • Cloud (1)
  • SQL (4)
    • Microsoft SQL Server (3)
  • Tips and Tricks (2)
  • Tutorials (1)

Recent Comments

    • Home
    • Microsoft SQL Server
    • Tips and Tricks
    • SQL
    • Tutorials
    • INCLUDE Clause in Non-clustered Indexes

    INCLUDE Clause in Non-clustered Indexes

    2016-09-03 Leave a Comment Written by Pavel Danilov

    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.

    Microsoft SQL Server, SQL, Tips and Tricks, Tutorials
    BigTable, HOT, INDEX, non-clustered index, performance, primary key, SQL
    Renaming Primary Keys to Standard

    Leave a Reply Cancel reply

    You must be logged in to post a comment.

    Recent Posts

    • How to shrink size of Linux EBS volume in AWS
    • Data Normalization
    • Renaming Primary Keys to Standard
    • INCLUDE Clause in Non-clustered Indexes
    • Using a DDL trigger to control and automate history of data changes

    Archives

    WebProfIT Consulting, Inc. 2016