SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Index with included column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rocknpop
Posting Yak Master

184 Posts

Posted - 09/06/2013 :  06:44:24  Show Profile  Reply with Quote
How should the index be created for such a query. I know included columns were introduced to overcome 900 bytes/16 col.s limitation and non-key are not part of the index but is there anything else too regarding the performance -

SELECT COL1, COL4 FROM TABLE1 WHERE COL12=1 AND COL3=2

CREATE INDEX index1 ON table1 (col1, col2) INCLUDE (col3,COL4)
or
CREATE INDEX index1 ON table1 (col1,col4) INCLUDE (col2, col3)
or
CREATE INDEX index1 ON table1 (col3,col4) INCLUDE (col1,col2)

Are the above indexes the same? Any performance differences?



--------------------
Rock n Roll with SQL

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 09/06/2013 :  07:55:59  Show Profile  Reply with Quote
I think INDEX should be as follows:
CREATE INDEX index1 ON table1 (col2,col3) INCLUDE (col1, col4)
GO
SELECT COL1, COL4 FROM TABLE1 WHERE COL12=1 AND COL3=2

http://blog.sqlauthority.com/2007/04/23/sql-server-understanding-new-index-type-of-sql-server-2005-included-column-index-along-with-clustered-index-and-non-clustered-index/

NOTE: Try viceversa INDEX and check the performance by enabling execution plan ( CTRL + M to enable execution plan in SSMS)

--
Chandu
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
362 Posts

Posted - 09/06/2013 :  12:29:28  Show Profile  Reply with Quote
The keys in the index should be the columns that are compared, in this case in the WHERE clause.

So the key is either: (col2, col3) or (col3, col2).

Which order they should go on depends on (1) if one column is always compared and the other is only sometimes compared, the one that is always compared should go first (2) if both are always specified, the one that is more selective (has fewer matching rows) should go first.

If this table is (almost) always looked up using those columns, it shouldn't be a covering index but a clustered index, on both columns if needed.
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 09/11/2013 :  02:24:02  Show Profile  Reply with Quote
Thank you for your replies.

--------------------
Rock n Roll with SQL
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000