Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to SQL Server Indexes: Step 5, Included Columns Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 5, Included Columns
Post #1063691
Posted Monday, March 07, 2011 6:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:47 AM
Points: 69, Visits: 212
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.



Post #1074092
Posted Wednesday, July 13, 2011 8:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:13 AM
Points: 257, Visits: 901
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?
Post #1141017
Posted Wednesday, July 13, 2011 10:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:07 AM
Points: 1,783, Visits: 1,921
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.
Post #1141177
Posted Wednesday, July 13, 2011 12:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:13 AM
Points: 257, Visits: 901
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.

Post #1141259
Posted Thursday, July 14, 2011 10:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, 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.


Post #1141925
Posted Monday, July 18, 2011 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 8:41 AM
Points: 48, Visits: 266
Can you elaborate on:

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

Thanks.
Post #1143558
Posted Sunday, April 07, 2013 3:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 04, 2013 1:47 AM
Points: 1, 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.
Post #1439634
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse