SQL Server 查询、搜索命令、语句

SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁

一. 阻塞查询 sp_lock

      执行 exec sp_lock  下面列下关键字段

      spid 是指进程ID，这个过滤掉了系统进程，只展示了用户进程spid>50。

      dbid 指当前实例下的哪个数据库 , 使用DB_NAME() 函数来标识数据库

      type 请求锁住的模式

      mode 锁的请求状态

                     GRANT：已获取锁。

                     CNVRT：锁正在从另一种模式进行转换，但是转换被另一个持有锁（模式相冲突）的进程阻塞。
                     WAIT：锁被另一个持有锁（模式相冲突）的进程阻塞。

     总结：当mode 不为GRANT状态时， 需要了解当前锁的模式，以及通过进程ID查找当前sql 语句 

                例如当前进程ID是416，且mode状态为WAIT 时，查看方式 DBCC INPUTBUFFER(416)

               用sp_lock查询显示的信息量很少，也很难看出谁被谁阻塞。所以当数据库版本为2005及以上时不建议使用。

二.阻塞查询  dm_tran_locks

SELECT 
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;

上面查询只显示有阻塞的会话， 关注blocking_session_id 也就是被阻塞的会话ID，同样使用DBCC INPUTBUFFER来查询sql语句

三.阻塞查询 sys.sysprocesses

SELECT 
spid,
kpid,
blocked,
waittime AS 'waitms', 
lastwaittype, 
DB_NAME(dbid)AS DB,  
waitresource, 
open_tran,
hostname,[program_name],
hostprocess,loginame,
[status]
FROM sys.sysprocesses WITH(NOLOCK) 
WHERE    kpid>0  AND  [status]<>'sleeping'  AND spid>50

sys.sysprocesses  能显示会话进程有多少， 等待时间， open_tran有多少事务， 阻塞会话是多少. 整体内容更为详细。
  关键字段说明:

       spid 会话ID（进程ID），SQL内部对一个连接的编号，一般来讲小于50

　　kipid 线程ID
　　blocked: 阻塞的进程ID， 值大于0表示阻塞, 值为本身进程ID表示io操作
　　waittime:当前等待时间（以毫秒为单位）。
　　open_tran: 进程的打开事务数
　　hostname:建立连接的客户端工作站的名称
　　program_name 应用程序的名称。
　　hostprocess 工作站进程 ID 号。
　　loginame 登录名。
　　[status]
　　　　running = 会话正在运行一个或多个批
　　　　background = 会话正在运行一个后台任务，例如死锁检测
　　　　rollback = 会话具有正在处理的事务回滚
　　　　pending = 会话正在等待工作线程变为可用
　　　　runnable = 会话中的任务在等待,由scheduler来运行的可执行队列中。(重要)
　　　　spinloop = 会话中的任务正在等待调节锁变为可用。
　　　　suspended = 会话正在等待事件（如 I/O）完成。(重要)
　　　　sleeping = 连接空闲

              wait resource 格式为 fileid:pagenumber:rid 如(5:1:8235440)

              kpid=0, waittime=0 空闲连接

              kpid>0, waittime=0 运行状态
              kpid>0, waittime>0 需要等待某个资源，才能继续执行,一般会是suspended（等待io）
              kpid=0, waittime=0 但它还是阻塞的源头，查看open_tran>0 事务没有及时提交。

              如果blocked>0，但waittime时间很短，说明阻塞时间不长，不严重
              如果status 上有好几个runnable状态任务，需要认真对待。 cpu负荷过重没有及时处理用户的并发请求

 

/*查询spid 对应的SQL*/
select er.session_id,CAST(csql.text AS varchar(255)) AS CallingSQL
from master.sys.dm_exec_requests er
WITH (NOLOCK)
       CROSS APPLY MASTER.sys.fn_get_sql (er.sql_handle) csql
where er.session_id=@spid --session_id

　　

--   更新、删除等事物锁查询
BEGIN   TRAN
delete Tab where ID in(1,2)
--   列出锁信息
EXEC   sp_lock   @@spid
--   提交或者回滚事务
COMMIT   TRAN

--SQL分页 offset fetch next方式（SQL2012以上的版本才支持：推荐使用 ）
select * from ArtistModels  order by ArtistId offset 4 rows fetch next 5 rows only
--order by ArtistId offset 页数 rows fetch next 条数 rows only ----

 

