t-sql 笔记(1)

Toad-for-SQL-Server-Freeware

1.查询哪些数据库对象使用了某个表

SELECT b.[name], a.[definition]
FROM sys.all_sql_modules a, sysobjects b
WHERE a.[object_id] = id AND definition LIKE '%表名%'

2.查询表的某一列，将结果变成用逗号分隔的字符串

select col+',' from mytable for xml path('')

排序：

create table pd(col1 varchar(5),col2 int)

insert into pd
 select 'A',3 union all
 select 'A',2 union all
 select 'A',1 union all
 select 'B',2 union all
 select 'B',1


select a.col1,
       stuff((select ','+rtrim(b.col2)
              from pd b
              where b.col1=a.col1
              order by b.col2 
              for xml path('')),1,1,'') 'col2' 
 from pd a
 group by a.col1

 

 

3.查询有哪些表的表名包含“storeroom”

 select * from sysobjects obj where LOWER(obj.name) LIKE N'%storeroom%' and xtype='U'

4.分组条件求和

DECLARE @t1 TABLE ( c1   NUMERIC (12), c2   VARCHAR (30) )
INSERT INTO @t1 (c1, c2) VALUES (1, 'a');
INSERT INTO @t1 (c1, c2) VALUES (2, 'a');
INSERT INTO @t1 (c1, c2) VALUES (3, 'b');
INSERT INTO @t1 (c1, c2) VALUES (4, 'b');
SELECT CASE WHEN max (c1) > 3 THEN sum (c1) ELSE 0 END AS c
  FROM @t1
GROUP BY c2;
/*
结果:
c
0
7
*/

5.求某一天所在星期的周日

http://www.cnblogs.com/wsdj-ITtech/archive/2011/10/06/2199736.html 

