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

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选项只有在大量复制时，才会影响数据表的锁定模式，所以当您没有执行大量复制时，不会降低执行效能。

　
 
图24-1　数据库「属性」窗口的「选项」卷标页
________________________________________
说明
要取得table lock on bulk load选项的执行效能，您必须使用 TABLOCK 设定。
________________________________________
大量复制工具程序
　
使用 BCP，可从一个数据文件中复制数据到 SQL Server，或是从 SQL Server 复制数据到数据文件中。若要将数据从其它数据库传送到 SQL Server，BCP 也很实用。本节会学习如何使用 BCP 及其选项，并学习如何将数据格式化，以便使用 BCP 将数据汇入 SQL Server 或从 SQL Server 汇出。
BCP 语法
　
 BCP 是指令行的执行程序，使用 BCP 需要特定的参数，而 BCP 提供许多选择性的参数供您使用。 BCP 指令的格式如下：
bcp {[[database_name.][owner].]{table_name|view_name} | "query"}
    {in | out | queryout | format}data_file
    [-mmax_errors] [-fformat_file] [-eerror_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
    [-q] [-Ccode_page] [-tfield_term] [-rrow_term]
    [-iinput_file] [-ooutput_file] [-apacket_size]
    [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h "hint[,...n]"]
必要参数
　
必要参数指定数据的撷取和插入的位置。如之前所述，使用 BCP 可以从数据文件撷取和插入数据到一个 SQL Server 数据表或检视表中，或是从一个数据表（或检视表）撷取和插入资料到数据文件中。
在大量复制操作中所使用的数据表或检视表，可用以下两种方式指定：第一种方法为使用table/view_definition参数，最简单的定义即为数据表或检视表的名称。如之前示范过的指令，可以指定数据表或检视表所在的数据库的名称，或指定数据表或检视表的拥有者。如果不指定数据库名称，数据表或检视表所在的数据库即为使用者登入时定义的预设数据库， 第三十四章 会详细讨论使用者定义。
另一种方法为使用查询指定数据表或检视表，使用这种方法可以指定要在资料表或检视表撷取的资料。（table/view_definition参数可用来指定用于撷取或插入数据的数据表或检视表。）查询必须加上双引号，可以为加上或不加上 ORDER BY 子句的 SELECT 陈述式。指定查询定义时，必须同时指定queryout参数（将在表 24-1 讨论）。
在大量复制操作中数据文件的位置可用data_file参数指定，但所指定的路径必须是有效路径。
最后，可指定一个或多个列在表 24-1 的参数
表24-1　指定大量复制的参数
参数  描述
In 从档案夹中大量复制数据到 SQL Server 数据库的数据表或检视表中。
Out 从 SQL Server 数据表或检视表撷取及大量复制数据到档案夹中。
queryout 指定在查询大量复制数据时，才从 SQL Server 数据库中撷取查询所定义的数据，当数据经撷取查询所选出后，会大量复制到档案夹。
Format BCP 会建立新的格式文件来执行大量复制作业，所指定的格式化选项（-n、-c、-w、-6 或 -N）和资料表或检视表的分隔符可用于建立格式档。如果使用了format参数，-f 选项也必须同时指定。格式档能让您储存 BCP 定义，之后当您再度使用 BCP 时，就不需要重复定义。
选择性使用的参数
　
您可选择使用列在表 24-2 的参数以修改 BCP 执行大量复制的方式。
表24-2　指定大量复制的选择性参数
参数  描述
-a packet_size 指定在客户端和服务器端之间经由网络封包所传送的字节数目。
-b batch_size 指定每一笔批次操作所复制的数据列数，每一笔批次操作皆视为一笔交易。根据默认值，指定数据文件内的所有数据列会被当成同一笔批次操作进行复制，因此若执行大量插入数据，可能会指定此选项，以使每一笔批次操作在执行完成时，解除资料表锁定，这样便能执行其它处理程序（也就是使资源锁定的时间降低）。
-c 指定 BCP 使用 char 字符数据型别来执行大量复制作业。
-e err_file 指定 BCP 错误文件记录的路径。
-f format_file 指定 BCP 所使用过的格式文件的路径。如果 BCP 执行时使用format选项，就会建立之前所提到的格式档（format file）。如果使用了该格式档，就不需使用其它格式选项。
-h "hint[,...n]" 指定大量复制时所使用的提示，以下为可选择的提示：
•   ORDER（column[ASC | DESC]） 指定数据行中的数据排序。
　
•   ROWS_PER_BATCH = number 指定每个批次操作的数据列数目这个选项和 -b 类似，但不应和 -b 联合一起使用。-b 选项将数据列指定的批次操作视为单一交易，传送到到 SQL Server。在未指定 -b 时，整个数据文件夹会视为单一交易，传送到 SQL Server，并使用 ROWS_PER_BATCH 评估测量加载大小尺寸，使大量加载最佳化。
　
•   KILOBYTES_PER_BATCH = number 指定每笔批次操作的千字节（KB）大约数目，此选项和 -b 类似，不同之处在于此选项以千字节 KB 为单位指定批次操作大小。
　
•   TABLOCK 指定在大量复制加载期间使用数据表层级的锁定。这个提示可大为提高效能，因为减少了数据表的锁定资源竞争只在大量复制作业期间保持锁定可降低数据表的锁定争论。
　
•   CHECK_CONSTRAINTS 指定大量复制加载期间检视条件约束。根据默认值，会忽略这些条件约束。
　
-i input_file 指定响应档的名称。响应文件中包含使用互动模式执行 BCP 时，对每个字段的指令提示 BCP 问题的响应。
-k 指定空的数据行应使用保留 Null 值，而不使用是默认值。
-m max_errors 指定在结束 BCP 操作取消大量复制作业之前，允许发生错误的最多次数。如果若未包含这个选项，则默认值为 10。
-n 指定 BCP 使用 原生（native） 数据型别。
-o output_file 指定接收 BCP 输出的输出档。这个输出文件可用一般的文字文件开启，如 Microsoft 的 Notepad 或其它工具记事本等等。
-q 指定当数据表或检视表名称包含非 ANSI 字符时（例如空格键），必须使用引号识别项双引号。
-r row_term 指定数据列终止符号，默认值是新行字符（Newline Character，\n）。
-t field_term 指定字段终止符号（也称为分隔符），默认值是 tTab 字符。
-v 打印 BCP 的版本编号和版权信息。
-w 指定 BCP 使用 Unicode 字符执行大量复制作业。
-C code_page 指定数据文件中数据的字码页（Code Page）。
-E 指定在被复制的汇入的档案中包含呈现识别 Identity 数据行的值。
-F first_row 指定大量复制操作的起始第一个数据列编号。若未指定数据列编号，第一个数据列的默认值为 1。如果要跳过在数据文件的标题信息，这个选项相当适合。
-L last_row 指定大量复制操作最后结束的数据列编号，默认值为 0（0 表示结束的数据列就是数据文件的最后一个数据列），表示指定数据文件的最后一个数据列。如果只想复制特定数目的数据列，这个选项相当适合。
-N 指定 B

[1] [2] [3] [4]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18