Table default data display order

Table default data display order

Today one of my friend asking me about default sort order of table. Hope most of them answer is, It was sorted according to clustered index column and with given “Order By condition” it can be “ascending or descending” my answer was same and again he ask me,

Then what happened if we haven’t any clustered index? What do you think? 
It’s good question any way I have two answers for that,

• If you create table without any clustered index and then insert some records to that table. Then default display order is whatever the data inserted order. Example is if you insert “2” first and “1” second then your first row will be “2” and second row will be “1”. 

• If you insert some records to table and then create “clustered index” and later you will be deleted clustered index. Then your data sort order is old clustered index column sort order.If you insert records after drop “clustered index” then, those records inserted after that. 

Seems to be he was very happy with my answers. But after one min he was again asking me,

What happened if we have “clustered index” with duplicate records?  
      I was bit smile and tell him then it will display “clustered index” column order and duplicate records will display when data inserted order. 
After that I was show him following small example,

use tempdb
go
drop table tblOrder;
go
create table tblOrder(col1 int, col2 int, col3 int);
go
insert into tblOrder values(1,2,3);
insert into tblOrder values(3,1,2);
insert into tblOrder values(2,3,1);
insert into tblOrder values(5,6,7);
insert into tblOrder values(5,8,13);
insert into tblOrder values(5,4,14);
insert into tblOrder values(5,5,15);
insert into tblOrder values(4,9,11);
insert into tblOrder values(20,19,311);
go
select * from tblOrder;
go
create clustered index [cix_tblorder_col1] on [dbo].[tblOrder]([col1] asc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col1] on [dbo].[tblOrder] with ( online = off );
go
select * from tblOrder;
go
create clustered index [cix_tblorder_col2] on [dbo].[tblOrder]([col2] asc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col2] on [dbo].[tblOrder] with ( online = off );
go
select * from tblOrder;
go
create clustered index [cix_tblorder_col3] on [dbo].[tblOrder]([col3] asc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col3] on [dbo].[tblOrder] with ( online = off );
go
--with descending
select * from tblOrder;
go
create clustered index [cix_tblorder_col1] on [dbo].[tblOrder]([col1] desc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col1] on [dbo].[tblOrder] with ( online = off );
go
--default ascending
select * from tblOrder;
go
create clustered index [cix_tblorder_col1] on [dbo].[tblOrder]([col1]);
go
select * from tblOrder;
go
drop index [cix_tblorder_col1] on [dbo].[tblOrder] with ( online = off );
go
--nonclustered index
select * from tblOrder;
go
create nonclustered index [cix_tblorder_col1] on [dbo].[tblOrder]([col1] asc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col1] on [dbo].[tblOrder] with ( online = off );
go

I was really happy about his third Question that's why i have decided to publish this post.

hope following tips will help who is reading this post,

• You need to select best column for "clustered index" whenever you are designing table.

• It should be much as small (smallint,int or less than 20 digits) then performance will be good.

• if your system is 99.9% of insert(log table) then very rarely using "SELECT" statements then better to create table without any indexes.(Ex: if you have very high insert rate table then reduce indexes much as possible)

• Try to create small indexes(index contain bytes) much as possible.

• According to index fragmentation try to Rebuild or reorganize indexes.     

Cheers...
and Most welcome your comments and ideas...