SQL SERVER管理及开发技巧

SQL SERVER管理技巧

将代码页从SQL Server 7.0改变到SQL Server 2000

发布日期：2002年7月1日

问：我注意到，SQL Server 7.0默认安装的代码页设置是SQL_Latin1_General_CP1_CI_AS，但是SQL Server 2000的默认代码页是Latin1_General_CI_AS。在我需要将SQL Server 7.0数据库恢复到SQL Server 2000之中时，默认代码页发生的变化会造成很大的差异。我应该如何适应这种改变？

答：SQL Server正在逐渐取消对原有的自定义代码页的支持，而转变为同Microsoft® Windows 代码页相匹配，Windows代码页是您在设置Windows区域ID时所选择设定的。同代码页相关的更多信息，请参阅SQL Server 2000在线图书中的“字符数据的存储方式”一文。

当您将SQL Server 7.0升级到SQL Server 2000之后，在默认情况下，您仍然可以保留现有的排序方式和代码页设置。但是，当您进行了一次干净的SQL Server 2000安装时，您将使用新的排序方式和代码页。如果您将某个数据库附加到一个SQL Server 2000实例中，SQL Server将保留该数据库在创建时所使用的排序规则。换句话说，如果您将一个SQL Server 7.0数据库附加到SQL Server 2000之中，你就可以保留SQL Server 7.0代码页。

由于SQL Server现在使用了新的默认代码页，我们建议：如果您想通过干净安装和数据库的附加（attch）操作进行升级，您可以在安装时选择同您的SQL Server 7.0排序规则相匹配的排序规则。您可以通过执行一次升级来确定该排序规则，例如，对pubs数据库进行一次升级，使用SERVERPROPERTY (Collation)函数运行一个查询，然后看看所得到的是何种排序规则。

Microsoft之所以对默认代码页进行修改，主要是为了减少SQL Server和Windows代码页的不兼容而引起的种种问题。

— Microsoft SQL Server开发团队

排序顺序会影响系统性能吗？

发布日期：2002年5月8日

问：一些资料介绍说，在SQL Server 6.5中，排序方式会对系统性能造成影响，但是我并没有发现有针对SQL Server 2000或SQL Server 7.0的类似说法。我想知道二分法检索对SQL Server 2000程序到底有何意义。SQL Server 2000所支持的各种排序方式之间在执行性能上存在哪些差异呢？

答：请记住：各类排序方式间的性能差异受制于SQL Server在数据排序和数据比较上所花费的CPU周期数量。排序速度快并不代表性能就一定会得到改善。事实上，选择较为“快速”的排序方法（例如二分法）可能会导致应用程序在其它方面的性能降低。

例如，假设您有一个查询，该查询通过last_name字段进行搜索。二分法排序的规则规定：Smith 并不等于smith。您的应用程序开发人员可以通过要求所有数据均为大写形式或小写形式来实现这个业务逻辑。但是，更为实际的解决办法是对所搜索表中的所有数据使用UPPER() 或者 LOWER() 函数。但不幸的是，如果类似UPPER()这样的函数对一个经过索引的列进行了操作，SQL Server将不再能够使用索引搜索数据。在这种情况下，通过使用二分法所获得的所有性能提升都将被花费在全表扫描上的巨大性能降低所抵消。所以，您无需考虑不同排序方法间可能存在的微小性能差异，您也无需费神考虑何种排序方法最能满足程序开发人员和最终用户的需要。

— SQL Server MVPs

日志文件的增长和DBCC DBREINDEX

发布日期：2002年6月10日

问：我有一个30GB的数据库，我使用完全恢复模式。无论什么时候，只要我使用数据库一致性检查程序（DBCC）语句DBCC DBREINDEX对特定的大型数据表进行重新索引，我都要将恢复模式改为Bulk_Logged，在重新索引过程完成后再改回完全模式。我希望这样做能够避免事务日志文件急剧增长，但是随后的日志文件备份工作量却非常大--有大约15GB。从逻辑上说，数据库中的数据在重新索引后同原先并没有什么不同，只是索引重新进行了组织，那么为什么日志文件还是那样大呢？我怎样才能避免日志文件出现这样的急剧增长呢？

