自己总结的MSSQL数据库技巧.doc

一、EXEC和sp_executesql的区别2009-09-17 16:551,EXEC的使用2，sp_executesql的使用 MSSQL为我们提供了两种动态执行SQL语句的命令，分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势，它提供了输入输出接口，而EXEC没有。还有一个最大的好处就是利用sp_executesql，能够重用执行计划，这就大大提供了执行性能，还可以编写更安全的代码。EXEC在某些情况下会更灵活。除非你有令人信服的理由使用EXEC，否则尽量使用sp_executesql。1,EXEC的使用EXEC命令有两种用法，一种是执行一个存储过程，另一种是执行一个动态的批处理。以下所讲的都是第二种用法。下面先使用EXEC演示一个例子,代码1DECLARE TableName VARCHAR(50),Sql NVARCHAR(MAX),OrderID INT;SET TableName = Orders;SET OrderID = 10251;SET sql = SELECT * FROM +QUOTENAME(TableName) +WHERE OrderID = +CAST(OrderID AS VARCHAR(10)+ ORDER BY ORDERID DESCEXEC(sql);注：这里的EXEC括号中只允许包含一个字符串变量，但是可以串联多个变量，如果我们这样写EXEC：EXEC(SELECT TOP(+ CAST(TopCount AS VARCHAR(10) +)* FROM +QUOTENAME(TableName) + ORDER BY ORDERID DESC);SQL编译器就会报错，编译不通过，而如果我们这样写：EXEC(sql+sql2+sql3);编译器就会通过； 所以最佳的做法是把代码构造到一个变量中，然后再把该变量作为EXEC命令的输入参数，这样就不会受限制了； EXEC不提供接口这里的接口是指，它不能执行一个包含一个带变量符的批处理，这里乍一听好像不明白，不要紧，下面有一个实例，你一看就知道什么意思.DECLARE TableName VARCHAR(50),Sql NVARCHAR(MAX),OrderID INT;SET TableName = Orders;SET OrderID = 10251;SET sql = SELECT * FROM +QUOTENAME(TableName) +WHERE OrderID = OrderID ORDER BY ORDERID DESCEXEC(sql);关键就在SET sql这一句话中，如果我们运行这个批处理，编译器就会提示以下错误Msg 137, Level 15, State 2, Line 1 必须声明标量变量 OrderID。使用EXEC时，如果您想访问变量，必须把变量内容串联到动态构建的代码字符串中，如：SET sql = SELECT * FROM +QUOTENAME(TableName) +WHERE OrderID = +CAST(OrderID AS VARCHAR(10)+ ORDER BY ORDERID DESC串联变量的内容也存在性能方面的弊端。SQL Server为每一个的查询字符串创建新的执行计划，即使查询模式相同也是这样。为演示这一点，先清空缓存中的执行计划DBCC FREEPROCCACHE (这个不是本文所涉及的内容，您可以查看MS的MSDN)/zh-cn/library/ms174283.aspx 将代码1运行3次，分别对OrderID 赋予下面3个值，10251，10252，10253。然后使用下面的代码查询SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE %cach% AND sql NOT LIKE %sys.% 点击F5运行，就会出现下面如图所示的查询结果：我们可以看到，每执行一次都要产生一次的编译，执行计划没有得到充分重用。EXEC除了不支持动态批处理中的输入参数外，也不支持输出参数。默认情况下，EXEC把查询的输出返回给调用者。例如下面代码返回Orders表中所有的记录数：DECLARE sql NVARCHAR(MAX)SET sql = SELECT COUNT(ORDERID) FROM Orders;EXEC(sql);然而，如果你要把输出返回给调用批处理中的变量，事情就没有那么简单了。为此，你必须使用INSERT EXEC语法把输出插入到一个目标表中，然后从这表中获取值后赋给该变量，就像这样：DECLARE sql NVARCHAR(MAX),RecordCount INTSET sql = SELECT COUNT(ORDERID) FROM Orders; CREATE TABLE #T(TID INT);INSERT INTO #T EXEC(sql);SET RecordCount = (SELECT TID FROM #T);SELECT RecordCount;DROP TABLE #T 2,sp_executesql的使用sp_executesql命令在SQL Server中引入的比EXEC命令晚一些，它主要为重用执行计划提供更好的支持。为了和EXEC作一个鲜明的对比,我们看看如果用代码1的代码，把EXEC换成sp_executesql,看看是否得到我们所期望的结果DECLARE TableName VARCHAR(50),sql NVARCHAR(MAX),OrderID INT ,sql2 NVARCHAR(MAX);SET TableName = Orders ;SET OrderID = 10251;SET sql = SELECT * FROM +QUOTENAME(TableName) + WHERE OrderID = +CAST(OrderID AS VARCHAR(50) + ORDER BY ORDERID DESCEXEC sp_executesql sql; 注意最后一行；事实证明可以运行；sp_executesql提供接口sp_executesql命令比EXEC命令更灵活，因为它提供一个接口，该接口及支持输入参数也支持输出参数。这功能使你可以创建带参数的查询字符串，这样就可以比EXEC更好的重用执行计划,sp_executesql的构成与存储过程非常相似，不同之处在于你是动态构建代码。它的构成包括：代码块，参数声明部分，参数赋值部分。说了这么多，还是看看它的语法吧EXEC sp_executesqlstmt = ,-类似存储过程主体params = , -类似存储过程参数部分 -类似存储过程调用stmt参数是输入的动态批处理，它可以引入输入参数或输出参数，和存储过程的主体语句一样，只不过它是动态的，而存储过程是静态的，不过你也可以在存储过程中使用sp_executesql;params参数与定义输入/输出参数的存储过程头类似，实际上和存储过程头的语法完全一样； 与调用存储过程的EXEC部分类似。为了说明sp_executesql对执行计划的管理优于EXEC,下面将使用前面讨论EXEC时用到的代码。DECLARE TableName VARCHAR(50),sql NVARCHAR(MAX),OrderID INT; SET TableName = Orders ; SET OrderID = 10251; SET sql = SELECT * FROM +QUOTENAME(TableName) + WHERE OrderID = OID ORDER BY ORDERID DESC EXEC sp_executesql stmt = sql, params = NOID AS INT , OID = OrderID在调用该代码和检查它生成的执行计划前，先清空缓存中的执行计划；DBCC FREEPROCCACHE将上面的动态代码执行3次，每次执行都赋予OrderID 不同的值，然后查询sys.syscacheobjects表，并注意它的输出，优化器只创建了一个备用计划，而且该计划被重用了3次SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE %cache% AND sql NOT LIKE %sys.% AND sql NOT LIKE %sp_executesql%点击F5运行，就会出现如下表所示的结果；sq_executesql的另一个与其接口有关的强大功能是，你可以使用输出参数为调用批处理中的 变量返回值。利用该功能可以避免用临时表返回数据，从而得到更高效的代码和更少的重新编译。定义和使用输出参数的语法与存储过程类似。也就是说，你需要在声明参数时指定OUTPUT子句。例如，下面的静态代码简单的演示了如何从动态批处理中利用输出参数p把值返回到外部批处理中的变量i.DECLARE sql AS NVARCHAR(12),i AS INT;SET sql = N SET p = 10;EXEC sp_executesql stmt = sql, params = Np AS INT OUTPUT, p = i OUTPUTSELECT i该代码返回输出10 create proc pro_text ( dwdm varchar(50) ) as begin -exec(select * from b01 where dept_code in (+dwdm+)select * from b01 where charindex(,+dept_code+,+dwdm+,)0end declare dw varchar(50) -set dw=1+,+104 set dw=1,104print dwexec pro_text dw 1.程序中使用动态参数作为in的条件时，必须使用exec()将in语句包含起来，才能起作用。2.如果SQL语句很长的话可以使用charindex来代替in，charindex的使用方法第一个参数与第二个参数进行比较，看第一个参数是否包含在第二个参数中。3.如果更新语句涉及到单引号可以使用两个单引号将一个单引号包含起来。使用insert into select 语句时如果字段名为数字则使用将字段名括起来，否则会报错二 、关于MSDTC一.A.不用事务,关用SELECT 语句.是否可以分布式查询? B.LINKSERVER 在做分布式更新事务时不能对本机操作.(就是不能环回分布式事务) C.DBCC TRACEON (3604, 7300)-用跟踪看更详细错误信息. D.下载MS提供的DTCPing.exe 分装在两台机上,按README说明来运行它.看出错信息. /download/complus/msdtc/1.7/nt45/en-us/DTCPing.exe 二.两台机的MSDTC是否都打开了. 三.MSDTC设置是否正确. 1.打开命令提示，运行net stop msdtc，然后运行net start msdtc。 2.转至组件服务管理工具。 3.浏览至启动管理工具。 4.选择组件服务。 a.展开组件服务树，然后展开我的电脑。 b.右键单击我的电脑，然后选择属性。 C.在 MSDTC 选项卡中，确保选中了下列选项： 网络 DTC 访问 网络管理 网络事务 XA 事务 e.另外，DTC 登录帐户一定要设置为NT AuthorityNetworkService。 5.单击确定。这样将会提示您MS DTC 将会停止并重新启动。 所有的依赖服务将被停止。请按是继续。单击是继续。 6.单击确定关闭我的电脑属性窗口。 四. MSDTC依赖于RPC,RPC使用的端口是135,测试135端口是否打开.是否有防火墙?如果有先关了防火墙. telnet IP 135 如果是关闭的打开它. 五. 有的机由于各种原因),SQLOLEDB不能使用分布式事务,更改为MSDASQL 的ODBC方式联接. 使用RRAS而不是RAS.(控制面版-管理工具-远程服务管理器) Check whether you are using Remote Access Server (RAS) to access remote servers. If so, make sure that you have implemented Routing RAS (RRAS). Linked server does not work on RAS because RAS allows only one way communication. 七.检查你的两台服务器是否在同一个域中. 如果不在同一个域中,是否建立可信任联接. 八.如果是WIN2000,升级到SP4 九.升级MDAC到2.6以上,最好是2.8. 需要同步的数据服务器说明：1. 双方启动MSDTC服务MSDTC服务提供分布式事务服务，如果要在数据库中使用分布式事务，必须在参与的双方服务器启动MSDTC（Distributed Transaction Coordinator）服务。2. MSDTC设置打开“管理工具组件服务”，以此打开“组件服务计算机”，在“我的电脑”上点击右键。在MSDTC选项卡中，点击“安全配置”按钮。在安全配置窗口中做如下设置：l选中“网络DTC访问”l在客户端管理中选中“允许远程客户端”“允许远程管理”l在事务管理通讯中选“允许入站”“允许出站”“不要求进行验证”l保证DTC登陆账户为：NT AuthorityNetworkService在您启动 SQL Server 之后，SQL Server 内存使用量将会持续稳定上升，即使当服务器上活动很少时也不会下降。另外，任务管理器和性能监视器将显示计算机上可用的物理内存稳定下降，直到可用内存降到 4 至 10 MB 为止。仅仅出现这种状态不表示内存泄漏。此行为是正常的，并且是 SQL Server 缓冲池的预期行为。默认情况下，SQL Server 根据操作系统报告的物理内存加载动态增大和收缩其缓冲池（缓存）的大小。只要有足够的内存可用于防止内存页面交换（在 4 至 10 MB 之间），SQL Server 缓冲池就会继续增大。像在与 SQL Server 分配内存位于相同计算机上的其他进程一样，SQL Server 缓冲区管理器将在需要的时候释放内存。SQL Server 每秒可以释放和获取几兆字节的内存，从而使它可以快速适应内存分配变化。 更多信息您可以通过服务器内存最小值和服务器内存最大值配置选项设置 SQL Server 数据库引擎使用的内存（缓冲池）量的上下限。在设置服务器内存最小值和服务器内存最大值选项之前，请查阅以下 Microsoft 知识库文章中标题为内存一节中的参考信息：319942 HOW TO：Determine Proper SQL Server Configuration Settings（确定正确的 SQL Server 配置设置）请注意，服务器内存最大值选项只限制 SQL Server 缓冲池的大小。服务器内存最大值选项不限制剩余的未保留内存区域，SQL Server 准备将该区域分配给其他组件，例如扩展存储过程、COM 对象、以及非共享 DLL、EXE 和 MAPI 组件。由于前面的分配，SQL Server 专用字节超过服务器内存最大值配置是很正常的。有关此未保留内存区域中分配的其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章： 316749 PRB：在使用大量数据库时可能没有足够的虚拟内存参考SQL Server 联机图书；主题：服务器内存最小值和最大值的影响；内存体系结构；服务器内存选项；SQL Server 内存池 如果要设置某一个字段为NULL可update tablename set tablecolumn=null where .3、 关于CTE一WITH AS的含义 WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。 特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。二使用方法先看下面一个嵌套的查询语句：select * from person.StateProvince where CountryRegionCode in (select CountryRegionCode from person.CountryRegion where Name like C%) 上面的查询语句使用了一个子查询。虽然这条SQL语句并不复杂，但如果嵌套的层次过多，会使SQL语句非常难以阅读和维护。因此，也可以使用表变量的方式来解决这个问题，SQL语句如下：declare t table(CountryRegionCode nvarchar(3)insert into t(CountryRegionCode) (select CountryRegionCode from person.CountryRegion where Name like C%)select * from person.StateProvince where CountryRegionCode in (select * from t) 虽然上面的SQL语句要比第一种方式更复杂，但却将子查询放在了表变量t中，这样做将使SQL语句更容易维护，但又会带来另一个问题，就是性能的损失。由于表变量实际上使用了临时表，从而增加了额外的I/O开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况。为此，在SQL Server 2005中提供了另外一种解决方案，这就是公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。 下面是CTE的语法： WITH ,n := expression_name ( column_name ,n ) AS ( CTE_query_definition ) 现在使用CTE来解决上面的问题，SQL语句如下： withcr as( select CountryRegionCode from person.CountryRegion where Name like C%)select * from person.StateProvince where CountryRegionCode in (select * from cr) 其中cr是一个公用表表达式，该表达式在使用上与表变量类似，只是SQL Server 2005在处理公用表表达式的方式上有所不同。 在使用CTE时应注意如下几点：1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：withcr as( select CountryRegionCode from person.CountryRegion where Name like C%)select * from person.CountryRegion - 应将这条SQL语句去掉- 使用CTE的SQL语句应紧跟在相关的CTE后面 -select * from person.StateProvince where CountryRegionCode in (select * from cr)2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示： withcte1 as( select * from table1 where name like abc%),cte2 as( select * from table2 where id 20),cte3 as( select * from table3 where price 100)select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id3. 如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了，如下面的SQL语句所示：- table1是一个实际存在的表withtable1 as( select * from persons where age 30)select * from table1 - 使用了名为table1的公共表表达式select * from table1 - 使用了名为table1的数据表4. CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。5. 不能在 CTE_query_definition 中使用以下子句：（1）COMPUTE 或 COMPUTE BY（2）ORDER BY（除非指定了 TOP 子句）（3）INTO（4）带有查询提示的 OPTION 子句（5）FOR XML（6）FOR BROWSE6. 如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下面的SQL所示：declare s nvarchar(3)set s = C%; - 必须加分号witht_tree as( select CountryRegionCode from person.CountryRegion where Name like s)select * from person.StateProvince where CountryRegionCode in (select * from t_tree)CTE除了可以简化嵌套SQL语句外，还可以进行递归调用，关于这一部分的内容将在下一篇文章中介绍。四、事务日志SELECT * into A2dic FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=D:A2dic.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).sheet1$ 事务日志文件(Transaction Log File)是用来记录数据库更新情况的文件，扩展名为LDF。 在 SQL Server 2000 中，如果设置了自动增长功能，事务日志文件将会自动扩展。一般情况下，在能够容纳两次事务日志截断之间发生的最大数量的事务时，事务日志的大小是稳定的，事务日志截断由检查点或者事务日志备份触发。然而，在某些情况下，事务日志可能会变得非常大，以致用尽空间或变满。通常，在事务日志文件占尽可用磁盘空间且不能再扩展时，您将收到如下错误消息：引用内容Error:9002, Severity:17, State:2 The log file for database %.*ls is full.除了出现此错误消息之外，SQL Server 还可能因为缺少事务日志扩展空间而将数据库标记为 SUSPECT。有关如何从此情形中恢复的其他信息，请参见 SQL Server 联机帮助中的“磁盘空间不足”主题。另外，事务日志扩展可能导致下列情形：非常大的事务日志文件。 事务可能会失败并可能开始回滚。 事务可能会用很长时间才能完成。 可能发生性能问题。 可能发生阻塞现象。原因事务日志扩展可能由于以下原因或情形而发生：未提交的事务 非常大的事务 操作：DBCC DBREINDEX 和 Create INDEX 在从事务日志备份还原时 客户端应用程序不处理所有结果 查询在事务日志完成扩展之前超时，您收到假的“Log Full”错误消息 未复制的事务解决方法方法一：清空日志1) 打开查询分析器，输入命令 BACKUP LOG 数据库名 WITH NO_LOG(或者DUMP TRANSACTION 数据库名 WITH NO_LOG )2) 再打开企业管理器右键你要压缩的数据库所有任务收缩数据库收缩文件选择日志文件在收缩操作里选择收缩文件至M，这里会给出一个允许收缩到的最小M数，直接输入这个数，确定就可以了。方法二：生成新的日志文件1) 分离数据库 2) 重命名/删除LOG文件3) 附加数据库(重新生成日志文件)此法生成新的LOG，大小只有500多K。这种方法有一定的风险性，因为SQL SERVER的日志文件不是即时写入数据库主文件的，如处理不当，会造成数据的损失，建议使用第一种方法。如果以后不想要它变大，SQL Server 2000下使用：在数据库上点右键属性选项故障恢复简单模型或用SQL语句：alter database 数据库名 set recovery simple错误: SQL Server 阻止了对组件 xp_cmdshell 的 过程sys.xp_cmdshell 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 xp_c EXEC sp_configure show advanced options, 1 -RECONFIGURE WITH OVERRIDE -EXEC sp_configure xp_cmdshell, 1 -RECONFIGURE WITH OVERRIDE - EXEC sp_configure show advanced options, 0 - declare dept varchar(200)set dept=select dept=dept+,+dept_code from b01print stuff(dept,1,1,)动态为dept赋值，并且拼接成1,2,3这种形式其中stuff(dept,1,1,)的使用为去掉表达式中从第一个开始的第一个字符，并用替代（也就是空）-查看被屏蔽掉的触发器select * from sysobjects where xtype = tr and (status & 2048) 0-屏蔽触发器alter table tablename disable trigger triggername-启用触发器alter table tablename enable trigger triggername仅当使用 ALTER DATABASE 启用了 RECURSIVE_TRIGGERS 设置时，才能发生前述行为。执行为特定事件定义的多个触发器时，并没有确定的执行顺序。每个触发器都应是自包含的。 禁用 RECURSIVE_TRIGGERS 的设置只能阻止直接递归。若要同时禁用间接递归，请使用 sp_configure 将 nested triggers 服务器选项设置为 0。如果任一触发器执行了 ROLLBACK TRANSACTION 语句，则无论嵌套级是多少，都不会再执行其他触发器。-得到当月的开始日期以及结束日期rq between convert(varchar(10),DATEADD(DD,-DAY(getdate()+1,getdate(),120) and convert(varchar(10),DATEADD(DD,-DAY(getdate(),dateadd(m,1,getdate(),120) 五、如何将用逗号分开的一行转换成多行。with roy as (select yjjh=cast(left(yjjh,charindex(,yjjh+,)-1) as nvarchar(100),Split=cast(stuff(yjjh+,1,charindex(,YJJH+,),) as nvarchar(100) from tjc13union allselect yjjh=cast(left(Split,charindex(,Split)-1) as nvarchar(100),Split=