使用RML utility可以得到结构化的分析结果，并以图形格式报表输出。
配套软件ReportViewer.exe

OVERVIEW

This project was done using SQL Server 2005 Enterprise Edition (with SP1 beta) using the BULK INSERT command with 60 parallel input files, each of approximately 16.67 GB. The best time I got was 50 minutes. Hewlett Packard was kind enough to loan me some time one night on the machine they use to do the TPC-C tests. It is an Itanium2 Superdome with 64 1.6GHZ 9M (Madison) CPUs. For the insert tests the system was equipped with 256GB of RAM. The HP SAN configuration is rated at 14GB per second throughput. You can find the specific configurations at http://tpc.org/tpcc/results/tpcc_result_detail.asp?id=103082701. (Note: The configurations at this site may be updated periodically).

我上个月有幸参加了在西雅图召开的PASS（Professional Association for SQL Server）峰会。我的同事Matt Masson做了个关于SQL Server 数据集成服务（Integration Services，SSIS）的讲座（下载），现场非常火爆，讲完后他被听众围住了个把小时。他的题目是Maximize Your SSIS Investment with Tuning Tricks and Tips，主要关于提升数据集成包（package）的性能。 他讲了四部分，其中第二部分深入浅出地介绍了SSIS数据流（Data flow）。我估计我国的用户会特别感兴趣这一块，因此在这里分享给你 :-)

使用聚集索引和非聚集索引的区别

 查看全文

SQL Server 事务日志可能会处于填满状态，这将阻止在数据库中继续进行 UPDATE、DELETE 或 INSERT 活动，包括 CHECKPOINT。通常为错误 1105：

Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

for MS SQL 2000.

http://support.microsoft.com/kb/298402/en-us/

本文使用一个实例来说明如何使用乐观锁定和悲观锁定来解决多用户并发的环境里，其他用户已经把你要修改的数据进行了修改而造成数据的不一致的问题。

本文来自: 编程入门网 http://www.bianceng.cn/data/SQLServer/jc/200712/6263.htm

将SQL Server数据表驻留内存是SQL Server提供的一项功能，在一般小型系统的开发过程中估计很少会涉及到。这里整理了相关文档资料，演示如何把SQL Server中一个表的所有数据都放入内存中，实现内存数据库，提高实时性。

本文来自: 编程入门网 http://www.bianceng.cn/data/SQLServer/jc/200804/8985.htm

中值有两种定义：
1、当组中包含奇数个元素时，我们将直接返回中间的值
2、当组中包含偶数个元素时，返回两个中间值的平均值

示例：

--构造Groups表
IF OBJECT_ID('dbo.Groups') IS NOT NULL
DROP TABLE dbo.Groups;
GO

CREATE TABLE dbo.Groups
(
groupid VARCHAR(10) NOT NULL,
memberid INT NOT NULL,
string VARCHAR(10) NOT NULL,
val INT NOT NULL,
PRIMARY KEY (groupid, memberid)
);

INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('a', 3, 'stra1', 6);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('a', 9, 'stra2', 7);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 2, 'strb1', 3);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 4, 'strb2', 7);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 5, 'strb3', 3);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('b', 9, 'strb4', 11);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 3, 'strc1',;
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 7, 'strc2', 10);
INSERT INTO dbo.Groups(groupid, memberid, string, val)
VALUES('c', 9, 'strc3', 12);
GO

当子查询中带TOP时，有连接条件和无连接条件结果是不一样的。

in和exists子句的结构也不一样。

看例子。

例如2000比1999的增长率为 0.10
2001比2000的增长率为 0.20
2002比2001的增长率为 0.25

设1999年的基数为10000,如何快速计算得到历年的值？

假设有表：
CREAET TABLE a(id int, name varchar(50))
INSERT INTO a SELECT 1, 'AAAA'
INSERT INTO a SELECT 2, 'BBBB'

存储过程：
CREAETE PROC usp_fetch_a
ASSELECT * FROM a

定时同步服务器上的数据的例子:--测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test
--1. 链接服务器上的表
create table [user](id int primary key,number varchar(4),name varchar(10))
go

--2. 本地服务器上的表
--本机的表,state说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user]
GO
create table [user](id int identity(1,1),number varchar(4),name varchar(10),state bit)
go

以下在局域网(本机操作)

递归存储过程中使用cursor可以用来向上遍历有向图，找出其直系父辈。

注意定义成local类型的cursor ！

declare @spt table (spid int)
declare @spid int , @sql varchar(255)

insert into @spt
select spid --, kpid, waittype, waittime, lastwaittype, dbid, status, cmd, hostname, loginame, program_name
from master.dbo.sysprocesses
where dbid = db_id('YourDBName')
and spid <> @@spid

declare cur_spid cursor for
select spid from @spt
open cur_spid

while @@FETCH_STATUS = 0
begin
fetch next from cur_spid into @spid
set @sql = 'kill '+ cast( @spid as varchar )
exec (@sql)
end

close cur_spid
deallocate cur_spid
go

微软官方文章，数据库服务器安全的论述。

http://www.microsoft.com/china/technet/security/guidance/secmod91.mspx

在多台SQL Server服务器之间进行事务处理时，我们用到分布式事务技术。分布式事务由 Microsoft 分布式事务处理协调器 (MS DTC)）这类事务管理器或其它支持 X/Open XA 分布式事务处理规范的事务管理器进行协调。Microsoft提供了MSDTC(Distributed Transaction Coordinator)服务支持分布式事务。

基本做法是，在两台SQL Server服务器上启动MSDTC服务，并在本地服务器上为另一台服务器建立Linked Server，这样本地服务器就可以访问另一台服务器。当对链接服务器执行分布式查询时，请对每个要查询的数据源指定完全合法的、由四部分组成的表名。这个由四部分组成的名称的格式应是：linked_server_name.catalog.schema.object_name

分布式事务通过两阶段提交(2PC)保证事务一致性。基本原理是，

准备阶段

当事务管理器收到提交请求时，它给该事务所涉及的所有资源管理器发送一个准备命令。然后，每个资源管理器将尽力使该事务持久，并且所有保存该事务日志映象的缓冲区将被刷新到磁盘中。当每个资源管理器完成准备阶段时，它会向事务管理器返回准备成功或准备失败的消息。

提交阶段

如果事务管理器收到所有资源管理器发来的准备成功消息，它将给每个资源管理器发送提交命令。然后资源管理器就可以完成提交。如果所有资源管理器都报告提交成功，那么事务管理器则向应用程序发送一个成功提示。如果有资源管理器报告准备失败，那么事务管理器将给每个资源管理器发送一个回滚命令，并向应用程序表示提交失败。

假设有这样一个实例：
create table a(dept_id int, user_id int)
insert into a select 1, 1
insert into a select 1, 2
insert into a select 1, 13
insert into a select 1, 4

insert into a select 2, 4
insert into a select 2, 1
insert into a select 2, 13

insert into a select 3, 1
insert into a select 3, 10
insert into a select 3, 4

select * from a

表a记录着dept和user的关系，2个dept中只要有2个以上的相同user，那么我们就认为这2个dept有关联，把这样的dept找出来，返回：

dept_id dept_id
1 2
1 3
2 3

（注意只保留一种对应情况，剔除掉重复情况）

实际案例：
通过人员比较来确定单位是否相同
其中只要有三个以上相同的人员，我们就认为是同一个单位了

在Windows Server 2003 上安装 MS SQL Server 2000 时，最后阶段安装程序在配置服务器时，出现“[Microsoft][ODBC 驱动程序管理器] 未发现数据源”，具体提示为：

###############################################################################


正在启动?

Chinese_PRC_CI_AS

-m -Q -T4022 -T3659

正在与服务?

driver={sql server};server=CCC-0DE3AN8Q6DV;UID=sa;PWD=;database=master

[Microsoft][ODBC 驱动程序管理器] 未发现数据源

driver={sql server};server=CCC-0DE3AN8Q6DV;UID=sa;PWD=;database=master

[Microsoft][ODBC 驱动程序管理器] 未发现数据源

driver={sql server};server=CCC-0DE3AN8Q6DV;UID=sa;PWD=;database=master

[Microsoft][ODBC 驱动程序管理器] 未发现数据源

SQL Server 配置?

###############################################################################

00:17:29 Process Exit Code: (-1)
00:17:37 安装程序配置服务器失败。参考服务器错误日志和 C:WINDOWSsqlstp.log 了解更多信息。
00:17:37 Action CleanUpInstall:
00:17:37 C:WINDOWSTEMPSqlSetupBinscm.exe -Silent 1 -Action 4 -Service SQLSERVERAGENT
00:17:37 Process Exit Code: (1060) 指定的服务未安装。

00:17:37 C:WINDOWSTEMPSqlSetupBinscm.exe -Silent 1 -Action 4 -Service MSSQLSERVER
00:17:37 Process Exit Code: (0)
00:17:37 StatsGenerate returned: 2
00:17:37 StatsGenerate (0x80000000,0x1,0xf00000,0x0,2052,303,0x0,0x1,0,0,0
00:17:37 StatsGenerate -1,Administrator)
00:17:37 Installation Failed.

这是由于ODBC for SQL Server driver出现了故障。

精确到日。

调用一个用户定义的SP，生成对阻塞的监控信息。

对“sp_blocker_pss80”存储过程的下列描述可捕获此信息：

一 触发器基础

在表中一系列的的纪录中，ID按照序列增长，如何找到空的ID呢？

通过2个函数CHARINDEX和PATINDEX以及通配符的灵活使用可达目的。

本文详细介绍了虚拟内存的设置和相关问题的解决方法。

摘自：西安浩敏网络有限公司

PIVOT和UNPIVOT关系运算符是SQL Server 2005提供的新增功能，因此，对升级到SQL Server 2005的数据库使用PIVOT和UNPIVOT时，数据库的兼容级别必须设置为90（可以使用sp_dbcmptlevel存储过程设置兼容级别）。

摘自 豆豆技术

http://www.ddvip.net/database/mssql/index2/71.htm

SQL Server 提供一些 Transact-SQL 语句和系统存储过程，用于对 SQL Server 实例进行特殊监视。当想要快速查看有关服务器性能和活动的信息时，可以使用这些语句。

除了使用企业管理器，我们还可以使用如下函数：

SERVERPROPERTY ( propertyname )
DATABASEPROPERTYEX( database , property )

数据库language不同，sql server对日期格式的选择也不同。

建立正确的索引，是提高系统性能的有效方法。正确的索引可能使查询效率提高1000倍，而无效的索引可能是浪费了数据库空间，甚至大大降低查询性能。

索引的效果是和检索数据的SQL语句、数据库的繁忙程度、数据库空间大小相关的，当数据库性能下降，需要通过调整索引达到提高性能的时候，DBA应该充分考虑到影响索引性能的这些因素来重新构造索引。合理的索引设计要建立在对各种查询的分析和预测上。

假设有个表table_tmp(id, name, info)，其数据中id值随有一定顺序但并不是按序列排列，现在想让其id列按序列重新生成。可以使用以下办法：

 查看全文

“唯一性约束”和“唯一性索引”是不同的。
一、 建立唯一性约束的语法，使用create table或者alter table
1. 在字段级约束定义
-- 命名
create table tmp_table
(
 a int constraint pk_tmp_table_a primary key,
 b varchar(10) constraint uq_tmp_table_b unique
)

-- 不命名
create table tmp_table
(
 a int primary key,
 b varchar(10) unique
)

2. 在表约束定义
-- 命名
create table tmp_table
(
 a int,
 b varchar(10),
 constraint pk_tmp_table_a primary key(a),
 constraint uq_tmp_table_b unique(b ASC)
)

-- 不命名
create table tmp_table
(
 a int,
 b varchar(10),
 primary key(a),
 unique(b ASC)
)

二、 建立唯一性索引的语法，使用create index

-- 必须命名
CREATE UNIQUE NONCLUSTERED INDEX xak_tmp_table_b ON tmp_table(b DESC)

--不指定[CLUSTERED | NONCLUSTERED]，缺省为NONCLUSTERED(非聚集)

三、当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性索引

这是以上脚本建立的约束：

这是索引：

四、题外话
表级约束中可以指定列的排序顺序(col_name ASC | DESC)，但不能指定列的NULL约束；
字段级约束中可以指定列的NULL约束，但不能指定列的排序顺序；

8、以最低权限帐户身份进行开发

通常情况下，保护开发服务器安全的最佳方法，就好似它正在生产环境中运行那样对它进行保护。您离这个目标越接近，那么就可以越自信于您开发的代码可以在一个安全的生产环境中正常运行。
    在开发过程中，大家都着迷于使用具有 sysadmin 或 dbo SQL Server 权限的帐户，直到部署之前才转换为一个权限更低的帐户。使用这种方法存在着一个问题：将设计人员的权限集还原为最低的所需权限集与在开发应用程序过程中编写这些权限集相比，前者要困难得多。
         鉴于部署应用程序之前您要决定可以取消哪些权限，所以请不要使用 SQL sysadmin 帐户开发 T-SQL 代码。如果使用 SQL sysadmin 帐户，可能会造成这样的结果，即应用程序会以比所需权限更多的特权帐户运行。因此，开发时请改为使用具有最低权限的帐户。
         使用这样的帐户进行开发时，您会逐渐地升高授予的特定权限，以 EXEC（执行）一些必需的存储过程、从某些表进行 SELECT（选择）等。请编写这些 GRANT 语句，以便可以将同样的最低权限轻松部署到生产环境中，而不会出现任何基于猜测的操作。
         这种理念同样适用于测试。执行临时测试以及结构更加复杂的测试时，所使用帐户拥有的权限集和用户权限应该与在生产环境中所使用帐户拥有的权限集和用户权限完全相同。
在开发过程中使用最低权限帐户的另一个优点在于，您可以避免不小心编写出需要危险权限或过高权限的代码。例如，假设您需要在 T-SQL 中与第三方 COM 组件进行交互。为此，一种方法是发送一个 SQL 批处理命令，它直接调用 sp_OACreate 和 sp_OAMethod 来操纵该 COM 对象。在应用程序使用 sysadmin 帐户连接 SQL Server 的开发环境中，上述方法效果很好。但是，当您尝试将已经开发完成的应用程序准备用于生产部署时，您就会发现如果使用权限较低的帐户，那么该方法不会奏效。为了让该应用程序能够使用非 sysadmin 帐户在生产环境中正常运行，您必须针对 sp_OACreate 显式授予 EXECUTE 权限。请考虑一下，如果某个用户最终找到了一个方法，可以使用该应用程序登录执行任意代码，并利用此权限针对 SQL Server 实例化一个类似Scripting.FileSystemObject 的 COM 对象，将会产生怎样的安全隐患？

9、T-SQL脚本安全

有一些 T-SQL 命令和扩展存储过程，它们具有自己独特的安全考虑事项。

（1）sp_OACreate 及其相关的系统过程系列

例如 sp_OAMethod、sp_OAGetProperty 等。通过授予应用程序登录直接访问这些过程的权限，会带来该安全问题。为了避免此问题的发生，请绝对不要编写直接调用 sp_OA 过程的应用程序代码，而要将对这些过程的所有引用都打包在您自己的 T-SQL 存储过程中，并只授予访问这些包装存储过程的权限。另外，请不要允许应用程序代码将 COM 对象或方法的名称作为可由包装过程无条件调用的字符串进行传递。

（2） xp_cmdshell

