18.2  格式化文件

在上一节中，提到了格式化文件的使用。实际上，就我们的观点而言，由于在SQL Server 2005中格式化文件发生了很多变化，因而这是很令人兴奋的领域。

可以把格式化文件想成是导入模板，并且，在如下的情况下，更容易支持重复的导入操作：

l    源文件与目标表的结构或顺序不匹配；

l    想要跳过目标表中的列；

l    文件中包含的数据使默认数据输入和顺序很困难或不能实现。

从这一个版本开始，格式化文件出现了两种类型：非XML和XML。我们将先讨论“老”方法（非XML版本），然后，在了解了每一种非XML版本后，讨论新的XML格式化文件。

为了能更好地了解每一种格式化文件是如何工作的，来看一些特定的例子。首先，看一下当源和目标匹配时是如果构建文件的。接下来，可以把这种情况与下面的情况进行比较：源文件中的字段数目与表列的数目不匹配，或者源文件中的字段按照与表列不同的顺序排列。

你可以创建默认的格式化文件（为了向后兼容性的原因，该文件是非XML的），当在交互模式中运行BCP时，该文件能够用作源。在提示输入列值信息后，可以选择保存该文件。默认的文件名是BCP.fmt，但是，也可以用任何有效的文件名来命名格式化文件。

如果要以这种方式为AdventureWorks数据库的HumanResources.Department表创建默认的格式化文件，可以运行：

这是一种快速创建格式化文件的便利的方式，然后，你可以在需要的时候编辑创建的格式化文件。由于能够对任何表这样做，因此，可以使用BCP在格式化文件的需求上获得一个起点。

对每一个文件接受默认的前缀和数据长度信息，并且，在这里以逗号作为字段分隔符。SQL Server将在你输入了所有的格式信息后，提示你保存格式化文件，我在此处将把它保存为Department.fmt。然后，可以使用任何文本编辑器（例如Windows Notepad）编辑格式化文件，使其满足特定的需求。

下面来看一下刚才产生的格式化文件：

文件中的前两行说明了BCP的版本号以及宿主文件中字段的数目。剩下的行描述宿主数据文件，以及字段如何与目标列和顺序相匹配。

第一列是宿主文件字段号。从1开始编号，一直到字段的总体数目。接下来是宿主文件数据类型。示例文件中混合了几种数据类型。所有的文本都使用Unicode格式，因此，所有字段的数据类型都是SQLNCHAR——如果此时这里没有特殊的字符，我们可以只使用SQLCHAR（ASCII）格式。

接下来的两列说明了数据字段的前缀长度和数据长度。前缀是字段中前缀字符的数目。前缀描述了实际BCP文件中数据的长度，并允许数据文件的大小为最紧凑。数据字段是存储在字段中数据的最大长度。接着，是字段终止符（分隔符）。在本例中，使用逗号作为字段终止符，并且，用换行符作为行终止符。再往后的两个列，通过提供服务器列顺序和服务器列名来说明目标表列。本例中，由于服务器列和宿主字段之间是直接匹配的，故列和字段号是相同的，因此，不必以这种方式来指明。最后（但并非最不重要），是每列的排序规则（要记住，在SQL Server 2000和更新的版本中，对于表中的每一列可以有不同的排序规则）。

现在，让我们来讨论XML版本。要创建XML版本的格式化文件，使用的命令几乎一样，只是这里增加了-x开关：

最终产生的文件看上去完全不同：

注意，在这里，所有的事情都被明确地标示出来。另外，这里有一个与XML格式化文件相关联的XML模式文档，这意味着可以在选择的XML编辑器中验证XML。

我钟爱新的XML格式化的版本，关于这一点我不想做任何辩解。对于我来说，如果不必担心向后兼容性问题，傻瓜都知道使用这种版本。

尽管早期的格式化文件同样能起作用，但是，每当我大量使用它们时，我都要考虑购买痛苦缓解公司的股票——如果必须做默认之外的事情，那它们真是很令人头痛。与它们有关的所有事情都必须是“小心谨慎的”，在较大的表中，由于没有清晰地分隔开字段，很容易遗漏打字错误。XML标记将解决所有这些问题，并能明确每一个小条目的作用——调试起来容易得多。

18.2.1  当列不匹配时

如果世界上的一切都完美无暇，如果我们收到的数据文件总是正好与表类似，那该有多好。

但是，总是要走出理想的境地。我对于眼下所处的世界还算满意，不过，这里几乎没有完美的地方，并且，需要进行大容量操作的数据文件类型很少与它们要到达的目标表类似。那么，当源数据文件与目标表不按照我们希望的方式匹配时，要如何做呢？或者，按另一种方式进行，又该怎样呢——从一个表到一个预期的与之不完全相同的数据文件格式。