答：是的，在重新索引前后的数据是完全一样的，但是索引却全面进行了更新。当您执行DBCC DBREINDEX命令的时候，SQL Server的日志仅仅记录了扩展盘区的分配情况（8页面单位），而不是记录了每一行或者每一页所发生的变化。这种类型的日志记录方式避免了物理文件由于系统故障而遭到破坏，并且将更详细的日志记录对系统吞吐量产生的影响降低到最小。

当您备份日志文件的时候，SQL Server必须对分配在扩展盘区中的页面进行备份，以便保持数据库备份和日志备份的一致性。如果SQL Server不备份这些页面，您将不能够切换回完全恢复模式，除非你进行一次完整的数据库备份。您必须能够从最近一次的完全备份、任何差异备份以及任何更新的事务日志备份中对数据库进行恢复。

— Microsoft SQL Server开发团队

管理结果集的分页

发布日期：2001年9月24日

问：我怎样才能使用SQL Server对结果集的分页进行管理？

答：您经常需要使用一次一页的形式来显示一个结果集，并保证用户可以轻松查看各个结果集页面，特别是您在为Web站点开发程序的时候。虽然您可以使用ADO Recordset对象对结果集进行分页，但是这种解决办法不具有伸缩性。

为了解决伸缩性问题，您需要在结果集中包括一个具有唯一ID的列，例如在表中包括一个主键。以下代码介绍了一个简单的例子，它使用两个存储过程在各个页面间进行导航：

CREATE PROCEDURE spGetNextPage
@id varchar(11) =' 0',
@rows int = 0
AS

SET NOCOUNT ON
SET ROWCOUNT @rows

SELECT
a.au_id,
a.au_fname + ' ' + au_lname AS name
FROM
authors a
WHERE
a.au_id > @id
ORDER BY
a.au_id

SET ROWCOUNT 0
SET NOCOUNT OFF
GO

CREATE PROCEDURE spGetPrevPage
@id varchar(11) =' 0',
@rows int = 0
AS

SET NOCOUNT ON
SET ROWCOUNT @ROWS

SELECT
a.au_id,
a.au_fname + ' ' + au_lname AS name
INTO
#temp
FROM
authors a
WHERE
a.au_id <@id
ORDER BY
a.au_id desc

SET ROWCOUNT 0

SELECT
*
FROM
#temp
ORDER BY
au_id

SET ROWCOUNT 0
SET NOCOUNT OFF

本示例所使用的样本数据来自pubs数据库，您可以对authors 表进行分页。如果想返回第一页的前两行数据，您可以使用带有以下参数的spGetNextPage存储过程： EXEC spGetNextPage @id=' 0', @rows=2

spGetNextPage 过程将返回authors表的前两个作者：

172-32-1176 Johnson White Marjorie Green

如需返回接下来的两个作者，您可以将后一行的ID传递给spGetNextPage：

EXEC spGetNextPage @id='213-46-8915', @rows=2

结果页显示：

238-95-7766 Cheryl Carson Michael O'Leary

如果想移动到先前页面，您可以使用第一行的ID调用spGetPrevPage：

EXEC spGetPrevPage @id='238-95-7766', @rows=2

结果将显示了您在前面看到的第一个页面。使用这种方法的一个缺点是具有唯一ID的列决定了结果集的顺序。在本文的情况下， au_id 字段必须在作者姓名字段的前面。

— SQL Server MVPs

修改日志传送角色

发布日期：2002年6月18日

问：我试图创建一些能够对日志传送角色进行修改的存储过程。与主服务器相同的登录帐户已经存在于备用服务器之中。为了将这些登录帐户从主服务器上复制到备用服务器中，我是否可以只使用用来改变角色的存储过程，而不创建一个数据转换服务（Data Transformation Services，DTS）包？如果我可以只使用存储过程来完成这些工作，我是否需要执行sp_resolve_logins存储过程来解决登录帐户和数据库用户间的所有不匹配问题？