这个系统存储过程可以运行任何可执行文件或系统命令。由于一些很显然的原因，xp_cmdshell 上的 EXEC 权限默认情况下仅为 sysadmin 用户，必须显示地为其他用户授予该权限。如果您需要应用程序在 SQL Server 上运行某个特定的命令或实用程序，则请注意，不要在应用程序中构建一个 xp_cmdshell 直接访问的相关内容。这样的风险与直接访问 sp_OACreate 的风险相似。一旦为某个帐户授予了 xp_cmdshell 的 EXEC 权限，该帐户不但能够执行您希望其访问的特定命令，而且能够执行成百上千个操作系统命令和其他可执行文件。与 sp_OACreate 相似，始终将 xp_cmdshell 调用打包在另一个存储过程中，避免直接在 xp_cmdshell 上授予 EXECUTE 权限。

您还应该避免将任何用户提供的字符串参数或者应用程序提供的字符串参数与将要通过 xp_cmdshell 执行的命令进行串联。如果无法达到上述要求，则必须了解，有一个专门针对 xp_cmdshell 的潜在的代码注入式攻击（至少在 SQL Server 中）。以下面的存储过程为例：

 CREATE PROCEDURE usp_DoFileCopy @filename varchar(255)
        AS
        DECLARE @cmd varchar (8000)
        SET @cmd = 'copy srcshare' + @filename + ' destshare'
        EXEC master.dbo.xp_cmdshell @cmd
        GO
        GRANT EXEC ON usp_DoFileCopy TO myapplogin

通过将 xp_cmdshell 调用打包在您自己的存储过程中并只针对该 usp_DoFileCopy 存储过程授予 EXEC 权限，您已经阻止了用户直接调用 xp_cmdshell 以执行任意命令。然而，以下面的 shell 命令插入为例：

EXEC usp_DoFileCopy @filename = ' & del /S /Q destshare & '

使用这个 @filename 参数，将要执行的字符串为 copy srcshare & del /S /Q destshare & destshare。和号 (&) 被操作系统命令解释器处理为命令分隔符，因此该字符串将被 CMD.EXE 视为三个互不相关的命令。其中第二个命令 (del /S /Q destshare) 将尝试删除 destshare 中的所有文件。通过利用该存储过程中某个 shell 命令插入漏洞，用户仍然可以执行任意操作系统命令。针对此类攻击进行防御的一种方法是将命令字符串打包在一个 T-SQL 函数中，如下所示。这个用户定义的函数会添加 shell 转义符 (^)，对出现的任何 & 字符或其他具有特殊意义的字符进行转义。
----------------------------------------------------------------------
-- Function: fn_escapecmdshellstring
-- Description: Returns an escaped version of a given string
--              with carets ('^') added in front of all the special
--              command shell symbols.
-- Parameter: @command_string nvarchar(4000)
----------------------------------------------------------------------
CREATE FUNCTION dbo.fn_escapecmdshellstring (
  @command_string nvarchar(4000)) RETURNS nvarchar(4000) AS
BEGIN
  DECLARE @escaped_command_string nvarchar(4000),
    @curr_char nvarchar(1),
    @curr_char_index int   
  SELECT @escaped_command_string = N'',
    @curr_char = N'',
    @curr_char_index = 1
  WHILE @curr_char_index <= LEN (@command_string)
  BEGIN
    SELECT @curr_char = SUBSTRING (@command_string, @curr_char_index, 1)
    IF @curr_char IN ('%', '<', '>', '|', '&', '(', ')', '^', '"')
    BEGIN
      SELECT @escaped_command_string = @escaped_command_string + N'^'
    END
    SELECT @escaped_command_string = @escaped_command_string + @curr_char
    SELECT @curr_char_index = @curr_char_index + 1
  END
  RETURN @escaped_command_string
END

下面是消除了命令 shell 插入漏洞之后的存储过程： 
    CREATE PROCEDURE usp_DoFileCopy @filename varchar(255) AS
    DECLARE @cmd varchar (8000)
    SET @cmd = 'copy srcshare' 
      + dbo.fn_escapecmdshellstring (@filename) 
      + ' destshare'
    EXEC master.dbo.xp_cmdshell @cmd

（3）允许执行动态构建的查询的命令

EXEC() 和 sp_executesql。转义对数据库引擎可能具有特殊意义的字符序列。在 T-SQL 中，注入式攻击中最常用的两个字符串为单引号字符 (') 和注释字符序列 (--)。 SQL 注入式攻击的风险并不是避免动态 SQL 的唯一理由。任何通过这些命令动态执行的查询都将在当前用户的安全上下文中运行，而不是在该存储过程所有者的上下文中运行。这就意味着，使用动态 SQL 可能会强制您授予用户直接访问基表的权限。以下面的存储过程为例：

CREATE PROC dbo.usp_RetrieveMyUserInfo
    AS
    SELECT * FROM UserInfo WHERE UserName = USER_NAME()

此过程会限制当前用户，使其无法查看其他任何用户的数据。但是，如果此过程中的 SELECT 语句是通过动态 EXEC() 或通过 sp_executesql 执行的，您则必须授予用户对 UserInfo 表的直接 SELECT 权限，这是因为这个动态执行的查询是在当前用户的安全上下文中运行的。如果用户能够直接登录服务器，他们则可以使用此权限跳过该存储过程提供的行级安全，查看所有用户的数据。

小结

（1）总而言之，下面的建议将有助于您开发在 SQL Server 中安全运行的 T-SQL 代码：

（2）去掉当前用户以下权限：

--脚本如下：
USE MASTER
GO

DECLARE @curr_user VARCHAR(128), @access_ctrl_sql VARCHAR(128)

SET @curr_user = USER_NAME()

-- 1. xp_cmdshell
SET @access_ctrl_sql = 'DENY EXECUTE ON xp_cmdshell TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 2. Sp_OACreate
SET @access_ctrl_sql = 'DENY EXECUTE ON Sp_OACreate TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 3. Sp_OADestroy
SET @access_ctrl_sql = 'DENY EXECUTE ON Sp_OADestroy TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 4. Sp_OAGetErrorInfo
SET @access_ctrl_sql = 'DENY EXECUTE ON Sp_OAGetErrorInfo TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 5. Sp_OAGetProperty
SET @access_ctrl_sql = 'DENY EXECUTE ON Sp_OAGetProperty  TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 6. Sp_OAMethod
SET @access_ctrl_sql = 'DENY EXECUTE ON Sp_OAMethod TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 7. Sp_OASetProperty
SET @access_ctrl_sql = 'DENY EXECUTE ON Sp_OASetProperty TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 8. Sp_OAStop
SET @access_ctrl_sql = 'DENY EXECUTE ON Sp_OAStop TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 9. xp_readmail
SET @access_ctrl_sql = 'DENY EXECUTE ON xp_readmail TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 10. xp_regread
SET @access_ctrl_sql = 'DENY EXECUTE ON xp_regread TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

-- 11. sp_makewebtask
SET @access_ctrl_sql = 'DENY EXECUTE ON sp_makewebtask TO ' + @curr_user
EXEC(@access_ctrl_sql)
IF @@ERROR <> 0
BEGIN
 GOTO ERROR_HANDLE
END

SUCCESS_HANDLE:
BEGIN 
 print '为用户 ' + @CURR_USER + ' 收回重要的存储过程执行权限成功'
 RETURN
END

ERROR_HANDLE:
BEGIN
 print '为用户 ' + @CURR_USER + ' 收回重要的存储过程执行权限失败'
 RETURN
END

GO

数据库是电子商务、金融以及ERP系统的基础，通常都保存着重要的商业伙伴和客户信息。大多数企业、组织以及政府部门的电子数据都保存在各种数据库中，他们用这些数据库保存一些个人资料，比如员工薪水、个人资料等等。数据库服务器还掌握着敏感的金融数据，包括交易记录、商业事务和帐号数据，战略上的或者专业的信息，比如专利和工程数据，甚至市场计划等等应该保护起来防止竞争者和其他非法者获取的资料。数据完整性和合法存取会受到很多方面的安全威胁，包括密码策略、系统后门、数据库操作以及本身的安全方案。但是数据库通常没有象操作系统和网络这样在安全性上受到重视。 
   微软的SQL Server是一种广泛使用的数据库，很多电子商务网站、企业内部信息化平台等都是基于SQL Server上的。广泛SQL Server数据库又是属于“端口”型的数据库，这就表示任何人都能够用分析工具试图连接到数据库上，从而绕过操作系统的安全机制，进而闯入系统、破坏和窃取数据资料，甚至破坏整个系统。
       在进行SQL Server 2000数据库的安全配置之前，首先你必须对操作系统进行安全配置，保证你的操作系统处于安全状态。然后对你要使用的操作数据库软件（程序）进行必要的安全审核，比如对ASP、PHP等脚本，这是很多基于数据库的WEB应用常出现的安全隐患，对于脚本主要是一个过滤问题，需要过滤一些类似 , ‘ ; @ / 等字符，防止破坏者构造恶意的SQL语句。接着，安装SQL Server2000后请打上补丁sp1以及最新的sp2、sp3。

下载地址是：http://www.microsoft.com/sql/downloads/2000/sp1.asp　和　http://www.microsoft.com/sql/downloads/2000/sp2.asp
        在做完上面三步基础之后，我们再来讨论SQL Server的安全策略。

1、使用安全的密码策略

我们把密码策略摆在所有安全配置的第一步，请注意，很多数据库帐号的密码过于简单，这跟系统密码过于简单是一个道理。对于sa更应该注意，同时不要让sa帐号的密码写于应用程序或者脚本中。健壮的密码是安全的第一步！ 
        SQL Server2000安装的时候，如果是使用混合模式，那么强烈推荐输入sa的密码。
        同时养成定期修改密码的好习惯。数据库管理员应该定期查看是否有不符合密码要求的登录帐号。比如使用下面的SQL语句：

Use master
        Select name,Password from syslogins where isntname = 0 and password is null

-- isntname = 0表示是SQL Server登录

2、使用安全的帐号策略

由于SQL Server不能更改sa用户名称，也不能删除这个超级用户，所以，我们必须对这个帐号进行最强的保护，当然，包括使用一个非常强壮的密码，最好不要在数据库应用中使用sa帐号，只有当没有其它方法登录到 SQL Server 实例（例如，当其它系统管理员不可用或忘记了密码）时才使用 sa。建议数据库管理员新建立一个拥有与sa一样权限的超级用户来管理数据库。安全的帐号策略还包括不要让管理员权限的帐号泛滥。
    SQL Server的认证模式有Windows身份认证和混合身份认证两种。如果数据库管理员不希望操作系统管理员来通过操作系统登陆来接触数据库的话，可以在帐号管理中把系统帐号“BUILTINAdministrators”删除。不过这样做的结果是一旦sa帐号忘记密码的话，就没有办法来恢复了。 
         根据实际需要分配帐号，并赋予仅仅能够满足应用要求和需要的权限。比如，只要查询功能的，那么就使用一个简单的public帐号能够select就可以了。

3、加强数据库日志的记录

通过联接可以告诉你谁在试图进入SQL Server，所以监控联接是保证数据库安全的一个好方法。对于一个大型的运行中的SQL Server，可能会有太多的链接数据需要监控，但是监控那些失败了的链接确有必要，因为他们可能代表企图进入的一些尝试。你可将那些失败的链接登录上企业管理器，1. 右击服务器，选择属性(Properties)。 2. 点击安全(Security)标签，在Audit Level之下选择Failure。 3. 停止和重新启动服务器，以获得检查的开始。
   也可将其中的审核级别选定为全部，这样在数据库系统和操作系统日志里面，就详细记录了所有帐号的登录事件。
   定期查看SQL Server日志检查是否有可疑的登录事件发生，或者使用DOS命令。
       findstr /C:"登录" “d:Microsoft SQL ServerMSSQLLOG*.* “

4、管理扩展存储过程

对存储过程进行大手术，并且对帐号调用扩展存储过程的权限要慎重。其实在多数应用中根本用不到多少系统的存储过程，而SQL Server的这么多系统存储过程只是用来适应广大用户需求的，所以请删除不必要的存储过程，因为有些系统的存储过程能很容易地被人利用起来提升权限或进行破坏。
       如果你不需要扩展存储过程xp_cmdshell请把它去掉。使用这个SQL语句：

use master
        sp_dropextendedproc 'xp_cmdshell'

xp_cmdshell是进入操作系统的最佳捷径，是数据库留给操作系统的一个大后门。
   对于 NT 和 WIN2000，当用户不是 sysadmin 组的成员时，xp_cmdshell 将模拟使用 xp_sqlagent_proxy_account 指定的 SQL Server 代理程序的代理帐户。如果代理帐户不能用，则 xp_cmdshell 将失败。所以即使有一个帐户是master数据库的db_owner，也不能执行这个存储过程。 
   如果我们有一个能执行xp_cmdshell的数据库帐号，比如是空口令的sa帐号。那么我们可以执行这样的命令：
   exec xp_cmdshell 'net user gooduser 123456 /add'
   exec xp_cmdshell 'net localgroup administrators gooduser /add' 

上面两次调用就在系统的管理员组中添加了一个用户：gooduser
    当我们获得数据库的sa管理员帐号后，就应该可以完全控制这个机器了。
    之后，删除此用户:
    net user gooduser /delete
    net localgroup administrators gooduser /delete
    可见数据库安全的重要性。

   如果你需要这个存储过程，请用这个语句也可以恢复过来。
        sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'

如果你不需要请丢弃OLE自动存储过程（会造成管理器中的某些特征不能使用），这些过程包括如下：
        Sp_OACreate，Sp_OADestroy，Sp_OAGetErrorInfo，Sp_OAGetProperty，Sp_OAMethod，Sp_OASetProperty，Sp_OAStop

去掉不需要的注册表访问的存储过程，注册表存储过程甚至能够读出操作系统管理员的密码来，如下： 
        Xp_regaddmultistring，Xp_regdeletekey，Xp_regdeletevalue，Xp_regenumvalues，Xp_regread，Xp_regremovemultistring，Xp_regwrite

还有一些其他的扩展存储过程，你也最好检查检查。这在T_SQL脚本安全中会更详细提到。

5、使用协议加密

SQL Server 2000使用的Tabular Data Stream协议来进行网络数据交换，如果不加密的话，所有的网络传输都是明文的，包括密码、数据库内容等等，这是一个很大的安全威胁。能被人在网络中截获到他们需要的东西，包括数据库帐号和密码。所以，在条件容许情况下，最好使用SSL来加密协议，当然，你需要一个证书来支持。

6、端口限制
   (1)不要让人随便探测到你的TCP/IP端口
         默认情况下，SQL Server使用1433端口监听，很多人都说SQL Server配置的时候要把这个端口改变，这样别人就不能很容易地知道使用的什么端口了。可惜，通过微软未公开的1434端口的UDP探测可以很容易知道SQL Server使用的什么TCP/IP端口了（请参考《深入探索SQL Server网络连接的安全问题》）。
         不过微软还是考虑到了这个问题，毕竟公开而且开放的端口会引起不必要的麻烦。在实例属性中选择TCP/IP协议的属性。选择隐藏 SQL Server 实例。如果隐藏了 SQL Server 实例，则将禁止对试图枚举网络上现有的 SQL Server 实例的客户端所发出的广播作出响应。这样，别人就不能用1434来探测你的TCP/IP端口了（除非用Port Scan）。 
        (2)修改TCP/IP使用的端口
         请在上一步配置的基础上，更改原默认的1433端口。在实例属性中选择网络配置中的TCP/IP协议的属性，将TCP/IP使用的默认端口变为其他端口。
   (3)拒绝来自1434端口的探测
         由于1434端口探测没有限制，能够被别人探测到一些数据库信息，而且还可能遭到DOS攻击让数据库服务器的CPU负荷增大，所以对Windows 2000操作系统来说，在IPSec过滤拒绝掉1434端口的UDP通讯，可以尽可能地隐藏你的SQL Server。  

7、对网络连接进行IP限制

SQL Server 2000数据库系统本身没有提供网络连接的安全解决办法，但是Windows 2000提供了这样的安全机制。使用操作系统自己的IPSec可以实现IP数据包的安全性。请对IP连接进行限制，只保证自己的IP能够访问，也拒绝其他IP进行的端口连接，把来自网络上的安全威胁进行有效的控制。
       关于IPSec的使用请参看：http://www.microsoft.com/china/technet/security/ipsecloc.asp
      上面主要介绍的一些SQL Server的安全配置，经过以上的配置，可以让SQL Server本身具备足够的安全防范能力。当然，更主要的还是要加强内部的安全控制和管理员的安全培训，而且安全性问题是一个长期的解决过程，还需要以后进行更多的安全维护。  

一 安全体系
登录、安全账户（用户）、角色和组是 Microsoft® SQL Server™ 2000 安全机制的基础。连接到 SQL Server 的用户必须使用特定的登录标识符 (ID) 标识自己。因此，用户只能查看经授权可以查看的表和视图，并且只能执行经授权可以执行的存储过程和管理功能。在SQL Server中，login（登录）是用于进入服务器时进行身份识别的，每个user和login相关联，保证能进入服务器，每个user属于各个role，拥有相应的数据库权限，整个体系如图：

二 系统存储过程和命令
1、存储过程：
登录： sp_grantlogin, sp_revokelogin, sp_denylogin
sp_addlogin, sp_droplogin
sp_helplogins
用户： sp_grantdbaccess, sp_revokedbaccess
sp_adduser, sp_dropuser (这两个SP是为了向后兼容，请使用前2个代替)
sp_helpuser
角色：
服务器角色 sp_addsrvrolemember, sp_dropsrvrolemember
数据库角色 sp_addrole, sp_droprole
sp_addapprole, sp_dropapprole
sp_helprole
sp_addrolemember, sp_droprolemember
sp_helprolemember
2、命令：grant, revoke, deny
三 固定角色
1、固定服务器角色：

2、固定数据库角色：

四 权限
包括两种类型的权限，即对象权限和语句权限：

注意：REFERENCE充许在GRANT、DENY、REVOKE语句中向有外键参照表中插入一行数据。

五 实用脚本
在sql server中，登录到某数据库必须有两个条件：
1、为SQL Server建立一个login；
2、为该login在数据库中建立user，给该用户关联role
/* DBA 的新增登录授权脚本*/
USE my_database
GO
--1. 新建登录
IF NOT EXISTS(SELECT 1 FROM master.dbo.syslogins WHERE name = 'wh_login' AND loginname = N'wh_login')
EXEC sp_addlogin 'wh_login', '123', 'plm25'
GO
--2. 新建用户
IF NOT EXISTS(SELECT * FROM sysusers WHERE name = N'lewis_user')
EXEC sp_grantdbaccess 'wh_login', 'lewis_user'
GO
--3. 分配权限
--语句权限
GRANT CREATE TABLE TO lewis_user
--对象权限
GRANT SELECT ON fb_app_module TO lewis_user
--授予所有权限
GRANT ALL TO lewis_user
--4. 授予角色
EXEC sp_addrolemember N'db_datareader', N'lewis_user'
EXEC sp_addrolemember N'db_datawriter', N'lewis_user'
EXEC sp_addrolemember N'db_accessadmin', N'lewis_user'
EXEC sp_addrolemember N'db_ddladmin', N'lewis_user' --建立存储过程权限
--5. 更改登录的默认数据库
EXEC sp_defaultdb @loginame = N'login', @defdb = N'default_DB_name'
--6. 更改登录的默认语言
EXEC sp_defaultlanguage @loginame = N'login', @language = N' french'

/*DBA 的删除登录授权脚本*/
--1. 拒绝授权
REVOKE SELECT ON [dbo].[fb_app_module] TO [lewis_user] CASCADE
REVOKE CREATE TABLE TO lewis_user
EXEC sp_droprolemember N'db_datareader', N'lewis_user'
--不能更改 public 角色的成员资格。也不需要移除。
--2. 删除用户
IF EXISTS(SELECT * FROM sysusers WHERE name = N'lewis')
EXEC sp_revokedbaccess 'lewis_user'
GO
--3. 删除登录
IF EXISTS(SELECT 1 FROM master.dbo.syslogins WHERE name = 'wh_login' AND loginname = N'wh_login')
EXEC sp_droplogin 'wh_login'
GO
/*DBA 的查看授权脚本*/
--1、查看服务器的登录
sp_helplogins 查看login和user
SELECT * FROM master.dbo.syslogins WHERE loginname = @login_name
--2、查看数据库的用户
sp_helpuser

SELECT * FROM dbo.sysusers where issqlrole = 1 /*数据库角色*/
SELECT * FROM dbo.sysusers where islogin = 1 /*登录*/
SELECT * FROM dbo.sysusers where issqluser = 1 /*SQL Server用户*/
SELECT * FROM dbo.sysusers where isntname = 1 /*NT用户*/
SELECT * FROM dbo.sysusers where isapprole = 1 /*服务器角色*/
--3、查看用户有什么权限和角色????
sp_helprolemember 'db_datareader'
--4、用户拥有的数据库中的对象????
select a.*, b.name, b.type, c.name
from syspermissions a
inner join sysobjects b on a.id = b.id
inner join sysusers c on a.grantee = c.uid
where c.name = 'UserName'

--5、查看用户、登录、角色等综合信息
select DISTINCT username = o.name,
loginname = (case when (o.sid = 0x00) then NULL
else l.loginname end),
rolegroup = user_name(o.gid),
userID = o.uid,
o.hasdbaccess,
o.uid
from dbo.sysusers o left join master.dbo.syslogins l on l.sid = o.sid
where ((o.issqlrole != 1 and o.isapprole != 1) or (o.sid = 0x00) and o.hasdbaccess = 1)
and o.isaliased != 1 and (o.name = N'@your_user_name')
六 示例-- ===================================================
-- Procedure Name: fb_plm_access_control
-- Function : set PLM Suite application user
-- Failure return: 1
-- Success return: 0
--
-- Parameters :
-- @login_name : application user name, create a account if the user does not exists, otherwise remain it.
-- @password : for the exists user, if the password is null, then remain its old password,
-- : for the new user, it can not be null.
-- @db_name : PLM Suite database name
-- ===================================================
IF EXISTS (select * from sysobjects where id = object_id(N'fb_plm_access_control') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE fb_plm_access_control
GO

CREATE PROCEDURE fb_plm_access_control
(
@login_name NVARCHAR(100),
@password NVARCHAR(100) = null,
@db_name NVARCHAR(100)
)
--WITH ENCRYPTION
AS
DECLARE @result INT
DECLARE @user_exists INT
DECLARE @execute_sp_role NVARCHAR(100)
DECLARE @create_table_role NVARCHAR(100)
DECLARE @object_name NVARCHAR(120)
DECLARE @object_type NVARCHAR(10)
DECLARE @sql VARCHAR(200)
DECLARE @error_msg VARCHAR(100)
BEGIN
-- check input paramters.
IF (@login_name IS NULL) OR (LTRIM(@login_name) = '') OR (lower(@login_name) = 'sa')
BEGIN
RAISERROR ('The login_name can not be null or sa.' , 16, 1) WITH NOWAIT
RETURN 1
END
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
BEGIN
SET @error_msg = 'The database ' + @db_name + ' does not exits.'
RAISERROR (@error_msg, 16, 1) WITH NOWAIT
RETURN 1
END

-- if user does not exits, create it
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @login_name)
BEGIN
SET @user_exists = 0
IF (@password IS NULL) OR (@password = '')
BEGIN
RAISERROR ('For new application user, the password can not be null.' , 16, 1) WITH NOWAIT
RETURN 1
END
EXEC sp_addlogin @loginame = @login_name, @passwd = @password, @defdb = @db_name, @deflanguage = @@language
IF @@ERROR <> 0
RETURN 1
END
ELSE
BEGIN
SET @user_exists = 1
IF (@password IS NOT NULL) AND (LTRIM(@password)<>'')
BEGIN
EXEC sp_password @old = null, @new = @password, @loginame = @login_name
IF @@ERROR <> 0
RETURN 1
END
END

-- create the execute sp role if it does not exists
SET @execute_sp_role = N'db_procexecutor'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @execute_sp_role AND issqlrole = 1)
BEGIN
EXEC sp_addrole @rolename = @execute_sp_role
IF @@ERROR <> 0
RETURN 1
END