USE [MSSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[My_OneDay_GetWeekFirstAndEndDay](@tmpDate DATETIME)
RETURNS  @tmpTable TABLE(FirstDay DATETIME , EndDay DATETIME)
AS
BEGIN
    INSERT INTO @tmpTable
    SELECT a.FirstDay,b.EndDay FROM (    
        SELECT 1 AS ID,DATEADD(wk, DATEDIFF(wk,0,@tmpDate), 0) AS FirstDAy
    ) a
    LEFT JOIN (
        SELECT 1 AS ID,DATEADD(wk, DATEDIFF(wk,0,@tmpDate), 6) AS EndDay
    ) b
    ON a.ID = b.ID
    RETURN
END

SELECT * from  My_OneDay_GetWeekFirstAndEndDay('2010-09-01')

 

6.求时间段内周日的数量

http://www.cnblogs.com/wsdj-ITtech/archive/2011/10/06/2199736.html 

CREATE FUNCTION [dbo].[MY_Range_GetWeekFirstAndEndDays](@tmpDateSTART DATETIME,@tmpDateEND DATETIME)
RETURNS  @tmpTable TABLE(WeekOrder INT,FirstDay DATETIME , EndDay DATETIME)
AS
BEGIN   
 DECLARE   @tmpDate   DATETIME
 DECLARE   @index         INT
 SET       @tmpDate=@tmpDateSTART
 SET       @index=1
     WHILE     @tmpDate <=@tmpDateEND
        BEGIN 
             INSERT INTO @tmpTable
                SELECT @index,a.FirstDay,b.EndDay FROM (    
                    SELECT 1 AS ID,DATEADD(wk, DATEDIFF(wk,0,@tmpDate), 0) AS FirstDAy) a
                LEFT JOIN (
                    SELECT 1 AS ID,DATEADD(wk, DATEDIFF(wk,0,@tmpDate), 6) AS EndDay) b
                ON a.ID = b.ID

          SET   @tmpDate=DATEADD(DAY,7,@tmpDate)
          SET   @index=@index+1
         END 
     RETURN
END

SELECT * from  My_Range_GetWeekFirstAndEndDays('2011-09-01','2011-10-06')

不使用临时表：

DECLARE @tmpDateSTART   DATETIME
DECLARE @tmpDateEND   DATETIME
SET @tmpDateSTART = '2015-1-1'
SET @tmpDateEND = '2015-1-21'

DECLARE @tmpDate   DATETIME
DECLARE @days   INT
SET @tmpDate = @tmpDateSTART
SET @days = 0

WHILE @tmpDate <= @tmpDateEND
BEGIN  
   DECLARE @theDate   DATETIME;
   SET @theDate = DATEADD (wk, DATEDIFF (wk, 0, @tmpDate), 6);
   IF @theDate > @tmpDateSTART AND @theDate < @tmpDateEND      
      SET @days = @days + 1  
   SET @tmpDate = DATEADD (DAY, 7, @tmpDate)
END

SELECT @days

 

7.查看正在执行的

SELECT [Spid] = session_Id,
       ecid,
       [Database] = DB_NAME (sp.dbid),
       [User] = nt_username,
       [Status] = er.status,
       [Wait] = wait_type,
       [Individual Query] =
          SUBSTRING (
             qt.text,
             er.statement_start_offset / 2,
               (  CASE
                     WHEN er.statement_end_offset = -1
                     THEN
                        LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
                     ELSE
                        er.statement_end_offset
                  END
                - er.statement_start_offset)
             / 2),
       [Parent Query] = qt.text,
       Program = program_name,
       Hostname,
       nt_domain,
       start_time,
       datediff (second, start_time, getdate ())
  FROM sys.dm_exec_requests er
       INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
       CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt
 WHERE session_Id > 50                                 -- Ignore system spids.
                      AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
ORDER BY datediff (second, start_time, getdate ()) DESC

 8.生成GUID

C#中用Guid.NewGuid().ToString()

Sql中用NEWID() 

以上方法生成的是36位的GUID，如果需要转换成32位，则需要替换掉其中的'-'字符。

Sql中的方法：replace(newid(), '-', '')

 

9.时间段统计

--将时间转换为小时
--例如8：22分转换为9
--查询语句如下
SELECT cast (
          datepart (
             hh,
             dateadd (
                mi,
                  (    datediff (mi,
                                 CONVERT (VARCHAR (10), getdate (), 112),
                                 getdate ())
                     / 60
                   + 1)
                * 60,
                CONVERT (VARCHAR (10), getdate (), 112))) AS INT)

 

http://bbs.csdn.net/topics/190127317

--环境
declare @t table (
时间 datetime,
金额 int
)
insert @t select
    '2007-1-1 10:00:23',           8 
union all select
    '2007-1-1 10:01:24',           4 
union all select
    '2007-1-1 10:05:00',           2   
union all select
    '2007-1-1 10:06:12',           3 
union all select
    '2007-1-1 10:08:00',           1 
union all select
    '2007-1-1 10:12:11',           5 

select dateadd(mi,(datediff(mi,convert(varchar(10),时间,112),时间)/5+1)*5,convert(varchar(10),时间,112)) as 时间段,
count(*) as 行数,sum(金额) as 总金额
from @t
group by dateadd(mi,(datediff(mi,convert(varchar(10),时间,112),时间)/5+1)*5,convert(varchar(10),时间,112))

--结果
时间段                                                    行数          总金额         
------------------------------------------------------ ----------- ----------- 
2007-01-01 10:05:00.000                                2           12
2007-01-01 10:10:00.000                                3           6
2007-01-01 10:15:00.000                                1           5

（所影响的行数为 3 行）

 10.存储过程，有时执行很慢

现在的解决办法是，将存储过程中加个空格，alter一下。

exec 存储过程 with recompile

 

http://bbs.csdn.net/topics/340185343

http://my.oschina.net/HenuToater/blog/177175

http://havebb.com/b/post/produce-suddenly-slow.aspx

http://www.cnblogs.com/luckylei66/archive/2012/07/30/2615000.html

    SQL优化之存储过程强制编译
     
    ASP.NET调用SQL后台存储过程时，有时突然就变得很慢，在后台直接执行存储过程没问题，但在前台调用存储过程时就是很慢，而且在前台调用成功后，再次调用还是一样的慢，但更新一下存储过程再调用就很快了。但这始终不能彻底解决问题，过段时间又会出来同样的问题。环境（NET4.0+SQL2008R2) 
     
    解决办法： 
     
    方法一：在可能比较耗时的语句后面加上option(recompile) 
     
    方法二：强制编译存储过程 
     
    SQL Server 提供三种重新编译存储过程的方法： 
     
    (1)、sp_recompile 系统存储过程强制在下次运行存储过程时进行重新编译。
     
    　　示例：exec sp_recompile 存储过程名 
     
    (2)、创建存储过程时在其定义中指定 WITH RECOMPILE 选项，表明 SQL Server 将不对该存储过程计划进行高速缓存；该存储过程将在每次执行时都重新编译。
     
    　　示例：Create Proc 存储过程名 WITH RECOMPILE AS 参数 
     
    (3)、在执行存储过程时指定 WITH RECOMPILE 选项，可强制对存储过程进行重新编译。仅当所提供的参数不典型，或者自创建该存储过程后数据发生显著更改时才应使用此选项。
    　　示例：存储过程名 WITH RECOMPILE

