Online Indexing in Sql Server 2005

In SQL Server 2005, the index can be created while Insert/update/delete happening on a table. Without affecting performance degradance to table the index will be created. This concept is called Online indexing in Sql server.

I have taken following sample queries to test online indexing :

Step 1: Create sampe tables to hold the datacreate table Index_Check (id int identity(1,1), name varchar(20),join_date datetime, Amount float)
Create table names (name varchar(50))
insert into names values ('Software company,Bangalore,India')


Step 2 : Executing cursor to insert the data continuously
declare venkat cursor local
for
Select NAME FROM NAMES
open venkat
Declare @aaa VARCHAR(20)
fetch next from venkat into @aaa
while @@fetch_status =0
exec ('insert into Index_Check (name,join_date) select '''+@aaa+''', GETDATE()')
fetch next from venkat into @aaa
deallocate venkat
close venkat


Step 3 : Connect the same server and same table from other connection, try to create cluster / nonclustered online index

Create clustered index IDX_Check on Index_Check(id) with (online=on)
Create nonclustered index IDX_Check1 on Index_Check(id) with (online=on)


Successfully index creates without disturbing the table process.


You can schedule rebuilding index depends on the data usage in the database. While performing rebuild it can be configured to perform online/offline. Online means it doens't impact anything to the table operation and does. But it has some restrictions also like or text data type columns we can't perform online reindexing and etc.