-- create the create table role if it does not exists
SET @create_table_role = N'db_createtable'
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = @create_table_role AND issqlrole = 1)
BEGIN
EXEC sp_addrole @rolename = @create_table_role
IF @@ERROR <> 0
RETURN 1
END

-- grant privilege to the role
DECLARE cur_sp_fun CURSOR
FAST_FORWARD
FOR
SELECT name, xtype
FROM sysobjects
WHERE xtype in (N'P', N'FN', N'IF', N'TF')
AND uid = (select uid from sysusers where name = USER_NAME())
OPEN cur_sp_fun
FETCH NEXT FROM cur_sp_fun INTO @object_name, @object_type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @object_type = N'P'
BEGIN
SET @sql='GRANT EXECUTE ON '+@object_name+' TO '+@execute_sp_role
END
IF @object_type = N'FN'
BEGIN
SET @sql='GRANT REFENENCES ON '+@object_name+' TO '+@execute_sp_role
SET @sql=' GRANT EXECUTE ON '+@object_name+' TO '+@execute_sp_role
END
IF @object_type IN (N'IF',N'TF')
BEGIN
SET @sql='GRANT SELECT ON '+@object_name+' TO '+@execute_sp_role
END
EXEC (@sql)
IF @@ERROR <> 0
BEGIN
SET @result = 1
BREAK
END
FETCH NEXT FROM cur_sp_fun INTO @object_name, @object_type
END
CLOSE cur_sp_fun
DEALLOCATE cur_sp_fun
IF @result = 1
RETURN 1

SET @sql = 'GRANT CREATE TABLE TO ' + @create_table_role
EXEC (@sql)
IF @@ERROR <> 0
RETURN 1

-- grant access database privilege to application user
IF @user_exists = 1
EXEC sp_revokedbaccess @name_in_db = @login_name
IF @@ERROR <> 0
RETURN 1

EXEC sp_grantdbaccess @loginame = @login_name, @name_in_db = @login_name
IF @@ERROR <> 0
RETURN 1
-- grant role to application user
EXEC sp_addrolemember @rolename = N'db_datareader', @membername = @login_name
IF @@ERROR <> 0
RETURN 1

EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @login_name
IF @@ERROR <> 0
RETURN 1

EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @login_name
IF @@ERROR <> 0
RETURN 1

EXEC sp_addrolemember @rolename = @create_table_role, @membername = @login_name
IF @@ERROR <> 0
RETURN 1

EXEC sp_addrolemember @rolename = @execute_sp_role, @membername = @login_name
IF @@ERROR <> 0
RETURN 1

RETURN 0
END
GO

具体步骤如下：
1、将服务器【身份验证】属性设置成【混合模式】（window与sql身份验证）
2、在【控制面板】中打开【服务】将【MSSQLSERVER】服务打开【属性】，选择【登录】页面，将登录身份设置成服务器本地帐号和该帐号密码，如administrator，密码123；
3、重新启动sqlserver服务，此时的服务指的是【SQL服务管理器】中的SQL SERVER服务；
假设【帐号】设置为administrator
此时达到的效果是：服务器本地帐号administrator与客户端上的administrator（并且该帐号的密码要与服务器密码相同）可以通过【查询分析器】进行调试；
如果想让【其他帐号】也能够调试，那么还需要如下设置：
1、在【服务器】上运行dcomcnfg.exe；
2、在【默认安全机制】中【默认访问权限】右边点击【编辑默认值】选择允许调试的帐号类型，如users用户类型，sample帐号有包含users组；
3、重新启动sqlserver服务；
3、在客户端上创建与服务帐号密码一样的用户，如sample；
做到这步就可以通过查询分析器的调试功能进行单步调试了。

注：第二步更改“启动服务帐户”，在第一次登录之前，必须更改用户密码。
不然，event log:
以当前密码登录的尝试因下列错误而宣告失败:
在第一次登录之前，必须更改用户密码。
  

相同点：

它们都返回插入到IDENTITY列中的值

不同点：

A.      @@IDENTITY不受作用域（存储过程、触发器、函数或批处理）的限制。返回最后一个插入的IDENTITY值。如果在程序中不同的作用域插入的表不同，那么返回最后一个表插入的IDENTITY值。例如：作用域A（存储过程）在表T1上插入IDENTITY列，而T1上的触发器会在T2上插入IDENTITY列。则用@@IDENTITY返回T2上插入的IDENTITY值。

B.      SCOPE_IDENTITY()受作用域的限制，只返回当前作用域中的最后一个IDENTITY值。上例中，如果在存储过程中使用SCOPE_IDENTITY()，则返回T1的IDENTITY值。

C.      IDENT_CURRENT()不受作用域的限制，调用它时必须提供表示表名的字符型参数，你可以得到你想要的任何表的最后一个IDENTITY值，即使你的代码里没有插入动作。例如：IDENT_CURRENT('T1')；

经验：根据不同的使用场合，选择不同的函数。如果在写存储过程时，向表中新增了一条数据，需要返回该IDENTITY值，则使用SCOPE_IDENTITY()，因为它和当前会话相关联，不会返回一个你不需要的值。如果你仅仅想得到某个IDENTITY列的最后一个值，则使用IDENT_CURRENT()最方便。

一. 为什么要引入锁

多个用户同时对数据库的并发操作时会带来以下数据不一致的问题:

丢失更新
A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果,比如订票系统

脏读
A用户修改了数据,随后B用户又读出该数据,但A用户因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致

不可重复读
A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致

并发控制的主要方法是封锁,锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

二 锁的分类

锁的类别有两种分法：

1. 从数据库系统的角度来看：分为独占锁（即排它锁），共享锁和更新锁

MS-SQL Server 使用以下资源锁模式。

锁模式 描述
共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。
更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
意向锁 用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
架构锁 在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
大容量更新 (BU) 向表中大容量复制数据并指定了 TABLOCK 提示时使用。

共享锁
共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。

更新锁
更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。

排它锁
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。

意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

锁模式 描述
意向共享 (IS) 通过在各资源上放置 S 锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。
意向排它 (IX) 通过在各资源上放置 X 锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX 是 IS 的超集。
与意向排它共享 (SIX) 通过在各资源上放置 IX 锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发 IS 锁。例如，表的 SIX 锁在表上放置一个 SIX 锁（允许并发 IS 锁），在当前所修改页上放置 IX 锁（在已修改行上放置 X 锁）。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。

独占锁：只允许进行锁定操作的程序使用，其他任何对他的操作均不会被接受。执行数据更新命令时，SQL Server会自动使用独占锁。当对象上有其他锁存在时，无法对其加独占锁。
共享锁：共享锁锁定的资源可以被其他用户读取，但其他用户无法修改它，在执行Select时，SQL Server会对对象加共享锁。
更新锁：当SQL Server准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server确定要进行更新数据操作时，他会自动将更新锁换为独占锁，当对象上有其他锁存在时，无法对其加更新锁。

2. 从程序员的角度看：分为乐观锁和悲观锁。
乐观锁：完全依靠数据库来管理锁的工作。
悲观锁：程序员自己管理数据或对象上的锁处理。

