《MS SQL Server 2000管理员手册》系列——24. 加载数据库[1]

24. 加载数据库
加载操作的效能
大量复制工具程序
BULK INSERT 陈述式
数据转换服务
Staging 资料表
SELECT...INTO 陈述式
本章总结
在学会了建立数据库和数据库数据表，就可学习加载数据了。将数据加载数据库的方法有好几种，采用何种方法取决于数据来源、对数据进行何种处理，以及资料将加载何处。本章将学习以下几种加载数据库的方法：
•   使用大量复制工具程序（Bulk Copy Program，BCP） ：BCP 是由 Microsoft SQL Server 2000 提供的外部程序，便于加载数据文件至数据库，BCP 也可以从 SQL Server 中的数据表复制数据至数据文件中。
　
•   使用 BULK INSERT 指令 ：BULK INSERT T-SQL 指令可从数据文件复制大量数据至 SQL Server 数据表中。由于此指令是 SQL 陈述式（在 ISQL、OSQL 或 Query Analyzer 中执行），处理过程将当成 SQL Server 的执行绪执行。该指令不能从 SQL Server 复制数据到数据文件中。
　
•   使用数据转换服务（Data Transformation Services，DTS） ：DTS 是一组由 SQL Server 提供的工具组，便于复制数据到 SQL Server，或从 SQL Server 复制出数据。DTS 包括汇出和汇入数据精灵。
　
________________________________________
说明
尽管 Staging 数据表并没有提供加载数据的方法，但一般用于数据库的加载。
________________________________________
每种方法都有其特点和性质，以适合使用者不同的加载需求。
________________________________________
说明
从备份文件复原数据库也可以视为数据库加载的一种形式，这点将在 第三十二章 和 第三十三章 进行讨论。
________________________________________
BCP 或 BULK INSERT 陈述式的某些数据库参数设定是共通的，且数据库参数定义了 BCP 如何执行，因此必须在执行加载操作之前完成参数的设定。
下列的方法也很实用：
•   SELECT...INTO 陈述式 ：用于将数据复制至另一个数据表。
　
•   Staging 资料表 ：为暂存数据表，通常用于在数据库中转换数据，使数据加载过程更顺利，并可于加载过程中修改数据。
　
加载操作的效能
　
本段会介绍三种常用于加强加载操作效能的设定选项。其中两种选项会在执行大量复制操作时影响交易记录文件，另一种则影响锁定。使用 大量复制 的方法一次复制大量数据，可视为重建一份数据最有效率的方法。
交易记录文件选项
　
SQL Server 使用复杂的交易记录文件机制来确定数据不会因为系统故障而遗失。交易记录文件对于系统中数据的完整性是必要的，但可能会大量的增加系统负担。透过减少大量加载数据时的交易数据记录量，可降低系统的负担。
________________________________________
说明
发生系统故障后，SQL Server 会复原数据库。发生故障时还未被认可的交易都会被 复原（Roll Back） （取消操作）；发生故障时已经认可的交易都会 向前复原（Roll Forward） 。复原或向前复原可以将系统复原到故障发生前的状态。备份和复原将在 第三十二章 和 第三十三章 讨论。
________________________________________
在预设状态下，所有的数据库插入操作都会被完全的记录下来，以便在系统故障时可向前复原或复原插入的数据。藉由停用大量数据复制时的完全记录（使用BCP、BULK INSERT 陈述式或 SELECT...INTO 陈述式），您可以减少记录的数据量，但也使系统只允许复原操作（认可的交易就消失了）。这个选项可使大量复制效能最佳化，但是由于用于向前复原的记录未被记录，因此当系统发生问题时，就必须重新开始数据库加载。
当满足下列状况时，大量加载操作的完全记录就会停用：
•  数据库选项 SELECT INTO/BULKCOPY 被设为 TRUE ，下列为使用sp_dboption预存程序的语法：
　
exec sp_dboption database_name, "select into/bulkcopy", TRUE
•  使用 Enterprise Manager 也可以设定该选项， 第八章 有 Enterprise Manager 的详细介绍。
　
•  加载数据的目标数据表未被设定为复写（复写在 第二十六章 、 第二十七章 及 第二十八章 有介绍）。
　
•  已经指定 TABLOCK 提示（关于提示的相关信息可参照本章 〈选择性参数〉 一节）。如果被加载的数据表已定义了索引，就不需要指定 TABLOCK 提示。
　
另外，当trunc.log on chkpt数据库选项设为 TRUE 时，会停止交易记录文件的记录。这也可改善大量复制的效能，但也代表当系统发生故障时，就无法执行向前复原或复原。
________________________________________
注意
您应该只在最初建立数据库，并且有将大量数据加载至数据库的需求时才将trunc.log on chkpt设为 TRUE，停止交易记录文件的记录会影响整个数据库，并会导致系统在发生问题时无法复原。因此在正常操作的情况下，如果「复原」这个项目对系统很重要，就不应该使用这个选项。如果已经将trunc.log on chkpt设为 TRUE，在完成加载操作后要记得将选项在设定为 FALSE。
________________________________________
trunc.log on chkpt可用sp_dboption设定，语法如下：
exec sp_dboptiondatabase_name, "trunc. log on chkpt", TRUE
________________________________________
说明
您可以在数据库 属性 窗口中的 选项 卷标页设定更多的选项，如图 24-1 所示。 限制存取 可以限制对特定角色或单一使用者存取数据库； 只读 设定不允许写入数据库。 预设为ANSI NULL 指定在预设状态下，数据库的数据行是否定义为 NULL 或 NOT NULL； 重复触发程序 让触发程序可以递归的触发； 自动更新统计资料 可让 SQL Server 在查询最佳化过程自动重建过期的统计资料； 损毁页侦测 可侦测到不完全的分页； 自动关闭 指定数据库将在其资源被释出并且所有使用者离开之后关闭； 自动压缩 指定 SQL Server 将会周期性的压缩数据库档案； 自动产生统计资料 指定查询最佳化所需的任何遗失统计数据将在最佳化过程中自动建立； 使用引号识别项 选取这个选项指定双引号只能用于识别项，例如数据行与数据表名称，而字符字符串必须括在单引号中。
________________________________________
锁定选项
　
使用table lock on bulk load选项还可改善大量复制的效能，在大量复制运作模式下，这个选项以单一数据表锁定取代多个数据列锁定。使用sp_tableoption来设定table lock on bulk load，参数的设定如下：
exec sp_tableoption "table_name", "table lock on bulk load", TRUE
加载完成后，记得要重新设定trunc.log on chkpt的参数。由于table lock on bulk load选项只有在大量复制时，才会影响数据表的锁定模式，所以当您没有执行大量复制时，不会降低执行效能。