--查询所有表
SELECT NAME,* FROM SYSOBJECTS WHERE XTYPE='U' order by SYSOBJECTS.name 
--查询所有存储过程
select * from sysobjects where type='P' order by [name]
--查询表约束
exec sp_helpconstraint @objname=book_detail --@objname=prx_class
go
-- 查看内容(存储过程)
sp_helptext 'P_Sys_MenuList'

--查询所有触发器
select name from sysobjects where xtype='TR' 

 --查询所有视图
select name from sysobjects where xtype='V'

--查询触发器与关联表
SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id WHERE tb1.type='TR'

/**判断是否存在该触发器**/
--if exists (select * from sysobjects where id=object_id(N'Trg_IC_Log_Insert1') and type='tr')
-- or
if (object_id('Trg_IC_Log_Insert1','tr') is not null )
drop TRIGGER [Trg_IC_Log_Insert1] 
go

 

/*修改表命令*/

--移除主键
alter table tb_name drop constraint PK_name
--添加多列主键
alter table  tb_name  add constraint PK_name primary key(column1,column2,column3) 
--修改表列名或属性 add 增加列,drop 移除列
alter table  tb_name alter column column1 varchar(10) null

 

--通过某个字段名称找到数据库中对应的表
USE Works 
GO
select DISTINCT object_name(id)
from syscolumns
where name like '%the_class%' order by 1 
GO

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

select * from sysobjects
where object_name(id) in (
'OE_ORDER_LINES_ALL',
'OE_ORDER_HEADERS_ALL',
'OE_TRANSACTION_TYPES_TL',
'FNDLOOKUPVALUES',
'HZ_CUST_SITE_USES_ALL',
'HZ_CUST_ACCT_SITES_ALL')

 

--sql 按默认顺序排序，生成RowNum列(行号列)
select identity(int,1,1) as sort,* into #temp from tb1
select * from #temp order by sort

 

sql读取系统日期和时间的方法如下：
--获取当前日期(如：yyyymmdd)

select CONVERT (nvarchar(12),GETDATE(),112)

--获取当前日期(如：yyyymmdd hh:MM:ss)
select GETDATE()

--获取当前日期(如：yyyy-mm-dd)
Select Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())

--获取当前日期(如：yyyy/mm/dd)
select DATENAME(YEAR,GETDATE())+'/'+DATENAME(MONTH,GETDATE())+'/'+DATENAME(DAY,GETDATE())

--获取几种日期
select DATENAME(YEAR,GETDATE()) --年份(YYYY)
select DATENAME(YY,GETDATE())

select DATENAME(MM,GETDATE()) --月份
select DATENAME(DD,GETDATE()) --日期
select dateName(hh,getdate()) --获取小时
select DATENAME(MI,GETDATE()) --获取分钟
select DATENAME(SECOND,GETDATE()) --获取秒
select DATENAME(WEEK,GETDATE()) --获取当前星期(周)是这一年中的第几个星期(周)
select DATENAME(WEEKDAY,GETDATE()) --星期几

*select convert(char(8),getdate(),108) as yourtime--获取当前时间
SELECT convert(char(10),getdate(),120) as yourdate--获取当前日期
SELECT left(convert(char(10),getdate(),120),4) as youryear--获取当前年份

 

sql 分组后根据最大值得到记录明细

select * from 表名 as a where exists (select 1 from 
(select 工单编号,max(处理时间) as 处理时间 from 表名 group by 工单编号) as b where a.工单编号=b.工单编号 and a.处理时间=b.处理时间)

SELECT * FROM
(
select row_number() over(partition by mn order by NotesTime DESC) as number,* from T_TaskNotes 
) as tb where number=1

 

SQLServer: 解决“错误15023:当前数据库中已存在用户或角色”

Use newzs
go
sp_change_users_login 'update_one', 'infos1', 'infos1'

 /*       
其中newzs为存在孤立用户的数据库，update_one是存储过程的参数，表示只处理一个用户，前一个infos1是“用户”，后一个infos1是“登录”，以上这个SQL表示将服务器登录“infos1”与 newzs数据库用户“infos1”重新连接起来。这样就可以正常使用数据库了
*/

 

/*数据库手动抛出错误异常*/
--内容 级别 
RAISERROR ('678 ''数据异常'' 已经结账' , 16, 1) WITH NOWAIT
RAISERROR ('无效用户ID。' , 16, 1)  

 /*分词搜索查找*/

