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

feixianxxx 2010-04-21 09:23:02

加精

详见博客地址：http://blog.csdn.net/feixianxxx/archive/2010/04/21/5513256.aspx

/*----------------------------------------------------------------

-- 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筛选器中出现指定星期几的求解

--环境

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_1

where 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筛选器中指定大小写查找的索引引用问题

--环境

--drop table test_2

create 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' 

go

create  clustered index in_value on test_2(value)

--我先要查找 值为'ABC'的记录 要区分大小写的

select * from test_2

where value COLLATE CHINESE_PRC_CS_AS ='ABC'

按CTRL+L看执行计划 发现时聚集索引扫描 这就说明它不是SARG，不考虑使用索引

解决方法:

select * from test_2

where value COLLATE CHINESE_PRC_CS_AS ='ABC' 

and value='ABC'

go

看执行计划,结果是聚集索引查找;

--3.自动全局临时表

在某些情况下，你可能需要跨会话的维护一些共享值，这里可以通过一些手段自动建立这样一个全局临时表够你使用

具体方法就是在master数据库中建立一个以sp_开头的特殊存储过程，并且使用'startup'标志此存储过程，这样每次重启数据库后都会自动运行此存储过程，

通过在存储过程中建立全局临时表，就达到了共享全局表的目的。

create procedure sp_Create_Global

as

create table ##Global

(

	name varchar(50),

	value sql_variant

)

go

sp_procoption 'sp_Create_Global','startup','true'

go

cmd->net stop mssqlserver 

cmd->net start mssqlserver

insert ##Global values('var_1','987abc')

select * from ##Global

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

动态批处理中 EXEC 不像 sp_executesql 一样提供接口(这里就讲输出参数) 但是也有方法去解决这个问题

--环境：

create table test_3

(

	id int identity(1,1),

	value int

)

insert test_3 

select 1 union 

select 5 union 

select 9

go

1.全部写入动态字符串中

exec (

'declare @n int 

select @N=count(*) from test_3 

select @N '

)



2.INSERT EXEC 形式

create table #cnt(n int)

insert #cnt

exec('select count(*) from test_3 ')

declare @cnt int

set @cnt=(select N from #cnt) 

select @cnt



3.动态批处理直接导入临时表

create table #cnt_2(n int)

exec (

'insert #cnt_2 

select count(*) from test_3'

)

declare @cnt int

set @cnt=(select N from #cnt) 

select @cnt

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

你可能会尝试直接转化

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.索引视图在特殊约束中的应用

--环境：

   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的烦恼

也许在你的业务需求中会碰到当如果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 INPUT

as 

begin 

	return '你输入了'+rtrim(@N)

end 

go

select dbo.f_test(null)--NULL

select dbo.f_test(1)--你输入了1

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

大多数不确定函数(比如rand() getdate())在一次查询中只调用一次，不会每行都调用.除非使用 newid().

--环境：

if object_id('tb') is not null

drop table tb

go

create table tb (s_id int,t_id int, fenshu int)

insert into tb

select 1,1,66 union all

select 1,2,67 union all

select 2,1,65 union all

select 2,2,78 union all

select 3,1,66 union all

select 3,2,55

go

给表记录中的fenshu字段加上随即的1-10分

--这个方法可以给不同数加上不同随机数（newid（））

select fenshu,(fenshu+cast(ceiling(RAND(CHECKSUM(NEWID()))*10)as int))as new_fenshu

from tb

/*

fenshu      new_fenshu

----------- -----------

66          70

67          77

65          72

78          87

66          70

55          64*/

--这个方法只能给不同数随机加上相同数（RAND（））

select fenshu,(fenshu+cast(CEILING(RAND() * 10) AS INT))as new_fenshu

from tb

/*

fenshu      new_fenshu

----------- -----------

66          69

67          70

65          68

78          81

66          69

55          58*/

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

你也许会碰到因为在一些特殊的时间要求在表上的特定操作不触发该操作的触发器,该如何做呢？

--环境

create table test_4

(

	id int,

	value int

)

go

create trigger tr_test_4 on test_4

after insert 

as

print '插入成功！'

go

insert test_4  values(1,2)--插入成功！

再插入下一条的时候,不想触动触发器，解决方法如下：

--方法1：DISABLE trigger

DISABLE TRIGGER tr_test_4 ON TEST_4

insert test_4  values(2,3)

enable TRIGGER tr_test_4 ON TEST_4



--方法2:利用触发器内部的判断

首先修改下触发器

alter trigger tr_test_4 on test_4

after insert 

as

if object_id('tempdb..#k') is null

print '插入成功！'

go

--这个时候不想触发器只需要建立#k的临时表

create table #k(a int)

insert test_4  values(3,4)

drop table #k

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

主要通过函数 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;

GO

DECLARE @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 <= 100

BEGIN

  SET @cmd =

    @cmd + N',col' + RTRIM(@i) +

    N' INT NOT NULL DEFAULT 0';

  SET @i = @i + 1;

END

SET @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 UPDATE

AS

SET 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 <= @numcols

BEGIN

  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;

END

select * from @UpdCols

GO

UPDATE dbo.T1

  SET col4 = 2, col8 = 2, col11 = 2, col6 = 2

WHERE keycol = 1;

GO

/*

ordinal_position

-----------------

col4

col6

col8

col11*/

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

这里介绍个用触发器生成自增列的方法

--环境

create table test_5

(

	id int primary key not null,

	value int

)

--保存最大序列值的表

create table Sequence

(

	rn int

)

insert Sequence select 0

go

create trigger tr_test_5 on test_5

Instead of  insert 

as

begin 

declare @n int

update Sequence

set rn=rn+@@rowcount,@n=rn

insert test_5 

select @n+row_number()over(order by getdate()),value from inserted 

end

go

insert test_5(value) 

select 1 union select 2 union select 3 

select * from test_5

/*

id          value

----------- -----------

1           1

2           2

3           3*/