SQl.docx
- 文档编号:10506302
- 上传时间:2023-02-17
- 格式:DOCX
- 页数:80
- 大小:183.02KB
SQl.docx
《SQl.docx》由会员分享,可在线阅读,更多相关《SQl.docx(80页珍藏版)》请在冰豆网上搜索。
SQl
【1】SQLServer约束和触发器的区别
SQLServer约束相信大家都比较了解了,那么,SQLServer约束和触发器的区别在哪里呢?
在SQLServer数据库中提供了两种主要机制来强制使用业务规则和数据完整性,他们是SQLServer约束和触发器。
触发器其实就是一个特殊类型的存储过程,可在在执行某个操作时自动触发。
触发器与约束都可以实现数据的一致性。
那么他们在使用的过程中,有哪些差异呢?
简单的来说,触发器可以实现约束的一切功能。
但是在考虑数据一致性问题的时候,首先要考虑通过约束来实现。
如果约束无法完成的功能,则再通过触发器来解决。
两者从功能上来说,他们的关系如下图所示:
触发器可以包含使用SQL代码的复杂处理逻辑。
如果单从功能上来说,触发器可以实现约束的所有功能。
但是由于其自身的种种缺陷,其往往不是实现数据一致性等特定功能的首选解决方案。
总的来说,只有在约束无法实现特定功能的情况下,才考虑通过触发器来完成。
这只是在处理约束与触发器操作过程中的一个基本原则。
对于他们两个具体的差异笔者在下面也进行了比较详细的阐述。
欢迎大家进行补充。
差异一:
错误信息的管理上。
当违反系统的SQLServer约束规则时,需要向用户返回一定的错误信息,方便用户进行排错。
约束与触发器在遇到问题时都可以返回给用户一定的错误信息。
但是,约束只能够通过标准化的系统错误信息来传递错误消息。
如果应用程序需要使用自定义消息和较为复杂的错误处理机制,则必须要使用触发器才能够完成。
如现在数据库中有一张产品信息表。
为了保证产品的唯一性,要求产品的编号必须唯一。
如果用户输入的产品编号跟企业现有的产品编号有重复的话,这条产品信息就不能够被保存。
从技术上来说,约束与触发器都可以实现这个需求。
但是,当违反这个唯一性规则时,他们提供的错误信息是不同的。
如利用约束来实现这个唯一性控制,那么当用户输入重复的编号时,则系统会提示违反了唯一性规则,不允许保存。
但是光凭这条消息的话,可能用户还不能够马上了解是怎么回事情。
有时候程序员希望能够返回更加具体的信息。
如在产品基本信息表中可能有多个字段具有唯一性约束,那么用户希望知道到底是哪个字段违反了唯一性约束。
如用户现在输入产品编号为DT001的产品编号时出现了这个错误。
那么用户可能希望系统能够显示出系统中原来存在的DT001这个产品的具体信息,如他的规格描述、产品名称等等。
这可以帮助员工来判断自己要建立的产品信息是否真的跟原来存在的产品信息重复。
还是只是产品编号的重复而已。
如果要在错误信息中带出更加详细的信息,则数据库管理员不得不采用触发器来对这个唯一性进行控制。
因为只有触发器可以返回数据库管理员自定义的错误信息;而且还可以实现比较复杂的逻辑控制。
而约束只能够范围系统定义的标准错误信息。
另外如果违反一些主键、外键约束的话,系统也只会提示标准的错误信息。
而不会提示到底是哪一张表中存在子记录等等详细的错误信息。
这就给用户排错的时候带来不必要的麻烦。
因为它需要先去查找这个约束或者主键的名字,然后再去看具体的约束定义才能够确定到底是哪里出了问题。
但是普通用户往往是不能够看到约束的具体定义的。
故在遇到这种情况时,最好也能够通过触发器来提供比较详细的错误信息,以提高应用程序的友好性。
差异二:
性能上的差异分析。
如现在有两张表,分别为销售订单头与销售订单行。
在销售订单中有一个订单ID,它是这张表的主键,也是销售订单行表的外键。
现在如果更改了销售订单头表的主键的值,那么必须要保证销售订单行表中订单ID的值也随之更改。
否则的话,销售订单头表与销售订单行表就无法对应起来。
此时触发器与约束都可以实现类似的功能。
触发器可以将销售订单头ID的更改通过级联更新的功能传播给数据库中其他相关的表,实现级联更新。
约束也可以实现类似的功能。
而且通常情况下,通过级联引用完整性约束可以更有效的执行这个级联更新。
如当上面这个更改发生后,触发器可以禁止或回滚违反引用完整性的更改,从而取消所尝试的数据修改。
当更改外键且新值与其主键不匹配时,这个的触发器将生效。
但是,数据库中有一个现成的解决方案,即FOREIGNKEY约束通常用于此目的。
如果触发器表上存在约束,则在INSTEADOF触发器执行后但在AFTER触发器执行前检查这些约束。
如果违反了约束,则回滚INSTEADOF触发器操作并且不执行AFTER触发器。
遇到这种情况后,往往就是两种处理方式。
一是如果要更改的主键在其他表中已经存在的话,那么就不允许其进行更改,系统会拒绝保存或者回滚用户的更改操作。
二是如果要更改的主键信息在其他表中已经存在相关的记录,而数据库管理员又允许其更改的话,为了保证数据的一致性,就要出发级联更新的功能。
让数据库系统在更改主键的同时自动更新其他表中的相关信息。
无论采取哪种方式,从性能上来说,约束的执行性能都要高一点。
而且系统本身就提供了一些约束规则,如级联引用完整性约束的等等。
故也省去了管理员写触发器代码的工作量。
不过有一点值得说明的是,虽然约束的执行性能比较高,但是其向用户提供的错误信息确实非常有限的。
如上面第一点所说,系统只提供了一些标准的错误信息。
如果管理员需要向用户提供比较详细的错误信息,则需要通过触发器的自定义错误信息来实现。
故在用户的友好性与数据库的执行性能之间,数据库管理员需要做出一个均衡。
差异三:
管理维护的工作量。
由于约束基本上都是数据库现成的解决方案。
无论是索引约束还是外键约束,又或者是check约束。
往往在数据库系统中已经有了现成的解决方案。
数据库管理员通过直接引用这些解决方案即可以实现特定的功能,而不用再费力的编写触发器来实现。
如要实现表中某个字段的唯一性约束,则只需要直接在这个字段上启用unique约束即可。
从而省去了编写触发器代码的时间。
所以通常来说,触发器的维护工作量要比约束来的多。
因为触发器中系统没有现成的可以引用,而都需要数据库管理员通过实际清理来进行编写。
如果不熟悉编制的话,还很容易引起不必要的错误。
为此,如果单从这个工作量来考虑的话,那么数据库管理员肯定喜欢采用这个约束,而不喜欢采用触发器。
最终的建议:
如果约束能够实现特定的功能,则数据库最好能够采用约束而不是触发器。
因为约束能够提供比较高的执行性能,而且数据库管理员维护的工作量也会小得多。
如实体完整性应在最低级别上通过索引进行强制,这些索引可以是PRIMARYKEY约束和UNIQUE约束的一部分,或者是独立于约束而创建的。
域完整性可以通过CHECK约束进行强制,而引用完整性则可以通过FOREIGNKEY约束进行强制。
当然使用约束的前提是假设这些约束的功能能够满足应用程序的功能需求。
如果系统中现成的约束无法满足企业用户的需求,如功能无法满足或者提供的错误信息不够等情况,此时数据库管理员就需要通过触发器来完成。
不过数据库管理员在编写触发器的时候,仍然可以借鉴相关约束的实现方式。
而不用从零开始,来重新设计触发器。
另外触发器可以防止一些恶意或错误的记录插入、删除以及更新操作,并强制执行比CHECK约束定义的限制更为复杂的其他限制。
其还可以提供比CHECK约束更复杂一点的功能。
如触发器可以引用其他表中的列。
可见触发器与约束各有各的特点。
数据库管理员要从执行性能、维护工作量、实现的功能、用户友好性等多个方面出发,选择合适的处理方式。
【2】SQLServer执行动态SQL两种正确方式
SQLServer执行动态SQL的话,应该如何实现呢?
下面就为您介绍SQLServer执行动态SQL两种正确方式,希望可以让您对SQLServer执行动态SQL有更深的了解。
动态SQL:
codethatisexecuteddynamically。
它一般是根据用户输入或外部条件动态组合的SQL语句块。
动态SQL能灵活的发挥SQL强大的功能、方便的解决一些其它方法难以解决的问题。
相信使用过动态SQL的人都能体会到它带来的便利,然而动态SQL有时候在执行性能(效率)上面不如静态SQL,而且使用不恰当,往往会在安全方面存在隐患(SQL注入式攻击)。
动态SQL可以通过EXECUTE或SP_EXECUTESQL这两种方式来执行。
EXECUTE
执行Transact-SQL批中的命令字符串、字符串或执行下列模块之一:
系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。
SQLServer2005扩展了EXECUTE语句,以使其可用于向链接服务器发送传递命令。
此外,还可以显式设置执行字符串或命令的上下文
SP_EXECUTESQL
执行可以多次重复使用或动态生成的Transact-SQL语句或批处理。
Transact-SQL语句或批处理可以包含嵌入参数。
在批处理、名称作用域和数据库上下文方面,SP_EXECUTESQL与EXECUTE的行为相同。
SP_EXECUTESQLstmt参数中的Transact-SQL语句或批处理在执行SP_EXECUTESQL语句时才编译。
随后,将编译stmt中的内容,并将其作为执行计划运行。
该执行计划独立于名为SP_EXECUTESQL的批处理的执行计划。
SP_EXECUTESQL批处理不能引用调用SP_EXECUTESQL的批处理中声明的变量。
SP_EXECUTESQL批处理中的本地游标或变量对调用SP_EXECUTESQL的批处理是不可见的。
对数据库上下文所作的更改只在SP_EXECUTESQL语句结束前有效。
如果只更改了语句中的参数值,则sp_executesql可用来代替存储过程多次执行Transact-SQL语句。
因为Transact-SQL语句本身保持不变,仅参数值发生变化,所以SQLServer查询优化器可能重复使用首次执行时所生成的执行计划。
一般来说,我们推荐、优先使用SP_EXECUTESQL来执行动态SQL,一方面它更加灵活、可以有输入输出参数、另外一方面,查询优化器更有可能重复使用执行计划,提高执行效率。
还有就是使用SP_EXECUTESQL能提高安全性;当然也不是说要完全摈弃EXECUTE,在特定场合下,EXECUTE比SP_EXECUTESQL更方便些,比如动态SQL字符串是VARCHAR类型、不是NVARCHAR类型。
SP_EXECUTESQL只能执行是Unicode的字符串或是可以隐式转换为ntext的常量或变量、而EXECUTE则两种类型的字符串都能执行。
下面我们来对比看看EXECUTE和SP_EXECUTESQL的一些细节地方。
1.EXECUTE(N'SELECT * FROM Groups') --执行成功
2.
3.EXECUTE('SELECT * FROM Groups') --执行成功
4.
5.SP_EXECUTESQL N'SELECT * FROM Groups'; --执行成功
6.
7.SP_EXECUTESQL 'SELECT * FROM Groups' --执行出错
8.
Summary:
EXECUTE可以执行非Unicode或Unicode类型的字符串常量、变量。
而SP_EXECUTESQL只能执行Unicode或可以隐式转换为ntext的字符串常量、变量。
1.DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
2.
3.EXECUTE('SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''); --'SUBSTRING' 附近有语法错误。
4.
5.DECLARE @Sql VARCHAR(200);
6.
7.DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
8.
9.SET@Sql ='SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''
10.
11.--PRINT @Sql;EXECUTE(@Sql);
12.
Summary:
EXECUTE括号里面只能是字符串变量、字符串常量、或它们的连接组合,不能调用其它一些函数、存储过程等。
如果要使用,则使用变量组合,如上所示。
1.DECLARE @Sql VARCHAR(200);
2.
3.DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
4.
5.SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'
6.
7.--PRINT @Sql;EXECUTE(@Sql); --出错:
必须声明标量变量 "@GroupName"。
SET@Sql ='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')
8.
9.EXECUTE(@Sql); --正确:
10.
11.DECLARE @Sql NVARCHAR(200);
12.
13.DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';
14.
15.SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'
16.
17.PRINT @Sql;
18.
19.EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR',@GroupName
查询出来没有结果,没有声明参数长度。
1.DECLARE @Sql NVARCHAR(200);
2.
3.DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';
4.
5.SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'
6.
7.PRINT @Sql;
8.
9.EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)',@GroupName
Summary:
动态批处理不能访问定义在批处理里的局部变量。
SP_EXECUTESQL可以有输入输出参数,比EXECUTE灵活。
下面我们来看看EXECUTE,SP_EXECUTESQL的执行效率,首先把缓存清除执行计划,然后改变用@GroupName值SuperAdmin、CommonUser、CommonAdmin分别执行三次。
然后看看其使用缓存的信息
1.DBCC FREEPROCCACHE;
2.
3.DECLARE @Sql VARCHAR(200);
4.
5.DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin'; --'CommonUser', 'CommonAdmin'
6.
7.SET@Sql ='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')
8.
9.EXECUTE(@Sql); SELECTcacheobjtype, objtype, usecounts, sql
10.
11.FROM sys.syscacheobjects
12.
13.WHERE sql NOTLIKE '%cache%'
14.
15.ANDsql NOTLIKE '%sys.%';
如下图所示:
依葫芦画瓢,接着我们看看SP_EXECUTESQL的执行效率.
1.DBCC FREEPROCCACHE;
2.
3.DECLARE @Sql NVARCHAR(200);
4.
5.DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin'; --'CommonUser', 'CommonAdmin'
6.
7.SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'
8.
9.EXECUTESP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;
10.
11.SELECTcacheobjtype, objtype, usecounts, sql
12.
13.FROM sys.syscacheobjects
14.
15.WHERE sql NOTLIKE '%cache%'
16.
17.ANDsql NOTLIKE '%sys.%';
执行结果如下图所示:
Summary:
EXEC生成了三个独立的adhoc执行计划,而用SP_EXECUTESQL只生成了一次执行计划,重复使用了三次,试想如果一个库里面,有许多这样类似的动态SQL,而且频繁执行,如果采用SP_EXECUTESQL就能提高性能。
【3】SQLServer删除视图的两种方法
SQLServer删除视图的方法经常会用的到,下面就为您介绍SQLServer删除视图的两种方法,如果您感兴趣的话,不妨一看。
当一个视图不再需要使用时,也可以将其删除。
SQLServer删除视图方法1在ManagementStudio中删除视图
下面以删除“view_例七”为例介绍如何在SQLServerManagementStudio中删除视图:
(1)启动【SQLServerManagementStudio】,连接到本地数据库默认实例。
(2)在【对象资源管理器】窗口里,展开树形目录,定位到【view_例七】。
右击【view_例七】,在弹出的快捷菜单里选择【删除】。
(3)在弹出的【删除对象】对话框里可以看到要删除的视图名称。
单击【确定】按钮完成操作。
SQLServer删除视图方法2用Dropview语句删除视图
在T-SQL语言里,用dropview语句可以删除视图,其语法代码为:
DROPVIEW[schema_name.]view_name[...,n][;]
例如删除“view_例一”视图的语名:
DROPVIEWview_例一
也可以一次删除多个视图,例如:
DROPVIEWview_例二,view_例三
【4】SQLServer视图的使用
SQLServer视图可是实现很多功能,下面就为您详细介绍SQLServer视图的使用,如果您对SQLServer视图方面感兴趣的话,不妨一看。
SQLServer视图可以被看成是虚拟表或存储查询。
可通过SQLServer视图访问的数据不作为独特的对象存储在数据库内。
数据库内存储的是SELECT语句。
SELECT语句的结果集构成视图所返回的虚拟表。
用户可以用引用表时所使用的方法,在Transact-SQL语句中通过引用视图名称来使用虚拟表。
使用视图可以实现下列任一或所有功能:
将用户限定在表中的特定行上。
例如,只允许雇员看见工作跟踪表内记录其工作的行。
将用户限定在特定列上。
例如,对于那些不负责处理工资单的雇员,只允许他们看见雇员表中的姓名列、办公室列、工作电话列和部门列,而不能看见任何包含工资信息或个人信息的列。
将多个表中的列联接起来,使它们看起来象一个表。
聚合信息而非提供详细信息。
例如,显示一个列的和,或列的最大值和最小值。
通过定义SELECT语句以检索将在SQLServer视图中显示的数据来创建视图。
SELECT语句引用的数据表称为视图的基表。
在下例中,pubs数据库中的titleview是一个视图,该视图选择三个基表中的数据来显示包含常用数据的虚拟表:
1.CREATE VIEW titleview
2.AS
3.SELECT title, au_ord, au_lname, price, ytd_sales, pub_id
4.FROM authors AS a
5.JOIN titleauthor AS ta ON (a.au_id = ta.au_id)
6.JOIN titles AS t ON (t.title_id = ta.title_id)
之后,可以用引用表时所使用的方法在语句中引用titleview。
1.SELECT * FROM titleview
一个视图可以引用另一个视图。
例如,titleview显示的信息对管理人员很有用,但公司通常只在季度或年度财务报表中才公布本年度截止到现在的财政数字。
可以建立一个视图,在其中包含除au_ord和ytd_sales外的所有titleview列。
使用这个新视图,客户可以获得已上市的书籍列表而不会看到财务信息:
1.CREATE VIEW Cust_titleview
2.AS
3.SELECT title, au_lname, price, pub_id
4.FROM titleview
SQLServer视图可用于在多个数据库或Microsoft?
SQLServer?
2000实例间对数据进行分区。
分区视图可用于在整个服务器组内分布数据库处理。
服务器组具有与服务器聚集相同的性能优点,并可用于支持最大的Web站点或公司数据中心的处理需求。
原始表被细分为多个成员表,每个成员表包含原始表的行子集。
每个成员表可放置在不同服务器的数据库中。
每个服务器也可得到分区视图。
分区视图使用Transact-SQLUNION运算符,将在所有成员表上选择的结果合并为单个结果集,该结果集的行为与整个原始表的复本完全一样。
例如在三个服务器间进行表分区。
在第一个服务器上定义如下的分区视图:
1.CREATE VIEW PartitionedView AS
2.SELECT *
3.FROM MyDatabase.dbo.PartitionTable1
4.UNION ALL
5.SELECT *
6.FROM Server2.MyDatabase.dbo.PartitionTable2
7.UNION ALL
8.SELECT *
9.FROM Server3.MyDatabase.dbo.PartitionTable3
在其它两个服务器上定义类似的分区视图。
利用这三个视图,三个服务器上任何引用PartitionedView的Transact-SQL语句都将看到与原始表中相同的行为。
似乎每个服务器上都存在原始表的复本一样,而实际上每个表只有一个成员表和分区视图。
有关更多信息,请参见视图使用方案。
只要所做的修改只影响视图所引用的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQl