How a clustered index sorts and stores data columns of a table?

  • michael.leach2015

    Default port

    Points: 1484

    Here is a test question I saw on a website.

    "A clustered index sorts and stores the data columns of a table or view in order, based on the clustered index key."

    The website says this is false.

    I thought this would have been true.  The values within the clustered index key would be sorted right?

  • Jonathan AC Roberts

    SSCoach

    Points: 16879

    From the Microsoft BOL:

    Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.

    The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

    https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017

    Looking at the wording you've provided, it looks like a trick question. The statement provided says:

    "A clustered index sorts and stores the data columns of a table or view in order, based on the clustered index key."

    It's the rows that are sorted not the columns. But the rows are sorted by the column values, so it's easy to miss that in the question.

     

  • John Mitchell-245523

    SSC Guru

    Points: 148306

    It's a poorly worded question.  I can think of two reasons why the statement could be construed as being wrong:

    (1) It's the rows that are stored in sorted order, not the columns.

    (2) The rows are stored in a logical sorted order, but not necessarily a physical one.   Fragmentation of the disk or of the index itself would destroy the physical order.

    John

  • michael.leach2015

    Default port

    Points: 1484

    This makes sense.  Thank you.

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

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