Timeline for T-SQL SELECT use multiple indexes for no reason
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 8, 2016 at 14:24 | comment | added | David Spillett | Also, the lookup of more data from the clustered index (or heap) is not always significant. If only a couple of rows are being inspected the difference is negligible. In some cases where it is significant rearrangements of the statement/procedures affected can be a better optimisation than taking extra space by enlarging the index. This is one of those places where you need to use your knowledge of the application(s) and/or specific benchmarks to decide the better way to go. | |
| Jan 7, 2016 at 10:02 | history | edited | David Spillett | CC BY-SA 3.0 |
added 138 characters in body
|
| Jan 7, 2016 at 9:54 | comment | added | David Spillett |
@SteffenMangold: For queries immediately after creating the index there will be no difference between the two versions, the difference with INCLUDE is that it doesn't need to keep the index in the order of those (effectively random) columns which leads to far fewer page splits on UPDATE and INSERT operations. As well as slowing changes this will over time lead to greater fragmentation that will slow down reads too and make your data take more space. So if you are only adding columns to an index to reduce extra references to the clustered index or heap, use INCLUDE.
|
|
| Jan 7, 2016 at 1:14 | comment | added | Antonín Lejsek | The time difference for query of newly created indexes would be zero. But the overhead of data change may differ a bit. | |
| Jan 6, 2016 at 20:27 | vote | accept | Steffen Mangold | ||
| Jan 6, 2016 at 20:27 | comment | added | Steffen Mangold | Thank you! Perfect answer, I don't know that the "bookmark lookup" from the index to the row data took so many time. Now my query time drop from 12sec to far under 1sec. As feedback: there is almost no difference in time or size between INCLUDE or not in my scenario. | |
| Jan 6, 2016 at 14:36 | history | answered | David Spillett | CC BY-SA 3.0 |