[推荐] 【SQL SERVER中一些特别地方的特别解法2】 100feixianxxx]

发布时间:2014-1-1 0:09:48编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"[推荐] 【SQL SERVER中一些特别地方的特别解法2】 100feixianxxx]",主要涉及到[推荐] 【SQL SERVER中一些特别地方的特别解法2】 100feixianxxx]方面的内容,对于[推荐] 【SQL SERVER中一些特别地方的特别解法2】 100feixianxxx]感兴趣的同学可以参考一下。

本帖最后由 feixianxxx 于 2010-04-21 21:37:34 编辑 详见博客地址: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*/   哪里写的不对或者可以更好的办法解决 欢迎大家指正

sf 我的。。

SSSSSSSSSSSS

引用 1 楼 feixianxxx 的回复:sf 我的。。

引用 1 楼 feixianxxx 的回复:sf 我的。。慢了.

UP。

可以结帖了,支持的请举手.

引用 6 楼 dawugui 的回复:可以结帖了,支持的请举手.

mark

路过,学习...

膜拜....

引用 6 楼 dawugui 的回复:可以结帖了,支持的请举手.

厉害.

强... mark。。。。

膜  拜

这样也可以。new!

看不懂哦,兄弟帮不了你了

看不懂!顶顶!

得分得分得分得分得分得分得分得分得分得分得分得分得分

厉害啊,精辟啊

引用 6 楼 dawugui 的回复:可以结帖了,支持的请举手. uping

厉害啊,精辟啊

接分,顶

接分,顶

好文,回贴支持一下

学习了!!!

接分,顶

要好好的学习

学习~~~

该回复于2012-08-28 09:31:19被版主删除

dinnnnnnnn

小麦知道的太多了。。。。小心石头

不错。

顶了顶了顶了顶了顶了

很不错啊

谢谢,很实用!

不错不错 ………………………………………………………………………………………………

接分~~~~

.............. sql  sql  

学习啦

oooooo

引用 31 楼 ldslove 的回复:小麦知道的太多了。。。。小心石头

学习了

当是学习了 现在对数据库还是初始阶段

引用 43 楼 llc321 的回复:当是学习了 现在对数据库还是初始阶段和LZ的头像很投缘啊

学习了

存档之

嘿嘿.看不懂

该回复于2010-10-25 16:50:41被版主删除

学习了

学习,学习

收藏了

厉害~

谢谢了,非常有用啊

去博客看.........

太好了,谢谢!

路过,努力啊

路过,努力啊

學習.我對sql還很弱

有的懂有的不懂。。。

路过,学习了

学习中

真的不错!!!!啊 学习学习!!!!

受教了!!!

太厉害了  学习了

不错,比较实用

路过,数据库起步中。。。   貌似不错,加油

该回复于2010-04-22 12:57:52被版主删除

哇哈哈,楼主很强大

学习,谢谢分享

哇哈哈,楼主很强大

awertsaytsdgrfag

ertsgfsgfdsgfdsgfdfg

本帖最后由 feixianxxx 于 2010-04-22 16:16:59 编辑

该回复于2010-04-22 12:58:55被版主删除

收藏了,学习!

貌似很有用。

看过回帖,顶。

.

天天看贴,不断接分

这学期刚好学

mark!!!UP

收益非浅!

谢谢楼主。刚好学

学习 .

厉害啊,精辟啊

结贴111!!!hao好!

thanks

路过看看···

....

看看,学习学习!

xue xi 学习

先收着.

mark一下

0.............

aho

学习了

学习了

收藏 回复内容太短了! 

 顶下。。。   不是很明白

学习一下


上一篇:[推荐] -------------我的一些笔记七(空间数据的一些操作)------------- 100fredrickhu]
下一篇:【SQL Server 优化性能的几个方面】 100feixianxxx]

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款