幸运的是，通过格式化文件，我们能够处理源与目标数据之间可能存在的几种不同的差异。接下来，讨论这些情况。

1．文件中的列比表中的列少

我们先讨论数据文件中的字段比目标表中的列少的情况。在这种情况下，需要修改已经在使用的格式化文件，以说明哪一个列在数据文件中不存在，因而要忽略表中的哪一列。要实现这一目的，可以把每一个缺失字段的前缀长度和数据长度设置为0，并把要跳过的列的表列顺序号设置为0。

例如，假设数据文件中只有DepartmentID、Name和GroupName，则需要把格式化文件修改为：

正如你能够看出的，ModifiedDate字段和列都以0表示。由于没有提供ModifiedDate，并且该列有默认值（Getdate()），因此，在插入的行中将使用默认值。

XML版本看起来没有太大的不同：

文件中没有列需要定义，因此就没有定义。在ModifiedDate列中不复制任何东西，于是我们跳过该列（依赖该列的默认值）。

2．文件中的列比表中的列多

当数据文件中的列比表中的列多时，这种情形实际上与前面讨论过的数据文件中的列较少的情形惊人的相似。这里唯一要特别处理的地方是，必须为额外的字段添加列信息，但前缀长度、数据长度以及列顺序号都设置为0：

这一次，宿主文件中包含部门的创建日期字段。目标表中没有接收该信息的列。把字段添加到最初的格式化文件中，并加入列顺序号为0的两个伪列。这将强迫BCP忽略该字段。

在这种情形下，XML版本必须处理文件中有需要提及的列的事实。然而，对于目标表而言，可以继续忽略该字段：

3．字段顺序不匹配

另一种可能的情形是：宿主数据文件与目标表拥有相同的字段，但是，字段的顺序不匹配。在这种情况下，可以修改服务器列顺序，从而与宿主数据文件中的字段顺序匹配：

本例中，在源数据文件里，组名在部门名之前列出。于是，我们对服务器列顺序进行了修改，以反映这一事实。注意，服务器列的列出顺序并没有改变，只是交换了列的顺序号而已。

那么，如果要把这些修改转换到XML中，只需对最初的XML文件改动一两个字段即可：

18.2.2  使用格式化文件

让我们来看一个导入中使用格式化文件的例子。该命令将以名为shortdept.txt的文件为基础，把记录复制到Department表中。我们使用ShortDept.fmt作为非XML格式化文件的例子，使用ShortDeptX.fmt作为XML格式化文件。

为了增加一点变化，上面的示例命令行使用了SQL Server身份验证，而不是Windows身份验证。如果你希望使用Windows身份验证，只需用前面经常使用的-T参数替换上面的-U和-P参数即可。

本例中使用的示例文件ShortDept.txt、ShortDept.fmt以及ShortDeptx.fmt，可以从Wrox网站或ProfessionalSQL.com下载。

18.2.3  最大化导入的性能

要最大化BCP的性能，一个很显而易见的方法是，确保目标表满足以不记录日志操作的方式执行BCP的所有要求。这或许意味着你必须：

l    删除目标表上任何现有的索引。实际上，仅当你想要以最小化方式记录日志的操作时，这才是必需的，事实是，在大容量操作期间停用索引而不考虑日志的状态，这是极大提高性能的方法。不过，在完成了大容量操作后，要确保重建索引。

l    试图按照与聚集索引（如果有的话）相同的顺序来创建源数据文件。在重建索引期间，这将允许使用SORTED_DATA_REORG选项，该选项能极大加快索引的创建速度（从而缩减BCP操作的总时间）。甚至当必须保留聚集索引时，在排好序的数据上执行BCP将允许使用ORDER列选项（在-HINT选项中）。

l    确保维护属性设置为简单或不记录日志。如果设置为完全恢复模式，则将不允许BCP按最小方式记录日志操作。

在向表中导入数据时，如果想要获得额外的性能提升，可以从多个客户端执行数据并行加载。要实现这一点，必须：

l    使用TABLOCK提示。

l    删除所有索引（可以在操作完成后重建索引）。

l    将服务器恢复选项设置为Bulk-Logged（大容量日志恢复模式）。

这是如何提升性能的呢？这里不是导入一个很大型的文件，而是将其分解为较小型的文件。然后，从多个客户系统进行BCP，每一个客户端导入较小型文件中的其中一个。显然，只有在这样的导入带来的性能提升所节省下来的时间多于准备源数据文件并将它们复制到客户端所花费的时间时，才会对这样的导入感兴趣。

SQL Server 6.5或更早的版本不支持并行加载。

通过让SQL Server忽略CHECK约束和触发器（默认的选项），能够获得额外的性能提升。要记住，这样做可能会导致加载的数据违反表的CHECK约束和任何由触发器强制的数据完整性规则。