之前對于“DISABLE索引後插入更新資料再REBUILD索引 和 保留索引直接插入更新資料的性能差異”這兩種方法一直認為其實應該差不多，因為無論如何索引最後都需要被維護，隻不過是個時間順序先後的問題，結果今天做了一個實驗之後大吃一驚，原來這種“DISABLE索引後插入更新資料再REBUILD索引”确實可以在性能上取得不錯的提升。當然了，前提假設是資料插入量達到一個級别，比如我的例子就是用了100萬行的資料插入。感覺對于兩者底層下SQL Server資料庫引擎到底幹了那些活确實還需要找時間找些技術叢書來研究下。
我的實驗環境是：Microsoft SQL Server 2012 Enterprise Edition
下面session 1用了41秒，而session 2用了19秒
USE [Test]
GO
/****** Object: Table [dbo].[Table_1] Script Date: 9/16/2015 8:39:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[col1] [int] NOT NULL,
[col2] [datetime] NOT NULL,
[col3] [nvarchar](100) NULL,
[col4] [float] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [NonClusteredIndex-20150916-152116] Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152116] ON [dbo].[Table_1]
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [NonClusteredIndex-20150916-152132] Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152132] ON [dbo].[Table_1]
(
[col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [NonClusteredIndex-20150916-152140] Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152140] ON [dbo].[Table_1]
(
[col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_col2] DEFAULT (getdate()) FOR [col2]
GO
/****** Object: Table [dbo].[Table_2] Script Date: 9/16/2015 8:39:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[col1] [int] NOT NULL,
[col2] [datetime] NOT NULL CONSTRAINT [DF_Table_2_col2] DEFAULT (getdate()),
[col3] [nvarchar](100) NULL,
[col4] [float] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [NonClusteredIndex-20150916-152202] Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2]
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] DISABLE
GO
/****** Object: Index [NonClusteredIndex-20150916-152210] Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2]
(
[col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] DISABLE
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [NonClusteredIndex-20150916-152218] Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2]
(
[col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] DISABLE
GO
--SESSION 1
insert [dbo].[Table_1](col1, col3, col4)
select [ID], REPLICATE('A',80), 123456.789
from [dbo].[Numbers]
--SESSION 2
ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] DISABLE
ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] DISABLE
ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] DISABLE
insert [dbo].[Table_2](col1, col3, col4)
select [ID], REPLICATE('A',80), 123456.789
from [dbo].[Numbers]
ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] REBUILD
ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] REBUILD
ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] REBUILD