整理的一些数据库不容易想到的SQL语句实例一
来源：博客园
1、行转列SQL语句 SELECT
dbo.TempTable where Weeks is not null
) p PIVOT( SUM([Qty]) FOR [Weeks] IN ([1],[2],[3],[4],[5],[6])) AS pvt 2、游标 declare P_Product_Main CURSOR FAST_FORWARD FORSELECT PID FROM [表名] order by PID-- 打开游标.
OPEN P_Product_Main
--填充数据.
FETCH NEXT FROM P_Product_Main INTO @PID
--假如检索到了数据，才处理.
WHILE @@fetch_status = 0
print @PID;
fetch next from P_Product_Main into @PID;
-- 关闭游标
CLOSE P_Product_Main
--释放游标.
DEALLOCATE P_Product_Main 3、 查看当前进程,或死锁进程,并能自动杀掉死进程，因为是针对死的,所以如果有死锁进程,只能查看死锁进程，当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程 create proc [dbo].[sp_lock2]@kill_lock_spid bit=1,
--是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示asdeclare @count int,@s nvarchar(1000),@i intselect id=identity(int,1,1),标志, 进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid, 数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu, 登陆时间=login_time,打开事务数=open_tran, 进程状态=status, 工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess, 域名=nt_domain,网卡地址=net_addressinto #t from( select 标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0 from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0 union all select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1 from master..sysprocesses a where blocked0)a order by s1,s2 select @count=@@rowcount,@i=1 if @count=0 and @show_spid_if_nolock=1begin insert #t select 标志='正常的进程',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address from master..sysprocesses set @count=@@rowcountend if @count>0begin create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255)) if @kill_lock_spid=1 begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end end else
while @i0 ) a
where not exists(select * from (select * from master.dbo.sysprocesses where
blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from master.dbo.sysprocesses where
IF @@ERROR0 RETURN @@ERROR
-- 找到临时表的记录数 select
@intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who
IF @@ERROR0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message -- 循环开始while @intCounter <= @intCountPropertiesbegin-- 取第一条记录
@spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end
-- 循环指针下移 set @intCounter = @intCounter + 1end
drop table #tmp_lock_who set nocount offreturn 0end 4、 临时表 select top(100) * into #temp_table from WOTable
as a where StartTime=@startdate and not exists (select ID from temp_WOCode where ID=a.ID and Status=0)
and [Status]=0 and CurrentStatus999 update a set a.BurdeningStatus=20,a.BurdeningDateTime=GETDATE(),a.BurdeningPerson=@userid
where exists (
select ID from
#temp_table where ID=a.ID and Status=0 ) 5、事务 begin tran
--开始事务BEGIN TRY
if @@trancount 0 begin
print 'has data, commit tran'
commit tran endEND TRYBEGIN CATCH if @@trancount 0 begin
rollback tran endEND CATCH 6、索引 //普通索引CREATE INDEX
ON tablename (列名)；//唯一索引CREATE UNIQUE INDEX
ON tablename (列名)；drop index
免责声明：本站部分内容、图片、文字、视频等来自于互联网，仅供大家学习与交流。相关内容如涉嫌侵犯您的知识产权或其他合法权益，请向本站发送有效通知，我们会及时处理。反馈邮箱&&&&。
学生服务号
在线咨询，奖学金返现，名师点评，等你来互动您的位置:>>> 文章内容
思缘教程网赞助联系方式
实例分析解决死锁导致网站无法访问故障 ---
本文通过实例来分析数据库死锁造成网站无法访问的解决方法。
问题:网站访问不了，主机重启之后，网站运行正常。
解决对策：由于数据库死锁造成。
死锁原因：
提取查询数据相应数据，修改Stat表，都是修改同一条数据，进行大数据量的操作，多用户同时操作时，造成数据库死锁和阻塞；
知识拓展：
1、SQL死锁和阻塞。
2、死锁测试方法：程序中将数据库操作，循环操作1万次，打开多个窗口同时执行。
3、查找数据库死锁原因的方法。
下面的SQL语句运行之后，便可以查找出SQLServer的死锁和阻塞的源头。
use master
declare @spid int,@bl int
DECLARE s_cur CURSOR FOR
select　0 ,blocked
from (select * from sysprocesses where　blocked&0 ) a
where not exists(select * from (select * from sysprocesses where　blocked&0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where　blocked&0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
if @spid =0
select '引起数据库死锁的是:
'+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '
进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
CLOSE s_cur
DEALLOCATE s_cur
exec sp_who2
4、查看当前进程,或死锁进程,并能自动杀掉死进程：
查看当前进程,或死锁进程,并能自动杀掉死进程。因为是针对死的,所以如果有死锁进程,只能查看死锁进程。当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程。
exec p_lockinfo
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,
是否显示正常进程信息,1 显示,0 不显示
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
域名=nt_domain,网卡地址=net_address
into #t from(
select 标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
select '|_牺牲品_&',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked&&0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
select 标志='正常的进程',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
if @count&0
create table #t1(id int identity(1,1),a nvarchar(30),
b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
declare @spid varchar(10),@标志 varchar(10)
while @i&=@count
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
while @i&=@count
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')'
from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
& 下一篇：
评论内容：(不能超过250字，需审核后才会公布，请自觉遵守互联网相关政策法规。SQLServer（194）
SQLServer 故障处理（54）
CPU 高的时候有很多方法定位，以下是一种比较慢的定位方法，不过本人也不会这样跟踪。实例如下：
首先确认高CPU是数据库内部消耗还是Windows其他应用程序消耗较高CPU。打开Windows 任务管理器可以确定哪个进程消耗高CPU。
确定是 sqlserver 实例内部消耗的CPU，打开性能计数器跟踪。
cmd——》perfmon——》选择计数器对象 thread ——》选择 % Processor Time、ID Process 、ID Thread——》对象实例选择所有的sqlserver/XX——》确定！
选择高亮，点击最高的线条，或者逐行查看sqlserver 线程的% Processor Time，确定为 51&
找到 ID Thread 为&51的，查看其&#20540;为2252，即为线程ID。
到数据库中查看系统表&sys.sysprocesses ，kpid为2252的连接，可以看多执行的状态和对象信息。
SELECT spid, kpid, dbid, cpu, lastwaittype,status,loginame
FROM sys.sysprocesses WHERE spid=55
查看执行语句：
DBCC INPUTBUFFER(55)
可以确定执行了什么语句，即可进行相关优化。或者对该登录连接进行相关操作。
本人一般直接查看当前请求的连接：
sp.[kpid],
der.[session_id],
der.[request_id],
der.[start_time],
der.[status],
der.[command],
dest.[text],
DB_NAME(der.[database_id]) [database],
der.[blocking_session_id],
der.[wait_type] ,
der.[wait_time],
der.[wait_resource],
der.[reads],
der.[writes] ,
der.[logical_reads],
der.[row_count],
sp.lastwaittype,sp.hostname,sp.program_name,sp.loginame,sp.cmd
FROM sys.[dm_exec_requests] AS der
INNER JOIN master.dbo.sysprocesses AS sp on der.session_id=sp.spid
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]&50 AND session_id&&@@SPID AND der.[database_id]=DB_ID()
ORDER BY [cpu_time] DESC
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：667618次
积分：10388
积分：10388
排名：第1252名
原创：382篇
转载：46篇
评论：90条
(4)(12)(18)(1)(6)(6)(12)(3)(1)(8)(6)(4)(2)(12)(14)(18)(12)(6)(12)(13)(22)(17)(21)(10)(13)(20)(11)(7)(4)(2)(4)(2)(3)(3)(25)(7)(5)(5)(11)(7)(5)(10)(13)(7)(11)(11)(6)(1)(1)