参数探测（Parameter Sniffing）与影响计划重用的SET选项

 http://www.cnblogs.com/bigholy/archive/2011/10/18/2216489.html

 SQL Server中存储过程比直接运行SQL语句慢的原因

http://blog.csdn.net/emili/article/details/2192081

 

在很多的资料中都描述说SQLSERVER的存储过程较普通的SQL语句有以下优点：

1.       存储过程只在创造时进行编译即可，以后每次执行存储过程都不需再重新编译，而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.       经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据 库的使用效率，减少程序的执行时间，这一点在较大数据量的数据库的操作中是非常重要的。在代码上看，SQL语句和程序代码语句的分离，可以提高程序代码的 可读性。

3.       存储过程可以设置参数，可以根据传入参数的不同重复使用同一个存储过程，从而高效的提高代码的优化率和可读性。

4.       安全性高,可设定只有某此用户才具有对指定存储过程的使用权存储过程的种类：

A.       系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,如 sp_help就是取得指定对象的相关信息。

B.       扩展存储过程 以XP_开头,用来调用操作系统提供的功能
exec master..xp_cmdshell 'ping 10.8.16.1'

C.       用户自定义的存储过程,这是我们所指的存储过程常用格式

    模版：Create procedure procedue_name [@parameter data_type][output]
    [with]{recompile|encryption} as sql_statement

    解释：output：表示此参数是可传回的

with {recompile|encryption} recompile:表示每次执行此存储过程时都重新编译一次;encryption:所创建的存储过程的内容会被加密。

 

   但是最近我们项目组中有人写了一个存储过程,其计算时间为1个小时47分钟,而有的时候运行时间都超过了两个小时,同事描述说如果将存储过程中的语句拿出来直接运行也就10分钟左右就运行完毕，我没当回事，但是今天我自己写的存储过程也遇到了这个问题，在查找资料后原因终于找到了原因，原来是Parameter sniffing问题。

    下面看我是如何将运行一个小时以上的存储过程优化成在一分钟之内完成的：

原存储过程

CREATE PROCEDURE [dbo].[pro_ImAnalysis_daily]

@THEDATE VARCHAR(30)

AS

BEGIN

    IF @THEDATE IS NULL

    BEGIN

       SET @THEDATE=CONVERT(VARCHAR(30),GETDATE()-1,112);

    END

 

 

    DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@THEDATE;

 

    INSERT RPT_IM_USERINFO_DAILY (THEDATE,ALLUSER,NEWUSER)

    SELECT AA.THEDATE,ALLUSER,NEWUSER

    FROM

    ( ( SELECT THEDATE,COUNT(DISTINCT USERID) ALLUSER

       FROM FACT

       WHERE THEDATE=@THEDATE

        GROUP BY THEDATE

       ) AA

       LEFT JOIN

       (SELECT THEDATE,COUNT(DISTINCT USERID) NEWUSER

        FROM FACT T1

        WHERE NOT EXISTS(

                         SELECT 1

                         FROM FACT T2

                         WHERE T2.THEDATE<@THEDATE

                              AND T1.USERID=T2.USERID)

              AND T1.THEDATE=@THEDATE

        GROUP BY THEDATE

        ) BB

       ON AA.THEDATE=BB.THEDATE);

GO

每日执行:exec pro_ImAnalysis_daily @thedate=null
耗时：1小时47分~2小时13分

