Sql Server的一些特别解法

renyanping

　　
　　
　　
　　摘自：http://topic.csdn.net/u/20100421/21/0D5D791B-AAE0-4944-9410-BAFA449D4522.html
　　
　　详见博客地址：http://blog.csdn.net/feixianxxx/archive/2010/04/21/5513256.aspx
　　

SQL code

<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->/*----------------------------------------------------------------
-- 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

<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->--环境
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筛选器中指定大小写查找的索引引用问题

SQL code

<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->--环境
--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.自动全局临时表

SQL code

<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->在某些情况下，你可能需要跨会话的维护一些共享值，这里可以通过一些手段自动建立这样一个全局临时表够你使用
具体方法就是在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不支持动态批处理输出参数的解决方法

SQL code

<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->动态批处理中 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.以十六进制的格式表示的二进制字符串转成二进制值

SQL code

<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->你可能会尝试直接转化
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)
/*