declare @a varchar(10) 
declare @s varchar(1000)
declare @t table(name varchar(10))
set @a='a,b,c'
set @s=right(replace(','+@a,',',''' as name union select '''),len(replace(','+@a,',',''' as name union select '''))-15)+''''
insert into @t exec(@s)
select * from tb b where exists
(select 1 from @t a where charindex(a.name,b.name)>0)

 

if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,name varchar(25))
insert into [tb]
select 1,'asdf富士康sfsaf' union all
select 2,'富士康sdaf跳楼' union all
select 3,'sdaf跳楼' union all
select 4,'asdf富士康as跳楼郭台铭asf' union all
select 5,'郭台铭asf'
 
 
declare @name varchar(50),@sql varchar(8000)
set @Name='富士康 跳楼 郭台铭'
set @sql=' (case when charindex('''+replace(@name,' ',''',name)>0 then 1 else 0 end)+(case when charindex(''')+''',name)>0 then 1 else 0 end)'
set @sql='select * from tb order by '+@sql+' desc'

 

/**全文检索（查询整个数据库中某个特定值所在的表和字段的方法 ）**/

一、

DECLARE @keyWord VARCHAR(200)
set @keyWord='hy'

-- 最大的长度.
DECLARE @MaxLength INT;
-- 数据库表名称
DECLARE @TableName VARCHAR(100);
-- 数据库表ID
DECLARE @object_id INT;
-- 查询列名的SQL
DECLARE @ColumnSql VARCHAR(500);
-- 列名.
DECLARE @clumnName VARCHAR(20);

-- 查询 数据是否存在的SQL
DECLARE @Sql NVARCHAR(500);

-- 查询 数据是否存在结果
DECLARE @RowCount INT;

BEGIN

-- 取得 传入的 字符串长度
-- 对于 表定义中， 字段长度小于 该长度的，将不检索.
SET @MaxLength = LEN(@keyWord);

-- 定义游标. 取得所有的数据库表名称.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
    SELECT object_id, name FROM sys.tables

-- 打开游标.
OPEN c_test_main;

-- 填充数据.
FETCH NEXT FROM c_test_main INTO @object_id, @TableName;

-- 假如检索到了数据，才处理.
WHILE @@fetch_status = 0
BEGIN

    -- 准备用于执行的 查询列信息的 SQL 语句.
    SET @ColumnSql = 'DECLARE cur_column CURSOR for SELECT col.name '
      + ' FROM sys.columns col, sys.types typ '   -- 从列视图 与 数据类型视图查询
      + ' WHERE '
      + '   col.system_type_id = typ.system_type_id' -- 列视图 与 数据类型视图 关联
      + '   AND typ.name IN(''varchar'', ''nvarchar'', ''char'', ''nchar'') ' -- 只查询指定数据类型的.
      + '   AND col.max_length > ' + LTRIM(STR(@MaxLength )) -- 最大长度要大于 输入信息长度的
      + '   AND col.object_id =' + LTRIM(STR(@object_id)); -- 表ID

    EXEC(@ColumnSql);

    OPEN cur_column;
    FETCH next FROM cur_column INTO @clumnName;
    WHILE @@FETCH_STATUS=0
      BEGIN

        SET @Sql = 'SELECT @RowCount = COUNT(1) FROM ' + @TableName + ' WHERE ' + @clumnName + ' LIKE ''%' + @keyWord + '%'' ';

        EXEC SP_EXECUTESQL @Sql, N'@RowCount INT OUTPUT', @RowCount OUTPUT;
       
        IF @RowCount > 0
        BEGIN
          PRINT @TableName + '.' + @clumnName;
        END

        FETCH next FROM cur_column INTO @clumnName;
      END
    CLOSE cur_column;
    DEALLOCATE cur_column;

    -- 填充下一条数据.
    FETCH NEXT FROM c_test_main INTO @object_id, @TableName;
END;
-- 关闭游标
CLOSE c_test_main;
-- 释放游标.
DEALLOCATE c_test_main;
END

 

二、

declare @sql nvarchar(max),@search nvarchar(100)
set @search='hy'
set @sql=''
select @sql=@sql+'select top 1 '''+sysobjects.name+''' as [table],'''+syscolumns.name+''' as [columns],'+syscolumns.name+' as [value] from '+sysobjects.name+' where '+syscolumns.name+'='''+@search+''' union all ' from sysobjects inner join syscolumns on sysobjects.id=syscolumns.id where sysobjects.xtype='U' and syscolumns.xtype in (34,35,99,167,175,231,239,241,231)
select @sql=@sql+'select null,null,null'
exec sp_executesql @sql

 

三、

select convert(varchar(255),'') dsca
into #y
where 1=0

-- delete #y
declare @s varchar(255)
set @s='hy'
DECLARE bbb cursor for
select  TABLE_NAME,column_name  FROM information_schema.columns
    where table_name in (select  table_name from information_schema.tables where TABLE_type='BASE TABLE' and table_name like '%%' )
    and data_type like '%varchar%'


declare @t varchar(255)
declare @f varchar(255)
open bbb
fetch  next from bbb into @t,@f
while @@fetch_status=0
begin

exec( ' if exists (select * from '+@t+' where '+@f+'='+''''+@s+''''+'  )  insert into #y select '+''''+@t+'.'+@f+''''  )


--insert into #t select  '+''''+'select * from  '+@t+' WHERE '+@f+'=@F'+'''

