3

I have created a table with primary key on name column, the ordering of the table is based on name column

create table TestPrimaryKey (Name varchar(20) primary key,id int identity)

insert into TestPrimaryKey (SomeText) values ('john')
insert into TestPrimaryKey (SomeText) values ('ryan')
insert into TestPrimaryKey (SomeText) values ('jennifer')
insert into TestPrimaryKey (SomeText) values ('martha')
insert into TestPrimaryKey (SomeText) values ('luke')
insert into TestPrimaryKey (SomeText) values ('alex')
insert into TestPrimaryKey (SomeText) values ('wayne')

Now I'm changing the clustered index on primary key to nonclustered index, still the ordering is based on the name column

    begin tran
    alter table dbo.TestPrimaryKey drop constraint PK__TestPrim__BC2905030401D947
    alter table dbo.TestPrimaryKey add constraint PK__TestPrim__BC2905030401D947 primary key nonclustered (SomeText)
    commit tran

Now I'm creating a unique clustered index on the other (id) column, still the ordering is the same

    create unique clustered index CI__TestPrim__BC29050354394FAA on TestPrimaryKey(id)

Now I'm dropping the clustered index from the table

    drop index CI__TestPrim__BC29050354394FAA on TestPrimaryKey

But now the ordering changes from the "name" column to the "id" column. I need to know why that happens and also if the table has a non clustered primary key and a clustered index column will the ordering be based on primary key column, clustered index column or both.

Improve this question
15

Unless you explicitly state a desired order using an ORDER BY clause you can not guarantee the order that data will be presented in response to a query. Without an ORDER BY clause the engine is free to present data to you in any order it finds most convenient at the time, which can mean a different order for the same query you ran earlier.

If there is a clustered index then the data pages are stored with the rows in this order, but that does not guarantee that they will be processed in exactly this order (it makes it likely if there are no joins or ordering clauses, but with no explicit ordering request the order rows will be output is officially "undefined").

Without a clustered index the rows are stored in data pages in an unordered manner (this is referred to as a "heap table"). This may initially be the same order that the data was inserted, but as soon as deletes and updates start to happen this becomes definitely not true.

If you want to read around a little theory lookup "set theory" upon which relational databases are based. Mathematical sets are by their nature unordered, which means anything in a relational database should be considered unordered by default.

Regarding non-clustered primary keys: these do not affect the ordering of stored row data at all. They have extra meaning to your data model but the don't physically do anything that a non-clustered index with the "unique" property set would do. Similarly a clustered primary key is just a clustered unique index internally with extra meaning for your data model.

tl;dr: if the data needs to be ordered a particular way, you must directly specify that order in an ORDER BY clause.

Improve this answer
3
  • I get it, But still i cannot get the reason why the rows were ordered based on the id column after dropping the index. And I don't want to use order by I want to know the how the indexes are used when the table has a non clustered primary key and a clustered index for a simple "select * from [TableName]" – mehmood shah Jul 5 '16 at 9:28
  • As mentioned in the answer, you should not assume any order unless you specify one. You may get the rows in the order you expect 999 out of 1000 times, but that may suddenly change. – Lennart Jul 5 '16 at 9:42
  • 4
    There is no reason for them not to be ordered by the ID after the index is dropped. With no clustering key the order of the data rows is undefined so the engine is free to order them as it see fit. If it does not need to rearrange the data pages when dropping the clustered index (which it probably won't need to, the pages just become leaf nodes in the heap instead of lowest-branch nodes in the indexes b+tree, with row IDs updated as needed) then it won't do extra work that isn't needed. The ordering won't be guaranteed going forward. In fact as already stated it isn't guaranteed at all. – David Spillett Jul 5 '16 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.