SQL开发中容易忽视的一些小地方 收藏
http://www.cnblogs.com/ASPNET2008/archive/2008/10/12/1308633.html
SQL开发中容易忽视的一些小地方(一)
写此系列文章缘由: 做开发三年来(B/S),发现基于web 架构的项目技术主要分两大方面:
第一:C#,它是程序的基础,也可是其它开发语言,没有开发语言也就不存在应用程序.
第二:数据库,现在是信息化世界,大多数信息都可以通过数据库存储来交换信息.常与应用程序互相交流信息.
但在SQL开发应用时,我们往往只观注些常用的方法(insert delete select update),对些小细节方面(系统存储过程,函数的应用,优化分析)研究的并不多或者是知其一不知其二,所以本人想把在学习工作当中遇到的问题总结些,希望还没有重视这些方面的朋友会有帮助,少走些弯路.
主旨:本文首先根据自己的经验整理了一下SQL中的null的用法及要注意的方面.
名词解释(英文辞典): null:无效的, 无价值的, 等于零的.
(SQL定义):SQL中, NULL 与空格, 零, 都不相同. 是指为未定义或是不可用的.
构成因素:造成某一列成为 NULL 的因素可能是:
(1),值不存在；
(2), 值未知；
(3), 列对表不可用.
它与普通的值最大的异同是:
相同点:
1:统统属于值范畴.数字1是一个值,字符串'aaa'同样是一个值,同理 null也是一个值.
2:都是合法的值,普通的数字,字符可以存在于表中字段,null也可以,而且是有意义的.
不同点:
先创建测试表:
USE [myTestDB]
GO
/****** 对象: Table [dbo].[testNull] 脚本日期: 10/11/2008 13:45:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testNull](
[ID] [int] IDENTITY(1,1) NOT NULL,
[a] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[b] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_testNull] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
插入相关测试值:
insert into testNull
values('1','')
insert into testNull
values('2',null)
1:普通的值一般都可能进行运算符操作,例如:ID列为int,所以可以这样:ID=ID+1等,但如果一列的值为null,null+1=null,就是说null与任何运算符运算后都为null,这就是大家说的黑洞,会吃掉所有的东西.
update testNull
set b=b+1
where b is null
结论:查询后发现b的值没有变化,仍然为null.
2:普通的值可以进行"="操作,例如条件中一般都会这样出现:sUserName='张三',如果sUserName的值为null,要想找出所有名字为null的记录时,不能这样用:sUserName=null,因为null不是一个具体的值,任何值与它比较时都会返回false.此时可借用is null 或者是is not null.
示例查询:
1:select * from testNull where a=null --返回空结果集
2:select * from testNull where b is null --返回结果集 2 2 NULL
结论:说明null是不能用"="来比较,可用is null来替换
3:在用统计函数count时会不同,例如count(ID):统计记录数.当统计的记录中的包含有null值时,它会忽略null值.
示例查询:
1:select count(*),count(b) from testNull 它的返回值为2 1
2: select count(*),count(isnull(b,'')) from testNull 它的返回值为2 2
结论:对于列包含null 时,统计行数是可用count(*),或者是先把null值转换成对应的值再统计,例如count(isnull(b,''));
4:对于in 的影响不同.
示例查询: 查询testNull表中b的值包含在null中的记录.
select * from testNull
where b in(null) --没有任何记录
结论:in在查询时会忽略null的记录,查询的时候可用is not null来查询.
5:排序时顺序有不同:当使用ORDER BY时，首先呈现NULL值。如果你用DESC以降序排序，NULL值最后显示。
1:select * from testNull
1 1 ''
2 2 NULL
2:select * from testNull order by b
2 2 NULL
1 1 ''
3:select * from testNull order by b desc
1 1 ''
2 2 NULL
6:当使用GROUP BY时，所有的NULL值被认为是相等的。这时先多插入几条数据,方便查看结果.
insert into testNull
values('3',null)
values('4','4')
select * from testNull
select count(b) from testNull
group by b
返回结果:
0 1 1
结论:可见在group by 的时候,null视为等同.
7:永远不会有什么数据等于NULL。1不等于NULL，2也一样。但NULL也不等于NULL。所以我们只能比较它“是”或“不是”。
总结:SQL中提供了如此众多的存储过程,函数供我们调用,而我们又真正的理解几个呢?只有真正了解它们,才会对开发中出现的种种问题迅速找出问题所在并解决它.
http://www.cnblogs.com/ASPNET2008/archive/2008/10/13/1309634.html
SQL开发中容易忽视的一些小地方(二)
目的:继上一篇:SQL开发中容易忽视的一些小地方(一) 总结SQL中的null用法后,本文我将说说表联接查询.
为了说明问题,我创建了两个表,分别是学生信息表(student),班级表(classInfo).相关字段说明本人以SQL创建脚本说明:
测试环境:SQL2005
CREATE TABLE [dbo].[student](
[ID] [int] IDENTITY(1,1) NOT NULL,
[sUserName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,--姓名
[sAddress] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,--地址
[classID] [int] NULL,--班级
[create_date] [datetime] NULL CONSTRAINT [DF_student_create_date] DEFAULT (getdate())--入班时间
) ON [PRIMARY]
学生表记录:插入数据999999行.可以说的上是一个不大不小的表.
CREATE TABLE [dbo].[classInfo](
[classID] [int] IDENTITY(1,1) NOT NULL,--所属班级ID
[sClassName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,--班级名称
[sInformation] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,--班级相关信息
[sDescription] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,--班级描述
[iSchooling] [int] NULL,--学费
CONSTRAINT [PK_classInfo] PRIMARY KEY CLUSTERED
(
[classID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
班级表:共插入100行,实际可能不存在这么多的班级.
示例需求:查询学生的基本信息以及所属班级名称,我们都会第一时间想到用表关联,这里我列出相关实现方法.
第一:将数据量较大的学生表放在前面.
--大表在前
select top 1000 a.sUserName,b.sClassName from student a
inner join classInfo b on
a.classID=b.classID
第二:将数据量较小的班级表放在前面.
--小表在前
select top 1000 a.sUserName,b.sClassName from classInfo b
inner join student a on
a.classID=b.classID
第三:用where 实现.
--join与where
select top 1000 a.sUserName,b.sClassName from classInfo b, student a
where a.classID=b.classID
归纳:以上三种方式查询的结果都完全相同,但它们在实现效率上会有不同吗?这里首先提出两个网络上的观点:
网络观点一:一般要使得数据库查询语句性能好点遵循一下原则:在做表与表的连接查询时，大表在前，小表在后.
执行计划效果如图一:
网络观点一结论:从图上可以非常清楚的看出,三者在执行计划上完成一样.为此本人并不同意网络观点一.表在前与后并不影响最终的执行效率.大家有什么不同的意见望指教.
说明:
1:WHERE子句中使用的连接语句，在数据库语言中，被称为隐性连接。INNER JOIN称为显性连接。WHERE 和INNER JOIN产生的连接关系，没有本质区别，结果也一样。但是！隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。
2:join的分类:
1> inner join：理解为“有效连接”，
2>left join：理解为“有左显示”，
3> right join：理解为“有右显示”
4> full join：理解为“全连接”
3 .join可以分主次表 左联是以左边的表为主，右边的为辅，右联则相反
网络观点二:inner join 与 where 在效率上是否一样?原文地址: http://topic.csdn.net/t/20050520/13/4022440.html 原文中有下面一段话:
---------------------------引用----------------------------------------------
4 指明多表关系会大大提高速度 ，如
SELECT A.X,B.Y FROM A B WHERE A.X=B.X
SELECT A.X,B.Y FROM A INNER JOIN B ON A.X=B.X
2句结果一样，但是速度相差很多，时间复杂度分别是 O(2n)和O(n*n)
------------------------------------------------------------------------------
我的观点:联接查询的时间复杂度并不是固定的,更不能说是由两种表现方式不同而决定的.join在查询的算法根据联接表的不同分三种情况:
第一种算法:NESTED LOOP:
定义: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表。
示例:上面有了一个班级表,下面我再创建一个班级课程表,
CREATE TABLE [dbo].[course](
[ID] [int] IDENTITY(1,1) NOT NULL,
[sCourseName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,--课程名称
[classID] [int] NULL,--所属班级ID
CONSTRAINT [PK_CKH] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
业务需求:查询所有班级对应的课程情况.
select sCourseName,sClassName from classInfo a
inner join course b
on a.classID=b.classID
执行计划效果图:
结论:通过查询执行计划可以非常清楚的看出,采用了嵌套查询,因为两表的数据量都不大,而且数据大小相当.此时的查询开销为n*n
第二种算法:HASH JOIN :
定义: 散列连接是做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。
结论:从图一中可能看出SQL在联接班级表和学生表是采用了hash join方式,因为班级表数据量大,班级表数据量小.这种方式的查询时间复杂度为2n.
注意点:hash join可能非常容易的变成nested loop,下面的查询为hash join
select top 10000 a.ID,a.sUserName,b.sClassName from classInfo b
inner join student a on
a.classID=b.classID
转换(hash join变nesteed loop):如果在后面组加上排序呢?此是会变成嵌套查询
select top 10000 a.ID,a.sUserName,b.sClassName from classInfo b
inner join student a on
a.classID=b.classID
order by a.ID
转换(nesteed loop变hash join):上面的嵌套查询又可以改选成hash join
select * from(
select top 10000 a.ID,a.sUserName,b.sClassName from classInfo b
inner join student a on
a.classID=b.classID) as tbl
order by ID
第三种算法:排序合并连接
定义:通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。
网络观点二结论:inner join和where 在查询效率上没有区别,只是体现形式不同而已.
总结:我们可以通过查看SQL的执行计划来分析SQL的性能,一句话正确与否不在于说话的人,而在于实践验证结果.本人就表联接谈了自己的理解,如果有不对的地方还望各们指教.
http://www.cnblogs.com/ASPNET2008/archive/2008/10/15/1311962.html
SQL开发中容易忽视的一些小地方( 三)
目的：这篇文章我想说说我在工作中关于in和union all 的用法.
索引定义 ： 微软的SQL SERVER提供了两种索引：聚集索引(clustered index，也称聚类索引、簇集索引)和非聚集索引(nonclustered index，也称非聚类索引、非簇集索引)。
SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下： 列名 操作符 <常数 或 变量>或<常数 或 变量> 操作符列名列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。
SARG的意义：如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。
讨论问题：现在有些观点直接说in不符合SARG标准,故在查询中全产生全表扫描.
我的观点:这个观点在早期的数据库中可能是这样,起码SQL2005足以证明上面的说法是错误的.
案例:有一会员表(member),里面包含代理信息,其中代理号proxyID上创建有索引.数量量在百万以上。
需求:查询指定代理的代理信息.
查询SQL:
方法1: select 相关字段 from member where proxyID IN('ID1','ID2',.....)
方法2: select 相关字段 from member where proxyID='ID1'
union all
select 相关字段 from member where proxyID='ID1'
union all
...
如何比较:
第一:proxyID的数量比较多,我测试时输入了30个proxyID
下面是两种方法的执行计划图:
1：union all的执行计划图：由于图比较长，所有分成两部分显示。
2：in的执行计划图：
结论:
1:无论哪种方法,都会用上索引.
2:两都的执行计划不同:当proxyID的数量比较多时,用in会直接查找索引,并有过滤的操作.union all则是连接了 n 个嵌套查询.
3:代理号比较多时,union all的效率明显高于in
第二:proxyID的数量比较小,现在分别输入两个,6个,15个,执行计划图可以看出,当proxyID的数量为15时,直接查找索引,而2个和6个时都选择嵌套查询来完成.因为union all的执行计划图总是一样的，所有贴于不同proxyID下，用in查询的执行计划图：
1:两个代理的执行计划图：
2:六个代理的执行计划图：
3:十五个代理的执行计划图：
结论:
1:无论哪种方法,都会用上索引.
2:proxyID的数据量比较小的时候在执行时间上和union all差距不大.
3:in里面的数据个数不同时，执行计划也会相应的同，数据量小时会采用嵌套查询，反之则直接查询索引以及其它相关辅助操作。
结论:现在的数据库引擎一般都会通过查询成本分析来选择最优的查询算法来执行,不能把以前的观点拿到现在说.in与union all的差别并不是永远不变的,看什么情况而定.类似in的还有or,对于or,有观点也说不能应用索引，其实和in一样，高版本中的都会用上索引。
http://www.cnblogs.com/ASPNET2008/archive/2008/10/16/1312593.html
SQL开发中容易忽视的一些小地方(四)
本篇我想针对网上一些对于非聚集索引使用场合的某些说法进行一些更正. 下面引用下MSDN对于非聚集索引结构的描述.
非聚集索引结构:
1:非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：
* 基础表的数据行不按非聚集键的顺序排序和存储。
* 非聚集索引的叶层是由索引页而不是由数据页组成。
2:非聚集索引行中的行定位器或是指向行的指针，或是行的聚集索引键，如下所述：
* 如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。
* 如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。
网络观点:order by 子句中使用了的列,可以在此列上建非聚集索引以提高查询速度.
原文地址:http://gocom.primeton.com/blog10697_1221.htm
本人观点:总之一句话,环境不同,表结构不同,数据分布不同,最终结果也不一定相同.
案例:本人最近做一个项目时有两个大表关联,都接近千万.一个表是订单表order,另一个是会员表member,订单中有一字段create_date:类型为datatime,其中的值都是不相同且唯一,而且并不连续,下面是一些值:
create_date
-----------------------
2008-10-05 04:00:56.000
2008-10-05 03:55:55.000
2008-10-05 03:55:42.000
2008-10-05 03:54:40.000
2008-10-05 03:54:32.000
2008-10-05 03:54:23.000
2008-10-05 03:47:16.000
2008-10-05 03:46:08.000
2008-10-05 03:42:28.000
2008-10-05 03:42:09.000
订单表和会员表有一个关联字段为proxyID,各自均建有索引.查询语句如下:
select * from order inner join member on order.proxyID=member.proxyID
where leaveDate between '开始时间' and '结束时间' order by create_date desc
测试:
情况一:在create_date上创建非聚集索引.执行的IO和所用时间消耗如下图:可以看出这种情况对memer表进行了大量的表扫描. 83588次.
情况二:删除create_date上的索引,按理来说应该会比有索引会慢些,下面是执行的IO和时间消耗图:
对此我有以下发现:
1:order by 字段没有创建索引的情况下,对member表只扫描了9次.远少于创建索引时的83588次.
2:还有一个现象就是如果按在查询分析器中全部显示出数据来看,没有创建索引最终所用时更少.
3:创建索引的查询会比没有创建索引的查询早一步显示数据,不过最终完成的时间要长.
测试未知难题:
1:就查询速度来说,是早一步在查询分析器中显示数据的查询快还是说要看最终完全的时间来判断.(create_date创建索引的情况会更早显示数据,不过总共用时会比不创建索引的慢)园友zping曾告诉我不要看时间要看IO数量.不知道大家是怎么分析的.
2:在一个字段上创建索引为什么会引发member表的多次表扫描.
测试说明:由于SQL2005有缓存功能,所有两次查询的时间段并不相同,但数据量都差不多.
根据园友 perfectdesign的观点,order by 时,如果字段是聚集索引将会是最优的,这点我个人以及MSDN都同意,奇怪的是,上面的语句中,leave_date上即聚集索引,然后order by leave_date desc,然而也会产生5万多次的member表扫描,好像是order by 索引字段,无论是聚集还是非聚集都会大量增加对member表的扫描.真是百思不得其解.下面是详细的ID情况:
(2000 row(s) affected)
Table 'member'. Scan count 52796, logical reads 234885, physical reads 0, read-ahead reads 3687, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'v_hotel'. Scan count 1, logical reads 3121, physical reads 0, read-ahead reads 28, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
测试结论:这种情况足以说明对于order by 字段创建索引并不一定能发挥非聚集索引的优势,至于其中原因本人不才,目前并无答案,如大家有答案还望指教一二.数据库的调优虽然有一定的原则及准则,但这些所谓准则并一定全对.本人觉的都仅供参考,还是要按实际情况来分析.
下面贴下MSDN对于非聚集索引应用场合的说明,我觉的还是可能参考的:
在创建非聚集索引之前，应先了解访问数据的方式。考虑对具有以下属性的查询使用非聚集索引：
使用 JOIN 或 GROUP BY 子句。
应为联接和分组操作中所涉及的列创建多个非聚集索引，为任何外键列创建一个聚集索引。
不返回大型结果集的查询。
创建筛选索引以覆盖从大型表中返回定义完善的行子集的查询。
包含经常包含在查询的搜索条件（例如返回完全匹配的 WHERE 子句）中的列。
http://www.cnblogs.com/ASPNET2008/archive/2008/10/21/1316330.html
SQL开发中容易忽视的一些小地方(五)
背景:
索引分类：众所周知,索引分为聚集索引和非聚集索引。
索引优点：加速数据查询。
问题：然而我们真的清楚索引的应用吗?你写的查询语句是否能充分应用上索引,或者说你如何设计你的索引让它更高效？
经历:以前本人只知道索引的好处,但是是否能够真正让它发挥作用,并无太多理论,为些本人做了些DEMO,来简单说明下什么情况下才能充分利用索引。
案例:
这里建立一个学生表:有如下字段,此时表中没有建立任何索引。
CREATE TABLE [dbo].[student](
[ID] [int] IDENTITY(1,1) NOT NULL,--学生ID
[sUserName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,--学生姓名
[sAddress] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,--学生地址
[classID] [int] NULL,--学生所属班级ID
[create_date] [datetime] NULL CONSTRAINT [DF_student_create_date] DEFAULT (getdate()) --入校时间
) ON [PRIMARY]
业务需求:
查询班级ID为9的所有学生的姓名和地址。
情况一:
--字段没有建立任何索引
select sUserName,sAddress from student
where classID=9
执行计划如下图：
结论:在没有任何索引的情况下,查询会选择全表扫描.
情况二:
给ID自增列创建一个聚集索引,我们很多情况下都是这样默认的,主键上就是聚集索引。同样的查询,不同的查询计划,发现此时虽然在输出列和条件中没有ID,但是查询选择了聚集查询.
执行计划图同图一。
情况三:在classID上创建非聚集索引。
结论:虽然条件列中出现了classID索引列,但是输出列中并没有创建任何索引,依然选用聚集扫描方式查询.
情况四:在sUserName上创建非聚集索引
结论:同上
情况五:继续在sAddress上创建非聚集索引
结论:同上
情况六:建立sUserName与sAddress的联合非聚集索引
结论:同上
情况七:在classID与sUserName上创建索引
结论:同上
情况八:在classID,sUserName,sAddress上创建联合非聚集索引
执行计划图如下：
结论:当条件中出现的列加上输出列和联合索引列完全匹配时全用上索引扫描.
情况九：删除所有索引，保留ID的聚集索引。以聚集索引列做为条件之一来查询.
select sUserName,sAddress from student
where ID=10021002
或者：select sUserName,sAddress from student
where ID=10021002 and classID=9
执行计划图：
所有情况总结:
1:当使用非聚集索引扫描时的IO情况:表 'student'。扫描计数 1，逻辑读取 70 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
2:当使用聚集索引扫描时的IO情况(条件中未出现聚集索引列):表 'student'。扫描计数 3，逻辑读取 8835 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
3:当使用聚集索引扫描时的IO情况（条件中出现聚集索引列） :表 'student'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
结论:
1:在没有正确的索引情况下,会增加表的扫描次数.
2:数据在查询时会先找匹配的索引.
1):如果在条件列中出现聚集索引列,则无论输出列是否建立索引都会按聚集索引查找(有聚集索引 ).
2):如果在条件列中没有出现聚集索引列,则查找匹配的非聚集索引,如果有匹配的索引则按相应索引查询,否则再扫描聚集索引(有聚集索引 ).
3):查找匹配的非聚集索引(没有聚集索引 ).
本文总结：
我只是简单的写了些关于索引使用的DEMO，在实际开发中要按实际情况来分析，有时并不能完全使用上索引，但是可以让查询产生最少的IO读取以及表扫描次数。
http://www.cnblogs.com/ASPNET2008/archive/2009/03/09/1407315.html
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
执行计划图如下：我们可以看到在执行删除时，数据库分为三部分：
1：查找到要删除的数据行；
2：包含一个top操作。
3：执行聚集索引删除。
区别一：由于course表的classID上创建了索引，所以查找时按PK_classID来查找，course2表的classID由于没有任何的索引，为了查找到要删除的数据行，就只能按聚集索引查找，此时实际上是全表扫描。
区别二：系统开销不同，让人意外的是，结果表明好像白天的观点是正确的，创建了索引的coure表在开销上比没有创建索引的course2还大一点。
分析区别二的原因：我们来看下聚集索引删除的具体内容，下面是在条件列classID上创建了非聚集索引的表course表在发生删除时的执行计划图,它在删除后需要维护索引PK_classID,占用部分的系统开销。而没有创建索引的表course2由于没有索引维护的额外开销，所以反而占优势。
第二步：分别给两个表插入相当的数据10000行，然后删除第5000条记录。
区别同第一步。难道我的观点真的正确？
第三步：分别给两个表插入相当的数据100000行，然后删除第50000条记录。执行计划图如下：
区别一：同前两步的区别一。
区别二：系统开销不同，此时会发现创建了索引的course表在开销上占5%，而没有创建索引的course2表占了95%，这可是10倍的区别啊。
第四步：分别给两个表插入相当的数据1000000行，然后删除第500000条记录。
区别同第三步。
总结：当删除语句的条件列没有创建索引时分两种情况：
第一：数据量较小，我测试时在10000以下，此时两者的差别不大，反而会因为创建了索引而引起磁盘开销。开销差距不大是因为数据量小时，即使全表扫描速度也不慢，此时索引的优势并不明显。
第二：数据量较大，我测试时在100000以上，此时两者的差别较大。条件列创建了索引的表明显效率高。
第三：归根结底，系统的主要开销还是在删除的第一步，查找数据行上。能更快查找到删除行的方案效率最高。
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/king_idea8848/archive/2009/10/30/4747857.aspx
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。