SQL Server最受欢迎技巧:解读DBA

【IT168 技术分析】在向2009年告别之际，我们来回顾一下过去的一年中最受欢迎的SQL Server技巧，包括了OPENROWSET、FILESTREAM等函数的用法、密码工具介绍以及DBA日常工作建议等内容。

　　通过对这些精华文章的再次回顾，希望可以帮助您梳理一下这一年以来的工作以及学习心得，对未来一年更进一步打下更坚实的基础。

　　微软SQL Server中的批量复制程序(Bulk Copy Program，BCP)能让数据库管理员将数据批量导入表中或将数据从表中批量导入文档中。它还支持一些定义数据如何导出、导入到什么地方、加载哪些数据等选项。

　　本技巧讨论一些用bcp命令批量复制数据迁入或迁出SQL Server表的示例。这些示例在SQL Server 2005和SQL Server 2008上已经测试过。并且我还用了AdventureWorks样本数据库。

　　用bcp工具导入数据

　　一个最简单的操作就是你可以用bcp工具将数据从SQL Server表bulk-copy到文本文件。在 Windows命令提示符中插入命令，你就可以运行bcp命令了。例如以下命令，从 AdventureWorks数据库里的Sales.vSalesPerson视图复制数据到C:/Data/SalesPerson.txt文件：

　　bcp AdventureWorks.Sales.vSalesPerson out C:/Data/SalesPerson.txt - c –T

　　如同你看到的一样，bcp命令以工具名称开头，后面为完全合格表名database.schema.table。接下来就是out 关键字，关键字告诉bcp工具数据将会从该表中导出。目标文本文件的路径和文件名称紧跟out 关键字之后。注意本文中列出的命令例子可能包括很多行，但是所有的例子应该像一个单独的命令一样运行。

　　除了这些基本参数，bcp工具还支持控制工具行为的switch。在以上例子中，无论数据是以何种方式存储在源表中的，-c switch表示所有的数据都应是字符数据。如果你没有指定-c开关或其他相关类型的switch，你就需要在进入bcp命令后指定每个列的switch类型。

　　上述例子中另一个switch就是-T，它主要是告诉bcp工具使用可靠连接来关联SQL Server示例。如果你没有指定-T，你就必须提供用户名(-U switch)和密码(-P switch)，或者你需要提供相关信息。

　　因为在先前列举的例子中没有指定实例，bcp工具就在本地机上使用的默认实例。要指定一个SQL Server实例，就要用到-S switch，后面紧跟的是服务器名称，如下所示：

　　bcp AdventureWorks.Sales.vSalesPerson out C:/Data/SalesPerson.txt - c - T -

　　S Server01

　　Bcp工具现在和Server01上的默认实例连接。如果你想连接到具体实例而不是默认的实例，你就必须指定实例名称和服务器名称，如Server01/SqlSrv。 通过默认，bcp工具使用制表符分隔目标文件中里的域。但你也可以用-t switch调过这一操作，如下：

　　bcp AdventureWorks.Sales.vSalesPerson out C:/Data/SalesPerson.csv - c -

　　T - t,

　　在这种情况下，-t switch后有一个逗号，意思就是说数据域现在由逗号分开。这样做可让你讲数据保存到.csv 文件，便于你在Microsoft Excel文件中查看这些数据。 以上一些例子只限于将数据从表中导出。但是你还可以用bcp命令运行Transact-SQL查询、到出查询条件。例如以下bcp命令，包括只从vSalesPerson视图中检索SalesPersonID、FirstName和LastName的SELECT语句：

　　bcp " SELECT SalesPersonID, FirstName, LastName FROM AdventureWorks.Sales.vSalesPerson"

　　queryout C:/Data/SalesPerson.csv - c - T - t,

　　这种情况下引号里的查询通过的是bcp命令而不是表名称。此外，queryout取代了out关键字。但是命令其他部分和先前的例子相同。结果，SalesPerson.csv文件现在只包含三个指定列。你还可以让查询更加精炼：例如你可以包括限定只从源表中返回那些行的WHERE子句。

　　用bcp工具导出数据

　　Bcp工具使得导入数据和导出一样简单。要在这部分里运行这个示例，首先就要执行下面的T-SQL脚本，在AdventureWorks数据库里创建SalesPeople表：

　　 USE AdventureWorks

　　 GO

　　 IF OBJECT_ID (N ' SalesPeople ' , N ' U ' ) IS NOT NULL

　　 DROP TABLE dbo.SalesPeople

　　 GO

　　 CREATE TABLE dbo.SalesPeople (

　　SalesPersonID INT IDENTITY PRIMARY KEY ,

　　FirstName NVARCHAR ( 50 ) NOT NULL ,

　　LastName NVARCHAR ( 50 ) NOT NULL

　　)

 　　要导出数据，你需要一个源文件从中复制数据。例如下面的命令就用了最近创建的SalesPerson.csv文件加载数据到SalesPeople表：

　　bcp AdventureWorks.dbo.SalesPeople in C:/Data/SalesPerson.csv - c - T - t,

　　首先，你必须指定目标表，这种情况下紧跟其后的为代替out或queryout的in关键字。其次，你必须指定源文件的路径和文件名称，后跟任意可用的switch。在运行这一命令并查看结果时，要注意源文件如果包括售货员的ID，这些值就不能插入到SalesPersonID列。该列定义为IDENTITY列，这样我们就可以忽视源数据。要保留

　　原始值，你就必须在命令里增加-E switch，如下面的例子中所示：

　　bcp AdventureWorks.dbo.SalesPeople in C:/Data/SalesPerson.csv - c - T - t, - E

　　现在表中就包含了你想要得到的数据。

　　使用格式文件

　　在导入或导出数据时，你会发现源数据架构和目标数据架构不匹配。例如，文本文件中的这些列可能和目标表中的列的顺序不一致，或者说这些列的多少还不一样。你可以通过创建格式文件映射源文件和目标架构解决这个问题。我们通过以下示例看看它是如何工作的：

　　假如你使用了以下命令从 vSalesPerson视图中导出数据到SalesPeople.txt文件：

　　bcp " SELECT LastName, FirstName, SalesPersonID FROM AdventureWorks.Sales.vSalesPerson"

　　queryout C:/Data/SalesPeople.txt - c - T - t,

　　该命令使用了先前例子中同样的参数。但是注意这些列从视图中检索的顺序：LastName、FirstName、最后是SalesPersonID。 现在假设你打算使用该文件导入数据到SalesPeople表。SalesPeople表中列的顺序和文本文件中列顺序不同。要解决这个问题，你可以创建一个格式文件将这些列从源文件映射到目的文件。以下命令说明如何创建一个格式文件：

　　bcp AdventureWorks.dbo.SalesPeople format nul - f C:/Data/SalesPeople.fmt -

　　c - T - t,

　　先前的例子表明，命令首先指定了目标表。而这一次表明后面为关键字format nul，表示bcp工具应该创建格式文件。-f参数用于指定格式文件的路径和文件名称，其后为switch。最后在你运行该命令时，就生成了包括SalesPeople表架构的格式文本。

　　下面的数据显示上述命令生成的SalesPeople.fmt格式文本内容：

　　 10.0

　　 3

　　 1 SQLCHAR 0 12 "," 1 SalesPersonID ""

　　 2 SQLCHAR 0 100 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS

　　 3 SQLCHAR 0 100 "/r/n" 3 LastName SQL_Latin1_General_CP1_CI_AS

　　文件(10.0)第一行确定了目前使用的bcp版本。第二行(3) 确定了表中的列数，接下来的三行为列的相关信息：

　　·第一个域为列在源文件中出现的顺序。

　　·第二个域显示每个列的源文件数据类型。因为在生成文件时指定了–c switch，在从数据文件中提取时，所有的域都用字符型数据类型。插入数据时，SQL Server会将数据转换到正确的类型。

　　·第三个域表示域的前缀长度，SQL Server通常会用它来提供最紧凑的文件存储。在创建格式文件时如果你指定-c switch，那么就会自动用到0。

　　·第四个域代表特殊域数据类型字节长度。

　　·第五个域表明如何终止行和列。由于在创建格式文件时用了-t switch，源文件的域值就必须通过逗号终止。

　　·第六个域映射这些列在SQL Server表中的排列顺序。

　　·第七个和最后一个域提供了SQL Server表中字符列的整理信息。

　　要用格式文件将数据导入SalesPeople表中，我们必须如下修改文件：

　　 10.0

　　 3

　　 1 SQLCHAR 0 100 "," 3 LastName SQL_Latin1_General_CP1_CI_AS

　　 2 SQLCHAR 0 100 "," 2 FirstName SQL_Latin1_General_CP1_CI_AS

