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.

RAID : Understanding of RAID and its functionality

Understanding of RAID......Redundant Array of Indepenent Disks (RAID)
RAID is a combination of multilple disks to improve the performance and safety of the data. This can be configured using SCSI contollers. SCSI is nothing but a card which can have facility of connect multiple disks. Once configured the RAID, the data is available on ARRAY comprising the available disks. There are mainly 3 terms need to remember while working with RAID levels. Mirroring, Stripped disks and fault tolerance.

There are different RAID levels for various requirements. We can implement the actual RAID level to achieve 100% success.

RAID 0 : It distributes the data across multiple disks. The data retrieving can be faster as the data is dividing into multilpe disks. But this levels doesn't have any data safety, means if one disk fails you will loose the data automatically.

RAID 1 : Mirror the data from one disk to another. Here we have more safety, if one disk fails automatically another disks works which already has mirrored data.

RAID 3 (OR) 4 : This level requires minimum 3 disks including 2 for data distribution and 1 for fault tolerance. Here we have data safety mechanism with full performance including redundant data identification. For this level 1 dedicated disk is required to store redundant data.

RAID 5 : This level also requires 3 or more disks to protect the data in case any one disk fails. Here we dont required dedicated disk for storing redundant data, this will be stored in the combination of all disks. This level has higher data safety allows only one disks to fail. If more than one disks fails then the data will not be available on array.

RAID 6 : Similar to RAID 5, but it requires minimum 4 disks and allows to fail 2 disks. Means 2 disks can fail in this raid level, if more than 2 disks fails than the data wouldn't be available.


There are 2 more new levels introduced i.e....,

RAID 1+0 : Combination of RAID 1 + RAID 0, whatever the features having both levels can incorporate here. But in the sequence like first it mirror the data (safety) then distribute the data across drives (performance).

RAID 0+1 : Combination of RAID 0 + RAID 1, whatever the features having both levels can incorporate here. But in the sequence like first it distribute the data across drives (performance) then mirror the data (safety).


When consider to SQL Server installation mostly we can use RAID 5 for backups of the databases where it requires only 3 disks (less hardware) and high safety with high performance. If the data is too much important and your company can able to spend money like data worth's than any thing, you can suggest RAID 6 level where it comprises 4 disks with dual disk safety.

RAID 1+0 is good for installing Tempdb database where TEMPDB database requires safety of data with good performance.