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 |
|
adlo
Posting Yak Master
108 Posts |
 Posted - 2010-04-08 : 07:53:22
|
| I have an existing tableColumn1 intColumn2 intColumn3 intColumn4 intColumn5 intColumn6 stringand an indexCREATE UNIQUE NONCLUSTERED INDEX IX_myTableIndex ON dbo.myTable(Column2,Column3) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]if i sort by column 2, column 3 and then column2 and column 4 the results would be 99% in the same sort order.Both column3 and column4 are exactly the same except from one scenario where column3 is sometimes zero and column4 is not zero.So combination of column 2, column 3 and column2, column4 are used in many where clauses so I need indexes.So 1. do i have to use two none clustered indexes? Column2, Column3 and column2, column 42. Keep the existing one as the sort order is almost exactly the same. So adding an existing index is not required.3. have only one index on Column2,column3, Column4. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-08 : 12:41:44
|
| Option 1.If you put an index on Column2,column3, Column4, then it will only be partially seekable if the query filters on Column2, Column4.If you only have one index on Column2, Column3, then it will only be partially seekable if the query filters on Column2, Column4.--Gail ShawSQL Server MVP |
 |
|
|
adlo
Posting Yak Master
108 Posts |
Posted - 2010-04-08 : 12:51:19
|
| OK thanks. Just trying to eliminate indexes.Another question if I have an index 1(col1,col2,col3) 1. do I need an index for Col1,Col2 if those two columns also needs to be indexed? I assume I won't since sql should be intelligent to use the first 2 columns of index 1 without any performance impact thus saving space but not having to keep an separate index for Col1,Col2.2. Lets say my primary key is col1,col2,col3. This would mean i never have to create an index for col1,col2 if the need is ever required when i use those 2 cols in where clauses often. Is this statement correct? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
|
|
|
|
|
indexes - 2 cols values always or near each other