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以下。