fetch  next from bbb into @t,@f

end
close bbb
deallocate  bbb

select * from #y
drop table #y 

 /**全文检索END*/

 

SET NOCOUNT ON;--存储过程,当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。
declare @date datetime,@date1 datetime,@substoreid varchar(20)
set @date='2014-09-22'
set @date1='2014-09-30'
set @substoreid='9001'
--临时表用法
select tag,code,name,num,money into #mid from (
select 1 as tag,prx_code as code,prx_name as name,sum(isnull(num,0)) as num,sum(isnull(money,0)) as money from tb1
union    
select 2 as tag,receive_minout.pay_class as code,name as name,0 as num,sum(isnull(money,0)) as money from tb2   
 ) A     
select * from #mid order by tag    
drop table #mid

--SQL每组数据只取前几条数据的写法 

select *
  from (select row_number() over(partition by t.type order by date desc) rn,
               t.*
          from 表名 t)
 where rn <= 2;
/*
type要分的类
date 排序
rn 每类要显示几条数据
*/

over partition by与group by 的区别

 

/*SQL执行中占CPU资源最多的前10条查询 */
    select top 20  
     total_worker_time/execution_count as avg_cpu_cost,plan_handle,  
    execution_count,  
    (select substring(text,statement_start_offset/2+1,  
    (case when statement_end_offset=-1  
    then len(convert(nvarchar(max),text))*2  
    else statement_end_offset  
    end - statement_start_offset)/2)  
    from sys.dm_exec_sql_text(sql_handle)) as query_text  
    from sys.dm_exec_query_stats  
    order by [avg_cpu_cost] desc  


-- 找出工作负荷中运行最频繁的查询  
    select top 10 total_worker_time,plan_handle,execution_count,  
       (select substring(text,statement_start_offset /2 +1,  
           (case when statement_end_offset = -1  
              then len(convert(nvarchar(max),text))*2  
              else statement_end_offset  
            end - statement_start_offset)/2)  
          from sys.dm_exec_sql_text(sql_handle)) as query_text  
       from sys.dm_exec_query_stats  
       order by execution_count desc  

--找到被编译得最多的前10位查询计划  
    select top 10 plan_generation_num,execution_count,  
      (select substring(text,statement_start_offset /2 +1,  
         (case when statement_end_offset = -1  
           then len(convert(nvarchar(max),text))*2  
           else statement_end_offset  
          end - statement_start_offset)/2)  
        from sys.dm_exec_sql_text(sql_handle)) as query_text  
    from sys.dm_exec_query_stats  
    where plan_generation_num>1  
    order by plan_generation_num desc  

--执行最慢的SQL语句
SELECT
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '�tch%'
ORDER BY
total_elapsed_time / execution_count DESC;
 --总耗CPU最多的前个SQL:
SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC


--平均耗CPU最多的前个SQL:
SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
    max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  execution_count>1
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

 

 

 

/*查询缺失索引*/
SELECT 
DatabaseName = DB_NAME(database_id)
,[Number Indexes Missing] = count(*) 
 FROM sys.dm_db_missing_index_details
 GROUP BY DB_NAME(database_id)
 ORDER BY 2 DESC;

