SQL SERVER中一些特别地方的特别解法

SQL code

/*------------------------------------------------------------------ Author  :feixianxxx(poofly)-- Date    :2010-04-20 20:10:41-- Version:--      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)         Mar 29 2009 10:27:29         Copyright (c) 1988-2008 Microsoft Corporation        Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )-- CONTENT：SQL SERVER中一些特别地方的特别解法2    ----------------------------------------------------------------*/

--1.关于where筛选器中出现指定星期几的求解

SQL code

--环境create table test_1(    id int,    value varchar(10),    t_time datetime)insert test_1 select 1,'a','2009-04-19' union select 2,'b','2009-04-20' union select 3,'c','2009-04-21' union select 4,'d','2009-04-22' union select 5,'e','2009-04-23' union select 6,'f','2009-04-24' union select 7,'g','2009-04-25' go我们一般通过 datepart(weekday )进行求解，比如求解星期2的记录select * from test_1where DATEPART(WEEKDAY,t_time+@@DATEFIRST-1)=2/*id          value      t_time----------- ---------- -----------------------3           c          2009-04-21 00:00:00.000*/这里涉及到 @@datefirst 这个系统变量，一般我们用来调节不同地方的日期习惯。如果你觉得关于这个变量很难也懒得去依赖它调节，这里还有一种方法你可以使用一个参照日期,通过相同星期数成7的倍数的原理进行查询select * from test_1 where DATEDIFF(DAY,'1900-01-02',t_time)%7=0/*id          value      t_time----------- ---------- -----------------------3           c          2009-04-21 00:00:00.000*/

--2.关于在where筛选器中指定大小写查找的索引引用问题

SQL code

--环境--drop table test_2create table test_2(    id int identity(1,1),    value varchar(10))insert test_2 select 'abc' union all select 'Abc' union all select 'ABC' union all select 'aBc' gocreate  clustered index in_value on test_2(value)--我先要查找 值为'ABC'的记录 要区分大小写的select * from test_2where value COLLATE CHINESE_PRC_CS_AS ='ABC'按CTRL+L看执行计划 发现时聚集索引扫描 这就说明它不是SARG，不考虑使用索引解决方法:select * from test_2where value COLLATE CHINESE_PRC_CS_AS ='ABC' and value='ABC'go看执行计划,结果是聚集索引查找;

--3.自动全局临时表

SQL code

在某些情况下，你可能需要跨会话的维护一些共享值，这里可以通过一些手段自动建立这样一个全局临时表够你使用具体方法就是在master数据库中建立一个以sp_开头的特殊存储过程，并且使用'startup'标志此存储过程，这样每次重启数据库后都会自动运行此存储过程，通过在存储过程中建立全局临时表，就达到了共享全局表的目的。create procedure sp_Create_Globalascreate table ##Global(    name varchar(50),    value sql_variant)gosp_procoption 'sp_Create_Global','startup','true'gocmd->net stop mssqlserver cmd->net start mssqlserverinsert ##Global values('var_1','987abc')select * from ##Global 

--4.关于EXEC不支持动态批处理输出参数的解决方法

SQL code

动态批处理中 EXEC 不像 sp_executesql 一样提供接口(这里就讲输出参数) 但是也有方法去解决这个问题--环境：create table test_3(    id int identity(1,1),    value int)insert test_3 select 1 union select 5 union select 9go1.全部写入动态字符串中exec ('declare @n int select @N=count(*) from test_3 select @N ')2.INSERT EXEC 形式create table #cnt(n int)insert #cntexec('select count(*) from test_3 ')declare @cnt intset @cnt=(select N from #cnt) select @cnt3.动态批处理直接导入临时表create table #cnt_2(n int)exec ('insert #cnt_2 select count(*) from test_3')declare @cnt intset @cnt=(select N from #cnt) select @cnt

--5.以十六进制的格式表示的二进制字符串转成二进制值

SQL code

