为防止正式环境数据库被篡改，但又要保证可以正常查询处理问题，可以将账号的数据库访问权限设置为db_datareader
数据库权限：单独设置的优先，如给了一账号只读权限，若别开一张表给于此账号写权限，那么单独设置的写权限优先级最高。
注：db_datareader没有存储过程访问权限，若想全开，如下：

固定数据库角色
db_owner 数据库所有者

db_accessadmin 数据库访问管理员

db_securityadmin 数据库安全管理员

db_ddladmin 数据库 DDL 管理员

db_backupoperator 数据库备份操作员

db_datareader 数据库数据读取者

db_datawriter 数据库数据写入者

db_denydatareader 数据库拒绝数据读取者

db_denydatawriter 数据库拒绝数据写入者

固定服务器角色 描述
sysadmin 在 SQL Server 中进行任何活动。该角色的权限跨越所有其它固定服务器角色。

serveradmin 配置服务器范围的设置，关闭服务器。

setupadmin 添加和删除链接服务器，并执行某些系统存储过程（如 sp_serveroption）。

securityadmin 管理服务器登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码。。

processadmin 管理在 SQL Server 实例中运行的进程。

dbcreator 创建、改变和除去数据库。

diskadmin 管理磁盘文件。

bulkadmin 执行 BULK INSERT 语句。

固定数据库角色的权限

1，数据文件等于CPU的数量数，不过要小于等于8
   Tempdb文件数量为cpu数目一半,不过大小要相等，否则自增长可能会发生在最大的文件上,（参考微软数据库支持组的：Tempdb怎么会成为性能瓶颈）
   文件大小可设置大一些如1G，自增长设置为1G，不要设置成百分比，数据会对不齐
   log文件不用，因为是顺序写，写满一个文件再写再一个
2，更改默认端口
3，如果条件允许(有3或3个以上的物理盘) ,日志和数据文件应该位于不同的磁盘
4，禁用SA
5，建立定时运行索引维护，以降低索引碎片，降低表空间。【索引维护1-索引碎片整理】

禁用sa用户.
create login [admin_sql] from windows;
go
sp_addsrvrolemember [admin_sql],sysadmin
go
ALTER LOGIN [sa] DISABLE
GO

根据cpu数目,更改tempdb数据文件,日志文件。
USE master
DECLARE @i int,@s varchar(4000),@c int
set @i=1
select @c=cpu_count from sys.dm_os_sys_info --给变量赋值,获取cpu数目
while (@i<@c/2 and @i<8)
begin
--获取创建tempdb文件的脚本
set 
@s=
'alter database tempdb add file (name=tempdev'
+convert(varchar,@i)
+',filename=''d:\SQLData\tempdev'   --文件存放路径命名规则:如果是2块盘,次要数据文件放在d盘,其余不变.
+convert(varchar,@i)
+'.ndf'',SIZE = 1024000KB , FILEGROWTH = 512000KB )'
--执行脚本
exec(@s)
set @i=@i+1
end

--修改日志文件 ，数据文件的初始配置
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 102400KB)
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 1024000KB , FILEGROWTH = 102400KB)
GO

参考：SQL Server中的高可用性（2）—-文件与文件组

Method 1:

EXEC MASTER..xp_fixeddrives

GO

Method 2:

SELECT DISTINCT DB_NAME(dovs.database_id) DBName,

mf.physical_name PhysicalFileLocation,

dovs.logical_volume_name AS LogicalName,

dovs.volume_mount_point AS Drive,

CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

ORDER BY FreeSpaceInMB ASC

GO

引自：SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

DECLARE @s VARCHAR(50)
SET @s = '42:1:550'


SELECT PARSENAME(replace(@s,':','.'),3),PARSENAME(replace(@s,':','.'),2),PARSENAME(replace(@s,':','.'),1)
//PARSENAME:返回指定位置的对象，对象以.分开

加密
ALTER  PROCEDURE [dbo].[MyProce]
(
@TranType VARCHAR(50)
)
with encryption
AS
IF @TranType='select'
BEGIN 
SELECT '111'
END 
RETURN

解密

--exec dbo.sp_windbi$decrypt 'MyProce',0 

Create PROCEDURE [dbo].[sp_windbi$decrypt]  
(@procedure sysname = NULL, @revfl int = 1)  
AS  
/**//*  
王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com  
调用形式为：  
exec dbo.sp__windbi$decrypt @procedure,0  
如果第二个参数使用1的话，会给出该存储过程的一些提示。  
--版本4.0  修正存储过程过长解密出来是空白的问题  
*/  
SET NOCOUNT ON  
IF @revfl = 1  
BEGIN  
PRINT '警告：该存储过程会删除并重建原始的存储过程。'  
PRINT ' 在运行该存储过程之前确保你的数据库有一个备份。'  
PRINT ' 该存储过程通常应该运行在产品环境的一个备份的非产品环境下。'  
PRINT ' 为了运行这个存储过程，将参数@refl的值更改为0。'  
RETURN 0  
END  
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int  
select @maxColID = max(subobjid) FROM  
sys.sysobjvalues WHERE objid = object_id(@procedure)  
--select @maxColID as 'Rows in sys.sysobjvalues'  
select @procNameLength = datalength(@procedure) + 29  
DECLARE @real_01 nvarchar(max)  
DECLARE @fake_01 nvarchar(max)  
DECLARE @fake_encrypt_01 nvarchar(max)  
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)  
declare @objtype varchar(2),@ParentName nvarchar(max)  
select @real_decrypt_01a = ''  
--提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称  
select @objtype=type,@parentname=object_name(parent_object_id)  
from sys.objects where [object_id]=object_id(@procedure)  
-- 从sys.sysobjvalues里提出加密的imageval记录  
SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =  
object_id(@procedure) and valclass = 1 order by subobjid)  
  