SELECT  TOP 10 
          [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
          , avg_user_impact
          , TableName = statement
          , [EqualityUsage] = equality_columns 
          , [InequalityUsage] = inequality_columns
          , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
        ON s.group_handle = g.index_group_handle 
 INNER JOIN    sys.dm_db_missing_index_details d 
        ON d.index_handle = g.index_handle
 ORDER BY [Total Cost] DESC;

 

/*将一列多行数据合并为一行*/
SELECT  DISTINCT [Student]

      ,STUFF(

                        (

                              SELECT ','+[Course]

                              FROM [dbo].[SC]

                              WHERE Student = A.Student

                              FOR XML PATH('')

                        )

                        ,1,1,''

                  )AS Course

  FROM [dbo].[SC] AS A

/*拆分字典匹配合并成一行*/
SELECT
    STUFF(
    (SELECT ',' + T1.CodeName FROM (
        SELECT Code,CodeName from T_ListCode TL JOIN dbo.f_splitstr(T_ST21.Com_FunctionCategory,',') TY ON TL.Code=TY.F1
    )AS T1 FOR XML PATH('')),1,1,'') AS CodeName
 from T_ST21  

 

/*SQL递归查询*/
WITH SearcRiver AS
(
    SELECT RiverID,UpRiverID FROM T_River 
    WHERE RiverID = '1100000000'
    UNION ALL
    SELECT A.RiverID,A.UpRiverID FROM T_River A, SearcRiver B
    WHERE A.UpRiverID=B.RiverID 
)
select * from SearcRiver

 

--try catch 事物-回滚
begin  try
begin transaction tr

   select * from Car_Brand
   update car_log set [reason]='ceshiceshiceshi' where [modelid]=557;
   select 1/0
   print 'i have executed!'
   select * from AREA
   
   
   print 'executed success';
   commit transaction tr
   
   
end try
begin catch
  print 'executed failed';
  rollback transaction tr
end catch

 

查询执行命令的错误信息

函数	描述
ERROR_NUMBER()	返回导致运行 CATCH 块的错误消息的错误号。
ERROR_SEVERITY()	返回导致 CATCH 块运行的错误消息的严重级别
ERROR_STATE()	返回导致 CATCH 块运行的错误消息的状态号
ERROR_PROCEDURE()	返回出现错误的存储过程名称
ERROR_LINE()	返回发生错误的行号
ERROR_MESSAGE()	返回导致 CATCH 块运行的错误消息的完整文本

 

BEGIN CATCH  
  
        IF @@TRANCOUNT > 0  
  
            ROLLBACK TRAN --RollBack in case of Error  
  
        -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception  
  
        RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)  
  
    END CATCH  

自定义错误

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='my_sp_test' AND TYPE='P') BEGIN
    DROP PROCEDURE my_sp_test;
END;
GO
create procedure my_sp_test @i int, @outstr varchar(100) out as
begin try
    declare @j int;
    if @i<10 begin
      set @outstr = 'system exception.';
      set @j = 10/0;  -- 因为被除数为0,所以这里将会抛出一个系统的异常
    end
    else begin
      set @j = @i;
      set @outstr = 'customer exception';
      -- 抛出自定义的异常,在最后的catch块中统一处理异常
      RAISERROR (66666, -- Message id.
           16, -- Severity,
           1 -- State,
           ) ;    
    end;
end try
begin catch 
    if @@ERROR=66666 begin  -- 通过@@ERROR的值来判断是否是自定义的异常
        set @outstr = @outstr  + '---------------- customer exception';
    end;
    return;
end catch;
go

 

如上代码，raiserror参数说明：

(1). Message id ： 异常的唯一标识，且这个值会被赋值给SQLServer的系统变量@@Error。自定义异常的Message Id建议使用50000以后的，因为50000以内的会被系统异常占用。

(2). Severity ： 异常的级别。 可输入1—19的数值。1—10之间不会被catch捕获。19以后是非常严重的级别。

(3). State ： 如果输入负值或大于255 的值会生成错误，产生错误则会中断数据库的连接

 

执行命令，不返回影响行数消息

SET ANSI_NULLS ON

 

如上代码，raiserror参数说明：

   (1). Message id ： 异常的唯一标识，且这个值会被赋值给SQLServer的系统变量@@Error。

          自定义异常的Message Id建议使用50000以后的，因为50000以内的会被系统异常占用。

  (2). Severity ： 异常的级别。 可输入1—19的数值。1—10之间不会被catch捕获。19以后是非常严重的级别。

   (3). State ： 如果输入负值或大于255 的值会生成错误，产生错误则会中断数据库的连接

 

/*时间范围交集*/
select *   
from test_table  
where (startTime > a AND startTime < b) OR  
        (startTime < a AND endTime > b) OR  
        (endTime > a AND endTime < b)  

