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... 

Change SQL Server HEAP table file group

Change SQL Server HEAP table file group

                         I have a small worker-round for sql server database, HEAP tables move to different file group. Of-cause this is bit critical work before starts I have a two solutions,

• Create CLUSTERED index of all given HEAP tables.
• Move all HEAP table to new file group.   

And every one is know, if you recreate or newly create CLUSTERED index on SQL server table, then index will be created given file group.which means simply move data to newly given file group.

But what will happens if we drop again,

• Will HEAP table again move to old file group?
• Or HEAP table remain as same file group?

 what Microsoft Saying?

Managing Heaps

• To create a heap, create a table without a clustered index. If a table already has a clustered index, drop the clustered index to return the table to a heap.

• To remove a heap, create a clustered index on the heap.

• To rebuild a heap to reclaim wasted space, create a clustered index on the heap, and then drop that clustered index. 

Will Have test this,

USE [MSDN]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[DatabaseUserGroup];
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[DatabaseUserGroup](
[colid] [int] IDENTITY(1,1) NOT NULL,
[colname] [varchar](100) NULL
) ON [PRIMARY];
END
GO
INSERT INTO [dbo].[DatabaseUserGroup] ([colname]) SELECT name FROM master.sys.objects;
GO 10
SELECT count(*) cnt FROM [dbo].[DatabaseUserGroup];
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND i.type_desc='HEAP'
AND o.[name]='DatabaseUserGroup';
GO
CREATE UNIQUE CLUSTERED INDEX PK_CIX_DatabaseUserGroup_colid ON [dbo].[DatabaseUserGroup](colid) WITH (ONLINE=ON) ON [MAIN];
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND o.[name]='DatabaseUserGroup';
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND name = N'PK_CIX_DatabaseUserGroup_colid') BEGIN
DROP INDEX [PK_CIX_DatabaseUserGroup_colid] ON [dbo].[DatabaseUserGroup] WITH ( ONLINE = ON );
END
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND o.[name]='DatabaseUserGroup';

Well.. Well... table remain as newly given file group.When a clustered index is created, SQL Server will temporarily duplicate the data from the heap into the clustered index key order and remove the original pages associated with the heap.But remember this is bit costly operation(depend on how large your table) you need to make sure use this very less server busy time.According to Microsoft,

Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.    

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