答：如果您已经将帐户复制到了备用服务器中，那么您只需使用sp_resolve_logins 过程将帐户挂接到数据库中即可，以使这些帐户同master..sysxlogin表中的安全ID（SID）相匹配。在您修改日志传送角色的时候，您必须使用sp_resolve_logins，因为SQL Server为每个用户帐户分配了一个唯一的SID，既使该用户名是先前在本台服务器或其他服务器上已经使用过的用户名也是如此。Sp_resolve_logins 可以重新设置SID，以在用户所能够访问的每个数据库中反映出新的SID。当您在多个系统中使用数据库的时候，请确信用户帐户的名称不会彼此发生冲突。

— Microsoft SQL Server开发团队

SQL Server 2000 Enterprise Edition的优点

发布日期：2001年9月10日

Q. 使用SQL Server 2000 Enterprise Edition（企业版）能给我带来什么好处？

A. SQL Server 2000 Enterprise Edition具有几个独一无二的特性，包括访问大于2GB的内存的能力、对故障恢复群集的支持以及分布式分区视图（aka 联合数据库服务器）。但是，就算您不需要这些特性，企业版也可以为您带来一些性能上的优化。例如，SQL Server 2000 Enterprise Edition可以根据可用的内存数量动态调整预读（read－ahead）页面的最大数量；预读页面的数量在其它任何版本的SQL Server 2000中都是固定不变的。而且只有SQL Server 2000 Enterprise Edition能让多个任务共享全表扫描。

这两个性能优化可能不会在响应时间方面带来很大的改善。但是，如果您需要最大限度地榨干应用程序的潜力，您肯定会在安装企业版时为这两个特性感到欢欣不已。您不需要手动配置它们。

更多信息

要获得同SQL Server 2000 Enterprise Edition的特性有关的更多信息，请参阅Microsoft文章“SQL Server 2000各个版本所支持的功能特性”以及“阅读文献页面”，它们都可以在MSDN站点上找到。

SQL Server 2000 Books Online （在线图书，BOL）中错误地指出：您只能在SQL Server 2000 Enterprise Edition中创建索引视图。Microsoft在文章PRB中对此进行了全面的更正：索引视图（Indexed Views）可以在所有版本的SQL Server 2000中创建，该文章可以从Microsoft 支持站点上找到。

—SQL Server MVP

水平分区合并复制

发布日期：2001年5月29日

Q. 在运行一个水平分区的合并复制时，为什么数据从订阅者那里消失了？

A. 数据看起来“消失”了，这个问题自SQL Server 7.0中的合并复制发布以来一直存在。合并复制使您可以在订阅数据库上处理事务并将数据传播回出版数据库。像这样的反向传播会在处理水平分区的出版时引发一个独特的问题。

在您应用水平分区时，您基本上是在查询中添加一个WHERE子句。这个WHERE子句决定了那些数据行可以被发送给一个特殊的订阅者。假定您将一个联系人数据库针对移动销售人员进行了分割，以便每个移动销售人员的数据库只保有相应于该销售人员所负责区域的联系人数据。例如，如果Joe Smith负责东部地区，他的数据库将只含有东部地区的联系人。水平分区加强了这种限制。

现在，假定Joe所负责的区域发生了变化，他现在负责东南部地区。而Joe的数据库包含的是东部地区的联系人信息，这会和水平分区规则发生冲突。在下一次合并中，Joe会收到所有东南部地区的联系人信息，以便数据库符合分区规则。但是，现在Joe既有东部地区也有东南部地区的联系人数据，这又产生了冲突。

合并复制可以防止您拥有违反分区规则的数据。因此，在合并作业将东南部地区的数据发送给Joe的时候，它还会删除Joe数据库中的东部地区的数据。这种解决办法显然会造成数据丢失，并且在分区规则改变时产生极大的网络流量。

在数据同分区规则发生冲突时，SQL Server会删除那些不符合分区规则的数据。但是，SQL Server不会限制这些数据进入到数据库中。Joe可以添加所负责地区以外的新的联系人记录。SQL Server将这些数据保存在Joe的数据库中，但是在下一次合并之后，这些数据将不会再被保留在数据库中。这样，用户好像就丢失一部分数据。但是实际上，SQL Server并没有真的删除这些数据，SQL Server将这些数据保存在出版者的数据库中，然后从订阅者的数据库中删除了所有与水平分区规则不符的数据。

