Stairway to SQL Server Indexes: Step 5, Included Columns

  • David Durant

    Ten Centuries

    Points: 1370

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

  • russella0-96423

    SSC-Addicted

    Points: 448

    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: 2764

    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: 42157

    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: 2764

    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

    Ten Centuries

    Points: 1370

    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: 742

    Can you elaborate on:

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

    Thanks.

  • supergomes

    SSC Rookie

    Points: 37

    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: 2210

    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: 51

    @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.

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

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