MS-SQLSERVER 使用锁在多个同时在数据库内执行修改的用户间实现悲观并发控制

三 锁的粒度
锁粒度是被封锁目标的大小,封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小

SQL Server支持的锁粒度可以分为为行、页、键、键范围、索引、表或数据库获取锁

资源 描述
RID 行标识符。用于单独锁定表中的一行。
键 索引中的行锁。用于保护可串行事务中的键范围。
页 8 千字节 (KB) 的数据页或索引页。
扩展盘区 相邻的八个数据页或索引页构成的一组。
表 包括所有数据和索引在内的整个表。
DB 数据库。

四 锁定时间的长短

锁保持的时间长度为保护所请求级别上的资源所需的时间长度。

用于保护读取操作的共享锁的保持时间取决于事务隔离级别。采用 READ COMMITTED 的默认事务隔离级别时，只在读取页的期间内控制共享锁。在扫描中，直到在扫描内的下一页上获取锁时才释放锁。如果指定 HOLDLOCK 提示或者将事务隔离级别设置为 REPEATABLE READ 或 SERIALIZABLE，则直到事务结束才释放锁。

根据为游标设置的并发选项，游标可以获取共享模式的滚动锁以保护提取。当需要滚动锁时，直到下一次提取或关闭游标（以先发生者为准）时才释放滚动锁。但是，如果指定 HOLDLOCK，则直到事务结束才释放滚动锁。

用于保护更新的排它锁将直到事务结束才释放。
如果一个连接试图获取一个锁，而该锁与另一个连接所控制的锁冲突，则试图获取锁的连接将一直阻塞到：

将冲突锁释放而且连接获取了所请求的锁。

连接的超时间隔已到期。默认情况下没有超时间隔，但是一些应用程序设置超时间隔以防止无限期等待

五 SQL Server 中锁的自定义

1 处理死锁和设置死锁优先级

死锁就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待

可以使用SET DEADLOCK_PRIORITY控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。

2 处理超时和设置锁超时持续时间。

@@LOCK_TIMEOUT 返回当前会话的当前锁超时设置，单位为毫秒

SET LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于 LOCK_TIMEOUT 设置时，系统将自动取消阻塞的语句，并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息

示例
下例将锁超时期限设置为 1,800 毫秒。
SET LOCK_TIMEOUT 1800

3) 设置事务隔离级别。

4 ) 对 SELECT、INSERT、UPDATE 和 DELETE 语句使用表级锁定提示。

5) 配置索引的锁定粒度
可以使用 sp_indexoption 系统存储过程来设置用于索引的锁定粒度

六 查看锁的信息

1 执行 EXEC SP_LOCK 报告有关锁的信息
2 查询分析器中按Ctrl+2可以看到锁的信息

七 使用注意事项

如何避免死锁
1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
3 优化程序，检查并避免死锁现象出现；
4 .对所有的脚本和SP都要仔细测试，在正是版本之前。
5 所有的SP都要有错误处理（通过@error）
6 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁

解决问题 如何对行 表 数据库加锁

八 几个有关锁的问题

1 如何锁一个表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 锁定数据库的一个表

SELECT * FROM table WITH (HOLDLOCK)

加锁语句：
sybase:
update 表 set col1=col1 where 1=0 ;
MSSQL:
select col1 from 表 (tablockx) where 1=0 ;
oracle:
LOCK TABLE 表 IN EXCLUSIVE MODE ；
加锁后其它人不可操作，直到加锁用户解锁，用commit或rollback解锁

几个例子帮助大家加深印象
设table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

1）排它锁
新建两个连接
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二个连接中执行以下语句
begin tran
select * from table1
where B='b2'
commit tran

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

2）共享锁
在第一个连接中执行以下语句
begin tran
select * from table1 holdlock -holdlock人为加锁
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran

在第二个连接中执行以下语句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran

若同时执行上述两个语句，则第二个连接中的select查询可以执行
而update必须等待第一个事务释放共享锁转为排它锁后才能执行 即要等待30秒

3）死锁
增设table2(D,E)
D E
d1 e1
d2 e2
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran

在第二个连接中执行以下语句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran

同时执行，系统会检测出死锁，并中止进程

补充一点:
Sql Server2000支持的表级锁定提示

HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别

NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别

PAGLOCK 在使用一个表锁的地方用多个页锁

READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁

ROWLOCK 强制使用行锁

TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表

UPLOCK 强制在读表时使用更新而不用共享锁

应用程序锁:
应用程序锁就是客户端代码生成的锁，而不是sql server本身生成的锁

处理应用程序锁的两个过程

sp_getapplock 锁定应用程序资源

sp_releaseapplock 为应用程序资源解锁

注意: 锁定数据库的一个表的区别

SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除

SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除

一 使用外联接

以前在Oracle中用=(+)和(+)=来进行左外联接和右外联接；后来用SQL Server时用*=和=*进行外连接左外联接和右外联接；
现在决定用SQL-92的标准方法：[OUTER] JOIN
OUTER是可以省略的。

• LEFT OUTER JOIN 或 LEFT JOIN        表示左外联接
• RIGHT OUTER JOIN 或 RIGHT JOIN   表示左外联接
• FULL OUTER JOIN 或 FULL JOIN        表示左外联接

外联接的意思不用多说，我们都懂，但是JOIN到底怎么用呢？没有找到很好的资料，只能从例子中学习了：
1、这个例子也许没有实际意义，只是为了说明问题：
CREATE TABLE orders(order_id int, firm_id int, p_id int)
CREATE TABLE firms (firm_id int, f_name int)
CREATE TABLE products(p_id int, p_name int)

select a.order_id, b.f_name, c.p_name
from orders a left join firms b on a.firm_id = b.firm_id
     left join products c on a.p_id = c.p_id
说明：orders表是主表，先和从表firms进行左联接，再和从表products进行左联接。
     判断是外联接中的主表还是从表主要看from从句中各个表在LEFT JOIN或RIGHT JOIN两边的位置：LEFT JOIN左边的表是主表，RIGHT JOIN右边的表是主表；
     ON表达了两个表连接的条件，一般外联接是等值联接，不等值联接意义不大；

在多个表的连接中，一个表既可以做主表又同时可以做从表，为了说明这个问题，我们修改以上SQL为：
select a.order_id, b.f_name, c.p_name
from orders a left join firms b on a.firm_id = b.firm_id
     right join products c on a.order_id = c.p_id
这个SQL没有什么意义，但从中可以看出a表既是b的主表又是c的从表；到底怎么用，还是要根据实际情况来决定是左联接还是右联接；

那天，看到了这样一个例子：
create table tab1 (c1 int, c2 int, c3 int)
create table tab2 (c1 int, c2 int, c3 int)
create table tab3 (c1 int, c2 int, c3 int)
create table tab4 (c1 int, c2 int, c3 int)

SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
    left OUTER JOIN tab3 right OUTER JOIN tab4
        ON tab3.c1 = tab4.c1
        ON tab2.c3 = tab4.c3

这种用法还真少见，具体怎么个意思，还在理解中...
我把它改写成：
SELECT *
FROM tab1 left JOIN tab2 ON tab1.c3 = tab2.c3
    LEFT OUTER JOIN tab4 ON tab2.c3 = tab4.c3
 RIGHT OUTER JOIN tab3  ON tab3.c1 = tab4.c1
也许它们是一个意思。

我发现加个括号，看的更清楚一些（它是个嵌套）

SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
    left OUTER JOIN
   (tab3 right OUTER JOIN tab4
    ON tab3.c1 = tab4.c1)
        ON tab2.c3 = tab4.c3

二 外联接中 "ON + AND" 与 "ON + WHERE" 的区别

1、on条件是外联接时在生成临时表时使用的联结条件，不论从表是确定值还是NULL，主表所有的值都会出现；
   如果再加上and条件：
       如果and条件引用的是主表的列，则对结果毫无影响，主表的所有纪录依然会全部出现；
       如果and条件引用的是从表的列，则不符合条件的从表纪录显示NULL；

2、where条件是在临时表生成后，再对临时表进行过滤的条件。临时表中的所有纪录都受影响，不符合条件的纪录被过滤出结果集；

3、示例：
   select a.module_id, a.name, b.module_name
   from fb_autocoding a left join fb_app_module b
   on a.module_id = b.module_id
   and b.module_internal_label <> 'LO';

   select a.module_id, a.name, b.module_name
   from fb_autocoding a left join fb_app_module b
   on a.module_id = b.module_id
   where b.module_internal_label <> 'LO';

三 其他Join运算

     merge join：在处理其他联结之前，先把相关两个表联结在一起；
     hash join：把一个表join到已经被执行过join的结果上；

     用括号改变join的顺序：
     select catalog.item, catalog.item_color, product.item, color.color_name
     from catalog full outer join (product cross join color)
     on catalog.item = product.item
     and catalog.item_color = color.color_name;

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified.

ALTER TABLE 只允许添加可包含空值或指定了 DEFAULT 定义的列。

如果：
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = N'tab_test') AND NOT EXISTS(SELECT 1 FROM SYSOBJECTS A, SYSCOLUMNS B WHERE B.ID = A.ID AND B.NAME = N'col_test' and a.name = N'tab_test')
alter table tab_test
add col_test smallint NOT NULL
GO
将会收到错误信息。

要增加非空列怎么办呢？
第1种办法：在add column时指定列有default
alter table tab_test
add col_test NOT NULL CONSTRAINT DFtab_test_col_test DEFAULT 1

第2种办法：先给表增加个table_constraint default,再alter column
alter table tab_test
add CONSTRAINT DFtab_test_col_test DEFAULT 1 for col_testalter table tab_test
alter column col_test int NOT NULL

第3种办法：只好先增加空列，在修改为非空啦：
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = N'tab_test') AND NOT EXISTS(SELECT 1 FROM SYSOBJECTS A, SYSCOLUMNS B WHERE B.ID = A.ID AND B.NAME = N'col_test' and a.name = N'tab_test')
alter table tab_test
add col_test smallint
GO

alter table tab_test
alter column col_test smallint not null
GO

当在表tab(id, name...)中生成一条新纪录，其他字段都与原纪录一致，但name字段必须按name, Copy of name, Copy (2) of name, Copy (3) of name的规律变化，用下面的SP可以轻松达到此目的。

它是通用的，可以针对任何表的varchar(char)类型的字段，生成新名称。

-- SP_generate_copy_object_name
-- ============================================
-- Procedure Name : SP_generate_copy_object_name
-- Function   : generate a object name that is copied
-- Failure return : ''
-- Success return : the new object name
--
-- Parameters  :
-- @table_name  :
-- @field_name  :
-- @field_value  : the value of the field
-- @field_length : the length of the field
--
-- Description:
-- original field name 'ProductReport' --> 'Copy of ProductReport' --> 'Copy (2) of ProductReport' --> 'Copy (3) of ProductReport'... --> 'Copy (9999) of ProductReport'...
-- ============================================

IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'SP_generate_copy_object_name') and objectproperty(id, N'IsProcedure') = 1)
 DROP PROCEDURE SP_generate_copy_object_name
GO

DROP PROCEDURE SP_generate_copy_object_name
GO
CREATE PROCEDURE SP_generate_copy_object_name
 (
 @table_name   varchar(128),
 @field_name   varchar(128),
 @field_value  VARCHAR(128),
 @field_length  INT = 128,
 @new_object_name varchar(128) OUTPUT
 )
--WITH ENCRYPTION
AS
DECLARE
 @exists    INT,
 @tmp_field_value VARCHAR(128),
 @sql     NVARCHAR(500),
 @i      INT
BEGIN
 
 -- Check input parameter field_value
 IF (@field_value IS NULL) OR (LTRIM(@field_value) = '')
 BEGIN
  RETURN 1
 END
 
 --  you need at least use the first five characters of object name
 
 -- 1. search 'Copy of ' + @field_value
 SET @tmp_field_value = @field_value
 IF len(@tmp_field_value) > @field_length - 8
 BEGIN
  SET @tmp_field_value = SUBSTRING(@tmp_field_value, 1, @field_length - 11) + '...'
 END
 ELSE
 BEGIN
  SET @tmp_field_value = @tmp_field_value
 END
 
 SET @sql = N'drop table ##tab_tmp create table ##tab_tmp(exist_flag int) insert into ##tab_tmp(exist_flag) SELECT count(1) FROM ' + @table_name + ' WHERE ' + @field_name + ' LIKE ''Copy of ' + @tmp_field_value + ''''
 EXEC SP_EXECUTESQL @sql

 select @exists = exist_flag from ##tab_tmp
 IF @exists = 0
 BEGIN
  SET @new_object_name = 'Copy of ' + @tmp_field_value
  RETURN 0
 END

 -- 2. search N'Copy (%) of ' + @field_value
 SET @i = 2
 WHILE ( @i < 9999 )
 BEGIN
  SET @tmp_field_value = @field_value
  IF len(@tmp_field_value) > @field_length - (11 + LEN(@i))
  BEGIN
   SET @tmp_field_value = SUBSTRING( @tmp_field_value, 1, @field_length - (11 + LEN(@i) + 3) ) + '...'
  END
  ELSE
  BEGIN
   SET @tmp_field_value = @tmp_field_value
  END
  SET @sql = N'drop table ##tab_tmp create table ##tab_tmp(exist_flag int) insert into ##tab_tmp(exist_flag) SELECT count(1) FROM ' + @table_name + ' WHERE ' + @field_name + ' LIKE ''Copy (' + CONVERT(VARCHAR(4), @i) + ') of ' + @tmp_field_value + ''''
  EXEC sp_executesql @sql

  select @exists = exist_flag from ##tab_tmp
  IF (@exists = 0)
  BEGIN
   SET @new_object_name = 'Copy (' + CONVERT(VARCHAR(4), @i) + ') of ' + @tmp_field_value
   RETURN 0
  END
  SET @i = @i + 1
 END
 
 -- if error occur return null
 RETURN 1
 
END

一、语法

FOREIGN KEY 约束主要目的是控制存储在外键表中的数据，但它还可以控制对主键表中数据的修改。FOREIGN KEY 约束并不仅仅只可以与另一表的 PRIMARY KEY 约束相链接，它还可以定义为引用另一表的 UNIQUE 约束。FOREIGN KEY 约束不允许空值，但是，如果任何组合 FOREIGN KEY 约束的列包含空值，则将跳过 FOREIGN KEY 约束的校验。FOREIGN KEY 可以引用同一表中的其它列（自引用）。一个表最多可包含 253 个 FOREIGN KEY 约束。每个表在其 FOREIGN KEY 约束中最多可以引用 253 个不同的表。
列级 FOREIGN KEY 约束放在列说明部分，表级 FOREIGN KEY 约束放在全部列说明之后，用","和列说明分开。

列级 FOREIGN KEY 约束的 REFERENCES 子句仅能列出一个引用列，且该列必须与定义约束的列具有相同的数据类型。

表级 FOREIGN KEY 约束的 REFERENCES 子句中引用列的数目必须与约束列列表中的列数相同。每个引用列的数据类型也必须与列表中相应列的数据类型相同。

