sql 笔记1

 

--添加一个字段
alter table TB_效率  add 抛盘率 int

--修改一个字段
EXEC sp_rename  'TB_效率.zhi' , '拦截率'

--10大占cpu的
SELECT TOP 10
   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

--修复会丢失数据
DBCC CHECKDB('xxx')

--列和列之间的比较
create table tb_A
(a int,
 b int,
 c int)

insert into TB_A
select 1,2,3
union all
select 4,5,6
union all
select 7,8,9

select * ,(select max(a) from (select a union all select b union all select c) as d) from TB_A

--更新远程表
update lianjie.stock_new.tb_A
set A=a.A
from TB_b a,lianjie.stock_new.tb_A b
where a.sid=b.sid

--依次复制
create table TB_B
(sid int,
 ziduan int)

insert into tB_B
select 1,null
union all select 1,null
union all select -1,null
union all select -1,null
union all select -1,null

declare @a int, @b int
set @a = 0
set @b = 0

UPDATE tB_B SET @b =
(case WHEN @a <> sid THEN @b +1
      ELSE @b END)
   ,@a = sid
   ,ziduan = @b
--数据库没有完全关闭,误删了日志文件
--注意：数据库的物理位置变动一下
USE MASTER
GO
SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE stock_new SET EMERGENCY
GO
sp_dboption 'stock_new', 'single user', 'true'
GO
DBCC CHECKDB('stock_new','REPAIR_ALLOW_DATA_LOSS')
GO
ALTER DATABASE stock_new SET ONLINE
GO
sp_configure 'allow updates', 0 reconfigure with override
GO
sp_dboption 'stock_new', 'single user', 'false'
GO

--堵塞分析
SELECT
blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
blocking_sql_text.text AS blocking_sql_text,
blocked_sql_text.text AS blocked_sql_text,
waits.wait_type AS blocking_resource,
blocked_query.command AS blocked_command,
blocking_query.command AS blocking_command,
blocked_query.wait_type AS blocked_wait_type,
blocked_query.wait_time AS blocked_wait_time,
blocking_query.total_elapsed_time AS blocking_elapsed_time,
GETDATE()
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests blocking_query ON
blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) blocking_sql_text
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
) blocked_sql_text
JOIN sys.dm_os_waiting_tasks waits ON
waits.session_id = blocking_query.session_id

--查看死锁
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0