July 3, 2019 at 3:00 pm
Hi,
Recently I observed we have an Indexed view having multiple NC Indexes with same set of Included columns. I wanted to know how it is going in impact on the performance, if it would.
Also one of the NC Index on same Indexed view having Key Column from other index as Included column. How if impact on the performance?
Thanks,
MH-09-AM-8694
July 3, 2019 at 4:02 pm
Having matching included columns on multiple non-clustered indexes are fine so long as you're not overlapping indexes. Included columns are great to ensure you can 'cover' a query in order to avoid key lookups. Here's the catch with indexes...the more you have the more overhead you have. Every insert, update and delete will need to be done on all qualifying indexes. More columns also means more data storage. Think of an index as another table. Indexes are crucial to keep things humming along but they need to be implemented properly.
A couple of examples:
These two indexes have the same included columns but that's fine if they are needed for different queries.
CREATE NONCLUSTERED INDEX [NC_example1] ON MyTable
(
KeyA ASC,
KeyB ASC
)
INCLUDE (
Column1,
Column2
)
CREATE NONCLUSTERED INDEX [NC_example2] ON MyTable
(
KeyX ASC,
Keyy ASC
)
INCLUDE (
Column1,
Column2
)
However in the next example we have overlapping indexes. You could get rid of the second index because the first index will handle queries that only use KeyA just as well. Keep in mind the order of the keys absolutely matter. If the order on the first index were reversed than they would not be overlapping.
CREATE NONCLUSTERED INDEX [NC_example1] ON MyTable
(
KeyA ASC,
KeyB ASC
)
INCLUDE (
Column1,
Column2
)
CREATE NONCLUSTERED INDEX [NC_example2] ON MyTable
(
KeyA ASC
)
INCLUDE (
Column1,
Column2
)
In the next example I'm queriying on 3 different columnns but only the first 2 are included in the index. In this case SQL would have to use key lookups and fetch the 3rd column from the Clustered Index (or Heap) which would cost you some extra overhead. In this case just inlcuding the 3rd column in the index would mean that the same query would be covered by the index and it would't have to fetch data from any other indexes.
CREATE NONCLUSTERED INDEX [NC_example2] ON MyTable
(
KeyA ASC
)
INCLUDE (
Column1,
Column2
)
SELECT Column1, Column2, Column3 FROM MyTable WHERE KeyA = '12345'
That doesn't mean you want to include every colum in every index. (see comment above) Planning and implementing indexes is important and it's more of an art. You really need to know how your tables are queried and properly build the right indexes without going over the top.
Hope this helps
July 4, 2019 at 7:40 am
Thank you very much for the details, But does it make sense having same columns in "Included" in NC index which are key columns of Clustered Index?
Regards
MH-09-AM-8694
July 8, 2019 at 12:31 pm
The key column(s) from your clustered index don't have to be added because they are automatically added to every single Non-Clustered index you create. You can add them yourself but it doesn't make any difference. SQL needs to make sure those keys are included in order to do key lookups. It cant' fetch the rest of the columns for a row if it doesn't know what row it belongs to the clustered index or heap. When you have a heap the system auto generates a unique id for each row and that id is included with any non-clustered index you create instead.
July 10, 2019 at 9:32 am
Thanks Y.B.!
MH-09-AM-8694
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply