Clustered Index on NON-Primary Key Column

Question

Creating Clustered Index on a NON-Primary Key Column, Will sort the values and store ONLY on Disk[ if VARCHAR then A-Z] and Not necessary to show them on query result set ?

Though the record inserted is not in order why query output always sorted based on primary key column [ not clustered]?

CREATE TABLE TEST ( N INTEGER NOT NULL, NAME VARCHAR(10)) CREATE CLUSTERED INDEX IDX_1 ON TEST(NAME) ALTER TABLE TEST ADD PRIMARY KEY (N) INSERT INTO TEST VALUES (2,'D'),(1,'C'),(4,'A'),(3,'B'),(100,'Z') SELECT * FROM TEST output: N NAME 1 C 2 D 3 B 4 A 100 Z

I thought result set would return like this :

N NAME 4 A 3 B 1 C 2 D 100 Z

For an interesting look at unexpected sorting, read Hugo's blog article. — vonPryz
– vonPryz, Commented Jan 29, 2017 at 11:24
Rows in a relational database are not sorted. The only (really: the only) way to get a guaranteed sort order is to use order by. You didn't use an order by so the database is free to return the rows in any order it likes. If you need a specific sort order you have to use order by. There is no alternative — user330315
– user330315, Commented Jan 29, 2017 at 11:38

Pரதீப் · Accepted Answer · 2017-01-29 11:19:50Z

You have two assumptions

Select query will result in the logical order clustered Index in your case it is NAME column

It is wrong. Unless you mention Order By in your select query result order is not guaranteed. Though you are seeing the result sorted in Primary key column(Non clustered Index) but the order is not guaranteed

Records of Clustered Index column is stored in data pages in same order as Clustered Index logical order.

Again it is wrong. The logical order of data is maintained in data pages not the physical order. In same Data page C can be stored first and A can be stored second. Only the logical ordering is maintained in the data pages.

Prdp, you mean values inserted for Clustered Indexed Column's [ C, A ,B] physical row address wont be shifted/changed?
No the physical address will not change. According to your comment example. C will be stored in first physical address of data page next A and next B
In Clustered index , a single page can only have rows related to that specif c table ? Page can not contain other table rows?
@VeeraV - Yes a page will be allocated only to a table. But a single extent can have page belong to more than one table

Rahul · Accepted Answer · 2017-01-29 11:03:58Z

Check your table schema after the last ALTER statement and am pretty sure the clustered index on name is ignored CREATE CLUSTERED INDEX IDX_1 ON TEST(NAME); since you are defining primary key on N column and defining PK on any column default creates a unique clustered index on that column and there can be utmost only one clustered index defined in a table. Thus the sorting would be on that column only. See this fiddle link http://sqlfiddle.com/#!3/0b4c8/1. As you see, commenting the primary key definition ALTER TABLE TEST ADD PRIMARY KEY (N); actually sorts the table based on your clustered index column NAME

This is the index status : IDX_1 clustered located on PRIMARY NAME --------------------------------------------------------------------------- PK__TEST__3BD019B27F0D35A2 nonclustered, unique, primary key located on PRIMARY N

mm759 · Accepted Answer · 2017-01-29 11:07:38Z

The order of rows that result from a SQL query is implementation - specific unless you use ORDER BY. Clustered indexes are about physical data structures. If you want a specific order as result of a query you will be able to use ORDER BY. Indexes help to make it fast.

So in the disk, the rows are stored based on NAME with Order By A-Z ?

Collectives™ on Stack Overflow

Clustered Index on NON-Primary Key Column

3 Answers 3

4 Comments

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Related