/*SQL判断两个时间段是否有交集
给定时间区间（begin，end），数据库字段startTime与endTime，*/
SELECT * FROM xxx
WHERE NOT ((endTime < begin) OR (startTime > end))

 

 

 

DBCC DROPCLEANBUFFERS  --清除缓冲区
DBCC FREEPROCCACHE  --删除计划高速缓存中的元素

SET STATISTICS TIME ON --执行时间
SET STATISTICS IO ON --IO读取

 

/*数据库表碎片整理*/
通过对扫描密度（过低），扫描碎片（过高）的结果分析，判定是否需要索引重建，主要看如下两个：
Scan Density [Best Count:Actual Count]-扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。
Logical Scan Fragmentation-逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。 

解决方式：
一是利用DBCC INDEXDEFRAG整理索引碎片
二是利用DBCC DBREINDEX重建索引。

两者区别调用如下：
DBCC INDEXDEFRAG 命令是联机操作，所以索引只有在该命令正在运行时才可用，而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。
重新创建聚集索引将对数据进行重新组织，其结果是使数据页填满。填满程度可以使用 FILLFACTOR 选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不会重新创建该索引。也就是说，要想获得好的效果，还是得用重建索引，所以决定重建索引。

 

/*整理碎片*/
--查找
DBCC SHOWCONTIG('TbName','IDX_TbName')
DBCC SHOWCONTIG('TbName','IDX_TbName')
--整理
DBCC INDEXDEFRAG([DBName],'TbName','IDX_TbName')
DBCC INDEXDEFRAG([DBName],'TbName','IDX_TbName')

/*表压缩*/
alter table TbName rebuild with(data_compression=row)
GO
SP_SPACEUSED 'TbName'

alter table TbName rebuild with(data_compression=row)
GO
SP_SPACEUSED 'TbName'


/*压缩索引*/
alter index IDX_TbName on TbName rebuild with(data_compression=row)
alter index IDX_TbName on TbName rebuild with(data_compression=row)

/*整体数据库收缩*/
use DBName
ALTER DATABASE DBName
SET RECOVERY SIMPLE;--设置简单恢复模式
GO
DBCC SHRINKFILE (CPLHP3Base_log, 1); 
GO
ALTER DATABASE DBName
SET RECOVERY FULL;--恢复为原模式
GO

 

所有关联查询

内连接（INNER JOIN）、外连接（outer join）、全连接-并集（full join）、left join 、right join、Cross join、CROSS APPLY、outer apply

LEFT JOIN （一对多） 左边主表至少显示一条数据，需要先过滤再查询，方式1 ON 关联 后面加条件，方式2 子查询过滤；

CROSS APPLY：关联多列子查询等；

 

--Sql Server 添加用户权限映射已存在 冲突
User DBName
sp_change_users_login 'update_one', 'UserName', 'UserName'

--添加用户数据库owner权限
User DBName
exec sp_addrolemember 'db_owner', [UserName]

 

--查询表结构列表文本（Word）
SELECT  ROW_NUMBER() OVER ( ORDER BY col.colorder ) AS 行号,
        col.name AS 列名 ,
        t.name AS 数据类型 ,
        col.length AS 长度 ,
        CASE WHEN col.isnullable = 1 THEN '√'
             ELSE ''
        END AS 允许空 ,
        CASE WHEN EXISTS ( SELECT   1
                           FROM     dbo.sysindexes si
                                    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                                              AND si.indid = sik.indid
                                    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                                              AND sc.colid = sik.colid
                                    INNER JOIN dbo.sysobjects so ON so.name = si.name
                                                              AND so.xtype = 'PK'
                           WHERE    sc.id = col.id
                                    AND sc.colid = col.colid ) THEN '√'
             ELSE ''
        END AS 主键 ,
        ISNULL(comm.text, '') AS 默认值,
        ISNULL(ep.[value], '') AS 备注 
FROM    dbo.syscolumns col
        LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype
        inner JOIN dbo.sysobjects obj ON col.id = obj.id
                                         AND obj.xtype = 'U'
                                         AND obj.status >= 0
        LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id
        LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id
                                                      AND col.colid = ep.minor_id
                                                      AND ep.name = 'MS_Description'
        LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
                                                         AND epTwo.minor_id = 0
                                                         AND epTwo.name = 'MS_Description'
WHERE   obj.name = 'TableName'
ORDER BY col.colorder ;