调整SQLServer2000运行中数据库结构www.129028.com金沙:

日期:2020-05-07编辑作者:数据库
Alter Table [HR_User] Alter Column [UserName] [varchar] (100) COLLATE Chinese_PRC_CS_AS NOT NULLGOAlter Table [HR_User] Alter Column [NickName] [varchar] (100) COLLATE Chinese_PRC_CS_AS NOT NULLGO

目录
一、SQLCDC简介
二、在数据库级别开启CDC
三、为某个数据表开启CDC
四、记录添加记录的数据改变
五、记录删除数据的数据改变
六、记录更新记录的数据改变

HR_User现有结构:

六、记录删除记录的数据改变

    我们最后来演示当我们对记录实施删除操作的时候,CDC会为我们记录下怎样的数据。现在我们执行如下的T-SQL将Users表中所有的记录均删除。

Delete From Users

 

    查看cdc.dbo_Users_CT的记录,多出的两笔记录正式我们删除的User记录,__$operation字段的值为1表示“删除”操作。

www.129028.com金沙 1

    本篇文章仅仅是简单介绍SQLCDC的基本原理和大体上的使用方式,这篇文章《Introduction to Change Data Capture (CDC) in SQL Server 2008[转]》会给你更加详尽的介绍。如果你想深入研究SQLCDC,还是参考SQL Server 2008在线文档。

 

 

转自:http://www.cnblogs.com/artech/archive/2010/11/20/cdc.html