经过查找资料，原因如下(由于源文是一篇英文，有些地方写的我不是特别清楚，原文见http://groups.google.com/group/microsoft.public.sqlserver.server/msg/ad37d8aec76e2b8f?hl=en&lr=&ie=UTF-8&oe=UTF-8)：

    在SQL Server中有一个叫做 “Parameter sniffing”的特性。SQL Server在存储过程执行之前都会制定一个执行计划。在上面的例子中，SQL在编译的时候并不知道@thedate的值是多少，所以它在执行执行计划的时候就要进行大量的猜测。假设传递给@thedate的参数大部分都是非空字符串，而FACT表中有40%的thedate字段都是null，那么SQL Server就会选择全表扫描而不是索引扫描来对参数@thedate制定执行计划。全表扫描是在参数为空或为0的时候最好的执行计划。但是全表扫描严重影响了性能。

    假设你第一次使用了Exec pro_ImAnalysis_daily @thedate=’20080312’那么SQL Server就会使用20080312这个值作为下次参数@thedate的执行计划的参考值，而不会进行全表扫描了，但是如果使用@thedate=null,则下次执行计划就要根据全表扫描进行了。

    有两种方式能够避免出现“Parameter sniffing”问题：

(1)通过使用declare声明的变量来代替参数：使用set @variable=@thedate的方式，将出现@thedate的sql语句全部用@variable来代替。

(2)  将受影响的sql语句隐藏起来，比如：

a)      将受影响的sql语句放到某个子存储过程中，比如我们在@thedate设置成为今天后再调用一个字存储过程将@thedate作为参数传入就可以了。

b)      使用sp_executesql来执行受影响的sql。执行计划不会被执行，除非sp_executesql语句执行完。

c)      使用动态sql（”EXEC(@sql)”来执行受影响的sql。

采用（1）的方法改造例子中的存储过程，如下：

    ALTER PROCEDURE [dbo].[pro_ImAnalysis_daily]

@var_thedate VARCHAR(30)

 

AS

BEGIN

    declare @THEDATE VARCHAR(30)

    IF @var_thedate IS NULL

    BEGIN

       SET @var_thedate=CONVERT(VARCHAR(30),GETDATE()-1,112);

    END

 

 

    SET @THEDATE=@var_thedate;

    DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@THEDATE;

 

   INSERT RPT_IM_USERINFO_DAILY (THEDATE,ALLUSER,NEWUSER)

    SELECT AA.THEDATE,ALLUSER,NEWUSER

    FROM

    ( ( SELECT THEDATE,COUNT(DISTINCT USERID) ALLUSER

       FROM FACT

       WHERE THEDATE=@THEDATE

        GROUP BY THEDATE

       ) AA

       LEFT JOIN

       (SELECT THEDATE,COUNT(DISTINCT USERID) NEWUSER

        FROM FACT T1

        WHERE NOT EXISTS(

                         SELECT 1

                         FROM FACT T2

                         WHERE T2.THEDATE<@THEDATE

                              AND T1.USERID=T2.USERID)

              AND T1.THEDATE=@THEDATE

        GROUP BY THEDATE

        ) BB

       ON AA.THEDATE=BB.THEDATE);

GO

 

测试执行速度为10分钟，我又检查了一下这个SQL，发现这个SQL有问题，这个SQL使用了not exists，在一个大表里面使用not exists是不太明智的，所以，我又对这个sql进行了改进，改成如下：

    ALTER PROCEDURE [dbo].[pro_ImAnalysis_daily]

@var_thedate VARCHAR(30)

 

AS

BEGIN

    declare @THEDATE VARCHAR(30)

    IF @var_thedate IS NULL

    BEGIN

       SET @var_thedate=CONVERT(VARCHAR(30),GETDATE()-1,112);

    END

 

 

    SET @THEDATE=@var_thedate;

    DELETE FROM RPT_IM_USERINFO_DAILY WHERE THEDATE=@THEDATE;

 

    INSERT RPT_IM_USERINFO_DAILY(THEDATE,ALLUSER,NEWUSER)

    select @thedate as thedate,

           count(distinct case when today>0 then userid else null end) as alluser,

           count(distinct case when dates=0 then userid else null end) as newuser

    from

    (

       select userid,

              count(CASE WHEN thedate>=@thedate then null else thedate end) as dates,

              count(case when thedate=@thedate then thedate else null end) as today

       from   FACT

       group by userid

    )as fact

