Stairway to SQL Server Indexes: Step 5, Included Columns

Question

Stairway to SQL Server Indexes: Step 5, Included Columns

David Durant

Default port

Points: 1422
More actions
February 14, 2011 at 10:37 am

#245471

Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 5, Included Columns

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

russella0-96423 SSC-Addicted Points: 448 More actions · Answer 1

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 SSCrazy Points: 2794 More actions · Answer 2

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 SSC-Forever Points: 42192 More actions · Answer 3

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 SSCrazy Points: 2794 More actions · Answer 4

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 Default port Points: 1422 More actions · Answer 5

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 Right there with Babe Points: 772 More actions · Answer 6

Can you elaborate on:

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

Thanks.

supergomes SSC Rookie Points: 37 More actions · Answer 7

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.

sizal0234 SSCrazy Points: 2272 More actions · Answer 8

Hi There,

Need little help. What version of AdventureWorks sample DB is being used in "Stairway to SQL Server Indexes"

I see some difference while running sample queries in my Adeventureworks2017 DB.

Thanks!!!

Tom Wickerath Valued Member Points: 53 More actions · Answer 9

@sizal0234 (SSCrazy):

Need little help. What version of AdventureWorks sample DB is being used in "Stairway to SQL Server Indexes"
I see some difference while running sample queries in my Adeventureworks2017 DB.

The author uses the 2005 version of Adventure Works. This was mentioned earlier, about midway in Level 1

https://www.sqlservercentral.com/steps/an-introduction-to-sql-server-indexes-stairway-to-sql-server-indexes-level-1

Creating and benefiting from a Nonclustered Index
We end this level by twice querying our sample database. Make sure you are using the version of AdventureWorks intended for SQL Server 2005, which can be used by SQL Server 2008. The AdventureWorks2008 database has a different table structure and the queries below will fail.