Introduction to Indexes: Part 2 – The clustered index

  • Dev (7/9/2012)


    ScottPletcher (7/9/2012)


    Dev (7/9/2012)‘select only a very small % of the total rows’ is applicable to Clustered Index as well for seek operation else it will scan the cluster.

    False, of course. If, for example, you have a table with two years' worth of data, 2010 and 2011, clustered on date, and you specify >= '20110101', SQL will read only the 2011 data.

    That is another main reason you should leverage the clus index as much as possible.

    It’s just an optimization added to SCAN operation but it won’t be good anyways if the data is skewed and ‘>= 20110101’ returns 90% of rows.

    Nope, sorry. You need to review the difference between "scan" and "seek".

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Sure. We will do it together…

    Scans vs. Seeks

    http://blogs.msdn.com/b/craigfr/archive/2006/06/26/647852.aspx

  • Sorry for dragging the discussion. I setup a small test below.

    CREATE TABLE DUMMY_TABLE

    (

    ID_COL INT,

    DESC_COL CHAR(8000),

    )

    GO

    INSERT INTO DUMMY_TABLE

    VALUES

    (1, REPLICATE('X',8000)),

    (2, REPLICATE('X',8000)),

    (3, REPLICATE('X',8000)),

    (4, REPLICATE('X',8000)),

    (5, REPLICATE('X',8000)),

    (6, REPLICATE('X',8000)),

    (7, REPLICATE('X',8000)),

    (8, REPLICATE('X',8000)),

    (9, REPLICATE('X',8000))

    GO

    CREATE CLUSTERED INDEX IX_DUMMY_TABLE ON DBO.DUMMY_TABLE

    (

    ID_COL

    )

    GO

    SET STATISTICS IO ON

    -- Query 1

    SELECT * FROM DUMMY_TABLE WHERE ID_COL > 1 --AND ID_COL < 9

    ------------------------

    -- Query 2

    SELECT * FROM DUMMY_TABLE

    ------------------------

    -- Query 3

    SELECT * FROM DUMMY_TABLE WHERE ID_COL = 1 --AND ID_COL < 9

    GO

    DROP TABLE DUMMY_TABLE

    SET STATISTICS IO OFF

    Scott, you were right. Execution plans (actual/estimate) show SEEK operation for 1st & 3rd queries. It’s quite surprising to me but I have to accept the fact, it’s SEEK operation.

    BUT when I see the IO statistics, I see no difference between first two queries. So the SEEK predicate on in first query is fake IMO.

    (9 row(s) affected)

    (8 row(s) affected)

    Table 'DUMMY_TABLE'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (9 row(s) affected)

    Table 'DUMMY_TABLE'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    Table 'DUMMY_TABLE'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Viewing 3 posts - 121 through 123 (of 123 total)

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