1) 新建
作为表定义的一部分在创建表时创建。一个表可以有多个 FOREIGN KEY 约束。
< column_constraint > ::= [ CONSTRAINT constraint_name ]
    { [ NULL | NOT NULL ]
        | [ { PRIMARY KEY | UNIQUE }
            [ CLUSTERED | NONCLUSTERED ]
            [ WITH FILLFACTOR = fillfactor ]
            [ON {filegroup | DEFAULT} ] ]
        ]
        | [ [ FOREIGN KEY ]
            REFERENCES ref_table [ ( ref_column ) ]
            [ ON DELETE { CASCADE | NO ACTION } ]
            [ ON UPDATE { CASCADE | NO ACTION } ]
            [ NOT FOR REPLICATION ]
        ]
        | CHECK [ NOT FOR REPLICATION ]
        ( logical_expression )
    }

< table_constraint > ::= [ CONSTRAINT constraint_name ]
    { [ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        { ( column [ ASC | DESC ] [ ,...n ] ) }
        [ WITH FILLFACTOR = fillfactor ]
        [ ON { filegroup | DEFAULT } ]
    ]
    | FOREIGN KEY
        [ ( column [ ,...n ] ) ]
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
        [ ON DELETE { CASCADE | NO ACTION } ]
        [ ON UPDATE { CASCADE | NO ACTION } ]
        [ NOT FOR REPLICATION ]
    | CHECK [ NOT FOR REPLICATION ]
        ( search_conditions )
    }

2) 删除
ALTER TABLE table_name DROP CONSTRAINT constraint_name

3)修改
必须首先删除已有的 FOREIGN KEY 约束，然后再通过新定义重新创建。
ALTER TABLE table_name DROP CONSTRAINT constraint_name
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (外键表的列) REFERENCES 主键表(主键表的列)

4) 主键表
显示关系中主表的名称，后面是构成主键的列。

5) 外键表
显示关系中外键表的名称，后面是构成外键的列。

二、说明

1) 创建中检查现存数据
在外键表上添加关系时，对数据库中已存在的数据应用约束。

2) 对 INSERT 和 UPDATE 强制关系
对在外键表中插入、删除或更新的数据应用该约束。如果外键表中存在匹配的行，还将禁止删除主表中的行。

3) 对复制强制关系
在将外键表复制到其它数据库时应用该约束。NOT FOR REPLICATION

4) 级联更新相关的字段
无论何时更新主键值，都指示 DBMS 自动更新该关系的外键值。ON UPDATE {CASCADE | NO ACTION}
如果指定 CASCADE，则在父表中更新被引用行时，也将在引用表中更新引用行；
如果指定 NO ACTION，SQL Server 将产生一个错误并回滚父表中的行更新操作。

5) 级联删除相关的字段
无论何时删除主表的被引用行，都指示 DBMS 自动删除外键表的行。ON DELETE {CASCADE | NO ACTION} 
如果指定 CASCADE，则从父表中删除被引用行时，也将从引用表中删除引用行；
如果指定 NO ACTION，SQL Server 将产生一个错误并回滚父表中的行删除操作。

一、 试验归类
测试SQL:
------------------------------------------------------------
drop table a
create table a(a varchar(2))

insert into a values('a')
insert into a values(N'a')
insert into a values('深圳')
insert into a values(N'深圳')
select a, len(a), datalength(a) from a

drop table #a
create table a(a varchar(2))

insert into #a values('a')
insert into #a values(N'a')
insert into #a values('深圳')
insert into #a values(N'深圳')
select a, len(a), datalength(a) from #a

------------------------------------------------------------
drop table a
create table a(a varchar(8000))

insert into a select REPLICATE('a', 8000)
insert into a select REPLICATE('深', 8000)
insert into a select REPLICATE(N'a', 8000)
insert into a select REPLICATE(N'深', 8000)
select a, len(a), datalength(a) from a

1. 字符集是支持双字节的字符集如中文字符集（Collation name为Chinese_PRC_CI_AS）
 
 <1>. 定义varchar(2)
 
  (1) 正式表
   总结：在中文字符集下，定义varchar(x),
    不论使用不使用N''，英文字符都占1个字节,即可以存x个英文字符;
      不论使用不使用N''，中文字符都占2个字节,即可以存(x / 2)个中文，select结果为汉字本身，不是乱码；
      
  (2) 临时表
   总结：在中文字符集下，定义varchar(x), 
    和正式表表现一样；
  
 <2>. 定义nvarchar(2)
 
  (1) 正式表
   总结：在中文字符集下，定义nvarchar(x),
    不论使用不使用N''，英文字符都占2个字节,即可以存x个英文字符;
      不论使用不使用N''，中文字符都占2个字节,即可以存x个中文，select结果为汉字本身，不是乱码；
      
  (2) 临时表
   总结：在中文字符集下，定义nvarchar(x), 
    和正式表表现一样；
    
 <3>. 类型为varchar时，长度 x 和 datalength()对应，都指字节大小；
    英文len() = datalength();
    中文len() = datalength() / 2;
    
    类型为nvarchar时，长度 x 和 len()对应，都指字符长度； 
   
2. 字符集是支持单字节的字符集如拉丁字符集（Collation name为Latin1_General_CI_AS）
  
 <1>. 定义varchar(2)
 
  (1) 正式表
   总结：在英文字符集下，定义varchar(x),
    不论使用不使用N''，英文字符都占1个字节,即可以存x个英文字符;
      不论使用不使用N''，中文字符都占1个字节,即可以存x个中文，但只保存前半截中文编码，所以select结果为乱码；
      （特殊：如果使用N''，此时插入的字符数最大为4000）
      英文和中文 len() = datalength()；
      
  (2) 临时表
   总结：在英文字符集下，定义varchar(x),
    不论使用不使用N''，英文字符都占1个字节,即可以存x个英文字符;
    不使用N''时，中文占1个字节，可以存x个汉字，但都只存入汉字前半截字符编码，显示为乱码；
    使用N''时，中文占2个字节，只可以存 x/2 个汉字，没有乱码，取出仍为汉字,说明在英文字符集下通过使用N''是可以保存汉字的；
    
    除用N''保存的中文外，其余英文和中文 len() = datalength()；
    用N''保存的中文字符len() = datalength() / 2；
    
 <2>. 定义nvarchar(2)
 
  (1) 正式表
   总结：在英文字符集下，定义nvarchar(x),
    不论使用不使用N''，英文字符都占2个字节,即可以存x个英文字符;（注意每个字符比varchar用的空间大）
      不论使用不使用N''，中文字符都占2个字节,即可以存x个中文字符，
      但不使用N''只保存前半截中文编码，所以select结果为乱码；
      使用N''则保存和取出都为汉字本身；
      
  (2) 临时表
   总结：在英文字符集下，定义nvarchar(x), 
    和正式表表现相同；
    
 <3>. 类型为varchar时，长度 x 和 datalength()对应，都指字节大小；
    （临时表中N''中文字符长度比较特殊；）
    类型为nvarchar时，长度 x 和 len()对应，都指字符长度； 
 
二、 使用归类
 抛开不常用的临时表不谈，只看正式表，再加上varchar和nvarchar类型的最大长度，得到以下经验：
 <1> 最大长度问题
  1. 在中文字符集下使用varchar，最大长度可定义8000,这个8000是指字节数（datalength()），即最大可以保存8000个英文字符，4000个中文字符；
   特殊：若存入字符N'a'，则最大能保存4000个字符，但其所占空间为4000字节；
  2. 在中文字符集下使用nvarchar，最大长度可定义4000,这个4000是指字符个数（len()），即最大可以保存4000个英文字符，4000个中文字符；
 
  3. 在英文字符集下使用varchar，最大长度可定义8000,这个8000是指字节数（datalength()），由于中文英文都保存为1字节，故最大可以保存8000个英文、中文字符；
  4. 在英文字符集下使用nvarchar，最大长度可定义4000,这个4000是指字符个数（len()），即最大可以保存4000个英文字符，4000个中文字符；
 <2> 文字显示问题
  1. N''要和数据类型nvarchar, nchar一起使用，如果对varchar, char字段类型强制使用N''，则会产生一些特殊现象，甚至无法控制； 
  2. 在英文字符集下，想要保存特殊符号字符、中文等双字节字符，在定义表结构时要使用nvarchar或者nchar，在保存时要用N''；
  3. 在中文字符集下，数据库系统缺省已经可以保存特殊符号字符、中文等双字节字符。即使用不使用N''，都按双字节处理。但为了统一期间建议：
   在定义表结构时如果使用nvarchar或者nchar，在保存时要用N''，
   在定义表结构时如果使用varchar和char，此时不要使用N''操作；
  4. SUBSTRING ( expression , start , length )
   length：是一个整数，指定子串的长度（要返回的字符数或字节数）。
   中文字符集中按字符数取；
   英文字符集中，char, varchar按字节数取，nchar, nvarchar按字符数取；

 
三、 其他参考
 使用 Unicode 数据
  unicode代码页、排序规则、SQL Server 排序规则基础知识、Windows 排序规则排序样式、选择 SQL 排序规则
 DBCS 字符

修复identity 类型字段数据的跳跃（译）

内容提要

1、引言
2、dbcc object_atts
3、下载'sp_identity' 存储过程
4、使用sp_identity显示使用identity 类型字段的表
5 使用sp_identity显示当前表的 identity 数字
6、使用sp_identity 修改表的 identity数字

1引言
在sybase 中，有一个使用未公开的dbcc 命令 ，即 dbcc object_atts 快速修复identity 跳跃的方法，因为 dbcc

object_atts 比较难于使用,而存储过程 sp_identity 更容易完成你的问题
警告：本文的方法没有出现在sybase 发布的文档中，并且没有被 sybase 支持，使用与否取决定你自己，如果出现问题，不能从
sybase 技术支持那里得到帮助

2、dbcc object_atts
dbcc object_atts 命令提供了 一个访问 OAM 页的后门，在 OMA 中，保存了表的 identity 计数器。
不幸的是，这个命令在文档中几乎没有任何说明，并且很难使用，其语法如下：
dbcc object_atts (table_name, 0, subcommand, [ new_value ] )
第一个参数 ：表名，第二个参数 0，
第三个参数： get ---显示 保存在 OMA 中的 identity 值
或put ---设置 OMA 中的 identity 值，设置的具体数据为 第四个参数数据
第四个参数 ：新设置的数

主要的问题是 dbcc object_atts 显示的数据为 16字节的 16进制字符，你必须自己解释为10进制，并且，如何解释依赖于
identity 列的长度，同样，设置它也是。在实际环境中，很少作为首选。
幸运的是，有一个存储过程 sp_identity 能够处理编码与解码， 并且给出所有含 identity 列的表的情况

下载 'sp_identity'
sp_identity 只支持 11。0 以后版本，下载文件中包含两个版本，一个用于 ASE 11.9/11.5/11.0 的叫

,sp_identity.119.sql，另外一个用于ASE 12.0及以后版本(sp_identity.120.sql)

4、使用sp_identity显示使用identity 类型字段的表
sp_identity 有几个不同的功能，不带任何参数运行，只显示当前数据库中 包含 identity 列的表，
以及最大可能的 identity 跳跃

1> sp_identity
2> go
Tables with an identity column in database '':

Owner.Table.Column datatype Maximum Identity Gap
------------------------------- -------------------------------
dbo.invoices.inv_nr numeric(18) 500000000000000 (burn)
dbo.small_gap_tab.a numeric(30) 10 (identity_gap)
dbo.my_table.n numeric(6) 500 (burn)

(burn) 表示 identity列的 一次取数由系统参数"identity burning set factor" 决定，而
(identity_gap) 表示 该表有自定义的identity_gap 属性

5 使用sp_identity显示当前表的 identity 数字
确定当前特定表的 OAM 页的identity 值，必须按照以下三步过程：
首先 打开跟踪标志 3604 (运行 dbcc traceon(3604) )
运行 指定特定表名运行 sp_identity

1> sp_identity invoices
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)

Reading identity value from OAM page...
object_atts:get:return value=1
0207E20C: 0001c6bf 52634001 00000000 00000000 ....Rc@.........
0207E21C: .
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.

这个 16字节的 16进制字符串是 OAM 中保存的 identity 值 （0001c6bf 52634001 00000000 00000000）

再次运行 sp_identity ,并且 指定这个字符串参数

1> sp_identity invoices, "0001c6bf 52634001 00000000 0000000"
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)

Decoded identity value on OAM page = 500000000000001
(hex=0001c6bf52634001)

现在，我们得到并解码了 OAM 中的 identity 值，但它意味着什么呢？
首先，如果你用 "with nowait" 停止服务器，这个 identity 列将跳跃多少。而不是表中数据的下一个。
而是保存在 OAM 中的数据加上 identity 种子设顶数(在 ASE 12.0, 为identity_gap 选项)
另外，当你运行 ASE 12.0, 可以通过运行 dbcc listoam看到这个数据

6、使用sp_identity 修改表的 identity数字
使用 sp_identity ， OAM 中identity 的 数字可以被设置为任意数. 这个需要如下步骤：
1、确认你要设置的数据（查看表中identity 的数据应该到多少,比如 10033 ）
2、正常停止服务器
3 重新启动服务器，以单用户模式，或者不让用户访问
4、 运行 sp_identity table_name, null, new_value ;
1> sp_identity invoices, null, 10032
2> go
Table = invoices (id=1804533462)
Identity column = a numeric(18)
Max. Identity Gap = 500000000000000
("identity burning set factor" = 5000 = 0.05%)

object_atts:put:return value=1
DBCC execution completed. If DBCC printed error messages,
contact a user with System Administrator (SA) role.

Identity value on OAM page has been set to 10032
(hex=0x00000000000027300000000000000000)
You should now do a 'shutdown with nowait' immediately.
After restarting the server, the value assigned to the
next row inserted into 'invoices' will be 10033.

5 然后立即运行 'shutdown with nowait' ，这样 表的下一个 identity 数字是 10033
6 重新正常启动sybase 服务

说明：如果你应用了 identity 数据类型，并且要求identity 列保持连续，并且数据量很大，那么，用这个方法需要的时间可能比较短
否则不推荐使用。

下面是SP_IDENTITY.120.SQL，用于 12.0 以后版本
/*
* SP_IDENTITY.120.SQL - version for ASE 12.0 or later
* (when running ASE 11.9/11.5/11.0, install SP_IDENTITY.119.SQL instead)
*
* Description
* ===========
* This file contains the stored procedure 'sp_identity', which performs some
* useful functions w.r.t. identity columns:
*
* - displays all tables with an identity column in the current database
* - displays the identity value stored on a table's OAM page
* - resets the identity value stored on a table's OAM page
*
* For details and background, see http://www.sypron.nl/idfix.html .
*
*
* Installation
* ============
* Execute this script using "isql", using a login having both "sa_role"
* and "sso_role".
* The stored procedure will be created in the sybsystemprocs database.
*
*
* Notes
* =====
* - Traceflag 3604 should be enabled before running sp_identity for a
* specific table ("dbcc traceon(3604)"); if omitted, you won't see
* any output.
*
* - To run sp_identity for a specific table, sa_role and sybase_ts_role
* are required.
*
* - sp_identity was successfully tested on ASE versions 11.0.3, 11.5,
* 11.9.2 and 12.0 on various platforms.
*
* - sp_identity will not work on ASE 11.0 because this version
* does not support the 'case' expression.
*
* - sp_identity was not tested on a 64-bit ASE version.
*
*
* Revision History
* ================
* Version 1.0 17-Dec-2000 First version
* Version 1.1 Oct-2002 Handle identity_gap=NULL (thanks to Alan Cooper),
* and some small improvements for installation
* Version 1.2 Apr-2003 Improved installation
*
*
* Copyright Note & Disclaimer :
* =============================
* This software is provided "as is"; there is no warranty of any kind.
* While this software is believed to work accurately, it may not work
* correctly and/or reliably in a production environment. In no event shall
* Rob Verschoor and/or Sypron B.V. be liable for any damages resulting
* from the use of this software.
* You are allowed to use this software free of charge for your own
* professional, non-commercial purposes.
* You are not allowed to sell or bundle this software or use it for any
* other commercial purpose without prior written permission from
* Rob Verschoor/Sypron B.V.
* You may (re)distribute only unaltered copies of this software, which
* must include this copyright note, as well as the copyright note in
* the header of each stored procedure.
*
* Note: All trademarks are acknowledged.
*
* Please send any comments, bugs, suggestions etc. to the below email
* address.
*
* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V.
* P.O.Box 10695
* 2501 HR Den Haag
* The Netherlands
*
* Email: rob@sypron.nl
* WWW : http://www.sypron.nl/
*----------------------------------------------------------------------------
*/