这时候,我们来构建应用数据库的修改语句,T-SQL修改表结构添加新字段语法为Alter TABLE TableName Add,这样我们要添加两个字段就应该这样写:

    说到这里,很多人都会想到采用触发器的方式来实现对数据改变的捕捉。但是这种实现方案具有一个最大的局限:由于触发器是在数据操作所在事务范围内执行的,所有会带来性能的问题,严重的话还会因为触发器的执行导致事务超市。所以在这里,我们介绍一种更好的解决方案:SQLCDC。

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HR_User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[HR_User] GO CREATE TABLE [dbo].[HR_User] ( [UserId] [int] NOT NULL , [UserName] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL , [NickName] [varchar] (50) COLLATE Chinese_PRC_CS_AS NOT NULL , [Birthday] [datetime] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[HR_User] ADD CONSTRAINT [DF_HR_User_UserId] DEFAULT (0) FOR [UserId], CONSTRAINT [DF_HR_User_UserName] DEFAULT ('') FOR [UserName], CONSTRAINT [DF_HR_User_NickName] DEFAULT ('') FOR [NickName], CONSTRAINT [DF_HR_User_Birthday] DEFAULT (getdate()) FOR [Birthday], CONSTRAINT [PK_HR_User] PRIMARY KEY CLUSTERED ( [UserId] ) ON [PRIMARY] GO exec sp_addextendedproperty N'MS_Description', N'出生日期', N'user', N'dbo', N'table', N'HR_User', N'column', N'Birthday' GO exec sp_addextendedproperty N'MS_Description', N'用户昵称', N'user', N'dbo', N'table', N'HR_User', N'column', N'NickName' GO exec sp_addextendedproperty N'MS_Description', N'用户Id', N'user', N'dbo', N'table', N'HR_User', N'column', N'UserId' 

一、SQLCDC简介

    CDC的全名为Change Data Capture,顾名思义,就是用于追踪和捕捉数据改变。CDC是在SQL Server 2008中才出现的新特性,而这个特性则在很早之前就出现在了Oracle中。对于SQL Server之前版本来说,在没有CDC的情况下,如果需要记录基于某个数据表的数据改变,我们只能采用触发器,具体来说就是通过手工创建After Insert、After Update和After Delete触发器去记录变化的数据。而CDC给了我们一种更为方便、易用和省心的方式去记录某个数据表的历史操作。

开发过程中的数据库结构结构,不可避免的会需要反复的修改。最麻烦的情况莫过于开发者数据库结构已经修改,而实际应用中数据库又有大量数据,如何在不影响 数据库中数据情况下,更新数据结构呢?当然,我们可以手工对应用数据库表结构各个添加、更正、删除的字段一一调整,这对一两个字段来说,是比较简单的,如 果改动比较大的时候,这个过程将是非常繁琐的。本文意在介绍使用SQLServer2000 T-SQL语句进行数据库结构调整,希望能够给各位带来些方便。下面以现有数据库表HR_User为例,讲解如何进行这类操作。

     对于大部分企业应用来用,有一个基本的功能必不可少,那就是Audit Trail或者Audit Log,中文翻译为追踪检查、审核检查或者审核记录。我们采用Audit Trail记录每一笔业务操作的基本信息,比如操作的基本描述、操作时间、操作者等。对于一些安全级别比较高的应用,或者操作一些比较敏感的数据,我们甚至需要记录该笔业务操作引起的数据的改变。具体来说,这里的“数据改变”指的是每一条影响的记录在操作执行前后的变化。对于添加的记录,需要记录下新插入的记录;对于删除的记录,需要记录下原来的记录;对于更新的记录,则需要同时记录下更新前后的记录。

现在,需要在HR_User中添加字段用户昵称[NickName] [varchar] (50) 不为空,出生日期[Birthday] [datetime] 不为空。在开发数据库中我们已经添加了这两个字段,在查询分析器或者企业管理器中生成新表的构造语句如下:

三、为某个数据表开启CDC

    由于CDC用于记录基于某个数据表的数据改变,所以在当前数据库CDC开启的情况下,你还需要显式地为某个数据表开启CDC特性。作为演示,我们通过如下T-SQL在TestDb下创建了一个简单的Users表,它仅仅具有三个字段:Id、Name和Birthday。

CREATE TABLE [dbo].[Users](
    [Id] [varchar](50) PRIMARY KEY,
    [Name] [nvarchar](50) NOT NULL,
    [Birthday] [date] NOT NULL)

 

    数据表的CDC特性的开启通过执行sys.sp_cdc_enable_table存储过程实现。调用该存储过程的最简的方式就是指定数据表的Schema、名称和用于提取改变数据必须具有的权限(角色)。我通过执行下面的T-SQL将我们创建的Users表的CDC特性打开,其中@role_name参数被设置成NULL,表明我不对读取改变数据操作进行授权。 sys.sp_cdc_enable_table具有很多参数,至于相应参数所影响的CDC行为,可以参考SQL Server 2008在线文档。

Use TestDb
Go
Exec sys.sp_cdc_enable_table 'dbo', 'Users', @role_name = NULL
Go

 

    需要注意的是,CDC实际上建立在SQL Server Agent之上的,所以在执行上述T-SQL之前需要启动SQL Server Agent。当某个数据表的CDC特性被开启之后,系统会为创建一个用于保存数据变化的追踪表(Tracking Table)。该表的Schema为cdc,命名方式为被追踪表的表名后加“CT”后缀。执行上面一段T-SQL之后,会有如下一个系统表被创建出来,我们发现Users表的三个字段也在该表中。此外。该表还具有5个额外字段:__$start_lsn、__$end_lsn、__$seqval、 __$operation 和__$update_mask,表示日志系列号(Log Sequence Number)、操作(删除、插入、修改前和修改后)信息。

www.129028.com金沙 2

 

本文由www.129028.com金沙发布于数据库,转载请注明出处:调整SQLServer2000运行中数据库结构www.129028.com金沙:

关键词:

Sql Server中的非聚集索引详细介

这篇我们来解开神秘面纱。 3:通过最后的槽位列表,可以得知173号索引页上存放着8条索引记录。 复制代码 代码如下...

详细>>

必须会的SQL语句(一) 创建数据库与删除数据库【www.129028.com金沙

1.创建数据库 drop命令用于删除数据库。 Create database 名称on primary{ name ='名称', filename ='c:xx名称.mdf', size = 10mb, --数据库...

详细>>

SqlServer提示“列前缀tempdb.无效: 未指定表名”问题解决方案www

在查询凭证、审核凭证时出现“列前缀tempdb.无效:未指定表名”的错误提示,怎么解决? 本文我们总结了几个在安装...

详细>>

【www.129028.com金沙】SQL Server中将查询结果转换为Json格式脚本分享

脚本源码: 复制代码 代码如下:SET ANSI_NULLS ONGOSETQUOTED_IDENTIFIER ONGOCREATEPROCEDURE[dbo].[SerializeJSON](@ParameterSQL ASVARCHAR(MAX)...

详细>>