你可能会尝试直接转化select CAST('Ox0123456abcd' as varbinary(110))/*0x4F783031323334353661626364*/这里因为是字符串 所以值都是ASCII值再转化，所以并不是你想要的,下面是通过动态来解决这个转化Declare @sql nvarchar(4000),@er varbinary(1000),@s varchar(1000)--设置十六进制的数字表示的二进制字符串set @s='0x0123456abcd';set @sql=N'set @n='+@s exec sp_executesql @sql,N'@n varbinary(1000) output',@n=@er output select @er  /*0x00123456ABCD*/--下面尝试用这个方法 将二进制的值转化成字符串Declare @sql2 nvarchar(4000),@er2 varbinary(1000),@s2 varchar(1000)--设置十六进制的数字表示的二进制字符串set @er2=0x0123456abcd;set @sql2=N'set @n='''+@er2+'''' exec sp_executesql @sql2,N'@n varchar(1000) output',@n=@s2 output select @s2  /*数据类型 nvarchar 和 varbinary 在 add 运算符中不兼容。。*/再尝试直接转化。。declare @er3 varbinary(1000),@s3 varchar(2000)set @er3=0x0123456abcd;select convert(varchar(1000),@er3)/* 4V*/--失败SQL SERVER提供了标量用户自定义函数 fn_varbintohexstr实现该转化declare @er4 varbinary(1000)set @er4=0x0123456abcd;select sys.fn_varbintohexstr(@er4)/*0x00123456abcd*/

--6.索引视图在特殊约束中的应用

SQL code

--环境：   IF OBJECT_ID('dbo.V1') IS NOT NULL     DROP VIEW dbo.V1;   GO   IF OBJECT_ID('dbo.T1') IS NOT NULL     DROP TABLE dbo.T1;   GO   CREATE TABLE dbo.T1   (     keycol  INT         NULL,     datacol VARCHAR(10) NOT NULL   );   GO我想在keycol这个字段上建立唯一约束(注意这里的字段是可以为NULL的),这样就意味着可以插入NULL值 问题是想可以插入多列NULL值，但是UNIQUE约束会认为NULL是相等的,当你插入第二个NULL值的时候会出错。那应该如何解决这个问题呢？下面用索引视图来解决这个问题   CREATE VIEW dbo.V1 WITH SCHEMABINDING   AS   SELECT keycol FROM dbo.T1 WHERE keycol IS NOT NULL--注意这里的where 条件   GO   CREATE UNIQUE CLUSTERED INDEX idx_uc_keycol ON dbo.V1(keycol);   GO   -- 插入数据   INSERT INTO dbo.T1(keycol, datacol) VALUES(1,    'a');   INSERT INTO dbo.T1(keycol, datacol) VALUES(1,    'b'); -- 这条失败的   INSERT INTO dbo.T1(keycol, datacol) VALUES(NULL, 'c');   INSERT INTO dbo.T1(keycol, datacol) VALUES(NULL, 'd');   GO   --进行查询   SELECT keycol, datacol FROM dbo.T1;   GO   /*   keycol      datacol   ----------- ----------   1           a   NULL        c   NULL        d   */视图索引保证不准插入重复值,但是因为WHERE keycol IS NOT NULL 所以它没有限定NULL的重复性.

--7.摆脱自定义函数判断输入参数是否为NULL的烦恼

SQL code

也许在你的业务需求中会碰到当如果UDF的输入参数为NULL的时候,函数不执行,返回NULL。你也许会在函数体内用IF 语句进行判断，这里提供一个函数选项：RETURNS NULL ON NULL INPUT / CALLED ON NULL INPUT前者表示如果输入参数为NULL，则不调用函数，直接返回NULL。后者顾名思义，即使输入参数为NULL业调用函数。create function f_test(@n int)returns varchar(100)with RETURNS NULL ON NULL INPUTas begin     return '你输入了'+rtrim(@N)end goselect dbo.f_test(null)--NULLselect dbo.f_test(1)--你输入了1

--8.小心不确定性函数

SQL code

大多数不确定函数(比如rand() getdate())在一次查询中只调用一次，不会每行都调用.除非使用 newid().--环境：if object_id('tb') is not nulldrop table tbgocreate table tb (s_id int,t_id int, fenshu int)insert into tbselect 1,1,66 union allselect 1,2,67 union allselect 2,1,65 union allselect 2,2,78 union allselect 3,1,66 union allselect 3,2,55go给表记录中的fenshu字段加上随即的1-10分--这个方法可以给不同数加上不同随机数（newid（））select fenshu,(fenshu+cast(ceiling(RAND(CHECKSUM(NEWID()))*10)as int))as new_fenshufrom tb/*fenshu      new_fenshu----------- -----------66          7067          7765          7278          8766          7055          64*/--这个方法只能给不同数随机加上相同数（RAND（））select fenshu,(fenshu+cast(CEILING(RAND() * 10) AS INT))as new_fenshufrom tb/*fenshu      new_fenshu----------- -----------66          6967          7065          6878          8166          6955          58*/

--9.“暂停”触发器操作

SQL code

你也许会碰到因为在一些特殊的时间要求在表上的特定操作不触发该操作的触发器,该如何做呢？--环境create table test_4(    id int,    value int)gocreate trigger tr_test_4 on test_4after insert asprint '插入成功！'goinsert test_4  values(1,2)--插入成功！再插入下一条的时候,不想触动触发器，解决方法如下：--方法1：DISABLE triggerDISABLE TRIGGER tr_test_4 ON TEST_4insert test_4  values(2,3)enable TRIGGER tr_test_4 ON TEST_4--方法2:利用触发器内部的判断首先修改下触发器alter trigger tr_test_4 on test_4after insert asif object_id('tempdb..#k') is nullprint '插入成功！'go--这个时候不想触发器只需要建立#k的临时表create table #k(a int)insert test_4  values(3,4)drop table #k

--10.审计表中哪几列进行了更新

SQL code

主要通过函数 COLUMNS_UPDATED() 和公式 IF (SUBSTRING(COLUMNS_UPDATED(),(@i - 1) / 8 + 1, 1)) & POWER(2, (@i - 1) % 8) > 0 则@i列受影响进行判断该函数的解释详见MSDN --环境：--有100个字段的表IF OBJECT_ID('dbo.T1') IS NOT NULL  DROP TABLE dbo.T1;GODECLARE @cmd AS NVARCHAR(4000), @i AS INT;SET @cmd =  N'CREATE TABLE dbo.T1(keycol INT NOT NULL IDENTITY PRIMARY KEY';SET @i = 1;WHILE @i <= 100BEGIN  SET @cmd =    @cmd + N',col' + RTRIM(@i) +    N' INT NOT NULL DEFAULT 0';  SET @i = @i + 1;ENDSET @cmd = @cmd + N');'EXEC sp_executesql @cmd;INSERT INTO dbo.T1 DEFAULT VALUES;--SELECT * FROM T1;GO--建立Update触发器，判断发生改变的行CREATE TRIGGER trg_T1_u_identify_updated_columns ON dbo.T1 FOR UPDATEASSET NOCOUNT ON;DECLARE @i AS INT, @numcols AS INT;DECLARE @UpdCols TABLE(ordinal_position varchar(100))SET @numcols = (SELECT COUNT(*) from sys.columns  where object_id=object_id('tempdb..T1'))SET @i = 1;WHILE @i <= @numcolsBEGIN  IF (SUBSTRING(COLUMNS_UPDATED(),(@i - 1) / 8 + 1, 1))       & POWER(2, (@i - 1) % 8) > 0     begin         INSERT INTO @UpdCols         select name from sys.columns where  column_id =@i  and object_id=object_id('tempdb..T1')     end   SET @i = @i + 1;ENDselect * from @UpdColsGOUPDATE dbo.T1  SET col4 = 2, col8 = 2, col11 = 2, col6 = 2WHERE keycol = 1;GO/*ordinal_position-----------------col4col6col8col11*/

--11.利用触发器生成自增列

SQL code

这里介绍个用触发器生成自增列的方法--环境create table test_5(    id int primary key not null,    value int)--保存最大序列值的表create table Sequence(    rn int)insert Sequence select 0gocreate trigger tr_test_5 on test_5Instead of  insert asbegin declare @n intupdate Sequenceset rn=rn+@@rowcount,@n=rninsert test_5 select @n+row_number()over(order by getdate()),value from inserted endgoinsert test_5(value) select 1 union select 2 union select 3 select * from test_5/*id          value----------- -----------1           12           23           3*/