Please note: I'm not talking about a clustered index versus a non-clustered index, I mean a clustered index that is not comparable to the index at all for performance insertion.
I saw many links as shown below: a clustered index has better performance without an index even for insert operation due to IAM / PFS / bitmap / ..., but with my testing, it seems the index is not faster than the cluster index, which is the problem ?
Removing the primary key (cluster index) to increase insert performance http://support.microsoft.com/kb/297861
my test scripts:
------------------------------------------- --prepare table with clustered index CREATE TABLE [dbo].[BigTable_CI]( [id] [int] IDENTITY(1,1) NOT NULL, [BigChar] [char](4100) NOT NULL ) GO CREATE CLUSTERED INDEX CIX_BigTable_CI ON BigTable_CI(id) GO ALTER TABLE [dbo].[BigTable_CI] ADD CONSTRAINT [DF_BigTable_BigChar_CI] DEFAULT ('a') FOR [BigChar] GO CREATE PROCEDURE [dbo].[AddDataToBigTable_CI] (@NumberOfRows bigint) AS SET NOCOUNT ON; DECLARE @Counter int = 0; DECLARE @Start datetime = GETDATE(); DECLARE @End datetime; DECLARE @ElapsedTime int = 0; DECLARE @RowsPerSecond int = 0; WHILE (@Counter < @NumberOfRows) BEGIN INSERT INTO dbo.BigTable_CI DEFAULT VALUES; SELECT @Counter += 1; END; -- Calculate elapsed time and rows/second SET @End = GETDATE(); SET @ElapsedTime = CONVERT(INTEGER, DATEDIFF (second, @Start, @End)); SET @RowsPerSecond = @NumberOfRows/@ElapsedTime; -- Record results in local table INSERT INTO dbo.Results (StartTime, EndTime, ElapsedTime, NumberOfRows, RowsPerSecond) VALUES (@Start, @End, @ElapsedTime, @NumberOfRows, @RowsPerSecond); RETURN; ------------------------------------------- --prepare table without any index at all. CREATE TABLE [dbo].[BigTable_NI]( [id] [int] IDENTITY(1,1) NOT NULL, [BigChar] [char](4100) NOT NULL ) GO ALTER TABLE [dbo].[BigTable_NI] ADD CONSTRAINT [DF_BigTable_BigChar_NI] DEFAULT ('a') FOR [BigChar] GO CREATE PROCEDURE [dbo].[AddDataToBigTable_NI] (@NumberOfRows bigint) AS SET NOCOUNT ON; DECLARE @Counter int = 0; DECLARE @Start datetime = GETDATE(); DECLARE @End datetime; DECLARE @ElapsedTime int = 0; DECLARE @RowsPerSecond int = 0; WHILE (@Counter < @NumberOfRows) BEGIN INSERT INTO dbo.BigTable_NI DEFAULT VALUES; SELECT @Counter += 1; END; -- Calculate elapsed time and rows/second SET @End = GETDATE(); SET @ElapsedTime = CONVERT(INTEGER, DATEDIFF (second, @Start, @End)); SET @RowsPerSecond = @NumberOfRows/@ElapsedTime; -- Record results in local table INSERT INTO dbo.Results (StartTime, EndTime, ElapsedTime, NumberOfRows, RowsPerSecond) VALUES (@Start, @End, @ElapsedTime, @NumberOfRows, @RowsPerSecond); RETURN; ------------------------------------------- --prepare the results table create table dbo.Results ( StartTime datetime, EndTime datetime, ElapsedTime int, NumberOfRows int, RowsPerSecond int ) ------------------------------------------- --run scripts: exec [dbo].[AddDataToBigTable_NI] 1000000 exec [dbo].[AddDataToBigTable_CI] 1000000