--创建一个临时表  
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,  
[real_decrypt] NVARCHAR(MAX) )  
--开始一个事务，稍后回滚  
BEGIN TRAN  
--更改原始的存储过程，用短横线替换  
if @objtype='P'  
  SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS select 1  
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'  
else if @objtype='FN'  
  SET @fake_01='ALTER FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1  
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END'  
else if @objtype='V'  
  SET @fake_01='ALTER view '+ @procedure +' WITH ENCRYPTION AS select 1 as col  
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'  
else if @objtype='TR'  
  SET @fake_01='ALTER trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)  
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'  
EXECUTE (@fake_01)  
--从sys.sysobjvalues里提出加密的假的  
SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =  
object_id(@procedure) and valclass = 1 order by subobjid )  
if @objtype='P'  
  SET @fake_01='Create PROCEDURE '+ @procedure +' WITH ENCRYPTION AS select 1  
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'  
else if @objtype='FN'  
  SET @fake_01='CREATE FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1  
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END'  
else if @objtype='V'  
  SET @fake_01='Create view '+ @procedure +' WITH ENCRYPTION AS select 1 as col  
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'  
else if @objtype='TR'  
  SET @fake_01='Create trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)  
  /**//*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'  
--开始计数  
SET @intProcSpace=1  
--使用字符填充临时变量  
SET @real_decrypt_01 = replicate(cast('A'as nvarchar(max)), (datalength(@real_01) /2 ))  
--循环设置每一个变量，创建真正的变量  
--每次一个字节  
SET @intProcSpace=1  
--如有必要，遍历每个@real_xx变量并解密  
WHILE @intProcSpace<=(datalength(@real_01)/2)  
BEGIN  
--真的和假的和加密的假的进行异或处理  
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,  
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^  
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^  
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))  
SET @intProcSpace=@intProcSpace+1  
END  
  
  
--通过sp_helptext逻辑向表#output里插入变量  
insert #output (real_decrypt) select @real_decrypt_01  
--select real_decrypt AS '#output chek' from #output --测试  
-- -------------------------------------  
--开始从sp_helptext提取  
-- -------------------------------------  
declare @dbname sysname  
,@BlankSpaceAdded int  
,@BasePos int  
,@CurrentPos int  
,@TextLength int  
,@LineId int  
,@AddOnLen int  
,@LFCR int --回车换行的长度  
,@DefinedLength int  
,@SyscomText nvarchar(max)  
,@Line nvarchar(255)  
Select @DefinedLength = 255  
SELECT @BlankSpaceAdded = 0 --跟踪行结束的空格。注意Len函数忽略了多余的空格  
CREATE TABLE #CommentText  
(LineId int  
,Text nvarchar(255) collate database_default)  
--使用#output代替sys.sysobjvalues  
DECLARE ms_crs_syscom CURSOR LOCAL  
FOR SELECT real_decrypt from #output  
ORDER BY ident  
FOR READ ONLY  
--获取文本  
SELECT @LFCR = 2  
SELECT @LineId = 1  
OPEN ms_crs_syscom  
FETCH NEXT FROM ms_crs_syscom into @SyscomText  
WHILE @@fetch_status >= 0  
BEGIN  
SELECT @BasePos = 1  
SELECT @CurrentPos = 1  
SELECT @TextLength = LEN(@SyscomText)  
WHILE @CurrentPos != 0  
BEGIN  
--通过回车查找行的结束  
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,  
@BasePos)  
--如果找到回车  
IF @CurrentPos != 0  
BEGIN  
--如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续  
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +  
@CurrentPos-@BasePos + @LFCR) > @DefinedLength  
BEGIN  
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +  
@BlankSpaceAdded)  
INSERT #CommentText VALUES  
( @LineId,  
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,  
@BasePos, @AddOnLen), N''))  
SELECT @Line = NULL, @LineId = @LineId + 1,  
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0  
END  
SELECT @Line = isnull(@Line, N'') +  
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')  
SELECT @BasePos = @CurrentPos+2  
INSERT #CommentText VALUES( @LineId, @Line )  
SELECT @LineId = @LineId + 1  
SELECT @Line = NULL  
END  
ELSE  
--如果回车没找到  
BEGIN  
IF @BasePos <= @TextLength  
BEGIN  
--如果@Lines长度的新值大于定义的长度  
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +  
@TextLength-@BasePos+1 ) > @DefinedLength  
BEGIN  
SELECT @AddOnLen = @DefinedLength -  
(isnull(LEN(@Line),0) + @BlankSpaceAdded)  
INSERT #CommentText VALUES  
( @LineId,  
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,  
@BasePos, @AddOnLen), N''))  
SELECT @Line = NULL, @LineId = @LineId + 1,  
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =  
0  
END  
SELECT @Line = isnull(@Line, N'') +  
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')  
if LEN(@Line) < @DefinedLength and charindex(' ',  
@SyscomText, @TextLength+1 ) > 0  
BEGIN  
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1  
END  
END  
END  
END  
FETCH NEXT FROM ms_crs_syscom into @SyscomText  
END  
IF @Line is NOT NULL  
INSERT #CommentText VALUES( @LineId, @Line )  
select Text from #CommentText order by LineId  
CLOSE ms_crs_syscom  
DEALLOCATE ms_crs_syscom  
DROP TABLE #CommentText  
-- -------------------------------------  
--结束从sp_helptext提取  
-- -------------------------------------  
--删除用短横线创建的存储过程并重建原始的存储过程  
ROLLBACK TRAN  
DROP TABLE #output  
go  

一、merge
1，利用merge不用再写update…insert,可以直接合并。
2，可以将更改的结果与更改前的结果输出
3，在sqlserver2008后引用。
4，源表中不能有重复的记录

参考：
MERGE (Transact-SQL)
SQL Server中的Merge关键字

二、output,在插入，更新，删除时记录

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

OUTPUT 子句 (Transact-SQL)

Service Broker：多服务器单信息传递，大小写敏感
组成：
Message:信内容，为便于区分保证唯一性，信息的前缀用域名好一些。
Contracts：信头，组合message及message由哪边发送，发起者或者接收者
Queue：信封,收到的信息存储的位置

建立方式：
1，定义 SERVICE：将上面三者组合
2，信息发送：
BEGIN TRY
 BEGIN TRANSACTION;
  DECLARE @ch UNIQUEIDENTIFIER
  DECLARE @msg NVARCHAR(MAX);

  BEGIN DIALOG CONVERSATION @ch
   FROM SERVICE [InitiatorService]
   TO SERVICE 'TargetService'
   ON CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
   WITH ENCRYPTION = OFF;

  SET @msg = 
   '<HelloWorldRequest>
     Klaus Aschenbrenner
   </HelloWorldRequest>';

  SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage] (@msg);
 COMMIT
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH
GO
3，接收信息
--***********************************************************************************
--*  Retrieve the sent message from the queue "TargetQueue" with a WAITFOR statement
--***********************************************************************************
DECLARE @cg UNIQUEIDENTIFIER
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML;

BEGIN TRY
 BEGIN TRANSACTION;

  WAITFOR (
   RECEIVE TOP (1)
    @cg = conversation_group_id,
    @ch = conversation_handle,
    @messagetypename = message_type_name,
    @messagebody = CAST(message_body AS XML)
   FROM TargetQueue
  ), TIMEOUT 60000

  IF (@@ROWCOUNT > 0)
  BEGIN
   PRINT 'Conversation group: ' + CAST(@cg AS NVARCHAR(MAX))
   PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
   PRINT 'Message type: ' + @messagetypename
   PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))
  END

 COMMIT
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH
GO
4，监控对列
CREATE EVENT NOTIFICATION PoisonMessageNotification ON QUEUE TargetQueue
FOR Broker_Queue_Disabled
TO SERVICE 'PoisonMessageNotifyService', 'current database'
GO


---------------------------明细
USE master;

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Chapter3_HelloWorldSvc')
BEGIN
	PRINT 'Dropping database ''Chapter3_HelloWorldSvc''';
	DROP DATABASE Chapter3_HelloWorldSvc;
END
GO

CREATE DATABASE Chapter3_HelloWorldSvc
GO

USE Chapter3_HelloWorldSvc
GO

--*********************************************
--*  Create the message type "RequestMessage"
--*********************************************
CREATE MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
VALIDATION = NONE
GO

--*********************************************
--*  Create the message type "ResponseMessage"
--*********************************************
CREATE MESSAGE TYPE
[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
VALIDATION = NONE
GO

--*********************************************
--*  Show the created message types
--*********************************************
SELECT * FROM sys.service_message_types 
GO

--************************************************
--*  Changing the validation of the message types
--************************************************
ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage]
VALIDATION = WELL_FORMED_XML
GO

ALTER MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage]
VALIDATION = WELL_FORMED_XML
GO

--************************************************
--*  Create the contract "HelloWorldContract"
--************************************************
CREATE CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
(
	[http://ssb.csharp.at/SSB_Book/c03/RequestMessage] SENT BY INITIATOR,
	[http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] SENT BY TARGET
)
GO

--*************************************************************
--*  Getting some information about the newly created contract
--*************************************************************
SELECT 
	sc.name AS 'Contract', 
	mt.name AS 'Message type', 
	cm.is_sent_by_initiator,
	cm.is_sent_by_target,
	mt.validation
FROM sys.service_contract_message_usages cm 
	INNER JOIN sys.service_message_types mt ON cm.message_type_id = mt.message_type_id
	INNER JOIN sys.service_contracts sc ON sc.service_contract_id = cm.service_contract_id
GO

--********************************************************
--*  Create the queues "InitiatorQueue" and "TargetQueue"
--********************************************************
CREATE QUEUE InitiatorQueue
WITH STATUS = ON
GO

CREATE QUEUE TargetQueue
WITH STATUS = ON
GO

--*************************************************************
--*  Getting some information about the newly created queues
--*************************************************************
SELECT * FROM sys.service_queues
GO

--************************************************************
--*  Create the queues "InitiatorService" and "TargetService"
--************************************************************
CREATE SERVICE InitiatorService
ON QUEUE InitiatorQueue 
(
	[http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
)
GO

CREATE SERVICE TargetService
ON QUEUE TargetQueue
(
	[http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
)
GO

--*************************************************************
--*  Getting some information about the newly created services
--*************************************************************
SELECT
	sv.name AS 'Service',
	sc.name AS 'Contract'
FROM sys.services sv
	INNER JOIN sys.service_contract_usages scu ON scu.service_id = sv.service_id
	INNER JOIN sys.service_contracts sc ON sc.service_contract_id = scu.service_contract_id
GO

--********************************************************************
--*  Sending a message from the InitiatorService to the TargetService
--********************************************************************
BEGIN TRY
	BEGIN TRANSACTION;
		DECLARE @ch UNIQUEIDENTIFIER
		DECLARE @msg NVARCHAR(MAX);

		BEGIN DIALOG CONVERSATION @ch
			FROM SERVICE [InitiatorService]
			TO SERVICE 'TargetService'
			ON CONTRACT [http://ssb.csharp.at/SSB_Book/c03/HelloWorldContract]
			WITH ENCRYPTION = OFF;

		SET @msg = 
			'<HelloWorldRequest>
					Klaus Aschenbrenner
			</HelloWorldRequest>';

		SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/RequestMessage] (@msg);
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--********************************************************************
--*  View the sent message on the queue "TargetQueue"
--********************************************************************
SELECT * FROM TargetQueue
GO

--********************************************************************
--*  View the created conversation endpoints
--********************************************************************
SELECT * FROM sys.conversation_endpoints
GO

--********************************************************************
--*  Retrieve the sent message from the queue "TargetQueue"
--********************************************************************
DECLARE @cg UNIQUEIDENTIFIER
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML;

BEGIN TRY
	BEGIN TRANSACTION;

		RECEIVE TOP(1)
			@cg = conversation_group_id,
			@ch = conversation_handle,
			@messagetypename = message_type_name,
			@messagebody = CAST(message_body AS XML)
		FROM TargetQueue

		PRINT 'Conversation group: ' + CAST(@cg AS NVARCHAR(MAX))
		PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
		PRINT 'Message type: ' + @messagetypename
		PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))

	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--***********************************************************************************
--*  Retrieve the sent message from the queue "TargetQueue" with a WAITFOR statement
--***********************************************************************************
DECLARE @cg UNIQUEIDENTIFIER
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML;

BEGIN TRY
	BEGIN TRANSACTION;

		WAITFOR (
			RECEIVE TOP (1)
				@cg = conversation_group_id,
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM TargetQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT > 0)
		BEGIN
			PRINT 'Conversation group: ' + CAST(@cg AS NVARCHAR(MAX))
			PRINT 'Conversation handle: ' + CAST(@ch AS NVARCHAR(MAX))
			PRINT 'Message type: ' + @messagetypename
			PRINT 'Message body: ' + CAST(@messagebody AS NVARCHAR(MAX))
		END

	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--**************************************************
--*  Create a table to store the processed messages
--**************************************************
CREATE TABLE ProcessedMessages
(
	ID UNIQUEIDENTIFIER NOT NULL,
	MessageBody XML NOT NULL,
	ServiceName NVARCHAR(MAX) NOT NULL
)
GO

--*******************************************************************
--*  Send a response message back to the service "InitiatorService"
--*******************************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML
DECLARE @responsemessage XML;

BEGIN TRY
	BEGIN TRANSACTION
		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM TargetQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT > 0)
		BEGIN
			IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
			BEGIN
				-- Store the received request message in a table
				INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'TargetService')

				-- Construct the response message
				SET @responsemessage = '<HelloWorldResponse>' + @messagebody.value('/HelloWorldRequest[1]', 'NVARCHAR(MAX)') + '</HelloWorldResponse>';

				-- Send the response message back to the initiating service
				SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] (@responsemessage);

				-- End the conversation on the target's side
				END CONVERSATION @ch;
			END
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO


--********************************************************************
--*  View the sent message on the queue "InitiatorQueue"
--********************************************************************
SELECT * FROM InitiatorQueue
GO

--********************************************************************
--*  View the processed message in the table "ProcessedMessages"
--********************************************************************
SELECT * FROM ProcessedMessages
GO

--*******************************************************************
--*  Service program for the service "InitiatorService"
--*******************************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML;

BEGIN TRY
	BEGIN TRANSACTION
		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM InitiatorQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT > 0)
		BEGIN
			IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
			BEGIN
				-- Store the received response) message in a table
				INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'InitiatorService')
			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
			BEGIN
				-- End the conversation on the initiator's side
				END CONVERSATION @ch;
			END
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--***********************************************************************************
--*  Endless receive loop for processing incoming messages on the initiator's queue
--***********************************************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML;

WHILE (1=1)
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION

		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM InitiatorQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT = 0)
		BEGIN
			ROLLBACK TRANSACTION
			BREAK
		END

		IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
		BEGIN
			-- Store the received response) message in a table
			INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'InitiatorService')
		END

		IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
		BEGIN
			-- End the conversation on the initiator's side
			END CONVERSATION @ch;
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO

--********************************************************************************
--*  Endless receive loop for processing incoming messages on the target's queue
--********************************************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML
DECLARE @responsemessage XML;

WHILE (1=1)
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION

		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM TargetQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT = 0)
		BEGIN
			ROLLBACK TRANSACTION
			BREAK
		END

		IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
		BEGIN
			-- Store the received request message in a table
			INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'TargetService')

			-- Construct the response message
			SET @responsemessage = '<HelloWorldResponse>' + @messagebody.value('/HelloWorldRequest[1]', 'nvarchar(max)') + '</HelloWorldResponse>';

			-- Send the response message back to the initiating service
			SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] (@responsemessage);

			-- End the conversation on the target's side
			END CONVERSATION @ch;
		END

		IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
		BEGIN
			-- End the conversation
			END CONVERSATION @ch;
		END

		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH
END
GO

--**********************************************************
--*  Error handling with a savepoint on the target's queue
--**********************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML
DECLARE @responsemessage XML;

WHILE (1=1)
BEGIN
	BEGIN TRANSACTION

	WAITFOR (
		RECEIVE TOP (1)
			@ch = conversation_handle,
			@messagetypename = message_type_name,
			@messagebody = CAST(message_body AS XML)
		FROM TargetQueue
	), TIMEOUT 60000

	IF (@@ROWCOUNT = 0)
	BEGIN
		ROLLBACK TRANSACTION
		BREAK
	END

	SAVE TRANSACTION MessageReceivedSavepoint

	IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
	BEGIN
		BEGIN TRY
			-- Store the received request message in a table
			INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'TargetService')

			-- Construct the response message
			SET @responsemessage = '<HelloWorldResponse>' + @messagebody.value('/HelloWorldRequest[1]', 'nvarchar(max)') + '</HelloWorldResponse>';

			-- Send the response message back to the initiating service
			SEND ON CONVERSATION @ch MESSAGE TYPE [http://ssb.csharp.at/SSB_Book/c03/ResponseMessage] (@responsemessage);

			-- End the conversation on the target's side
			END CONVERSATION @ch;
		END TRY
		BEGIN CATCH
			IF (ERROR_NUMBER() = 1205) 
			BEGIN
				-- A deadlock occurred. 
				-- We can try it again...
				ROLLBACK TRANSACTION
				CONTINUE
			END
			ELSE
			BEGIN
				-- A other error occurred.
				-- The message can't be processed successfully, because it's a poison message
				ROLLBACK TRANSACTION MessageReceivedSavepoint
				PRINT 'Error occured: ' + CAST(@messagebody AS NVARCHAR(MAX))
			END
		END CATCH
	END

	IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
	BEGIN
		-- End the conversation
		END CONVERSATION @ch;
	END

	COMMIT TRANSACTION
END
GO

--**************************************
--*  Poison messages in Service Broker
--**************************************
-- Reactivating the queue
ALTER QUEUE TargetQueue WITH STATUS = ON
GO

-- Service program for handling the poison message
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML

WHILE (1=1)
BEGIN
	BEGIN TRANSACTION

	WAITFOR (
		RECEIVE TOP (1)
			@ch = conversation_handle,
			@messagetypename = message_type_name,
			@messagebody = CAST(message_body AS XML)
		FROM TargetQueue
	), TIMEOUT 60000

	IF (@@ROWCOUNT = 0)
	BEGIN
		ROLLBACK TRANSACTION
		BREAK
	END

	-- Rollling back the current transaction
	PRINT 'Rollback the current transaction - simulating a poison message...'
	ROLLBACK TRANSACTION
END
GO

--**********************************************************
--*  Setting up the event notification for poison messages
--**********************************************************
-- Create the queue which stores the event notification messages
CREATE QUEUE PoisonMessageNotifyQueue
GO

-- Create the service that accepts the event notification messages
CREATE SERVICE PoisonMessageNotifyService ON QUEUE PoisonMessageNotifyQueue
(
	[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO

-- Create the event notification itself
CREATE EVENT NOTIFICATION PoisonMessageNotification ON QUEUE TargetQueue
FOR Broker_Queue_Disabled
TO SERVICE 'PoisonMessageNotifyService', 'current database'
GO

-- Select the received event notification message
SELECT * FROM PoisonMessageNotifyQueue
GO

--**********************************************************
--*  End an conversation with an error
--**********************************************************
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML

BEGIN TRY
	BEGIN TRANSACTION
		WAITFOR (
			RECEIVE TOP (1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM TargetQueue
		), TIMEOUT 60000

		IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/RequestMessage')
		BEGIN
			-- End the conversation with an error
			END CONVERSATION @ch WITH ERROR = 4242 DESCRIPTION = 'My custom error message'
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

-- Table that stores error information
CREATE TABLE ErrorLog
(
	ID UNIQUEIDENTIFIER NOT NULL,
	ErrorCode INT NOT NULL,
	ErrorMessage NVARCHAR(MAX) NOT NULL
)
GO

-- Service program for the InitiatorService, that handles also error message types
DECLARE @ch UNIQUEIDENTIFIER 
DECLARE @messagetypename NVARCHAR(256)
DECLARE	@messagebody XML
DECLARE @errorcode INT
DECLARE @errormessage NVARCHAR(3000);

BEGIN TRY
	BEGIN TRANSACTION
		WAITFOR (
			RECEIVE TOP(1)
				@ch = conversation_handle,
				@messagetypename = message_type_name,
				@messagebody = CAST(message_body AS XML)
			FROM InitiatorQueue
		), TIMEOUT 60000

		IF (@@ROWCOUNT > 0)
		BEGIN
			IF (@messagetypename = 'http://ssb.csharp.at/SSB_Book/c03/ResponseMessage')
			BEGIN
				-- Store the received response) message in a table
				INSERT INTO ProcessedMessages (ID, MessageBody, ServiceName) VALUES (NEWID(), @messagebody, 'InitiatorService')
			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
			BEGIN
				-- End the conversation on the initiator's side
				END CONVERSATION @ch;
			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
			BEGIN
				-- Extract the error information from the sent message
				SET @errorcode = (SELECT @messagebody.value(
					N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"; 
					(/brokerns:Error/brokerns:Code)[1]', 'int'));
				SET @errormessage = (SELECT @messagebody.value(
					'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
					(/brokerns:Error/brokerns:Description)[1]', 'nvarchar(3000)'));

				-- Log the error
				INSERT INTO ErrorLog(ID, ErrorCode, ErrorMessage)
				VALUES (NEWID(), @errorcode, @errormessage)

				-- End the conversation on the initiator's side
				END CONVERSATION @ch;
			END
		END
	COMMIT
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
END CATCH
GO

--find SQL login with blank passwords
select name,type_desc,create_date from sys.sql_logins where pwdcompare('', password_hash) = 1

--find SQL login with password same as name
select name,type_desc,create_date from sys.sql_logins where pwdcompare(name, password_hash) = 1

--比对sa的密码是否为123abc
SELECT TOP 50 * 
FROM sys.sql_logins sl
WHERE name = 'sa' AND pwdcompare('123abc', password_hash) = 1

with(nolock)并意味着没有锁，实际上在查询一张表时，还是有锁，会对对象增加架构锁，
防止表会修改，会对数据库增加共享锁。若使用drop index,则要等到架构锁释放。

sql server2005提供了快照隔离和读取已提交快照这两种新的不加锁、无阻塞的事务隔离级别，可使用
快照：每次从数据进行修改时，会在teampdb上存储上一版本
好处：
select不要求锁，会大大降低整个库的锁负载量
nolock会读取到未提交事务时修改的数据，而读快照读取的是修改之前的数据，故nolock易读取到脏数据
读快照与nolock相同的地方在于都不加共享锁，但都会加对象架构锁与数据库的共享锁，区别在于，nolock需要
在每个sql语句后加，而读快照不用，并用读快照不会读到未提交事务的数据。
行版本控制：在任何一个修改之前，先对修改前的版本做一个复制 ，后续的一切读操作都会去读这个复制的版本，修改将创建一个新的版本。在这种处理方式下，读、写操作不会相互阻塞。使用这种行版本控制机制的好处，是程序的并发性比较高，但是缺点是用户读到的虽然不是一个脏数据，但是可能是个正在被修改马上就要过期的数据值
【注：加上行版本控制后，会最大限度降低死锁，但不是没有死锁】

相关存储过程：select * from sys.dm_tran_version_store


把SQL Server数据库事务隔离级别设置为已提交读快照隔离：
如果直接运行下面的语句：
ALTER Database [mydbname] SET READ_COMMITTED_SNAPSHOT ON
会可能被阻塞很长时间。
你可以选择运行下面的语句：
if(charindex('Microsoft SQL Server 2005',@@version) > 0)
begin
declare @sql varchar(8000)
select @sql = '
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'
Exec(@sql)
end
通过查询 sys.databases的 is_read_committed_snapshot_on字段
select is_read_committed_snapshot_on from sys.databases where name = DB_Name()
is_read_committed_snapshot_on如果为1表示目前为已提交读快照隔离级别

引用：
7 Things Developers Should Know About SQL Server
Using Read-Committed Snapshot Isolation - Mini-Lab
SQL Server已提交读快照隔离级别的设置
SQL Server 2005使用基于行版本控制的隔离级别初探

在sqlserver服务器有一个名为SQL Server Browser的服务，一直没用到，最近一台服务器安装了多个实例，其中一个实例在外部怎么都访问不了，这时查资料才明白需要开启SQL Server Browser服务。
如果一个物理服务器上面有多个SQL Server实例，那么为了确保客户端能访问到正确的实例，所以SQL Server 2005以后提供了一个新的Browser服务。
参考：SQL Server : Browser服务是干什么的

--只有mdf文件，没有ldf文件，可通过选项来恢复。FOR ATTACH_REBUILD_LOG;
--有日志文件则附加上，若无，则新建
CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'M:\Program Files\Microsoft SQL Server201201\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG;

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Repl_DropArticlePeer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Repl_DropArticlePeer]
GO

/*对等复制删除Article
 EXEC LS_DropArticlePeer 'testrepl'
*/
CREATE PROCEDURE Repl_DropArticlePeer
@article AS sysname
AS 
BEGIN 
	IF ISNULL(@article,'') = ''
	BEGIN
		RETURN
	END  

       DECLARE @publication AS sysname;  
	SET @publication =DB_NAME(); 
	--SET @article = N'testRepl'

	--删除对应订阅
	IF @@SERVERNAME = 'DataBaseC' --在哪台机器上运行，就不删除自己
	BEGIN
		--exec sp_dropsubscription @publication = @publication, @article = @article, @subscriber = 'databasec'
		exec sp_dropsubscription @publication =@publication, @article =@article, @subscriber = 'DataBaseD'
		exec sp_dropsubscription @publication = @publication, @article = @article, @subscriber = 'sqlcluster'
	END
	ELSE IF @@SERVERNAME = 'DataBaseD'
	BEGIN
		exec sp_dropsubscription @publication = @publication, @article = @article, @subscriber = 'databasec'
		--exec sp_dropsubscription @publication =@publication, @article =@article, @subscriber = 'DataBaseD'
		exec sp_dropsubscription @publication = @publication, @article = @article, @subscriber = 'sqlcluster'
	END
	ELSE IF @@SERVERNAME = 'SQLCLUSTER'
	BEGIN
		exec sp_dropsubscription @publication = @publication, @article = @article, @subscriber = 'databasec'
		exec sp_dropsubscription @publication =@publication, @article =@article, @subscriber = 'DataBaseD'
		--exec sp_dropsubscription @publication = @publication, @article = @article, @subscriber = 'sqlcluster'
	END


	EXEC sp_droparticle 
	  @publication = @publication, 
	  @article = @article,
	  @force_invalidate_snapshot = 1;
END 

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Repl_AddArticlePeer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Repl_AddArticlePeer]
GO

/*对等复制增加Article
 EXEC Repl_AddArticlePeer 'testrepl'
*/

CREATE PROCEDURE Repl_AddArticlePeer
@article AS sysname
AS 
BEGIN 
	IF ISNULL(@article,'') = ''
	BEGIN
		RETURN
	END  

	DECLARE @publication AS sysname;  
	SET @publication =DB_NAME(); 

	DECLARE
        @ins_cmd	nvarchar (255),
        @del_cmd	nvarchar (255),
        @upd_cmd	nvarchar (255) 

	SET @ins_cmd = 'CALL sp_MS' + 'ins' + '_dbo' + @article
	SET @del_cmd = 'CALL sp_MS' + 'del' + '_dbo' + @article
	SET @upd_cmd = 'CALL sp_MS' + 'upd' + '_dbo' + @article

	EXEC sp_addarticle @publication = @publication, @article = @article, @source_owner = N'dbo', 
	@source_object =@article, @type = N'logbased', @description = null,@creation_script = null,
	@pre_creation_cmd = N'drop', @schema_option = 0x0000000008035DDB, @force_invalidate_snapshot = 1, @identityrangemanagementoption = N'manual', 
	@destination_table = @article, @status = 16,@vertical_partition = N'false', @ins_cmd = @ins_cmd, @del_cmd = @del_cmd, @upd_cmd = @upd_cmd
END 

系统信息级别：http://blog.sqlauthority.com/2007/04/25/sql-server-error-messages-sysmessages-error-severity-level/

测试：
BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;

当sqlserver有用户级错误时记录
CREATE event session xEvents_Session on server
add event sqlserver.error_reported
(
action
(
sqlserver.session_id, -- SPID which raises the error
sqlserver.plan_handle, -- Plan handle which can be used to retrieve the graphical plan
sqlserver.tsql_stack, -- T-SQL stack for extended debugging purposes
package0.callstack, -- Callstack for extended debugging purposes
sqlserver.sql_text, -- T-SQL query which encountered the error
sqlserver.username, -- Name of the user that reported the error
sqlserver.client_app_name, -- Client application name
sqlserver.client_hostname, -- Host which initiated the query
sqlserver.database_name -- Database against which the query was being executed
) 
 WHERE severity >= 11 AND Severity <=16
)
add target package0.ring_buffer
with (max_dispatch_latency=1seconds)
GO
-- start the tracing
alter event session xEvents_Session on server state = start

--alter event session xEvents_Session on server state = stop
--drop event session xEvents_Session on SERVER



--查看

SELECT 
    n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    n.value('(event/@package)[1]', 'varchar(50)') AS package_name,
    DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],  
n.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS [database_name],
    n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
