Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rocknpop
Posting Yak Master

201 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
2241 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
Aged Yak Warrior

USA
550 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

201 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  
 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.06 seconds. Powered By: Snitz Forums 2000