set nocount on
go
set flushmessage on
go

use sybsystemprocs
go

-- we need to be at ASE 12.0 or later; if not, abort this script
if isnull(object_id("master.dbo.sysqueryplans"),99) >= 99
begin
print ""
print ""
print "********************************************"
print "********************************************"
print " This script is for ASE 12.0 or later."
print " Please install SP_IDENTITY.119.SQL instead."
print "********************************************"
print "********************************************"
print " "
print " "
print ""
set background on -- terminate this script now
end
go

print ""
print "Installing 'sp_identity'..."
print ""
go

if object_id("sp_identity_help") <> NULL
begin
drop proc sp_identity_help
end
go

create proc sp_identity_help
/* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */
as
begin
print " "
print " Usage: sp_identity table_name, ""hex-string-from-OAM-page"", new-identity-value "
print " Notes: "
print " - specifying only parameter 1 retrieves the current identity value for"
print " that table from the OAM page as a hexadecimal string."
print " - specifying this hexadecimal string as the second parameter (in quotes)"
print " will decode the hex value to a numeric value."
print " - specifying a numeric value for the third parameter will set that"
print " value as the new identity value for this table."
print " - parameters 2 and 3 cannot be specified together: one of them must be NULL."
print " - ""dbcc traceon(3604)"" must be run before using option 2 or 3"
print " "
print " Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V."
print " See http://www.sypron.nl/idfix.html for background information & updates."
print " "
end
go

grant execute on sp_identity_help to public
dump tran sybsystemprocs with truncate_only
go

if object_id("sp_identity") <> NULL
begin
drop proc sp_identity
end
go

create proc sp_identity
/* Copyright (c) 2000-2002 Rob Verschoor/Sypron B.V. */
@p0 varchar(50) = NULL, -- table name
@p1 varchar(50) = NULL, -- hex value to decode
@p2 numeric(38) = NULL -- new value to set
as
begin
set nocount on
declare @n numeric(38), @j numeric(38), @n256 numeric(3), @n10 numeric(38)
declare @i int, @max int, @idlen int, @idlenb int, @ib int, @len int
declare @p0_id int, @b int, @b1 binary(1), @lsb int
declare @idgap int, @idburn int, @maxidgap numeric(38), @max1 int, @max2 int
declare @idburnpct numeric(5,2), @v int, @ntab int
declare @c2 char(2), @doampg int, @indid int, @dbname varchar(32)
declare @vc50 varchar(50), @colname varchar(32), @vb16 varbinary(16)

select @dbname = db_name()

-- check version
select @v = 0
if exists (select * from sysobjects
where name = "sysqueryplans" and type = "S")
begin
select @v = 12
end

if @p0 = '?'
begin
exec sp_identity_help
return 0
end

-- numeric values
select @n256 = 256
select @n10 = 10

-- id burning set factor
select @idburn = value from master.dbo.syscurconfigs where config=141
select @idburnpct = convert(numeric(5,2), @idburn * 0.00001)

-- figure out msb/lsb
select @lsb = 0
if substring(convert(binary(4), 1), 1 ,1) = 0x01 select @lsb = 1
if substring(convert(binary(4), 1), 2 ,1) = 0x01 select @lsb = 2
if substring(convert(binary(4), 1), 3 ,1) = 0x01 select @lsb = 3
if substring(convert(binary(4), 1), 4 ,1) = 0x01 select @lsb = 4

-- get all tables containing identity columns in this database
select id = so.id, owner = su.name, uid = so.uid, so.name, si.doampg, si.indid, idgap =

isnull(si.identitygap,0),
colname = sc.name, sc.prec, maxgap = convert(numeric(38), ceiling((@idburn * 0.0000001) *

power(@n10,prec)))
into #id
from sysindexes si, syscolumns sc, sysobjects so, sysusers su
where si.indid < 2
and so.type = "U"
and sc.status & 128 = 128
and so.id = sc.id
and so.id = si.id
and so.uid = su.uid
select @ntab = @@rowcount

set arithabort numeric_truncation off

if @p0 = null
begin
-- display all tables with identity columns in the current DB
if @ntab = 0
begin
print " There are no tables with an identity column in database '%1!'", @dbname
return 0
end
print " Tables with an identity column in database '%1!':", @dbname
print " "
select @max1 = max(char_length(owner + "." + name + "." + colname)) + 12 from #id
select @max2 = max(char_length(convert(varchar(38), maxgap))) + 7 from #id

