Ordering of table is based on Clustered index or non clustered primary key?

Question

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.

Answer 1

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.