在SQL Server 7.0中，如果您对订阅数据库进行了一些修改，SQL Server会简单地转储这些修改，并且覆盖它们。因此，在改变分区之前，您必需确保所有的数据都被同步了，否则您将丢失数据。SQL Server 2000包括了一个标志，该标志可以强制系统在重新整理数据以符合分区规则之前先上传所有的修改。在开始对齐数据之前，您可以通过对订阅进行重新初始化来实现数据的预对齐上传。

检查日期错误

发布日期：2001年12月12日

Q. 我正在将一个Microsoft Access程序迁移到SQL Server上，我在移动数据时遇到了一个问题。为了解决这个问题，我需要特别注意一些什么事情吗？

A. 一个常见问题是：Access支持的日期从100年1月1日起，而Microsoft Visual FoxPro支持的日期从1年1月1日起，而SQL Server的日期从1753年1月1日起。

您可能认为，程序中不可能存在早于1753年1月1日的日期，但是，在数据输入的过程中，很容易产生这个错误，日期可能在Access和Visual FoxPro中都有效，而在SQL Server中无效。您很可能会把1990年5月12日误输成199年5月12日或者190年5月12日。这可能就是您所遇到的问题的根源所在。

—SQL Server开发团队

使用CHECK约束来强制执行触发器的唯一值

发布日期：2002年1月21日

Q. 我的SQL Server数据表中有一列允许为空（NULL）值。在该列有非空值的时候，我想让该列的值为唯一值。通过编程实现这一目标的最佳做法是什么呢？如果我在该列中设置一个UNIQUE约束，我可以只在一个记录中保有空值。我正在使用触发器来强制执行这个限制，但是你能推荐一种更简单的方法来确保所有非空的值都是唯一的吗？

A. SQL Server没有内置的机制可以禁止非空值产生重复，所以你需要使用一个自定义的CHECK约束来实现这个限制。例如，以下代码就可以实现你所要的那种完整性。

USE tempdb

CREATE table t1 (c1 int NULL, c2 char(5) NULL)

CREATE trigger mytrigger on t1 for insert, update as

BEGIN

IF (select max(cnt) from (select count(i.c1)

as cnt from t1, inserted i where t1.c1=i.c1 group

by i.c1) x) > 1

ROLLBACK TRAN

END

在SQL Server 2000中，你还可以使用INSTEAD OF触发器来执行这个限制。同INSTEAD OF触发器有关的更多信息，请参阅以下文章，要查看这些文章，请访问SQL Server Magazine，在InstantDoc（快速文档）框中输入InstantDoc编号，然后点击“Go”。文章包括：

INSTEAD OF触发器的使用窍门；InstantDoc编号 15828
视图中的INSTEAD OF触发器；InstantDoc 编号 15791
INSTEAD OF触发器；InstantDoc 编号 15524

—SQL Server MVP

将数据库拥有者（dbo）帐户改变为系统管理员（sa）

发布日期：2001年11月19日

Q. 在我观察Enterprise Manager，或者对某个特殊的数据库运行“sp_helpdb ''”的时候，数据库的拥有者（dbo）显示为某个Microsoft Windows NT®的用户，而且该用户并没有添加到SQL Server 7.0服务器的登录当中。（该Windows NT用户是Windows NT 本地管理员组的成员。）但是，在我运行以下Transact-SQL语句时：

USE

EXEC sp_helpuser

结果显示：系统管理员（sa）帐户映射到了这个用户dbo上，对我来说，这意味着sa就是dbo。因此，执行以下语句

EXEC sp_changedbowner 'sa'

将失败，因为SQL Server认为dbo就是数据库的拥有者。究竟谁是数据库的拥有者——Windows NT用户还是sa？如果Windows NT用户是拥有者，我如何才能将dbo改变为sa？

A. sa帐户总是会被映射到dbo上，即使sa并不是数据库的真正拥有者。dbo帐户注册于master数据库的sysdatabases系统表中，这正如sp_helpdb系统存储过程显示的一样。你可以将dbo从Windows NT改变为sa。实现这一目的的最快办法是首先分离（detach）该数据库，然后以sa身份重新连接该数据库。注意，这种方法会产生停机时间。