if @max1 <= 52 and @max2 <= 24
begin
select convert(varchar(52), owner + "." + name + "." + colname + " numeric(" +

convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(24), case when idgap = 0 then convert(varchar(38),maxgap) + "

(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"

from #id
order by name
end
else
if @max1 <= 32 and @max2 <= 44
begin
select convert(varchar(32), owner + "." + name + "." + colname + " numeric(" +

convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(44), case when idgap = 0 then convert(varchar(38),maxgap) + "

(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"

from #id
order by name
end
else
begin
select convert(varchar(78), owner + "." + name + "." + colname + " numeric(" +

convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(50), case when idgap = 0 then convert(varchar(38),maxgap) + "

(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end

print " "
print " Legend:"
print " (burn) : gap size is determined by ""identity burning set factor"" "
if @v = 12
begin
print " (identity_gap) : gap size is determined by the ""identity_gap"" setting"
end
print " "
print " Current value for ""identity burning set factor"" = %1! (=%2!%%)", @idburn, @idburnpct

return 0
end

if charindex("sa_role", show_role()) = 0
begin
print "You must have 'sa_role' to run this procedure."
return -1
end

if charindex("sybase_ts_role", show_role()) = 0
begin
print "You must have 'sybase_ts_role' to run this procedure."
return -1
end

-- get some info on the object
select @p0_id = object_id(@p0)
if @p0_id = NULL
begin
print " Error: '%1!' is not a user table.", @p0
return -1
end

if @p0 not like "%.%"
begin
select @ntab = count(*) from sysobjects where name = @p0 and type = "U"

if @ntab > 1
begin
print " %1! tables named '%2!' exist in this database:", @ntab, @p0
print " "
select owner + "." + name "owner.table_name"
from #id where name = @p0 order by uid
print " "
print " Specify 'owner.table_name' to identify the table."
return -1
end
end

select @doampg = doampg, @indid = indid, @idgap = idgap
from #id where id = @p0_id

if not exists (select 1 from syscolumns where id = @p0_id and status & 128 = 128)
begin
print " Error: Table '%1!' does not have an identity column", @p0
return -1
end

-- retrieve the length of the numeric identity column as declared when the
-- table was created
select @len = prec, @colname = name
from syscolumns where id = @p0_id and status & 128 = 128
if @@rowcount = 0
begin
print " Error: Could not retrieve length of identity column for table '%1!'", @p0
return -1
end

select @idlen = @len -- column precision (positions)
select @idlenb = (@idlen+1-((@idlen+5)/12)-(@idlen/12))/2 -- column length (bytes)

print " "
print " Table = %1! (id=%2!)", @p0, @p0_id
print " Identity column = %1! numeric(%2!)", @colname, @len
--print " OAM page = %1!", @doampg
--print " Bytes on OAM page = %1!", @idlenb
if @idgap > 0
begin
print " Max. Identity Gap = %1! (""identity_gap"" = %2!)", @idgap, @idgap
end
else
begin
set arithabort numeric_truncation off
select @maxidgap = ceiling((convert(numeric(38),@idburn) * 0.0000001) * power(@n10,@len))
print " Max. Identity Gap = %1!", @maxidgap
print " (""identity burning set factor"" = %1! = %2!%%)", @idburn, @idburnpct
if @v = 12
begin
print " (Note: ""identity_gap"" has not been set for this table !)"
end
end
print " "

if @p1 = NULL and @p2 = NULL -- retrieve current ID value
begin
print " "
print " Reading identity value from OAM page... (traceflag 3604 should be enabled !)"
print " "
dbcc object_atts (@p0, 0, get)
print " "
print " To decode this hexadecimal value, re-run 'sp_identity' with the hexadecimal"
print " string as a quoted 2nd parameter (you can leave the spaces in). "
print " Example: sp_identity %1!, ""the-hex-string-from-the-above-output"" ", @p0
print " "
--
-- only for 12.0+, dbcc listoam will display the values in the OAM and the DES
--
--dbcc listoam(@dbname, @p0_id, @indid)
--
return 0
end

if upper(@p1) like "[ 0-9A-F]%" -- entered a hex string, decode it
begin

if @p2 != NULL
begin
print " Error: When decoding a hex value from the OAM page, parameter 3"
print " (the new identity value) must be NULL."
exec sp_identity_help
return -1
end

select @ib = @idlenb -- counts bytes
select @i = char_length(@p1) -- counts string
select @n = 0 -- holds result

while 1 = 1
begin
select @c2 = substring(@p1, (char_length(@p1)-@i)+1, 2)

--print "Step %1!, [%2!]", @ib, @c2
if upper(substring(@c2,1,1)) = " " -- ignore single space pasted in by user
begin
select @i = @i - 1
continue
end

if (upper(@c2) not like "[0-9A-F][0-9A-F]")
begin
print " Error: Invalid characters (%1!) in hex string", @c2
break
end

-- add this byte to the result
select @vc50 = @vc50 + @c2
select @j = power(@n256, (@ib-1))
select @n = @n + @j * hextoint("0x" + @c2)
--print "Step %1!, exp= %2!, result = %3!", @ib, @j, @n

-- next byte
select @ib = @ib - 1
if @ib = 0 break -- ready

-- next 2 chars
select @i = @i - 2
if @i = 0
begin
-- we shouldn't have got here, error
print " Error: ID column length and specified hex string do not match !"
break
end
end

if @ib = 0
begin
print " Decoded identity value on OAM page = %1!", @n
print " (hex= %1!)", @vc50
print " "
return 0
end
else
begin
print " Error: Aborted due to error."
return -1
end
end

if @p2 != NULL -- entered a new ID value, set it
begin

if @p1 != NULL
begin
print " Error: When setting a new identity value, parameter 2"
print " (the hex string from the OAM page) must be NULL."
exec sp_identity_help
return -1
end

-- set the new ID value
if @p2 <= 0
begin
print " Error: The new identity value must be >= 0."
print " "
exec sp_identity_help
return -1
end

if char_length(convert(varchar, @p2)) > @idlen
begin
select @vc50 = replicate("9", @idlen)
print " Error: New identity value (%1!) is too large", @p2
print " for this identity column. The maximum possible value", @p2
print " is %1! (%2! positions).", @vc50, @idlen
return -1
end

if @lsb = 0
begin
print " "
print "Internal error in 'sp_identity':"
print "Cannot figure out byte order on this platform."
print "Please send a note to 'rob@sypron.nl' with the text of this"
print "error and your @@version string."
return -1
end

-- convert the numeric value to hex
select @ib = @idlenb -- counts bytes
select @i = 15

select @vc50 = NULL -- holds result
select @vb16 = NULL -- holds result
select @n256 = 256
select @n = @p2
set arithabort numeric_truncation off

while 1 = 1
begin
select @j = power(@n256, @i)

select @b = @n / @j
select @n = @n - (@b * @j)
select @c2 = right(inttohex(@b),2)
select @b1 = substring(convert(binary(4), @b), @lsb,1)

select @vb16 = @vb16 + @b1

--print "Step %1!, @b=%2!, hex(c2)=%3!, hex(b1)=%5!, result=%4!", @i, @b, @c2, @vb16, @b1

select @i = @i - 1
if @i < 0 break -- ready
end

if @i < 0
begin
select @vb16 = substring(@vb16, 16 - (@idlenb) + 1, (@idlenb) ) + substring(@vb16, 1, 16 -

(@idlenb) )

-- set the new ID value
dbcc object_atts(@p0, 0, "put", @vb16)

print " "
print " Identity value on OAM page has been set to %1!", @p2
print " (hex= %1!)", @vb16
print " "
select @p2 = @p2 + 1
print " You should now do a 'shutdown with nowait' immediately."
print " After restarting the server, the value assigned to the next row "
print " inserted into '%1!' will be %2!.", @p0, @p2
print " "
return 0
end
else
begin
print " Error: Aborted due to error."
return -1
end

end

-- we should never get here
print "Internal error in 'sp_identity': Reached invalid end of procedure."
return -1
end
go

grant execute on sp_identity to public
dump tran sybsystemprocs with truncate_only
go

print "Ready. For usage information, run 'sp_identity ""?"" '."
print ""
go

/*
** end
*/

一、当用一个表中的数据来更新另一个表中的数据，T-SQL提供多种写法(下面列出了二种)，但建议用第一种写法，虽然传统，但结构清晰。
并且要注意，当用一个表中的数据来更新另一个表中的数据时，二个表一定要有关联！

1.
update  t1 set t1.c2 = t2.c2
from  t2
where  t1.c1 = t2.c1

2.
Update  t1 set t1.c2 = t2.c2
from  t1 inner join t2 on t1.c1 = t2.c1

二、FROM 子句中指定的表的别名不能作为 SET column_name 子句中被修改字段的限定符使用。例如，下面的内容无效：
UPDATE titles
    SET t.ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
    AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)


若要使上例合法，请从列名中删除别名 t 或使用本身的表名。
1.
UPDATE titles
    SET ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
    AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
2.
UPDATE titles
    SET titles.ytd_sales = t.ytd_sales + s.qty
    FROM titles t, sales s
    WHERE t.title_id = s.title_id
    AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)


SQL Server中易混淆的数据类型
 
(1)char、varchar、text和nchar、nvarchar、ntext
char和varchar的长度都在1到8000之间，它们的区别在于char是定长字符数据，而varchar是变长字符数据。
所谓定长就是长度固定的，当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，使长度达到相应的长度；
而变长字符数据则不会以空格填充。text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。

后面三种数据类型和前面的相比，从名称上看只是多了个字母"n"，它表示存储的是Unicode数据类型的字符。
写过程序的朋友对Unicode应该很了解。字符中，英文字符只需要一个字节存储就足够了，
但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，
Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，
即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。
和char、varchar比较：nchar、nvarchar则最多存储4000个字符，
不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。
可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

(2)datetime和smalldatetime
datetime：从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒。
smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。

(3)bitint、int、smallint、tinyint和bit
bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据。
int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据。
smallint：从-2^15(-32,768)到2^15-1(32,767)的整数数据。
tinyint：从0到255的整数数据。
bit：1或0的整数数据。

(4)decimal和numeric
这两种数据类型是等效的。都有两个参数：p（精度）和s（小数位数）。p指定小数点左边和右边可以存储的十进制数字的最大个数，
p必须是从 1到38之间的值。s指定小数点右边可以存储的十进制数字的最大个数，s必须是从0到p之间的值，默认小数位数是0。

(5)float和real
float：从-1.79^308到1.79^308之间的浮点数字数据。
real：从-3.40^38到3.40^38之间的浮点数字数据。在SQL Server中，real的同义词为float(24)。

1. 如果一个field有default 值时，应加上它的constraint 名称，如：
Crate table fb_user
      (
      Id          int,
      Name    varchar(10) constraint DF_fb_user_name DEFAULT ‘aaa’
      )
2.  如果要删除一个有default值的字段，但以前又没有指定其constraint名称，这时应先查找这个字段上的constraint名称，然后先删除该constraint,再删除这个field.      
      select name as constraint_name,object_name(parent_obj)as table_name from sysobjects where xtype = 'D'
3.  下面这一段SQL能够重建所有的Default约束，所有Default约束应严格的按约定的规则来命名:DF_tableName_fieldName
begin tran
declare @constraint_name         varchar(100)
declare @table_name                 varchar(100)
declare @column_name             varchar(100)
declare @default_value               varchar(400)
declare @drop_sql                     nvarchar(1000)
declare @add_sql                      nvarchar(1000)
declare cur_constraint cursor 
            static forward_only
            for
            select A.name as constraint_name,
                   object_name(A.parent_obj) as table_name,
                   col_name(A.parent_obj ,A.info) as column_name,
                   B.text AS default_value
              from sysobjects A
                   inner join syscomments B on A.id = B.id
             where xtype = 'D'

open cur_constraint
fetch next from cur_constraint into @constraint_name, @table_name, @column_name, @default_value
while @@fetch_status = 0
    begin
        set @drop_sql = N'alter table ' + @table_name + ' drop constraint ' + @constraint_name
        set @add_sql = N'alter table ' + @table_name + ' add constraint ' + @constraint_name + ' default ' + @default_value + ' for ' + @column_name                       
        exec sp_executesql @stmt = @drop_sql
        exec sp_executesql @stmt = @add_sql 
        fetch next from cur_constraint into @constraint_name, @table_name, @column_name, @default_value
    end

close cur_constraint
deallocate cur_constraint
rollback tran

GO

-- ============================================================
-- Procedure Name : wh_check_dataobject
-- Function   : check the dataobjects between two database
-- Failure return : 1
-- Success return : 0
--
-- Parameters  :
-- @database_check : the database that is checked
-- @dataobject  : (tb-table, sp-stored procedure, fn-function, tr-trigger, vw-view, all-all objects)
-- author   : royalhigh, Jan, 2005/20
-- ============================================================
IF EXISTS (select * from sysobjects where id = object_id(N'wh_check_dataobject') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE wh_check_dataobject
GO

CREATE procedure wh_check_dataobject
 (
 @database_check  varchar(128),
 @dataobject   varchar(3)
 )
--WITH ENCRYPTION
AS
DECLARE @script_table table
 (
  col_scripts varchar(400)
 )
 
DECLARE @object_table table
 (
  col_objects varchar(128)
 )
DECLARE @scripts varchar(400)

BEGIN
 SET NOCOUNT ON

 IF (@database_check IS NULL or @database_check = '')
 BEGIN
  RAISERROR ('Parameter can not be NULL, input them, Please', 16, 1) WITH NOWAIT
  RETURN 1
 END
 -- 1. TABLE CHECK
 IF (@dataobject = 'tb' OR @dataobject = 'all')
 BEGIN
  PRINT 'the inexistent table:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype = 'U'
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
  

 END
 
 -- 2. SP CHECK
 IF (@dataobject = 'sp' OR @dataobject = 'all')
 BEGIN
  PRINT'the inexistent stored procedure:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype = 'P'
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
 END

 -- 3. FUNCTION CHECK
 IF (@dataobject = 'fn' OR @dataobject = 'all')
 BEGIN
  PRINT'the inexistent function:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype in('FN','TF', 'IF')
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
 END

 -- 4. TRIGGER CHECK
 IF (@dataobject = 'tr' OR @dataobject = 'all')
 BEGIN
  PRINT'the inexistent trigger:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype = 'TR'
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
 END

 -- 5. VIEW CHECK
 IF (@dataobject = 'vw' OR @dataobject = 'all')
 BEGIN
  PRINT'the inexistent view:'
  insert into @script_table(col_scripts)
  select 'IF NOT EXISTS (SELECT 1 FROM ' + @database_check + '.dbo.sysobjects WHERE name = N''' + name + ''')'
   + ' PRINT''' + name + ''''
   from dbo.sysobjects where xtype = 'V'
   
  IF (@@error <> 0)
  BEGIN
   RETURN 1
  END
  
  DECLARE cur_temp CURSOR
   FAST_FORWARD FOR
   select col_scripts from @script_table
  OPEN cur_temp
  FETCH NEXT FROM cur_temp INTO @scripts
  WHILE @@FETCH_STATUS = 0
  BEGIN
   EXEC (@scripts)

   IF (@@error <> 0)
   BEGIN
    CLOSE cur_temp
    DEALLOCATE cur_temp
    RETURN 1
   END

   FETCH NEXT FROM cur_temp INTO @scripts
  END
  CLOSE cur_temp
  DEALLOCATE cur_temp
  
  DELETE FROM @script_table
  DELETE FROM @object_table
  SET @scripts = ''
 END
 
 IF (@@error <> 0)
 BEGIN
  RETURN 1
 END
END

GO

-- ============================================================

1、使用说明
 检查X数据库中是否存在Y版本的所有对象
 (1) 在标准Y版本数据库建立SP：wh_check_dataobject
 (2) 执行wh_check_dataobject，得到比较结果

2、使用方法
 @database_check ：代表需要检查的X数据库名
 @dataobject ：(tb-table, sp-stored procedure, fn-function, tr-trigger, vw-view, all-all objects)
 例如：wh_check_dataobject j_jill, 'all'

3、在SP中，不能调用object_id来取对象id值，而只能用name做为条件在sysobjects中检索来判断对象是否存在。
  因为用“SELECT 1 FROM ' + @database_check + '.dbo.sysobjects”切换过数据库，而object_id只取当前数据库，
  二者取的不是同一个库，id值会有差别；

在SQL Server 2000中建表：
CREATE TABLE man_sp_content (
        sp_script            varchar(8000) NOT NULL,
        version              varchar(20) NOT NULL,
        sp_content_id        int NOT NULL,
        modify_desc          varchar(1000) NOT NULL,
        sp_id                int NOT NULL,
        modify_user          varchar(20) NOT NULL,
        return_value         varchar(400) NULL,
        parameter            varchar(500) NULL,
        modify_date          datetime NULL
 )
go

则得到如下警告：
警告: 已创建表 'man_sp_content'，但其最大行大小（9990）超过了每行的最大字节数（8060）。如果结果行长度超过 8060 字节，则此表中行的 INSERT 或 UPDATE 将失败。

生成sql server2000对象创建脚本的存储过程
通过读取sysobjects 等系统表的纪录，生成表、视图等数据库对象脚本的存储过程。

已知问题：
存储过程长度有限制，不能超过8000个字节，因为用来存储sql脚本的变量为varchar型，最大就是8000。

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
 生成当前数据库所有CHECK约束的脚本
 适用于 Sql Server 2000
 beiqiao（qbboxcn@hotmail.com） 2004/02/29
*/
CREATE PROCEDURE sp_create_check AS
select 'ALTER TABLE ' + d.name + ' WITH NOCHECK ADD CONSTRAINT '  + a.name
 + case when b.status in (133141,2069) then ' default ' else ' check ' end
 + c.text 
 + case when b.status in (133141,2069) then ' for ' + col_name(b.id,b.colid) else '' end

from sysobjects a , sysconstraints b, syscomments c ,sysobjects d
where  b.constid = a.id and b.constid = c.id and b.id = d.id and d.name <> 'dtproperties'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
 生成当前数据库所有FK约束的脚本
 适用于 Sql Server 2000
 beiqiao（qbboxcn@hotmail.com） 2004/02/29
*/
CREATE PROCEDURE sp_create_fk AS
select
 ' alter table ' + t_obj.name +
 ' add constraint ' + c_obj.name +
 ' foreign key (' +
 col_name(t_obj.id, fkey1) +
 -- 处理复合外键
 case when fkey2<>0 then ',' + col_name(t_obj.id, fkey2) else '' end +
 case when fkey3<>0 then ',' + col_name(t_obj.id, fkey3) else '' end +
 case when fkey4<>0 then ',' + col_name(t_obj.id, fkey4) else '' end +
 case when fkey5<>0 then ',' + col_name(t_obj.id, fkey5) else '' end +
 case when fkey6<>0 then ',' + col_name(t_obj.id, fkey6) else '' end +
 case when fkey7<>0 then ',' + col_name(t_obj.id, fkey7) else '' end +
 case when fkey8<>0 then ',' + col_name(t_obj.id, fkey8) else '' end +
 case when fkey9<>0 then ',' + col_name(t_obj.id, fkey9) else '' end +
 case when fkey10<>0 then ',' + col_name(t_obj.id, fkey10) else '' end +
 case when fkey11<>0 then ',' + col_name(t_obj.id, fkey11) else '' end +
 case when fkey12<>0 then ',' + col_name(t_obj.id, fkey12) else '' end +
 case when fkey13<>0 then ',' + col_name(t_obj.id, fkey13) else '' end +
 case when fkey14<>0 then ',' + col_name(t_obj.id, fkey14) else '' end +
 case when fkey15<>0 then ',' + col_name(t_obj.id, fkey15) else '' end +
 case when fkey16<>0 then ',' + col_name(t_obj.id, fkey16) else '' end +
  ') '  +
 ' references ' + r_obj.name + '(' +
 index_col(object_name(rkeyid), rkeyindid, 1 ) +
 -- 处理复合外键
 case when index_col(object_name(rkeyid), rkeyindid, 2 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 2 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 3 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 3 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 4 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 4 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 5 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 5 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 6 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 6 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 7 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 7 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 8 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 8 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 9 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 9 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 10 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 10 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 11 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 11 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 12 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 12 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 13 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 13 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 14 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 14 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 15 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 15 ) else '' end +
 case when index_col(object_name(rkeyid), rkeyindid, 16 )is not null then ',' + index_col(object_name(rkeyid), rkeyindid, 16 ) else '' end +
 ')'
from
 sysobjects c_obj
 ,sysobjects t_obj
 ,sysobjects r_obj
 ,syscolumns col
 ,sysreferences  ref
where
 c_obj.xtype in ('F')
 and t_obj.id = c_obj.parent_obj
 and t_obj.id = col.id
 and col.colid   in
 (ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,
 ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,
 ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)
 and c_obj.id = ref.constid
 and r_obj.id = ref.rkeyid

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
 生成当前数据库所有索引的脚本
 适用于 Sql Server 2000
 beiqiao（qbboxcn@hotmail.com） 2004/02/29
*/
CREATE PROCEDURE sp_create_index AS
declare @tableName nvarchar(128)
declare @indexName nvarchar(128)
declare @status int
declare @OrigFillFactor int
declare @columnName nvarchar(128)
declare @indid smallint
declare @clusteredString nvarchar(16)
declare @uniqueString nvarchar(16)
declare @fillfactorString nvarchar(1024)
declare @sql nvarchar(1024)

select @fillfactorString = ''
select @sql = ''

create table #tmpTable (sql nvarchar(4000))

DECLARE myCursor  CURSOR FOR
select b.name as tableName, a.name as indexName, a.status, a.OrigFillFactor, index_col(b.name, indid, 1 ) as columnName ,a.indid
from sysindexes a, sysobjects b
where a.id = b.id and b.xtype = 'U' and indid > 0 and indid < 255
and (a.status & 8388608)  = 0 --去掉不需要的记录
and (a.status & 2048) = 0 --去掉primary key

OPEN myCursor

FETCH NEXT FROM myCursor into @tableName,@indexName,@status,@OrigFillFactor,@columnName,@indid

WHILE @@FETCH_STATUS = 0
BEGIN

 if (@status & 16)<>0
  select @clusteredString = ' clustered '
 else
  select @clusteredString = ' nonclustered '

 if (@status & 2)<>0
  select @uniqueString = ' UNIQUE '
 else
  select @uniqueString = ' '
 
 if @OrigFillFactor <>0
  select @fillfactorString = ',FILLFACTOR=' + ltrim(rtrim(str(@OrigFillFactor)))
 else
  select @fillfactorString =''

 if (@status &1)<>0
  select @fillfactorString =  @fillfactorString + ',IGNORE_DUP_KEY'

 if (@status &256)<>0
  select @fillfactorString =  @fillfactorString + ',PAD_INDEX'

 if (@status &16777216)<>0
  select @fillfactorString =  @fillfactorString + ',STATISTICS_NORECOMPUTE'
 
 if  len(@fillfactorString) <> 0
  select @fillfactorString = ' with ' + substring( @fillfactorString, 2, len(@fillfactorString)-1 )

 if (@status & 4096)<>0
  select @sql = 'ALTER TABLE ' + @tableName + ' WITH NOCHECK ADD CONSTRAINT '
  + @indexName + @clusteredString + @uniqueString + '('
  + index_col(@tableName, @indid, 1 )
  + case when index_col(@tableName, @indid, 2 )is not null then ',' + index_col(@tableName, @indid, 2 ) else '' end
  + case when index_col(@tableName, @indid, 3 )is not null then ',' + index_col(@tableName, @indid, 3 ) else '' end
  + case when index_col(@tableName, @indid, 4 )is not null then ',' + index_col(@tableName, @indid, 4 ) else '' end
  + case when index_col(@tableName, @indid, 5 )is not null then ',' + index_col(@tableName, @indid, 5 ) else '' end
  + case when index_col(@tableName, @indid, 6 )is not null then ',' + index_col(@tableName, @indid, 6 ) else '' end
  + case when index_col(@tableName, @indid, 7 )is not null then ',' + index_col(@tableName, @indid, 7 ) else '' end
  + case when index_col(@tableName, @indid, 8 )is not null then ',' + index_col(@tableName, @indid, 8 ) else '' end
  + case when index_col(@tableName, @indid, 9 )is not null then ',' + index_col(@tableName, @indid, 9 ) else '' end
  + case when index_col(@tableName, @indid, 10 )is not null then ',' + index_col(@tableName, @indid, 10 ) else '' end
  + ')'
  + @fillfactorString
 else
  select @sql = 'create ' + @clusteredString + @uniqueString + ' INDEX ' + @indexName + ' ON ' + @tableName + '('
  + index_col(@tableName, @indid, 1 )
  + case when index_col(@tableName, @indid, 2 )is not null then ',' + index_col(@tableName, @indid, 2 ) else '' end
  + case when index_col(@tableName, @indid, 3 )is not null then ',' + index_col(@tableName, @indid, 3 ) else '' end
  + case when index_col(@tableName, @indid, 4 )is not null then ',' + index_col(@tableName, @indid, 4 ) else '' end
  + case when index_col(@tableName, @indid, 5 )is not null then ',' + index_col(@tableName, @indid, 5 ) else '' end
  + case when index_col(@tableName, @indid, 6 )is not null then ',' + index_col(@tableName, @indid, 6 ) else '' end
  + case when index_col(@tableName, @indid, 7 )is not null then ',' + index_col(@tableName, @indid, 7 ) else '' end
  + case when index_col(@tableName, @indid, 8 )is not null then ',' + index_col(@tableName, @indid, 8 ) else '' end
  + case when index_col(@tableName, @indid, 9 )is not null then ',' + index_col(@tableName, @indid, 9 ) else '' end
  + case when index_col(@tableName, @indid, 10 )is not null then ',' + index_col(@tableName, @indid, 10 ) else '' end
  + ')' 
  + @fillfactorString
 insert into #tmpTable (sql)values(@sql)

 FETCH NEXT FROM myCursor into @tableName,@indexName,@status,@OrigFillFactor,@columnName,@indid
end
CLOSE myCursor
DEALLOCATE myCursor

select * from #tmpTable

drop table #tmpTable
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
 生成当前数据库所有PK和UQ约束的脚本
 适用于 Sql Server 2000
 beiqiao（qbboxcn@hotmail.com） 2004/02/29
*/
CREATE PROCEDURE sp_create_pk_uq AS
declare @oldTableName nvarchar(128)
declare @sqlString nvarchar(1024)
declare @columnList nvarchar(1024)

declare @constraintName nvarchar(128)
declare @oldConstraintName nvarchar(128)
declare @tableName nvarchar(1024)
declare @columnName nvarchar(1024)
declare @indexId smallint
declare @objType char(2)

declare @oldIndexId smallint
declare @oldObjType char(2)
declare @clusteredString nvarchar(16)
declare @objTypeString nvarchar(16)

select @oldIndexId = 1
select @oldObjType = 'PK'
select @oldConstraintName = ''
select @oldTableName = ''
select @sqlString = ''
select @columnList =''
select @clusteredString = ' CLUSTERED '
select @objTypeString = ' PRIMARY KEY  '

create table #tmpTable (sql nvarchar(4000))

DECLARE myCursor  CURSOR FOR
select
 i.name     as constraintName
 ,t_obj.name    as tableName
 ,col.name    as columnName
 ,i.indid    as indexId
 ,c_obj.xtype    as objType
from
 sysobjects  c_obj
 ,sysobjects  t_obj
 ,syscolumns  col
 ,master.dbo.spt_values  v
 ,sysindexes  i
where
 c_obj.xtype in ('UQ' ,'PK')
 and t_obj.id = c_obj.parent_obj
 and t_obj.xtype  = 'U'
 and t_obj.id = col.id
 and col.name = index_col(t_obj.name,i.indid,v.number)
 and t_obj.id = i.id
 and c_obj.name  = i.name
 and v.number  > 0
  and v.number  <= i.keycnt
  and v.type  = 'P'
 and t_obj.status >0
order by tablename

OPEN myCursor

FETCH NEXT FROM myCursor into @constraintName, @tableName, @columnName, @indexId, @objType

WHILE @@FETCH_STATUS = 0
BEGIN

 if @constraintName <> @oldConstraintName and @oldConstraintName <> ''
 begin
  --删除最后一个逗号
  select @columnList = substring( @columnList, 1, len(@columnList)-1 )
 
  if @oldIndexId > 1
   select @clusteredString = ' NONCLUSTERED '
  else
   select @clusteredString = ' CLUSTERED '
 
  if @oldObjType = 'UQ'
   select @objTypeString = ' UNIQUE '
  else
   select @objTypeString = ' PRIMARY KEY ' 

  select @sqlString = 'alter table ' + @oldTableName + ' WITH NOCHECK ADD CONSTRAINT ' +
   @oldConstraintName + @objTypeString + @clusteredString + ' (' + @columnList +')'
 
  --下一个columnlist开始
  select @columnList =''

  insert into #tmpTable (sql)values(@sqlString)
 end
 
 select @oldTableName = @tableName
 select @oldConstraintName = @constraintName
 select @oldIndexId = @indexId
 select @oldObjType = @objType

 select @columnList = @columnList + @columnName + ', '

    FETCH NEXT FROM myCursor into @constraintName, @tableName, @columnName, @indexId, @objType
END

select @columnList = substring( @columnList, 1, len(@columnList)-1 )
 
--插入最后一条记录
if @oldIndexId > 1
 select @clusteredString = ' NONCLUSTERED '
else
 select @clusteredString = ' CLUSTERED '

if @oldObjType = 'UQ'
 select @objTypeString = ' UNIQUE '
else
 select @objTypeString = ' PRIMARY KEY ' 

select @sqlString = 'alter table ' + @oldTableName + ' WITH NOCHECK ADD CONSTRAINT ' +
  @oldConstraintName + @objTypeString + @clusteredString + ' (' + @columnList +')'
insert into #tmpTable (sql)values(@sqlString)

CLOSE myCursor
DEALLOCATE myCursor

select * from #tmpTable
drop table #tmpTable
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
 生成当前数据库所有存储过程，视图，函数，触发器的脚本
 适用于 Sql Server 2000
 beiqiao（qbboxcn@hotmail.com） 2004/02/29
*/
CREATE PROCEDURE sp_create_proc AS
select b.text as sql from sysobjects a,syscomments b where a.xtype in ('TR','TF','V','P') and a.id = b.id and a.status >0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
 生成当前数据库所有表的创建脚本
 适用于 Sql Server 2000
 beiqiao（qbboxcn@hotmail.com） 2004/02/29
*/
CREATE PROCEDURE sp_create_table AS
declare @count int
declare @tableName nvarchar(128)
declare @columnName nvarchar(128)
declare @columnLength smallint
declare @isnullable int
declare @typeName nvarchar(128)
declare @autoval nvarchar(128)

declare @oldTableName nvarchar(128)
declare @sqlString nvarchar(1024)
declare @columnList nvarchar(1024)

declare @identityString nvarchar(128)

select @oldTableName = ''
select @columnList =''

create table #tmpTable (sql nvarchar(4000))

DECLARE myCursor  CURSOR FOR
SELECT a.name as tableName, b.name as columnName, b.Length as columnLength, b.isnullable, c.name as typeName, b.autoval
from sysobjects a, syscolumns b, systypes c 
where a.xtype ='U'  and a.status >0 and a.id = b.id and b.xtype = c.xtype --a.status >0 是为了过滤表dtproperties

OPEN myCursor

FETCH NEXT FROM myCursor into @tableName,@columnName,@columnLength,@isnullable,@typeName,@autoval

WHILE @@FETCH_STATUS = 0
BEGIN

 if @tableName <> @oldTableName and @oldTableName <> ''
 begin
  --删除最后一个逗号
  select @columnList = substring( @columnList, 1, len(@columnList)-1 )

  select @sqlString = 'create table ' + @oldTableName + ' (' + @columnList +')'
 
  --下一个columnlist开始
  select @columnList =''

      insert into #tmpTable (sql)values(@sqlString)
 end
 
 select @oldTableName = @tableName

 select @columnList = @columnList + @columnName + ' ' + @typeName
 
 --添加数据类型的长度声明
 if @typeName = 'varchar' or @typeName = 'char' or @typeName = 'nchar' or @typeName ='nvarchar'
  select @columnList = @columnList + '(' + rtrim(ltrim(str(@columnLength))) + ')'

 --添加IDENTITY限定
 if @autoval is not null
 begin
  select @identityString = ' IDENTITY(' + ltrim(rtrim(str(IDENT_SEED( @tableName )))) + ',' +ltrim(rtrim(str(IDENT_INCR( @tableName )))) +')'
  select @columnList = @columnList + @identityString
 end
 
 --添加null限定
 if @isnullable = '1'
  select @columnList = @columnList + ' null'
 else
  select @columnList = @columnList + ' not null'
 
 --逗号分割
 select @columnList = @columnList + ', '

    FETCH NEXT FROM myCursor into @tableName,@columnName,@columnLength,@isnullable,@typeName,@autoval
END

--插入最后一条记录
select @columnList = substring( @columnList, 1, len(@columnList)-1 )
select @sqlString = 'create table ' + @oldTableName + ' (' + @columnList +')'
insert into #tmpTable (sql)values(@sqlString)

CLOSE myCursor
DEALLOCATE myCursor

select * from #tmpTable
drop table #tmpTable
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-----------------------------------------------------------------------------------------------------------------------------
版权声明：CSDN是本Blog托管服务提供商。如本文牵涉版权问题，CSDN不承担相关责任，请版权拥有者直接与文章作者联系解决。
发表于 2004年11月02日 5:27 PM
原文地址：http://blog.csdn.net/beiqiao/archive/2004/11/02/164028.aspx

在数据库上运行下列脚本将使 SQL Server 企业管理器可以处理该数据库中的数据库关系图：
alter table dbo.dtproperties add uvalue nvarchar(255) null
go
alter table dbo.dtproperties alter column version int null
go

if exists(select 1 from dbo.dtproperties)
exec('update dbo.dtproperties set uvalue = convert(nvarchar(255), value)')
go

该脚本运行后，SQL Sever 2000 中的 SQL Sever 企业管理器和可视化数据库工具的早期版本都可以访问该数据库中的数据库关系图。

SQL SERVER 2000中，

• 字符类型一般用char和varchar数据类型(短字符型)，最长为8000字节，超过8000个字节的文本就要使用ntext或者text数据类型来存储；
• 二进制类型一般用binary、varbinary ，最长为8 KB，储超过 8 KB 的可变长度的二进制数据，如 Microsoft Word 文档、Microsoft Excel 电子表格、包含位图的图像、图形交换格式 (GIF) 文件和联合图像专家组 (JPEG) 文件，使用image 数据类型来存储。
• ntext、text 和 image 数据类型在单个值中可以包含非常大的数据量，最大可达 2 GB。

一、下面的函数和语句可以与 ntext、text 或 image 数据一起使用。

二、用法示例

1、替换

--创建数据测试环境
create table #tb(aa text)
insert into #tb select 'abc123abc123,asd'

--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='123', --要替换的字符串
         @d_str='000' --替换成的字符串

--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1 from #tb
while @postion>0
begin
   updatetext #tb.aa @p @postion @rplen @d_str
   select @postion=charindex(@s_str,aa)-1 from #tb
end

--显示结果
select * from #tb

--删除数据测试环境
drop table #tb

2、全部替换

DECLARE @ptrval binary(16)
DECLARE @ptrvld int
SELECT @ptrval = TEXTPTR(aa), @ptrvld = TEXTVALID('#tb.aa', TEXTPTR(AA))  FROM  #tb  WHERE aa like '%数据2%'
-- 一定要加上条件判断，否则若找不到目标文件指针下一句SQL就会报错（很重要！）
if @ptrval is not null and  @ptrvld = 1
   UPDATETEXT #tb.aa @ptrval 0 null '数据3'

3、在字段尾添加

--定义添加的的字符串
declare @s_str varchar(8000)
select @s_str='*C'   --要添加的字符串
--字符串添加处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(detail) from test where id='001'
updatetext test.detail @p null null @s_str

三、总结

1. Text字段类型不能直接用replace函数来替换，必须用updatetext；
2. 字段比较不能用　where 字段 = ‘某数据’,可以用like来代替；
3. updatetext时，若dest_text_ptr值为NULL时会报错，需注意。错误信息：向UpdateText 函数传递了 NULL textptr（text、ntext 或 image 指针）；注意，BLOB列为NULL而所在行不为空时，dest_text_prt为NOT NULL，若BOLB所在行为空，则dest_text_prt为NULL。delete_length必须小于等于字段总长度，否则报错：删除长度  不在可用的 text、ntext 或 image 数据范围内。
4. PATINDEX / CHARINDEX 函数都返回指定模式的开始位置。PATINDEX 可使用通配符，而 CHARINDEX 不可以。IS NULL、IS NOT NULL 和 LIKE，这些是 WHERE 子句中对 text / ntext类型有效的仅有的其它比较运算。除此之外，PATINDEX 也可用于 WHERE 子句中；
5. 使用 TEXTVALID 来检查文本指针是否存在。在无有效文本指针时，不能使用 UPDATETEXT、WRITETEXT 或 READTEXT；例，SELECT 'Valid (if 1) Text data'
      = TEXTVALID ('pub_info.logo', TEXTPTR(logo)) FROM pub_info WHERE logo like '%hello%'； 
6. LEN只对短字符型有效，对于text/ntext/image类型，则使用DATALENGTH来得到数据长度；

一 系统预定义错误代码

SQL Server 有3831个预定义错误代码，由master.dbo.sysmessages表维护。每一个错误代码都有相应的级别和描述。
错误定义的级别从0到25。20以上的错误代表重大错误，通常意味着该错误会导致存储进程立刻终止，并且所有的客户连接都要重新初始化。
非关键性错误只是禁止掉当前运行的程序行，并继续执行。

判断错误是否发生和得到错误描述信息的方法：

IF @@ERROR = 0

begin

    SELECT @ERRORMESSAGETXT = description FROM master.dbo.sysmessages WHERE error = @@ERROR

end

二 raiserror语法

语法
RAISERROR ( { msg_id ¦ msg_str } { , severity , state }
[ , argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

参数
msg_id

存储于 sysmessages 表中的用户定义的错误信息。用户定义错误信息的错误号应大于 50,000。由特殊消息产生的错误是第 50,000 号。

msg_str

是一条特殊消息，其格式与 C 语言中使用的 PRINTF 格式样式相似。此错误信息最多可包含 400 个字符。如果该信息包含的字符超过 400 个，
则只能显示前 397 个并将添加一个省略号以表示该信息已被截断。所有特定消息的标准消息 ID 是 14,000。

msg_str 支持下面的格式：

% [[flag] [width] [precision] [{h ¦ l}]] type

可在 msg_str 中使用的参数包括：

flag

用于确定用户定义的错误信息的间距和对齐的代码。

{h | l} type

与字符类型 d、i、o、x、X 或 u 一起使用，用于创建 short int (h) 或 long int (l) 类型的值。

说明  不支持 float、双精度和单精度字符类型。

severity

用户定义的与消息关联的严重级别。用户可以使用从 0 到 18 之间的严重级别。19 到 25 之间的严重级别只能由 sysadmin 固定服务器角色成员使用。
若要使用 19 到 25 之间的严重级别，必须选择 WITH LOG 选项。

注意 20 到 25 之间的严重级别被认为是致命的。如果遇到致命的严重级别，客户端连接将在收到消息后终止，并将错误记入错误日志和应用程序日志。

state

从 1 到 127 的任意整数，表示有关错误调用状态的信息。state 的负值默认为 1。

argument

是用于取代在 msg_str 中定义的变量或取代对应于 msg_id 的消息的参数。可以有 0 或更多的替代参数；然而，替代参数的总数不能超过 20 个。
每个替代参数可以是局部变量或这些任意数据类型：int1、int2、int4、char、varchar、binary 或 varbinary。不支持其它数据类型。

option

错误的自定义选项。option 可以是以下值之一：

值 描述
LOG 将错误记入服务器错误日志和应用程序日志。记入服务器错误日志的错误目前被限定为最多 440 字节。
NOWAIT 将消息立即发送给客户端。
SETERROR 将 @@ERROR 的值设置为 msg_id 或 50000，与严重级别无关。

三 用户自定义错误处理

IF (@role_type_id IS NULL)
  BEGIN
   RAISERROR ('Parameter ''role_type_id'' can not be null.' , 16, 1) WITH NOWAIT
   RETURN 1
  END

四 定制错误信息

当然，大多数情况下，错误信息会使用多次，一遍一遍的输入相同的信息显然不理智。
使用sp_addmessage 存储过程将定制错误信息添加到sysmessages 表里。以后，在使用RAISERROR 时候就可以引用新的信息ID号。

定制错误信息的标识号必须大于等于50000，信息本身的长度不能超过255个字符。
下面是例子，创建新的信息，赋予识别号55555，级别为10，调用方式如下：sp_addmessage 55555, 10, 'New error message.'
你可以在存储进程中增加提交新的错误：RAISERROR 55555, 10

2004年11月17日，下午14：30，成功申请itpub blog，十分感谢itpub提供这么个好东东，感谢tigerfish。

http://blog.itpub.net/admin.php