n.value('(event/data[@name="message"]/value)[1]', 'nvarchar(max)') AS [message],
n.value('(event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS [username],
n.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname],
n.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS [client_app_name],
n.value('(event/action[@name="session_id"]/value)[1]', 'nvarchar(max)') AS [session_id],
n.value('(event/action[@name="plan_handle"]/value)[1]', 'nvarchar(max)') AS [plan_handle],
n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@handle)[1]', 'varchar(max)') AS [tsql_stack],
n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetStart)[1]', 'int') AS [statement_start_offset],
n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetEnd)[1]', 'int') AS [statement_end_offset],
n.value('(event/action[@name="callstack"]/value)[1]', 'nvarchar(max)') AS [callstack],
n.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
    n.value('(event/data[@name="object_type"]/value)[1]', 'nvarchar(128)') AS [object_type],
    n.value('(event/data[@name="duration"]/value)[1]', 'int') AS [duration],
    n.value('(event/data[@name="cpu"]/value)[1]', 'int') AS [cpu],
    n.value('(event/data[@name="reads"]/value)[1]', 'int') AS [reads],
    n.value('(event/data[@name="writes"]/value)[1]', 'int') AS [writes] into #1120
FROM
(    SELECT td.query('.') as n
    FROM 
    (
        SELECT CAST(target_data AS XML) as target_data
        FROM sys.dm_xe_sessions AS s 
        JOIN sys.dm_xe_session_targets AS t 
            ON t.event_session_address = s.address
        WHERE s.name = 'xEvents_Session'
          --AND t.target_name = 'ring_buffer'
    ) AS sub
    CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as TAB

--查看有sql的
SELECT TIMESTAMP,database_name,[message],sql_text,username,client_hostname,client_app_name
FROM #1120 a
WHERE a.sql_text != ''


--查看应用执行的
SELECT TIMESTAMP,database_name,[message],
SUBSTRING(qt.text,a.statement_start_offset/2+1,
(case when a.statement_end_offset = -1
then DATALENGTH(qt.text) 
else a.statement_end_offset end -a.statement_start_offset)/2 + 1) sql_text,qt.text alltext,
username,client_hostname,client_app_name
FROM #1120 a
CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(max),a.tsql_stack,1) )  qt
WHERE a.sql_text IS NULL AND tsql_stack != '' AND client_app_name = '.Net SqlClient Data Provider'
ORDER BY TIMESTAMP DESC

