Advertisements
Home > Indexes, Misconception > Clustered Index will not always guarantee Sorted Rows

Clustered Index will not always guarantee Sorted Rows


In my previous post I discussed about the how Clustered Index’s Data-Pages and Rows are allocated in memory (Disk). I tried to prove that that Clustered Indexes do not guarantee Physical Ordering of Rows. But instead they are Logically Ordered and Sorted.

As they are Logically Sorted and when you query a table without an “ORDER BY” clause you get Sorted Rows, but this is not what will happen always. You can also get rows in Unsorted Order, so to get Sorted rows always apply an “ORDER BY” clause. Here in this post we will see under what circumstances a table will not return Sorted rows:

–> Let’s create a simple table with a Clustered Index on it and add some records:

-- Create  table with 2 columns, first beign a PK and an IDENTITY column:
CREATE TABLE test2
(
	i INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	j INT
)

-- Let's insert some records in random order on second column:
INSERT INTO test2 (j)
SELECT 500 UNION ALL
SELECT 300 UNION ALL
SELECT 900 UNION ALL
SELECT 100 UNION ALL
SELECT 600 UNION ALL
SELECT 200

-- Now we will query the table without using ORDER BY clause:
SELECT * FROM test2

–> Following is the Output of the first SELECT query above:

ClusteredIndex_Sotring01

You get sorted rows, as the Execution plan shows that Clustered Index was used to fetch the records.


–> Now, what if this table also has a Non Clustered Index, let’s see:

-- We will create a Non Clustered Index on the same table on second column:
CREATE INDEX NCI_test ON test2 (j)

-- Again query the table without using ORDER BY clause:
SELECT * FROM test2

–> Following is the Output of the second SELECT query above:

ClusteredIndex_Sotring02

This time the query returned rows in un-ordered fashion. As you can see in the Execution plan Query optimizer preferred to do a Non Clustered Scan. Thus the records were returned in un-ordered fashion.


–> Now, If you add an ORDER BY Clause to your SELECT Query how does it return rows:

-- Added an ORDER BY Clause:
SELECT * FROM test2 ORDER BY i

–> Following is the Output of the third SELECT query above:

ClusteredIndex_Sotring03

After adding an “ORDER BY” clause the query optimizer preferd to use the Clustered Index and returns rows in Ordered fashion again.


-- Final Cleanup
DROP TABLE test2

So, it is necessary to provide an “ORDER BY” clause to your Queries when you expect to get sorted results, even if the table has Clustered Index on it.

Advertisements
  1. December 22, 2013 at 3:16 pm

    Why Query optimizer preferred to do a Non Clustered Scan?

  2. Lalit
    September 11, 2013 at 8:27 pm

    Can you tell why optimizer considered non clustered index in second case

  3. June 10, 2013 at 11:26 pm

    Very handy tips! great ! Nice one dear

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: