本文主要介绍SQL Server中记录数据变更的四个方法：触发器、Output子句、变更数据捕获（Change Data Capture 即CDC）功能、同步更改跟踪。其中后两个为SQL Server 2008所新增。
一、触发器
　　在SQL Server的早期版本中，如果要记录某个表或视图的Insert/Update/Delete操作，我们可以借助触发器（Trigger）（）,这在数据量较小的情况下往往是有效的方式之一，其中后触发器（After Trigger）只能跟踪表的三个操作中的任意组合，而前触发器（Instead Of trigger）可以处理表和视图的更新（即使普通的Update View语句在某些列不明确的情况下报错）。我们看两个例子：
　　准备基础数据：
USE testDb2
--创建两个测试表
IF NOT OBJECT_ID('DepartDemo') IS NULL
DROP TABLE [DepartDemo]
IF NOT OBJECT_ID('DepartChangeLogs') IS NULL
DROP TABLE [DepartChangeLogs]
CREATE TABLE [dbo].[DepartDemo](
[DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,
[DName] [nvarchar](200) NULL,
[DCode] [nvarchar](500) NULL,
[Manager] [nvarchar](50) NULL,
[ParentID] [int] NOT NULL DEFAULT ((0)),
[AddUser] [nvarchar](50) NULL,
[AddTime] [datetime] NULL,
[ModUser] [nvarchar](50) NULL,
[ModTime] [datetime] NULL,
[CurState] [smallint] NOT NULL DEFAULT ((0)),
[Remark] [nvarchar](500) NULL,
[F1] [int] NOT NULL DEFAULT ((0)),
[F2] [nvarchar](300) NULL
--记录日志表
CREATE TABLE [DepartChangeLogs]
([LogID] [bigint] IDENTITY(1001,1) NOT NULL PRIMARY KEY,
[DID] [int] NOT NULL,
[DName] [nvarchar](200) NULL,
[DCode] [nvarchar](500) NULL,
[Manager] [nvarchar](50) NULL,
[ParentID] [int] NOT NULL DEFAULT ((0)),
[AddUser] [nvarchar](50) NULL,
[AddTime] [datetime] NULL,
[ModUser] [nvarchar](50) NULL,
[ModTime] [datetime] NULL,
[CurState] [smallint] NOT NULL DEFAULT ((0)),
[Remark] [nvarchar](500) NULL,
[F1] [int] NOT NULL DEFAULT ((0)),
[F2] [nvarchar](300) NULL,
[LogTime] DateTime Default(Getdate()) Not Null,
[InsOrUpd] char not null
　　创建触发器：
/*******　　　创建一个After DML触发器　　******/
/*********
邀月***************/
CREATE TRIGGER dbo.tri_LogDepartDemo
ON [dbo].[DepartDemo]
AFTER INSERT, Delete
/************此处使用update与&Insert,Delete&效果是一样的，邀月注
**********/
SET NOCOUNT ON
--屏蔽触发器发送&受影响的行数&给应用程序
-- Inserted rows
INSERT [DepartChangeLogs]
(DID,[DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
LogTime, InsOrUPD)
SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
GETDATE(), 'I'
FROM inserted i
-- Deleted rows
INSERT [DepartChangeLogs]
(DID,[DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
LogTime, InsOrUPD)
SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2],
GETDATE(), 'D'
FROM deleted d
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
VALUES (N'国家统计局房产审计一科', N'0', N'胡不归', 0, N'DeomUser',
CAST(0x0B6F96 AS DateTime), N'', CAST(0x0000 AS DateTime),
1, N'专业评估全国房价，为老百姓谋福祉', 0, N'')
----该Update不会被触发器记录，但Update会生效
UPDATE departDemo SET [Manager]='任我行' WHERE DID=101
DELETE FROM departDemo where DID=101
SELECT * FROM [DepartChangeLogs]
　　统计效果：如果你觉得触发器过于浪费，你可以试着根据某些字段以缩小触发器的范围
/********* 使用DML触发器记录特定列的修改 ***/
/*********
邀月***************/
CREATE TRIGGER dbo.[tri_LogDepartDemo2]
ON [dbo].[DepartDemo]
AFTER Update
IF Update([Manager])
print '该部门主管实行终身任免制，不得中途更改！'
Rollback ----回滚Update操作
UPDATE departDemo SET [Manager]='任我行' WHERE DID=101
　　执行结果：
　　但触发器的缺陷也是显而易见的，使用触发器请注意以下几点：
1、触发器通常很隐蔽，换句话说，易忘记，特别在检查性能和逻辑问题时。
2、长时间运行的触发器会严重减慢数据操作，特别是在数据频繁修改的数据库中。
3、不记录日志的更新不会引起DML触发器的触发（如WRITETEXT、Trunacte table及批量插入操作）。
4、约束通常比触发器运行更快。
5、处理某些逻辑时，存储过程通常比触发器要更易维护和管理。
6、不允许在触发器中使用Select返回结果集。
　　关于触发器的更多内容，请看MSDN（）
二、使用Output子句
　　官方解释：OutPut子句（）返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息，或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处理应用程序，以供在确认消息、存档以及其他类似的应用程序要求中使用。 也可以将这些结果插入表或表变量。 另外，您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果，然后将这些结果插入目标表或视图。
/********* 使用Output记录表记录的修改 *****/
/*********
邀月***************/
----删除前面的触发器
Drop TRIGGER dbo.[tri_LogDepartDemo]
DROP TRIGGER dbo.[tri_LogDepartDemo2]
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
OUTPUT Inserted.*,getdate(),'I' ---注意这行是新增的
INTO DepartChangeLogs
---注意这行是新增的
VALUES (N'发改委', N'0', N'向问天', 0, N'DeomUser',
CAST(0x0B6F96 AS DateTime), N'', CAST(0x0000 AS DateTime),
1, N'油价，我说了算', 0, N'')
SELECT * FROM [DepartChangeLogs]
　　注意：
　　1、从OUTPUT 中返回的列反映 INSERT、UPDATE 或 DELETE 语句完成之后但在触发器执行之前的数据。
　　2、SQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。
　　3、与触发器相比，OutPut子句可以直接处理Merge语句。
&　　以上两种方法各有千秋，在合适的情况下采取合适的方法才是明智的选择，令人惊喜的是，SQL Server 2008起，为我们提供了更为强大的内建的方法－变更数据捕获（CDC，）和更改跟踪，下面我们隆重介绍它们。
三、使用&变更数据捕获&（CDC）功能
　　SQL Server 2008提供了内建的方法变更数据捕获（Change Data Capture 即CDC）以实现异步跟踪用户表的数据修改，而且这一功能拥有最小的性能开销。可以用于其他数据源的持续更新，例如将OLTP数据库中的数据变更迁移到数据仓库数据库。
　　要使用CDC功能，首先我们得在数据库中启用该功能。在此我们沿用上例中使用的数据库Testdb2
/**************异步跟踪数据更新演示*************/
/*************
邀月***************/
use master
IF EXISTS (SELECT [name] FROM sys.databases WHERE name = 'TestDb2')
drop DATABASE TestDb2
CREATE DATABASE TestDb2
--查看是否启用CDC
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'TestDb2'
USE TestDb2
----启用当前数据库的CDC功能
EXEC sys.sp_cdc_enable_db
　　如果报15517错误，请换用其他owner，邀月注
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'TestDb2'
is_cdc_enabled
USE testDb2
CREATE TABLE [dbo].[DepartDemo](
[DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,
[DName] [nvarchar](200) NULL,
[DCode] [nvarchar](500) NULL,
[Manager] [nvarchar](50) NULL,
[ParentID] [int] NOT NULL DEFAULT ((0)),
[AddUser] [nvarchar](50) NULL,
[AddTime] [datetime] NULL,
[ModUser] [nvarchar](50) NULL,
[ModTime] [datetime] NULL,
[CurState] [smallint] NOT NULL DEFAULT ((0)),
[Remark] [nvarchar](500) NULL,
[F1] [int] NOT NULL DEFAULT ((0)),
[F2] [nvarchar](300) NULL
/**********************************
需要启用SQL Server Agent服务，否则会报错，邀月注
SQLServerAgent is not currently running so it cannot be notified of this action.
***********************************/
/****** 捕获所有的行变更，只返回行的净变更，其他默认 *******/
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'DepartDemo',
@role_name = NULL,
@capture_instance = NULL,
@supports_net_changes = 1,
@index_name = NULL,
@captured_column_list = NULL,
@filegroup_name = default
　　注意此时，SQL Server 自启动了两个job,一个捕获，一个清除，注意清除是默认凌晨２点，清除72小时以上的数据。如果同一数据库的表中CDC已经启用，不会重建job。
Job 'cdc.TestDb2_capture' started successfully.
Job 'cdc.TestDb2_cleanup' started successfully.
--确认表已经被跟踪
SELECT is_tracked_by_cdc FROM sys.tables
WHERE name = 'DepartDemo' and schema_id = SCHEMA_ID('dbo')
is_tracked_by_cdc
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'DepartDemo'
可以看到，SQL Server 增加了一个表[cdc].[dbo_DepartDemo_CT] 相比源表多了个字段： [__$start_lsn] ,[__$end_lsn] ,[__$seqval] ,[__$operation] ,[__$update_mask]
不建议直接查询该表，而应该使用下面的技巧：
USE TestDb2
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
VALUES (N'银监会', N'0', N'云中鹤', 0, N'DemoUser1',
CAST(0x0B6F96 AS DateTime), N'', CAST(0x0000 AS DateTime),
1, N'监管汇率', 0, N'')
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
VALUES (N'统计局', N'0', N'神算子', 0, N'DemoUser2',
CAST(0x0B6F96 AS DateTime), N'', CAST(0x0000 AS DateTime),
1, N'统计数据', 0, N'')
UPDATE [dbo].[DepartDemo]
SET Manager='段正淳'
WHERE DID =101
DELETE [dbo].[DepartDemo]
WHERE DID = 102
　　要查询变更，我们需要借助大名鼎鼎的日志序列号（Log Sequence Numbers）即LSN（）来实现LSN级别的跟踪数据变更。 下面示例中sys.fn_cdc_map_time_to_lsn（）用于LSN转换为时间。
/******* 使用LSN 查看CDC记录 *********/
--/zh-cn/library/bbv=sql.100%29.aspx
SELECT sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , ' 16:09:30') as BeginLSN
0x000AA0003
SELECT sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , ' 23:59:59') as EndLSN
0x001C20005
/**************查看所有CDC记录*************/
/*************
邀月***************/
DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , ' 16:09:30')
DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , ' 23:59:59')
__$operation,
__$update_mask,
FROM [cdc].[fn_cdc_get_all_changes_dbo_DepartDemo]
(@FromLSN, @ToLSN, 'all')
/************查看所有更新*************************
__$operation __$update_mask DID DName Manager
2 0x1FFF 105 银监会 云中鹤
2 0x1FFF 106 统计局 神算子
1 0x1FFF 101 银监会 段正淳
1 0x1FFF 103 银监会 云中鹤
1 0x1FFF 104 统计局 神算子
1 0x1FFF 105 银监会 云中鹤
1 0x1FFF 106 统计局 神算子
2 0x1FFF 107 银监会 云中鹤
2 0x1FFF 108 统计局 神算子
4 0x 银监会 段正淳
1 0x1FFF 108 统计局 神算子
/**************查看所有CDC记录*************/
/*************
邀月***************/
DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , ' 16:09:30')
DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , ' 23:59:59')
--解释一下Operation的具体含义
CASE __$operation
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
END Operation,
__$update_mask,
FROM [cdc].[fn_cdc_get_all_changes_dbo_DepartDemo]
(@FromLSN, @ToLSN, 'all update old')
/**************查看净更改（Net changes）CDC记录*************/
/*************
邀月 ***************/
INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID],
[AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2])
VALUES (N'药监局', N'0', N'蝶谷医仙', 0, N'DemoUser3',
CAST(0x0B6F96 AS DateTime), N'', CAST(0x0000 AS DateTime),
1, N'制定药价', 0, N'')
UPDATE [dbo].[DepartDemo]
SET Manager='胡青牛'
WHERE DID =109
DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , ' 16:09:30')
DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , ' 23:59:59')
CASE __$operation
WHEN 1 THEN 'DELETE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'Before UPDATE'
WHEN 4 THEN 'After UPDATE'
WHEN 5 THEN 'MERGE'
END Operation,
__$update_mask,
FROM [cdc].[fn_cdc_get_net_changes_dbo_DepartDemo]
(@FromLSN, @ToLSN, 'all with mask')
　　我们还可以通过转换CDC更新掩码获得更为直观的结果，这里需要借助于另外两个函数sys.fn_cdc_is_bit_set（）和sys.fn_cdc_get_column_ordinal（）
/************** 转换CDC更新掩码 *************/
/*************
邀月 **************/
UPDATE dbo.[DepartDemo]
SET [Manager] = '东方不败'
WHERE DID =107
UPDATE dbo.[DepartDemo]
SET ParentID = 109
WHERE DID =107
DECLARE @FromLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'smallest greater than or equal' , ' 16:09:30')
DECLARE @ToLSN varbinary(10) =
sys.fn_cdc_map_time_to_lsn
( 'largest less than or equal' , ' 23:59:59')
sys.fn_cdc_is_bit_set (
sys.fn_cdc_get_column_ordinal (
'dbo_DepartDemo' , 'Manager' ),
__$update_mask) Manager_Updated,
sys.fn_cdc_is_bit_set (
sys.fn_cdc_get_column_ordinal (
'dbo_DepartDemo' , 'ParentID' ),
__$update_mask) ParentID_Updated,
FROM cdc.fn_cdc_get_all_changes_dbo_DepartDemo
(@FromLSN, @ToLSN, 'all')
WHERE __$operation = 4
　　除了前面介绍的指定LSN边界的方法，SQL Server还提供了一系列的获取边界的方法：
sys.fn_cdc_get_max_lsn（）
sys.fn_cdc_get_min_lsn（）
sys.fn_cdc_increment_lsn（）
sys.fn_cdc_decrement_lsn（）
　　示例如下：
/************** 获取LSN边界的其他方法 *************/
/*************
邀月 **************/
--获取最小边界
SELECT sys.fn_cdc_get_min_lsn ('dbo_DepartDemo') Min_LSN
--获取可用的最大边界
SELECT sys.fn_cdc_get_max_lsn () Max_LSN
--获取最大边界的下一个序号
SELECT sys.fn_cdc_increment_lsn (sys.fn_cdc_get_max_lsn()) New_Lower_Bound_LSN
--获取最大边界的前一个序号
SELECT sys.fn_cdc_decrement_lsn (sys.fn_cdc_get_max_lsn())
New_Lower_Bound_Minus_one_LSN
　　通过以下存储过程在数据库和表级禁用CDC
sys.sp_cdc_disable_table （）
sys.sp_cdc_disable_db（）注意，该命令同时也删除了CDC架构和相关的SQL代理作业。
/************** 在数据库和表级禁用CDC *************/
/*************
邀月 **************/
EXEC sys.sp_cdc_disable_table 'dbo', 'DepartDemo', 'all'
SELECT is_tracked_by_cdc FROM sys.tables
WHERE name = 'DepartDemo' and schema_id = SCHEMA_ID('dbo')
--当前数据库上禁用CDC
EXEC sys.sp_cdc_disable_db
四、使用&更改跟踪&以最小的磁盘开销跟踪净数据更改
　　CDC可以用来对数据库和数据仓库的持续数据变更进行异步数据跟踪，而SQL Server 2008中新增的&更改跟踪&却是一个同步进程，是DML操作本身（I/D/U）事务的一部分，它的最大优势是以最小的磁盘开销来侦测净行变更，它允许修改的数据以事务一致的形式表现，并提供了检测数据冲突的能力。它甚至可以根据外部传入的应用程序上下文，来完成更细颗粒度的更改处理，参看WITH CHANGE_TRACKING_CONTEXT （）
/***使用&更改跟踪&以最小的磁盘开销跟踪净数据更改****/
/*************
邀月 **************/
IF EXISTS (SELECT [name] FROM sys.databases WHERE name = 'TestDb4')
drop DATABASE TestDb4
CREATE DATABASE TestDb4
--启用更新跟踪，36小时清理一次
ALTER DATABASE TestDb4
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 36 HOURS,
AUTO_CLEANUP = ON)
　　注意，下一步是允许快照隔离，这是微软推祟的&最佳实践&，尽管这样行版本的生成会增加额外的空间使用，从而会增加总的I/O数量,但不使用快照会引发事务不一致的变更信息。
ALTER DATABASE TestDb4
SET ALLOW_SNAPSHOT_ISOLATION ON
SELECT DB_NAME(database_id) 数据库名称,is_auto_cleanup_on,
retention_period,retention_period_units_desc
FROM sys.change_tracking_databases
数据库名称 is_auto_cleanup_on retention_period retention_period_units_desc
TestDb4 1 36 HOURS
USE TestDb4
--创建测试表
CREATE TABLE dbo.DepartDemo
([DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY,
[DName] [nvarchar](200) NULL,
[Manager] [nvarchar](50) NULL,
[ParentID] [int] NOT NULL DEFAULT ((0)),
[CurState] [smallint] NOT NULL DEFAULT ((0)),
----TRUNCATE table dbo.DepartDemo
--启用表的列更新跟踪
ALTER TABLE dbo.DepartDemo
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
--确认是否更新跟踪开启
SELECT OBJECT_NAME(object_id) ObjNM,is_track_columns_updated_on
FROM sys.change_tracking_tables
ObjNM is_track_columns_updated_on
DepartDemo 1
--增加测试数据
INSERT dbo.DepartDemo
(DName,ParentID)
('明教', 0),
('五行集', 101),
('少林派',0)
SELECT * FROM dbo.DepartDemo
--当前版本
SELECT CHANGE_TRACKING_CURRENT_VERSION ()
as 当前版本
SELECT CHANGE_TRACKING_MIN_VALID_VERSION
( OBJECT_ID('dbo.DepartDemo') )as 最小可用版本
最小可用版本
函数ChangeTable有两种用法来检测更改:一、使用Changes关键字二、使用Version关键字
一、使用Changes关键字
SELECT DID,SYS_CHANGE_OPERATION,
SYS_CHANGE_VERSION
FROM CHANGETABLE
(CHANGES dbo.DepartDemo, 0) AS CT
UPDATE dbo.DepartDemo
SET Manager='张无忌'
WHERE DID = 101
UPDATE dbo.DepartDemo
SET [DName] = '五行旗'
WHERE DID = 102
DELETE dbo.DepartDemo
WHERE DID = 103
SELECT CHANGE_TRACKING_CURRENT_VERSION () as 当前版本
--版本1之后的更改
SELECT DID,
SYS_CHANGE_VERSION,
SYS_CHANGE_OPERATION,
SYS_CHANGE_COLUMNS
FROM CHANGETABLE
(CHANGES dbo.DepartDemo, 1) AS CT
--返回哪些列被修改，1为真,0为假
SELECT DID,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.DepartDemo'),'DName', 'ColumnId') ,
SYS_CHANGE_COLUMNS) 是否改变DName,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.DepartDemo'), 'Manager', 'ColumnId') ,
SYS_CHANGE_COLUMNS) 是否改变Manager
FROM CHANGETABLE
(CHANGES dbo.DepartDemo, 1) AS CT
WHERE SYS_CHANGE_OPERATION = 'U'
DID 是否改变DName 是否改变Manager
二、使用Version关键字
SELECT d.DID, d.DName, d.Manager,
ct.SYS_CHANGE_VERSION
FROM dbo.DepartDemo d
CROSS APPLY CHANGETABLE
(VERSION dbo.DepartDemo , (DID), (d.DID)) as ct
UPDATE dbo.DepartDemo
SET DName = '中原明教',
CurState = 0
WHERE DID = 101
SELECT d.DID, d.DName, d.Manager,
ct.SYS_CHANGE_VERSION
FROM dbo.DepartDemo d
CROSS APPLY CHANGETABLE
(VERSION dbo.DepartDemo , (DID), (d.DID)) as ct
SELECT CHANGE_TRACKING_CURRENT_VERSION () as 当前版本
--跟踪外部程序哪一部分引起的更改，这样好找出源头
DECLARE @context varbinary(128) = CAST('明教内讧引起分裂' as varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@context)
INSERT dbo.DepartDemo
(DName, Manager)
('天鹰教', '殷天正')
--查询Context更改
SELECT DID,
SYS_CHANGE_OPERATION,
SYS_CHANGE_VERSION,
CAST(SYS_CHANGE_CONTEXT as varchar) ApplicationContext
FROM CHANGETABLE
(CHANGES dbo.DepartDemo, 5) AS CT
DID SYS_CHANGE_OPERATION SYS_CHANGE_VERSION ApplicationContext
104 I 6 明教内讧引起分裂
　　本文总结了SQL Server中记录数据变更的四个方法：触发器、Output子句、SQL Server 2008中新增的变更数据捕获（CDC）功能、同步更改跟踪。其中后两个是SQL Server 2008中新增的功能，在SQL Server 2012中更是与Always ON紧密集成。
　　1、不建议前两个。
　　2、CDC用以实现异步跟踪用户表的数据修改，而且这一功能拥有最小的性能开销，可以用于其他数据源的持续更新，例如将OLTP数据库中的数据变更迁移到数据仓库数据库。
　　3、&更改跟踪&的最大优势是以最小的磁盘开销来侦测净行变更，它允许修改的数据以事务一致的形式表现，并提供了检测数据冲突的能力。
其他推荐文章：
1、在VS中如何将数据同步配置为使用 SQL Server 更改跟踪
2、SQL Server 2012中复制、更改跟踪、更改数据捕获和 AlwaysOn 可用性组 (SQL Server)
3、在SSIS 2012中使用CDC（数据变更捕获）
4、在SQL Server 2012中实现CDC for Oracle
阅读(...) 评论()1765人阅读
SQLServer（2）
sys.dm_db_index_physical_stats (Transact-SQL)
SQL Server 2008 R2
返回指定表或视图的数据和索引的大小和碎片信息。对于索引，针对每个分区中的 B 树的每个级别，返回与其对应的一行。对于堆，针对每个分区的 IN_ROW_DATA 分配单元，返回与其对应的一行。对于大型对象 (LOB) 数据，针对每个分区的 LOB_DATA 分配单元返回与其对应的一行。如果表中存在行溢出数据，则针对每个分区中的 ROW_OVERFLOW_DATA 分配单元，返回与其对应的一行。有关分配单元和分区的信息，请参阅。
sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
database_id | NULL | 0 | DEFAULT
数据库的 ID。database_id 的数据类型为 smallint。有效的输入包括数据库的 ID 号、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL 可返回 SQL Server 实例中所有数据库的信息。如果为 database_id 指定 NULL，则还必须为
object_id、index_id 和
partition_number 指定 NULL。
可以指定内置函数 。如果在不指定数据库名称的情况下使用 DB_ID，则当前数据库的兼容级别必须是 90 或更高。
object_id | NULL | 0 | DEFAULT
该索引所基于的表或视图的对象 ID。object_id 的数据类型为 int。
有效的输入包括表和视图的 ID 号、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL 可返回指定数据库中的所有表和视图的信息。如果为 object_id 指定 NULL，则还必须为
index_id 和 partition_number 指定 NULL。
index_id | 0 | NULL | -1 | DEFAULT
索引的 ID。index_id 的数据类型为 int。有效输入包括索引的 ID 号、0（如果
object_id 为堆）、NULL、-1 或 DEFAULT。默认值为 -1。在此上下文中，NULL、-1 和 DEFAULT 是等效值。
指定 NULL 可返回基表或视图的所有索引的信息。如果为 index_id 指定 NULL，则还必须为
partition_number 指定 NULL。
partition_number | NULL | 0 | DEFAULT
对象中的分区号。partition_number 的数据类型为 int。有效输入包括索引或堆的
partion_number、NULL、0 或 DEFAULT。默认值为 0。在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL，以返回有关所属对象的所有分区的信息。
partition_number 从 1 开始。未分区的索引或堆的
partition_number 设置为 1。
mode | NULL | DEFAULT
模式的名称。mode 指定用于获取统计信息的扫描级别。mode 的数据类型为
sysname。有效输入为 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。默认值 (NULL) 为 LIMITED。
database_id
表或视图的数据库 ID。
索引所在的表或视图的对象 ID。
索引的索引 ID。
partition_number
所属对象内从 1 开始的分区号；表、视图或索引。
1 = 未分区的索引或堆。
index_type_desc
nvarchar(60)
索引类型的说明：
CLUSTERED INDEX
NONCLUSTERED INDEX
PRIMARY XML INDEX
SPATIAL INDEX
alloc_unit_type_desc
nvarchar(60)
对分配单元类型的说明：
IN_ROW_DATA
ROW_OVERFLOW_DATA
LOB_DATA 分配单元包含类型为 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)
和 xml 的列中所存储的数据。有关详细信息，请参阅。
ROW_OVERFLOW_DATA 分配单元包含类型为 varchar(n)、nvarchar(n)、varbinary(n) 和
sql_variant 的列（已推送到行外）中所存储的数据。有关详细信息，请参阅。
index_depth
索引级别数。
1 = 堆，或 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。
index_level
索引的当前级别。
0 表示索引叶级别、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。
大于 0 的值表示非叶索引级别。index_level 在索引的根级别中属于最高级别。
仅当 mode = DETAILED 时才处理非叶级别的索引。
avg_fragmentation_in_percent
索引的逻辑碎片，或 IN_ROW_DATA 分配单元中堆的区碎片。
此值按百分比计算，并将考虑多个文件。有关逻辑碎片和区碎片的定义，请参阅“备注”。
0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配单元。
对于堆，当 mode 为 SAMPLED 时，为 NULL。
fragment_count
IN_ROW_DATA 分配单元的叶级别中的碎片数。有关碎片的详细信息，请参阅“备注”。
对于索引的非叶级别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。
对于堆，当 mode 为 SAMPLED 时，为 NULL。
avg_fragment_size_in_pages
IN_ROW_DATA 分配单元的叶级别中的一个碎片的平均页数。
对于索引的非叶级别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。
对于堆，当 mode 为 SAMPLED 时，为 NULL。
page_count
索引或数据页的总数。
对于索引，表示 IN_ROW_DATA 分配单元中 b 树的当前级别中的索引页总数。
对于堆，表示 IN_ROW_DATA 分配单元中的数据页总数。
对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示该分配单元中的总页数。
avg_page_space_used_in_percent
所有页中使用的可用数据存储空间的平均百分比。
对于索引，平均百分比应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。
对于堆，表示 IN_ROW_DATA 分配单元中所有数据页的平均百分比。
对于 LOB_DATA 或 ROW_OVERFLOW DATA 分配单元，表示该分配单元中所有页的平均百分比。
当 mode 为 LIMITED 时，为 NULL。
record_count
总记录数。
对于索引，记录的总数应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。
对于堆，表示 IN_ROW_DATA 分配单元中的总记录数。
对于堆，此函数返回的记录数可能与通过对堆运行 SELECT COUNT(*) 返回的行数不匹配。这是因为一行可能包含多个记录。例如，在某些更新情况下，单个堆行可能由于更新操作而包含一条前推记录和一条被前推记录。此外，多数大型 LOB 行在 LOB_DATA 存储中拆分为多个记录。
对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中总记录数。
当 mode 为 LIMITED 时，为 NULL。
ghost_record_count
分配单元中将被虚影清除任务删除的虚影记录数。
对于 IN_ROW_DATA 分配单元中索引的非叶级别，为 0。
当 mode 为 LIMITED 时，为 NULL。
version_ghost_record_count
由分配单元中未完成的快照隔离事务保留的虚影记录数。
对于 IN_ROW_DATA 分配单元中索引的非叶级别，为 0。
当 mode 为 LIMITED 时，为 NULL。
min_record_size_in_bytes
最小记录大小（字节）。
对于索引，最小记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。
对于堆，表示 IN_ROW_DATA 分配单元中的最小记录大小。
对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的最小记录大小。
当 mode 为 LIMITED 时，为 NULL。
max_record_size_in_bytes
最大记录大小（字节）。
对于索引，最大记录的大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。
对于堆，表示 IN_ROW_DATA 分配单元中的最大记录大小。
对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的最大记录大小。
当 mode 为 LIMITED 时，为 NULL。
avg_record_size_in_bytes
平均记录大小（字节）。
对于索引，平均记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。
对于堆，表示 IN_ROW_DATA 分配单元中的平均记录大小。
对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的平均记录大小。
当 mode 为 LIMITED 时，为 NULL。
forwarded_record_count
堆中具有指向另一个数据位置的转向指针的记录数。（在更新过程中，如果在原始位置存储新行的空间不足，将会出现此状态。）
除 IN_ROW_DATA 分配单元外，对于堆的其他所有分配单元都为 NULL。
当 mode = LIMITED 时，对于堆为 NULL。
compressed_page_count
压缩页的数目。
对于堆，新分配的页未进行 PAGE 压缩。堆在以下两种特殊情况下进行 PAGE 压缩：大量导入数据时和重新生成堆时。导致页分配的典型 DML 操作不会进行 PAGE 压缩。当 compressed_page_count 值增长到超过您所需的阈值时，将重新生成堆。
对于具有聚集索引的表，compressed_page_count 值表示 PAGE 压缩的效率。
sys.dm_db_index_physical_stats 动态管理函数将替换 DBCC SHOWCONTIG 语句。此动态管理函数不接受来自 CROSS APPLY 和 OUTER APPLY 的相关参数。
函数的执行模式将确定为了获取此函数所使用的统计信息数据而执行的扫描级别。mode 被指定为 LIMITED、SAMPLED 或 DETAILED。该函数遍历分配单元的页链，这些分配单元构成表或索引的指定分区。sys.dm_db_index_physical_stats 只需要一个意向共享 (IS) 表锁，而忽略其运行所处的模式。有关锁定的详细信息，请参阅。
LIMITED 模式运行最快，扫描的页数最少。对于索引，只扫描 B 树的父级别页（即叶级别以上的页）。对于堆，只检查关联的 PFS 和 IAM 页；不扫描堆的数据页。在 SQL Server 2005 中，在 LIMITED 模式下扫描堆的所有页。
在 LIMITED 模式下，compressed_page_count 为 NULL，这是因为数据库引擎只能扫描 B 树的非叶页和堆的 IAM 和 PFS 页。使用 SAMPLED 模式可以获取 compressed_page_count 的估计值，使用 DETAILED 模式可以获取 compressed_page_count 的实际值。SAMPLED 模式将返回基于索引或堆中所有页的 1％ 样本的统计信息。SAMPLED 模式下的结果应被视为近似值。如果索引或堆少于 10,000 页，则使用 DETAILED
模式代替 SAMPLED。
DETAILED 模式将扫描所有页并返回所有统计信息。
从 LIMITED 到 DETAILED 模式，速度将越来越慢，因为在每个模式中执行的任务越来越多。若要快速测量表或索引的大小或碎片级别，请使用 LIMITED 模式。它的速度最快，并且对于索引的 IN_ROW_DATA 分配单元中的每个非叶级别，不返回与其对应的一行。
可以使用 Transact-SQL 函数
指定 database_id 和
object_id 参数的值。但是，将无效的值传递给这些函数可能会导致意外结果。例如，如果找不到数据库或对象名（因为它们不存在或拼写错误），则两个函数都返回 NULL。sys.dm_db_index_physical_stats 函数将 NULL 解释为指定所有数据库或所有对象的通配符值。
此外，将在调用 sys.dm_db_index_physical_stats 函数之前处理 OBJECT_ID 函数，因此会在当前数据库（而不是在
database_id 中指定的数据库）的上下文中对 OBJECT_ID 函数取值。此行为可能会导致 OBJECT_ID 函数返回 NULL 值；或者，如果当前数据库上下文和指定数据库中都存在对象名，则可能返回一条错误消息。以下示例说明这些意外的结果。
在下面的示例中，将在 master 数据库的上下文中计算 OBJECT_ID。因为
master 中不存在 Person.Address，所以此函数返回
NULL。当将 NULL 指定为
object_id 时，将返回数据库中的所有对象。当指定了无效的对象时，将返回相同的结果。
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
下面的示例显示当指定的一个有效对象名称同时位于当前数据库上下文中和在 sys.dm_db_index_physical_stats 函数的
database_id 参数中指定的数据库中时所生成的结果。因为 OBJECT_ID 返回的 ID 值与指定数据库中的对象的 ID 值不匹配，所以返回一个错误。
CREATE DATABASE T
CREATE SCHEMA P
CREATE Table Person.Address(c1 int);
USE AdventureWorks2008R2;
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
-- Clean up temporary database.
DROP DATABASE T
请始终确保使用 DB_ID 或 OBJECT_ID 时返回了有效的 ID。例如，在使用 OBJECT_ID 时，请指定由三部分构成的名称，如
OBJECT_ID(N'AdventureWorks2008R2.Person.Address')，或者在 sys.dm_db_index_physical_stats 函数中使用由函数返回的值之前对这些值进行测试。下面的示例 A 和 B 演示了一种指定数据库和对象 ID 的安全方法。
在对表进而对表中定义的索引进行数据修改（INSERT、UPDATE 和 DELETE 语句）的整个过程中都会出现碎片。由于这些修改通常并不在表和索引的行中平均分布，所以每页的填充度会随时间而改变。对于扫描表的部分或全部索引的查询，这种碎片会导致附加的页读取。从而延缓了数据的并行扫描。
SQL Server 2008 中的碎片计算算法比 SQL Server 2000 中的更精确。因此，碎片值显得更高。例如，在 SQL Server 2000 中，如果一个表的第 11 页和第 13 页在同一区中，而第 12 页不在该区中，该表不被视为含有碎片。但是访问这些页需要两次物理 I/O 操作，因此，在 SQL Server 2008 中，这将算作碎片。
索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中。对于堆，此值表示堆的区碎片。对于索引，此值表示索引的逻辑碎片。与 DBCC SHOWCONTIG 不同，这两种情况下的碎片计算算法都会考虑跨越多个文件的存储，因而结果是精确的。
这是索引的叶级页中出错页所占的百分比。对于出错页，分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。
这是堆的叶级页中出错区所占的百分比。出错区是指：包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。
为了获得最佳性能，avg_fragmentation_in_percent 的值应尽可能接近零。但是，从 0 到 10％ 范围内的值都可以接受。所有减少碎片的方法（例如重新生成、重新组织或重新创建）都可用于降低这些值。有关如何分析索引中碎片程度的详细信息，请参阅。
当索引分段的方式导致碎片影响查询性能时，有三种方法可减少碎片：
删除并重新创建聚集索引。
重新创建聚集索引将对数据进行重新分布，从而使数据页填满。填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。这种方法的缺点是索引在删除和重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不能重新创建索引。有关详细信息，请参阅
使用 ALTER INDEX REORGANIZE（代替 DBCC INDEXDEFRAG）按逻辑顺序重新排序索引的叶级页。由于这是联机操作，因此在语句运行时仍可使用索引。中断此操作时不会丢失已经完成的任务。此方法的缺点是在重新组织数据方面不如索引重新生成操作的效果好，而且不更新统计信息。
使用 ALTER INDEX REBUILD（代替 DBCC DBREINDEX）联机或脱机重新生成索引。有关详细信息，请参阅 。
不需要仅因为碎片的原因而重新组织或重新生成索引。碎片的主要影响是，在索引扫描过程中会降低页的预读吞吐量。这将导致响应时间变长。如果含有碎片的表或索引中的查询工作负荷不涉及扫描（因为工作负荷主要是单独查找），则删除碎片可能不起作用。有关详细信息，请参阅此
如果在收缩操作中对索引进行部分或完全移动，则运行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 可能产生碎片。因此，如果必须执行收缩操作，则应在删除碎片之前进行。
若要减少堆的区碎片，请对表创建聚集索引，然后删除该索引。在创建聚集索引时将重新分布数据。同时会考虑数据库中可用空间的分布，从而使其尽可能优化。当删除聚集索引以重新创建堆时，数据不会移动并保持最佳位置。有关如何执行这些操作的信息，请参阅
通过创建并删除某一表的聚集索引，对该表两次重新生成所有非聚集索引。
默认情况下，ALTER INDEX REORGANIZE 语句将压缩包含大型对象 (LOB) 数据的页。因为不会释放空的 LOB 页，所以在删除大量 LOB 数据或 LOB 列时，压缩此数据可改善磁盘空间使用情况。
重新组织指定的聚集索引将压缩聚集索引中包含的所有 LOB 列。重新组织非聚集索引将压缩作为索引中非键（已包括）列的所有 LOB 列。如果语句中指定 ALL，则将对与指定表或视图关联的所有索引进行重新组织。此外，将压缩与聚集索引、基础表或带有包含列的非聚集索引关联的所有 LOB 列。
avg_page_space_used_in_percent 列指示页填充度。为了使磁盘使用状况达到最优，对于没有很多随机插入的索引，此值应接近 100％。但是，对于具有很多随机插入且页很满的索引，其页拆分数将不断增加。这将导致更多的碎片。因此，为了减少页拆分，此值应小于 100％。使用指定的 FILLFACTOR 选项重新生成索引可以改变页填充度，以便符合索引中的查询模式。有关填充因子的详细信息，请参阅。此外，ALTER
INDEX REORGANIZE 还试图通过将页填充到上一次指定的 FILLFACTOR 来压缩索引。这将增加 avg_space_used_in_percent 中的值。请注意，ALTER INDEX REORGANIZE 不会降低页填充度。相反，必须执行索引重新生成。
碎片由分配单元中同一文件内的物理连续的叶级页组成。一个索引至少有一个碎片。索引可以包含的最大碎片数等于索引的页级别页数。碎片越大，意味着读取相同页数所需的磁盘 I/O 越少。因此，avg_fragment_size_in_pages 值越大，范围扫描的性能越好。avg_fragment_size_in_pages 和 avg_fragmentation_in_percent 值彼此成反比。因此，重新生成或重新组织索引会减少碎片数量，但同时增大碎片大小。
需要下列权限：
对数据库中的指定对象具有 CONTROL 权限。
VIEW DATABASE STATE 权限，以便使用对象通配符 @object_id= NULL 返回有关指定数据库中所有对象的信息。
VIEW SERVER STATE 权限，以便使用数据库通配符 @database_id = NULL 返回有关所有数据库的信息。
授予 VIEW DATABASE STATE 权限允许返回数据库中的所有对象，而不考虑对特定对象拒绝的任何 CONTROL 权限。
拒绝 VIEW DATABASE STATE 将禁止返回数据库中的所有对象，而不管对特定对象授予的任何 CONTROL 权限。此外，如果指定数据库通配符 @database_id=NULL，则省略数据库。
有关详细信息，请参阅。
以下示例将返回 AdventureWorks2008R2 数据库中
Person.Address 表的所有索引和分区的大小和碎片统计信息。为了获得最佳性能并限制返回的统计信息，扫描模式设置为
'LIMITED'。执行此查询至少需要对 Person.Address 表的 CONTROL 权限。
Transact-SQL
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.Person.Address');
IF @db_id IS NULL
PRINT N'Invalid database';
ELSE IF @object_id IS NULL
PRINT N'Invalid object';
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
以下示例将返回有关 AdventureWorks2008R2 数据库中
dbo.DatabaseLog 堆的所有统计信息。由于表包含 LOB 数据，因此除了对存储堆的数据页的 IN_ROW_ALLOCATION_UNIT 返回与其对应的一行外，还对
LOB_DATA 分配单元返回与其对应的一行。执行此查询至少需要对 dbo.DatabaseLog 表的 CONTROL 权限。
Transact-SQL
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.dbo.DatabaseLog');
IF @object_id IS NULL
PRINT N'Invalid object';
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
以下示例通过为所有参数指定通配符 NULL，来返回有关 SQL Server 实例中所有表和索引的所有统计信息。执行此查询需要 VIEW SERVER STATE 权限。
Transact-SQL
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
以下示例将自动重新组织或重新生成数据库中平均碎片超过 10％ 的所有分区。执行此查询需要 VIEW DATABASE STATE 权限。此示例在不指定数据库名称的情况下，指定
DB_ID 作为第一个参数。如果当前数据库的兼容级别为 80 或更低，则会产生错误。若要纠正此错误，请用有效的数据库名称替换
DB_ID()。有关数据库兼容级别的详细信息，请参阅 。
Transact-SQL
-- Ensure a USE &databasename& statement has been executed first.
SET NOCOUNT ON;
DECLARE @p
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent & 10.0 AND index_id & 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_
-- Open the cursor.
-- Loop through the partitions.
WHILE (1=1)
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @
IF @@FETCH_STATUS & 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
object_id = @objectid AND index_id = @
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag & 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag &= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount & 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @
-- Close and deallocate the cursor.
DEALLOCATE
-- Drop the temporary table.
DROP TABLE #work_to_
下面的示例演示如何显示其行和页已压缩的页以及与总页数进行比较。此信息可用于确定压缩为索引或表提供的好处。
SELECT o.name,
ips.partition_number,
ips.index_type_desc,
ips.record_count, ips.avg_record_size_in_bytes,
ips.min_record_size_in_bytes,
ips.max_record_size_in_bytes,
ips.page_count, pressed_page_count
FROM sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN sys.objects o on o.object_id = ips.object_id
ORDER BY record_count DESC;
以下示例显示 SAMPLED 模式如何返回不同于 DETAILED 模式结果的近似值。
CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
BEGIN TRAN
DECLARE @idx int = 0;
WHILE @idx & 1000000
INSERT INTO t3 (col1, col2)
VALUES (@idx,
REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
SET @idx = @idx + 1
SELECT page_count, compressed_page_count, forwarded_record_count, *
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'SAMPLED');
SELECT page_count, compressed_page_count, forwarded_record_count, *
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'DETAILED');
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：139672次
积分：1608
积分：1608
排名：第17583名
转载：106篇
评论：21条
(1)(2)(4)(1)(1)(1)(1)(2)(2)(2)(2)(1)(1)(1)(2)(3)(1)(1)(2)(1)(6)(3)(2)(2)(1)(7)(7)(4)(12)(19)(6)(8)(6)(6)(1)