—SQL Server开发团队

如何添加Oracle链接服务器

发布日期：2001年11月19日

Q. 在我试图将一个Oracle服务器作为一个链接服务器添加到安装了SQL Server的本地计算机时，操作失败了。以下是我的计算机的一些参数设置：

General

Server: \\bao\cjf (\\domain\computer name)

Product name: oracle

Data source: msdaora

Provider: proview (alias)

Security

Local login:cjf (sa)

Remote login: internal/oracle

难道SQL Server不支持将链接服务器添加到本地计算机吗？

A: 根据SQL Server Books Online（在线图书），你可以使用Microsoft OLE DB Provider for Oracle来查询Oracle数据库中的数据。SQL Server Books Online规定了该提供者工作所必须满足的几个条件。请仔细阅读这些指南以确保您满足这些条件。

例如，OLE DB Provider for Oracle需要Oracle客户端软件支持文件版本为7.3.3.4.0或更高， Oracle SQL*Net的版本为2.3.3.0.4。SQL Server在线图书包括了与如何创建一个SQL*Net别名和创建从SQL Server登录到Oracle登录有关的信息。Books Online还为如何引用Oracle数据库的实例和Oracle链接服务器中的数据表提供了一些指南。

根据这些指南，如想建立Oracle链接服务器，您应该运行以下命令：

exec sp_addlinkedserver 'localOracle', 'Oracle', 'MSDAORA', 'proview'
/* SQL Server调用服务器“localOracle”，SQL*NET将服务器引用为 proview. */

exec sp_addlinkedsrvlogin 'localOracle', false, 'sa', 'internal', 'oracle'
/* 以“sa”身份登录到SQL Server，sa将映射到Oracle中的一个内部帐户。*/

然后，以sa用户的身份登录到SQL Server服务器上，然后运行一个如下所示的分布式查询：

SELECT * FROM localOracle..Schema.Table

—SQL Server开发团队

SQL Server Profiler和参数化语句

发布日期：2002年1月7日

问：自从升级到SQL Server 2000后，我在SQL事件查看器中遇到了一个问题：我无法捕获带有参数的Transact-SQL语句。我希望获得查询执行过程中参数的实际取值，而并非诸如@p1这样的参数形式。请问如何在不引用跟踪过程中其它行的情况下对参数取值加以置换。

答：您必须获取跟踪过程的其它部分（如存储过程StmtCompleted所返回的结果），并将这些部分收集在一起（语句放在最前面）以捕获完整的查询内容。之所以会出现这样的结果，其原因在于从SQL Server关系型引擎恢复数据的SQL事件查看器方法。这样的问题在SQL Server 7.0中同样存在。然而，由于当事件产生时，位于引擎内部的参数取值是未知的，因此，我们无法在这一时刻做出修改。

--Microsoft SQL Server开发团队

将一个SQL Server系统用作一台PDC

发布日期：2002年1月14日

问：我的公司建立了一个灾难恢复站点，并且计划在一台新配备的计算机上安装SQL Server。Microsoft Windows NT管理员希望使用运行SQL Server的计算机作为主域控制器（PDC）。我听说，由于PDC需要完整维护与复制网络帐号数据库所引发的资源密集型任务并且需要执行网络登陆验证操作，因此，这种配置方案并非一种良好的方式。那么，请问运行SQL Server的计算机能够转而充当备份域控制器（BDC）呢？从技术上讲，将SQL Server配置为PDC或BDC是否可能呢？

答：当您在PDC或BDC上安装SQL Server时，它仍可保持良好的工作状态。如需确定某种配置方案能否适应于您的站点，应当考虑您所拥有的资源。如果您拥有足够的网络带宽、内存空间与空闲处理器，那么，使用运行SQL Server的计算机作为域控制器便是一种合理的方案。然而，在这种情况下，根据域的实际规模，相应的计算机可能需要承担大量工作负载。如果您认为SQL Server的任务将会非常繁重，那么，便请不要再让其运行任何其它服务。

--Microsoft SQL Server开发团队

管理技巧

发布日期：2002年1月28日