SQL开发中容易忽视的一些小地方(六)

本文主旨：条件列上的索引对数据库delete操作的影响。

事由：今天在博客园北京俱乐部MSN群中和网友讨论了关于索引对delete的影响问题,事后感觉非常汗颜,因为我的随口导致错误连篇。大致话题是这样的，并非原话：

[讨论：] delete course where classID=500001 classID上没有创建任何索引，为了提高删除效率，如果在classID上创建一个非聚集索引会不会提高删除的效率呢？　　

我当时的观点：不能。

我当时的理由：数据库在执行删除时，如果在classID上创建了非聚集索引，首先按这个非聚集索引查找数据，找到索引行后，根据索引行后面带的聚集索引地址最后找到真正的物理数据行，并且执行删除，这个过程看起来没有作用，只能创建聚集索引来提高删除效率，因为如果classID是聚集索引，那么直接聚集索引删除，此时的效率最高。

下班后对这个话题再次想了下，觉的自己的观点都自相矛盾，既然知道删除时，会在条件列上试图应用已经存在的索引，那么为什么创建非聚集索引会无效呢？如果表的数据相当大，classID上如果没有任何索引，查找数据时就要执行表扫描，而表扫描的速度是相当慢的，为此为了证明下这个问题，我特意做了一个示意性的实验。

创建两个表course 和course2,创建语句如下，它们唯一的区别就在于索引，course表中classID上创建了非聚集索引，而course2上没有创建任何索引。

CREATE　TABLE　[dbo].[course](
　[ID]　[int]　IDENTITY(1,1)　NOT　NULL,
　[sCourseName]　[nchar](10)　COLLATE　Chinese_PRC_CI_AS　NULL,
　[classID]　[int]　NULL,
　CONSTRAINT　[PK_CKH]　PRIMARY　KEY　CLUSTERED　
(
　[ID]　ASC
)WITH　(IGNORE_DUP_KEY　=　OFF)　ON　[PRIMARY]
)　ON　[PRIMARY]
--创建索引
create　index　IX_classID
on　course(classID)

CREATE　TABLE　[dbo].[course2](
　[ID]　[int]　IDENTITY(1,1)　NOT　NULL,
　[sCourseName]　[nchar](10)　COLLATE　Chinese_PRC_CI_AS　NULL,
　[classID]　[int]　NULL,
　CONSTRAINT　[PK_CKH2]　PRIMARY　KEY　CLUSTERED　
(
　[ID]　ASC
)WITH　(IGNORE_DUP_KEY　=　OFF)　ON　[PRIMARY]
)　ON　[PRIMARY]

实验过程：

第一步：分别给两个表插入相当的数据1000行，然后删除第500条记录。

delete　course
where　classID=500
delete　course2
where　classID=500