--以下xevent记录了通过sqlserver查询工具直接更新，删除表数据时的记录。
IF NOT EXISTS( SELECT 1 FROM sys.dm_xe_sessions dxs(NOLOCK) WHERE name = 'EventErrorMonitor' )
BEGIN 
	CREATE EVENT session EventErrorMonitor on server
	ADD EVENT sqlserver.error_reported
	(
	ACTION
	(
	sqlserver.session_id, -- SPID which raises the error
	sqlserver.plan_handle, -- Plan handle which can be used to retrieve the graphical plan
	sqlserver.tsql_stack, -- T-SQL stack for extended debugging purposes
	package0.callstack, -- Callstack for extended debugging purposes
	sqlserver.sql_text, -- T-SQL query which encountered the error
	sqlserver.username, -- Name of the user that reported the error
	sqlserver.client_app_name, -- Client application name
	sqlserver.client_hostname, -- Host which initiated the query
	sqlserver.database_name -- Database against which the query was being executed
	) 
	 WHERE severity >= 11 AND Severity <=16
	)
	ADD TARGET package0.ring_buffer
	WITH (max_dispatch_latency=1seconds)

	ALTER EVENT SESSION EventErrorMonitor on server state = START
  
END 
ELSE
BEGIN

	SELECT 
		 DATEADD(hh, 
				DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
				n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],  
		n.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS [database_name],
		n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
		n.value('(event/data[@name="message"]/value)[1]', 'nvarchar(max)') AS [message],
		n.value('(event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS [username],
		n.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname],
		n.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS [client_app_name],
		n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@handle)[1]', 'varchar(max)') AS [tsql_stack],
		n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetStart)[1]', 'int') AS [statement_start_offset],
		n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetEnd)[1]', 'int') AS [statement_end_offset]
	into #ErrorMonitor
	FROM
	(    SELECT td.query('.') as n
		FROM 
		(
			SELECT CAST(target_data AS XML) as target_data
			FROM sys.dm_xe_sessions AS s 
			JOIN sys.dm_xe_session_targets AS t 
				ON t.event_session_address = s.address
			WHERE s.name = 'EventErrorMonitor'
			  --AND t.target_name = 'ring_buffer'
		) AS sub
		CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
	) as TAB
  
	INSERT INTO PerfLogicMessage(StartTime,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)
	SELECT TIMESTAMP,database_name,[message],sql_text,'',username,client_hostname,client_app_name
	FROM #ErrorMonitor a
	WHERE a.sql_text != '' AND client_app_name !='Microsoft SQL Server Management Studio - 查询'

	INSERT INTO PerfLogicMessage(StartTime,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)
	--查看应用执行的
	SELECT  TIMESTAMP,database_name,[message],
	SUBSTRING(qt.text,a.statement_start_offset/2+1,
				(case when a.statement_end_offset = -1
				then DATALENGTH(qt.text) 
				else a.statement_end_offset end -a.statement_start_offset)/2 + 1) sql_text,qt.text alltext,
	username,client_hostname,client_app_name
	FROM #ErrorMonitor a
	CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(max),a.tsql_stack,1) )  qt
	WHERE a.sql_text IS NULL AND tsql_stack != '' AND client_app_name = '.Net SqlClient Data Provider'

	DROP TABLE #ErrorMonitor

	--重启以清空
	ALTER EVENT SESSION EventErrorMonitor ON SERVER STATE = STOP
	ALTER EVENT SESSION EventErrorMonitor on server state = START
END 

--varchar -> varbinary
declare @hexstring varchar(64),@binstring varbinary(64)
set @hexstring = '0x01000200A09EFC30B04A69DE02000000000000000000000000000000000000';
SET @binstring = CONVERT(varbinary(64), @hexstring, 1);
SELECT @binstring
go

--varbinary -> varchar
declare @hexstring varchar(64),@binstring varbinary(64)
set @binstring = 0x01000200A09EFC30B04A69DE02000000000000000000000000000000000000
SET @hexstring = master.dbo.fn_varbintohexstr(@binstring)
SELECT @hexstring