SELECT CONVERT(VARCHAR(50),CONNECTIONPROPERTY('local_net_address'))host ,client_net_address,program_name,db.NAME,convert(varchar(max),t.[text]) SQLText,CONVERT(varchar(20),login_time,120) login_time,

FROM master.sys.dm_exec_sessions AS s (nolock)
INNER JOIN master.sys.dm_exec_connections AS c (nolock) ON s.session_id=c.session_id
INNER JOIN master.sys.databases db ON s.database_id = db.database_id
OUTER APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
LEFT JOIN dbo.mdSystemIP msi ON client_net_address = msi.IP
WHERE
NOT EXISTS( –非限定IP访问
SELECT 1 FROM dbo.mdSystemIP ms WHERE client_net_address = ms.IP
)
OR

( –服务器访问但是非合法客户端，包含用ssms直联也是不合法
EXISTS(SELECT 1 FROM dbo.mdSystemIP ms WHERE client_net_address = ms.IP AND VisitType='Server')
AND program_name NOT LIKE '.Net SqlClient Data Provider%' –程序连接
AND program_name NOT LIKE 'SqlQuery%' –缓存，数据库依赖
AND program_name NOT LIKE 'SQLAgent%' –job
AND program_name NOT LIKE 'Repl%' –复制分发
AND program_name NOT LIKE 'DATABASE%' –复制分发
AND program_name NOT LIKE 'SQLCLUSTER%' –复制分发
AND program_name != 'Microsoft SQL Server' –自身执行
AND program_name != 'Microsoft® Windows® Operating System'
AND login_name != 'NT AUTHORITY\SYSTEM'
AND db.NAME NOT IN('master','msdb','distribution')
)

sqlserver在新增注册服务器时，发现框里空白，没有数据库引擎的选项。引起的原因是将其他服务器的注册配置文件导入过来引起，处理方法：
删除：C:\Users\Administrator\AppData\Roaming\Microsoft\Microsoft SQL Server\110\Tools\Shell\RegSrvr.xml
重启ssms

sqlserver2008开始提供压缩备份，好处是备份与还原时间缩短，备份文件也很小，测试结果显示文件小了2/3,不过不好的地方在于CPU要大量计算，CPU要占不少资源，不过若放在晚上执行的话，cpu可以忽略。

备份压缩 (SQL Server)

SQL Server 2008提供了内建的方法“”变更数据捕获“”（Change Data Capture 即CDC）以实现异步跟踪用户表的数据修改，而且这一功能拥有最小的性能开销。可以用于其他数据源的持续更新，例如将OLTP数据库中的数据变更迁移到数据仓库数据库。

--开启cdc
EXEC sys.sp_cdc_enable_db

--net_changes说的是针对一行记录，如果有多个更改的话，那么以最后的一条为准。
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='frmuser',@capture_instance='frmuser',@supports_net_changes=0,@role_name=null 

--确认表已经被跟踪  
  
SELECT is_tracked_by_cdc FROM sys.tables  
WHERE name = 'frmuser' and schema_id = SCHEMA_ID('dbo')  

EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'frmuser' 

--更改表数据查询效果
UPDATE a
SET a.isvalid  = 0
FROM frmuser a
WHERE Account ='sa'


SELECT TOP 50 * 
FROM [cdc].[frmuser_CT]

DECLARE @FromLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'smallest greater than or equal' , '2013-09-27 08:30')  
  
DECLARE @ToLSN varbinary(10) =  
sys.fn_cdc_map_time_to_lsn  
( 'largest less than or equal' , '2013-09-27 23:59:59')  
  
--注意若在上面的日期范围内无数据的话，将提示参数不足
SELECT  
CASE __$operation  
WHEN 1 THEN 'DELETE'  
WHEN 2 THEN 'INSERT'  
WHEN 3 THEN 'Before UPDATE'  
WHEN 4 THEN 'After UPDATE'  
END   ,a.*
FROM [cdc].[fn_cdc_get_all_changes_frmuser]  
(@FromLSN, @ToLSN, 'all')   a


--撤销CDC
EXEC sys.sp_cdc_disable_table 'dbo', 
'FactInternetSales','All'

EXEC sys.sp_cdc_disable_db

更改是不会永远保存，会定期清除

USE MASTER
GO
GRANT VIEW ANY DATABASE TO PUBLIC; -- turn this back on if it was off
GO
DENY VIEW ANY DATABASE TO USER_A;
GO

How to hide SQL Server user databases in SQL Server Management Studio

以SP_开头的存储过程不是会先去找系统存储过程。 是先去master数据库中找这个存储过程。。。之后再去用户数据库中找这个存储过程,所以假如自己写了一个对index操作的SP。 直接放在master数据库中，以SP_开头。 这样在Job的定义中。 选择到用户数据库，然后执行这个SP即可。

若要配置用户以创建或执行 Microsoft SQL Server 代理作业，必须先将某个现有 SQL Server 登录名或 msdb 角色添加到 msdb 数据库中的下列 SQL Server 代理固定数据库角色之一：SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole。

配置帐户以创建和管理 SQL Server 代理作业

通过sp_help_agent_profile查看代理类型所对应的profile_id,下面是此过程的返回结果 profile_id    profile_name    agent_type    type    description    def_profile
1    默认代理配置文件    1    0    NULL    1
2    默认代理配置文件    2    0    NULL    1
3    详细历史记录代理配置文件    2    0    用于详细历史记录日志的代理配置文件。    0
4    默认代理配置文件    3    0    NULL    1
5    详细历史记录代理配置文件    3    0    用于详细历史记录日志的代理配置文件。    0
6    默认代理配置文件    4    0    NULL    1
7    慢速链接代理配置文件    4    0    用于低带宽连接的代理配置文件。    0
8    详细历史记录代理配置文件    4    0    用于详细历史记录日志的代理配置文件。    0
9    Windows Synchronization Manager profile    4    0    Windows 同步管理器所用的配置文件。    0
10    Windows Synchronization Manager profile    3    0    Windows 同步管理器所用的配置文件。    0
11    默认代理配置文件    9    0    用于已复制的排队事务读取器的代理配置文件。    1
12    行计数验证配置文件    4    0    合并代理用于执行行计数验证的配置文件。    0
13    行计数和校验和验证配置文件    4    0    合并代理用于执行行计数和校验和验证的配置文件。    0
14    遇到数据一致性错误时继续。    3    0    用于跳过数据一致性错误的代理配置文件。它只能由 SQL Server 订阅服务器使用。    0
15    大容量服务器对服务器的配置文件    4    0    合并代理配置文件已经优化，适用于服务器之间的大容量同步。    0
16    用于 OLEDB 流式处理的分发配置文件    3    0    使用 OLEDB 流式处理为处理 LOB 数据启用的分发代理配置文件。    0

通常 快照代理 agent_type = 1，所对应的profile_id = 1

日志读取代理 agent_type = 2，所对应的profile_id = 2

分发代理 agent_type = 3，所对应的profile_id = 4

如下命令返回某个代理的配置信息，注意后面的参数是profile_id

sp_help_agent_parameter @profile_id

sp_help_agent_parameter 1 返回快照代理配置参数

profile_id parameter_name value

1 -BcpBatchSize 100000

1 -HistoryVerboseLevel 2

1 -LoginTimeout 15

1 -QueryTimeout 1800

在快照代理中增加MaxBcpThreads配置参数为20

sp_add_agent_parameter 1,'MaxBcpThreads',20

在日志读取代理中修改PollingInterval配置参数为３

sp_change_agent_parameter 2,'PollingInterval',3

exec sp_dropserver <(用select @@SERVERNAME显示出来的旧名)>
GO
exec sp_addserver <你现在的计算机名>， LOCAL
GO

SELECT @@SERVERNAME,SERVERPROPERTY('ServerName')

为防止正式环境数据库被篡改，但又要保证可以正常查询处理问题，可以将账号的数据库访问权限设置为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)