　　 3 SQLCHAR 0 12 "/r/n" 1 SalesPersonID ""

 　　你可以看到，列的顺序已经进行了修改，这一顺序就是它们在格式文件中的排列顺序。SalesPersonID列现在排在最后，并且以/r/n结尾。LastName列现在排在开头并且以逗号结尾。

　　修改、保存格式文件后，准备在bcp命令中用了。下面的例子说明如何调用格式文件：

　　bcp AdventureWorks.dbo.SalesPeople in C:/Data/SalesPeople.txt -

　　f C:/Data/SalesPeople.fmt –T

 　　注意，你从SalesPeople.txt文件中导入数据时，还必须用到-f switch调用格式文件。还要注意到你现在已经不需要包括-t 和-c switch，因为现在在格式文件中已经包括了这些信息。

　　无论你用的是格式文件还是只运行基本命令，你现在都应该更好地了解到了如何使用bcp工具。记住bcp工具支持的switch比我所列举的要多得多。同时，本技巧还提供了有关启用bcp工具、轻松将数据导入到SQL Server表以及导出数据到文本文件的比较充足的信息。

 

　　2.使用SQL Server的OPENROWSET函数

　　你可能常常会需要运行一个ad hoc查询从远程OLE DB数据源提取数据，或者批量向SQL Server表导入数据。在这种情况下，你可以在T-SQL(Transact-SQL，微软对SQL的扩展)中用OPENROWSET函数给数据源传入一个连接串和查询来提取需要的数据。

　　你可能常常会需要运行一个ad hoc查询从远程OLE DB数据源提取数据，或者批量向SQL Server表导入数据。在这种情况下，你可以在T-SQL(Transact-SQL，微软对SQL的扩展)中用OPENROWSET函数给数据源传入一个连接串和查询来提取需要的数据。

　　你可以使用OPENROWSET函数从任何支持注册OLE DB的数据源获取数据，比如从SQL Server或Access的远程实例中提取数据。如果你用OPENROWSET从SQL Server实例中获取数据，该实例必须配置为允许ad hoc分布式查询。

　　要配置远程SQL Server实例支持ad hoc查询，需要使用系统存储过程sp_configure先设置advanced options，再启用Ad Hoc Distributed Queries(ad hoc分布式查询)。请看下面的T-SQL脚本：

　　 EXEC sp_configure ' show advanced options ' , 1 ;

　　 GO

　　 RECONFIGURE ;

　　 GO

　　 EXEC sp_configure ' Ad Hoc Distributed Queries ' , 1

　　 GO

　　 RECONFIGURE ;

　　 GO

 　　要注意的是，在运行完存储过程之后，你必须运行“RECONFIGURE”命令。 一旦你配置好了远程SQL Server实例，你就可以对它使用OPENROWSET函数。这个函数可以在SELECT语句的FROM从句里使用。下面的例子显示了该函数的基本语法：

　　 OPENROWSET ( ' provider ' , ' connection string ' , target)

 　　可以看到，这个函数有三个参数：

　　·Provider —— 某特定数据源支持的OLE DB提供者的人机友好名称(ProgID)。Provider的名字必须用单引号括起来。

　　·Connection string —— 连接串。它是与具体提供者provider相关的字符串，包括连接到给字符串中指定的数据源所需要的细节信息。根据provider的不同，连接串信息需要用一对或多对单引号括起来。

　　·Target —— target参数可以使一个数据库对象或者一个查询。

　　·Object —— 数据库对象的名字，比如表或者视图的名称。对象的完整名字必须提供，它们不需要用单引号括起来。

　　·Query —— query是从远程数据源提取数据的Select语句。Query必须用单引号括起来。

　　下面的例子展示了OPENROWSET函数的用法：

　　 SELECT Employees. *

　　 FROM OPENROWSET (

　　 ' SQLNCLI ' ,

　　 ' Server=SqlSrv1;Trusted_Connection=yes ' ,

　　 ' SELECT EmployeeID, FirstName, LastName, JobTitle

　　FROM AdventureWorks.HumanResources.vEmployee

　　ORDER BY LastName, FirstName '

　　) AS Employees

 　　注意该Select语句的FROM从句中使用了OPENROWSET函数和3个参数。第一个参数SQLNCLI是SQL Server OLE DB提供者的名称。

