CODEWITHSUNDEEP
sql-serversql-server-2008indexing
Daniel
Daniel

Reputation: 8368

Nonclustered index in SQL Server 2008 R2 with includes vs non includes

Running the advisor it suggests I should create an index on Path, Value and to include Id.

Path varchar(250)
Value bigint
Id uniqueidentifier

  1. When using the index in a query, will it be faster having Id in the index rather than as an include?

  2. Will inserts be slower having Id as an include rather than in the index?

Upvotes: 0

Views: 884

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21756

  • 1) Depends on query if it looks like

    select Id 
from YourTable 
WHERE Path = x and Value = y and Id =(or>, <, between etc.) z

i.e. - if query involves exact values (not ranges) of Path and Value - then yes, else - no, will be slower because of more thicker non-leaf rows

  • 2) Actually - yes, but it can be negligible, because the sql needs to sort AND by Id, but if your Path and Value is versatile enough - the difference will be very tiny. In IO there will be a little gain if saving Id in INCLUDE section - because no needs to save it on non-leaf pages

As you noticed:

Query uses Id, Path and Value. Id and Path in join restriction and Value in where. The Id is furthermore a FK on primary table. With this short info, How would you design the index?

So, its better include all the rows in index, and the best order of columns will depend on structure/indexes of other table in JOIN.

The index should look like (Value, Id, Path) or (Value, Path, Id) - depending on mentioned above.

Upvotes: 1

Lucero
Lucero

Reputation: 60190

1) If you do operations on the ID which could be addressed by the index (e.g. compare against it), then having it in the index instead of only including it can make the query faster.

2) No, if anything it may be a bit faster and maybe cause less fragmentation in the index as include instead of being part of the index, because the server doesn't have to sort the included columns to put them in the index storage.

Queries run fastest if all the required columns are stored in the index data. Including columns will do just that: add more data to the index without actually indexing it, so that no lookup via PK is required to get the data.

Upvotes: 2

Related Questions