SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to SQL Server Indexes: Step 5, Included Columns


Stairway to SQL Server Indexes: Step 5, Included Columns

Author
Message
David Durant
David Durant
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 5, Included Columns
russella0-96423
russella0-96423
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 255
A good article but much poorer than the first 4 steps.

When you refer to 'non read' activity - you do very little to point out what types of operations in the execution plan are 'non read'.

In fact apart from telling us to switch it on you don't seem to discuss the execution plan at all although my guess is we would need to look at it to understand you when you're explaining how a query was resolved.

Still a good article though.



Mike Dougherty-384281
Mike Dougherty-384281
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 944
I would like to have seen more comparison of the difference between included columns and additional columns in the index key.

I appreciate the distinction that they are stored as "extra" data and the benefit comes from not having to move rows in the index when the extra column is updated in the base table. In the case of rarely-write, frequently read data with groups that are sometimes requested via either 2nd or 3rd key specificity in the where clause, I would expect the multi-key covering index to perform better than included columns. Yes, that may be a contrived scenario but it might also be common for some shops.

How does the index with included columns compare to an indexed view? I guess storage space is of little concern if we're going to keep redundant copies of data in indexed views or included columns - but we commonly pay the price to trade space for speed, right?
Chris Harshman
Chris Harshman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11136 Visits: 4698
Mike Dougherty-384281 (7/13/2011)
...In the case of rarely-write, frequently read data with groups that are sometimes requested via either 2nd or 3rd key specificity in the where clause, I would expect the multi-key covering index to perform better than included columns

It depends on how frequently "sometimes requested" is, and how big a datatype that extra column is. Since an index is a tree structure, adding that extra column as part of the index key means SQL will be able to store less record keys on all the index pages, so while the sometimes query with the extra key will perform better, the queries that only use the first key fields would have to do more I/O to scan the same number of record keys. With INCLUDE columns, that extra value is only stored in the leaf nodes of the tree.

How does the index with included columns compare to an indexed view?

While include column indexes behave simmilarly in that SQL's optimizer will use them automatically when it is more efficent (in any edition not just Enterprise), an indexed view is clustered, and requires schema binding set on the view. Both would only have the non-key values in the leaf nodes. Both can have WHERE clause conditions, but an indexed view can be on more than one table while the include column indexes can obviously only be on one table.
Mike Dougherty-384281
Mike Dougherty-384281
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 944
It depends on how frequently "sometimes requested" is, and how big a datatype that extra column is. Since an index is a tree structure, adding that extra column as part of the index key means SQL will be able to store less record keys on all the index pages, so while the sometimes query with the extra key will perform better, the queries that only use the first key fields would have to do more I/O to scan the same number of record keys. With INCLUDE columns, that extra value is only stored in the leaf nodes of the tree.


I didn't think of the impact on keys per page with larger keys.

Thanks for the education.
David Durant
David Durant
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 23
Chris and Mike - Thanks for stepping in and clarifying some things.

Indexed views were one of the subjects that did not make the cut when we were discussing what should or should not be a Level.

In a nut shell - when you create an indexed view, by creating a clustered on the view; SQL Server turns your logical object, the view, into a physical object, essentially a clustered indexed table. Under the covers, SQL Server creates the table and clustered index, selects the data of the view into the table, and creates triggers on the tables referenced by the view to keep the data in the new physical object in sync with the original view definition.

Indexed views are useful in a reporting database where updates to the data occur periodically (perhaps nightly or weekly) with the data being predominately static in between updates. Querying the indexed view can eliminate the need to do more expensive queries that would join all the tables in the underlying view definition. When the periodic data updates occur, first drop the indexed view, then apply the data updates, then recreate the indexed view.

I have found them to be very beneficial in the appropriate situation. See Books-on-Line for more information.

Dave.
pkrudysz
pkrudysz
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 267
Can you elaborate on:

"The effort required to locate an entry(s) in the index is less."

Thanks.
supergomes
supergomes
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 10
There seems to be a mistake on the table

Listing 5.4: The "Activity totals by date" query

Because option 2 seems more efficient on IO as it sends this results.

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON SalesOrderDetail_index (ProductID, ModifiedDate)

SELECT ModifiedDate ,
ProductID ,
SUM(OrderQty) 'No of Items' ,
AVG(UnitPrice) 'Avg Price' ,
SUM(LineTotal) 'Total Value'
FROM SalesOrderDetail_index
WHERE ModifiedDate = '2003-10-01'
GROUP BY ModifiedDate ,
ProductID ;


Tabla 'SalesOrderDetail_index'. Recuento de exámenes 1, lecturas lógicas 384

Tiempo de CPU = 0 ms, tiempo transcurrido = 7 ms.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search