GO

测试结果为30ms以下。

 

11.表变量及临时表

http://support.microsoft.com/kb/305977/zh-cn

问题 1：为什么在已经有了临时表的情况下还要引入表变量？

解答 1：与临时表相比，表变量具有下列优点：

    如 SQL Server 联机丛书“表”(Table) 一文中所述，表变量（如局部变量）具有明确定义的范围，在该范围结束时会自动清除这些表变量。
    与临时表相比，表变量导致存储过程的重新编译更少。
    涉及表变量的事务仅维持表变量上更新的持续时间。因此，使用表变量时，需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分，所以事务回滚并不影响它们。

问题 2：如果说使用表变量比使用临时表导致存储过程的重新编译更少，这意味着什么？

解答 2：下面的文章讨论了重新编译存储过程的一些原因：

243586 存储过程重新编译的疑难解答
“由于某些临时表操作引起的重新编译”一节还列出了为避免一些问题（例如使用临时表导致重新编译）而需要满足的一些要求。这些限制不适用于表变量。

表变量完全独立于创建这些表变量的批，因此，当执行 CREATE 或 ALTER 语句时，不会发生“重新解析”，而在使用临时表时可能会发生“重新解析”。临时表需要此“重新解析”，以便从嵌套存储过程引用该表。表变量完全避免了此问题，因此存储过程可以使用已编译的计划，从而节省了处理存储过程的资源。

问题 3：表变量有哪些缺陷？

解答 3：与临时表相比，它存在下列缺陷：

    在表变量上不能创建非聚集索引（为 PRIMARY 或 UNIQUE 约束创建的系统索引除外）。与具有非聚集索引的临时表相比，这可能会影响查询性能。
    表变量不像临时表那样可以维护统计信息。在表变量上，不能通过自动创建或使用 CREATE STATISTICS 语句来创建统计信息。因此，在大表上进行复杂查询时，缺少统计信息可能会妨碍优化器确定查询的最佳计划，从而影响该查询的性能。
    在初始 DECLARE 语句后不能更改表定义。
    表变量不能在 INSERT EXEC 或 SELECT INTO 语句中使用。
    表类型声明中的检查约束、默认值以及计算所得的列不能调用用户定义的函数。
    如果表变量是在 EXEC 语句或 sp_executesql 存储过程外创建的，则不能使用 EXEC 语句或 sp_executesql 存储过程来运行引用该表变量的动态 SQL Server 查询。由于表变量只能在它们的本地作用域中引用，因此 EXEC 语句和 sp_executesql 存储过程将在表变量的作用域之外。但是，您可以在 EXEC 语句或 sp_executesql 存储过程内创建表变量并执行所有处理，因为这样表变量本地作用域将位于 EXEC 语句或 sp_executesql 存储过程中。

问题 4：与临时表或永久表相比，表变量的仅存在于内存中的结构保证了更好的性能，是否因为它们是在驻留在物理磁盘上的数据库中维护的？

解答 4：表变量不是仅存在于内存中的结构。由于表变量可能保留的数据较多，内存中容纳不下，因此它必须在磁盘上有一个位置来存储数据。与临时表类似，表变量是在 tempdb 数据库中创建的。如果有足够的内存，则表变量和临时表都在内存（数据缓存）中创建和处理。

问题 5：必须使用表变量来代替临时表吗？

解答 5：答案取决于以下三个因素：

    插入到表中的行数。
    从中保存查询的重新编译的次数。
    查询类型及其对性能的指数和统计信息的依赖性。

在某些情况下，可将一个具有临时表的存储过程拆分为多个较小的存储过程，以便在较小的单元上进行重新编译。

通常情况下，应尽量使用表变量，除非数据量非常大并且需要重复使用表。在这种情况下，可以在临时表上创建索引以提高查询性能。但是，各种方案可能互不相同。Microsoft 建议您做一个测试，来验证表变量对于特定的查询或存储过程是否比临时表更有效。

 

 

 

x.待续