　　第二个参数是连接串。对于SQL Server提供者，整个连接串应该被单引号括起来，连接串内的每一组信息用分号分割。在上面的例子中，第一组信息指定了目标服务器SqlSrv1，第二组信息指定了该连接可信任连接。在指定目标Server时，如果实例不是该Server的默认实例，则一定要在连接串中指定实例名。(注意：SQLNCLI提供者还支持其他参数。)

　　OPENROWSET函数的最后一个参数是实际执行的Select语句。注意SQL语句中使用了完整对象名来访问视图。

　　这样我们就可以使用OPENROWSET函数了。函数返回一个结果集(我把它用AS命名为“Employees”)，From使用该结果集的方式与使用其他普通查询的方式一样。 我们在上面提到，你也可以从SQL Server以外的数据源提取数据。例如：下面的Select语句查询微软Access数据库的Employees表。

　　 SELECT Employees. *

　　 FROM OPENROWSET (

　　 ' Microsoft.Jet.OLEDB.4.0 ' ,

　　 ' C:/Data/Employees.mdb ' ; ' admin ' ; ' ' ,

　　 ' SELECT EmployeeID, FirstName, LastName, JobTitle

　　FROM Employees

　　ORDER BY LastName, FirstName '

　　) AS Employees

 　　你可能注意到了，这次的provider不同于我们在访问SQL Server时使用的Provider。在本例中，Provider是Microsoft.Jet.OLEDB.4.0(注意：对于Access 2007，有新的Provider可用)。

　　连接串与前面例子中的写法也不一样。整个连接串从头到尾分成了三部分，每一部分都被单引号单独括起来，各部分之间用分号分割。

　　第一部分指定了Access数据库文件的路径和文件名，后面紧跟着是用户账号admin(Access数据库内部的管理员账号)。第三部分是一个空字符串，是Access数据库的密码。因为admin账号没有设定密码，所以使用空字符串。如果该账号设置了密码，应该把密码写在第三部分。

　　整个连接串与后面用来从Access数据库查询数据的Select语句用逗号“，”隔开。(我在Access中使用的Employees表是从SQL Server的vEmployee视图导入的) 这就是从Access数据库查询数据要做的全部事情。你的查询会返回一个结果集，该结果集与访问本地SQL Server数据库时得到的结果集类似。

　　你也可以使用OPENROWSET函数从多个数据源中查询数据。例如：下面的例子我使用inner join(内连接)从远程SQL Server实例和Access数据库查询数据。

　　 SELECT e1.EmployeeID, e2.FirstName, e2.LastName, e1.JobTitle

　　 FROM OPENROWSET (

　　 ' SQLNCLI ' ,

　　 ' Server=SqlSrv1;Trusted_Connection=yes; ' ,

　　 ' SELECT EmployeeID, FirstName, LastName, JobTitle

　　FROM AdventureWorks.HumanResources.vEmployee '

　　) AS e1

　　 INNER JOIN OPENROWSET (

　　 ' Microsoft.Jet.OLEDB.4.0 ' ,

　　 ' C:/Data/Employees.mdb ' ; ' admin ' ; ' ' ,

　　 ' SELECT EmployeeID, FirstName, LastName, JobTitle

　　FROM Employees '

　　) AS e2

　　 ON e1.EmployeeID = e2.EmployeeID

　　 ORDER BY e2.LastName, e2.FirstName

 　　注意：外层的Select语句从两个表返回数据——从SQL Server返回员工ID和工作头衔，从Access数据库返回姓和名。由于你可以得到可靠的连接查询，尽管你是从本地SQL Server实例连接表中查询的数据，你可以处理这些数据。

　　现在我们来看看OPENROWSET函数的另一个重要功能——批量导入。为了举例需要，我在AdventureWorks数据库中用下面的脚本创建了表Employees并导入数据。

　　 USE AdventureWorks

　　 GO

　　 IF OBJECT_ID (N ' Employees ' , N ' U ' ) IS NOT NULL

　　 DROP TABLE dbo.Employees

　　 GO

　　 SELECT EmployeeID, FirstName, LastName, JobTitle

　　 INTO Employees

　　 FROM HumanResources.vEmployee

　　 GO

　　 ALTER TABLE Employees

　　 ADD ResumeFile VARBINARY ( MAX ) NULL

　　 GO

　　注意：我没有把ResumeFile列的数据导入，它的数据类型是VARBINARY(MAX)。我会用下面的Update语句把Employee1.docx文件作为二进制数据批量导入到该列。

　　 USE AdventureWorks

　　 GO

　　 UPDATE Employees