Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rocknpop
Posting Yak Master
201 Posts |
 Posted - 2013-09-06 : 06:44:24
|
| 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=2CREATE INDEX index1 ON table1 (col1, col2) INCLUDE (col3,COL4)orCREATE INDEX index1 ON table1 (col1,col4) INCLUDE (col2, col3) orCREATE INDEX index1 ON table1 (col3,col4) INCLUDE (col1,col2)Are the above indexes the same? Any performance differences?--------------------Rock n Roll with SQL |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-09-06 : 12:29:28
|
| 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. |
 |
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-09-11 : 02:24:02
|
| Thank you for your replies.--------------------Rock n Roll with SQL |
|
|
|
|
|
|
|
|
Index with included column