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.