数据库程序设计课件

数据库程序设计——SQLServer2000数据库程序设计第1章SQLServer概述第2章创建和管理数据库第3章创建数据类型和表第4章实现数据完整性第5章Transact-SQL介绍第6章使用Transact-SQL查询工具第7章检索数据第8章数据分组与汇总第9章多表联接第10章子查询第11章修改数据第12章全文索引查询

第13章规划索引第14章创建和维护索引第15章实现视图第16章实现存储过程第17章实现用户定义函数第18章实现触发器第19章多服务器编程第20章优化查询性能第21章分析查询第22章管理事务和锁第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作分布式查询介绍分布式查询支持SQLServer用户访问存储在多个SQLServer实例中的分布式数据存储在各种可使用OLEDB提供程序访问或具有ODBC驱动的关系和非关系数据源中的异类数据从SQLServer中访问OLEDB数据源特殊查询：当不需要多次访问数据源的远程数据时，可使用OPENROWSET或OPENDATASOURCE函数编写特殊查询链接服务器查询：若要多次访问远程数据，可使用链接服务器和四部分对象名称19.1分布式查询介绍分布式查询介绍（续）指定在何处处理对OLEDB

数据源的查询本地SQLServer：对链接服务器，SQLServer默认在本地服务器处理分布式查询远程OLEDB数据源（直接传递查询）：可以使用OPENQUERY函数和链接服务器指定对查询的处理在远程服务器上进行，这称为直接传递查询检查连接设置在发出分布式查询的对话中，必须打开ANSI_NULLS和ANSI_WARNINGS选项19.1分布式查询介绍第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作在远程数据源上执行特殊查询OPENROWSET函数允许用户连接并访问远程数据源的数据，而不用设置链接服务器

语法：OPENROWSET(‘提供程序名‘{’数据源’;‘用户名’;

‘密码’|‘提供程序连接字符串’},{[目录名称.][架构名称.]对象名称|‘查询'})19.2在远程数据源上执行特殊查询参数描述提供程序名数据源相应的OLEDB提供程序的惟一的友好名数据源由OLEDB提供程序解释的数据源名称用户名传递到指定OLEDB提供程序的用户名密码将被传递到OLEDB提供程序的用户密码连接字符串提供程序特定的连接字符串，惟一标识了一个数据源目录名称目录或数据库的名称，其中驻留着指定的对象架构名称架构的名称或指定对象的对象所有者名称对象名称对象名称，它惟一地标识出将要操作的对象查询发送到提供程序并由提供程序执行的查询字符串。若不单指定了远程对象名称，还指定了查询，则查询作为直接传递查询执行在远程数据源上执行特殊查询（续）特殊查询示例使用OPENROWSET函数执行查询的事实和指导原则如果OLEDB提供程序在指定的数据源中支持多个目录和架构（对SQLServer而言，是数据库和对象拥有者），那么就需要目录及架构名称，否则可以省略传递到OLEDB提供程序的用户名的权限确定相关连接的权限OPENROWSET函数可以使用在SELECT语句FROM子句中的表名的位置19.2在远程数据源上执行特殊查询SELECTa.*FROMOPENROWSET('SQLOLEDB','LONDON1';

'newcustomer';'mypassword',

'SELECTProductID,UnitPrice

FROMNorthwind.dbo.ProductsORDERBYUnitPrice')

ASa第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作设置链接服务器环境19.3设置链接服务器环境链接到远程数据源建立链接服务器安全性配置链接服务器选项获得关于链接服务器的信息设置链接服务器环境（续）19.3设置链接服务器环境使用链接服务器的原因链接服务器提供了使用跨SQLServer联接和其他查询的手段通过使用链接服务器，可将Transact-SQL语句直接提交给远程SQLServer，这些语句可作为分布式事务的一部分执行使用链接服务器的事实和指导原则可访问存储在多个SQLServer上的分布式数据，以及存储在各类关系和非关系数据源的异类数据若具有OLEDB提供程序，非SQLServer的其他数据源也可以定义为链接数据库对于需要经常访问的数据源，应定义链接服务器关于链接服务器的信息存储在sysservers

系统表中链接到远程数据源为了在远程SQLServer

或OLEDB数据源上执行Transact-SQL语句，必须建立到远程服务器或数据源的连接企业管理器系统存储过程sp_addlinkedserver

语法：sp_addlinkedserver[@server=]‘服务器名称’

[,[@srvproduct=]‘产品名称’][,[@provider=]‘提供程序名称’][,[@datasrc=]‘数据源名称’][,[@location=]‘位置’]

[,[@provstr=]‘提供程序连接字符串’][,[@catalog=]‘目录名称’]19.3.1链接到远程数据源参数描述@server要创建的链接服务器的本地名称@svrproduct要添加为链接服务器的OLEDB数据源的产品名称@provider数据源相应的OLEDB提供程序的惟一的友好名@datasrc由OLEDB提供程序解释的数据源名称@locationOLEDB提供程序所解释的数据库的位置@provstrOLEDB提供程序特定的连接字符串，它可标识惟一的数据源@catalog建立OLEDB提供程序的连接时所使用的目录链接到远程数据源（续）链接到远程SQLServer若链接到运行SQLServer的服务器，只需要提供@srvproduct

和@server两个参数，不必指定其他参数。SQLServer自动使用SQLServerOLEDB程序提供者（N’SQLOLEDB’）链接到OLEDB数据源若链接到SQLServer之外的其他数据源，则当创建链接服务器时，需要指定所有参数19.3.1链接到远程数据源建立链接服务器安全性在本地服务器和远程服务器间建立安全性当用户登录到本地SQLServer并执行分布式查询时，本地SQLServer代表用户登录到远程SQLServer若用户的账户和密码在本地和远程的SQLServer中都存在，则本地SQLServer可以使用用户凭据登录到远程SQLServer中可以使用系统存储过程sp_addlinkedsrvlogin

在本地和远程SQLServer之间映射用户账户和密码可以链接到多个服务器，以及随着每个服务器更改，保留原始客户端的身份验证凭据，这称为安全账户委托若链接服务器不支持安全账户委托，则必须设置从Windows身份验证登录账户到链接服务器登录账户的本地登录映射19.3.2建立链接服务器安全性建立链接服务器安全性（续）系统存储过程sp_addlinkedsrvlogin语法：sp_addlinkedsrvlogin[@rmtsrvname=]‘远程服务器名称’

[,[@useself=]‘是否使用自己的凭据’]

[,[@locallogin=]‘本地登录账户’]

[,[@rmtuser=]‘远程用户名’]

[,[@rmtpassword=]‘远程密码'19.3.2建立链接服务器安全性参数描述@rmtsrvname应用登录映射的链接服务器名称@useself决定SQLServer登录账户是使用其自己的凭据还是@rmtuser

和@rmtpassword

参数的值连接到@rmtsrvname

参数指定的服务器。若使用Windows身份验证登录账户，则@useself

不能为TRUE值@locallogin可选的本地服务器上的登录账户。若使用了，@locallogin

必须存在于本地服务器上。若此值为空，则所有本地SQLServer服务器上的登录账户将映射到@rmtuser

指定的远程服务器上的账户@rmtuser可选的当@useself

为FALSE时连接到@rmtsrvname

的用户名@rmtpassword可选的与@rmtuser

关联的密码配置链接服务器选项通过使用sp_serveroption

系统存储过程为链接服务器设置选项 语法：sp_serveroption[‘服务器名称’][,‘选项名称’][,‘选项值']只有sysadmin

服务器角色的成员才能设置选项Sp_serveroption

支持的选项CollationCompatible

如果该选项设置为true，则SQLServer假定链接服务器中的所有字符在字符集和排序规则上与本地服务器兼容CollationName和UseRemoteCollationCollationName：如果useremotecollation是true，而数据源不是SQLServer数据源，则指定远程数据源使用的排序规则名称UseRemoteCollation：确定是使用远程列的排序规则还是本地服务器的排序规则19.3.3配置链接服务器选项配置链接服务器选项（续）Sp_serveroption

支持的选项（续）DataAccess

启用和禁用分布式查询访问的链接服务器仅用于通过sp_addlinkedserver

添加的sysserver

项RPC和RPCoutRPC选项从给定的服务器启用RPCRPCout选项对给定的服务器启用RPCLazySchemaValidation

确定是否检查远程表的架构。如果为true，则在查询开始时跳过远程表的架构检查推迟架构检查可以提高性能19.3.3配置链接服务器选项获得关于链接服务器的信息系统存储过程sp_linkedserverssp_catalogs

sp_indexessp_primarykeys

sp_foreignkeyssp_tables_ex

sp_columns_ex

返回定义在本地服务器上的链接服务器列表指定链接服务器的目录和描述列表指定远程表的索引信息指定表的主键列，每列一行指定远程表上定义的外键指定链接服务器中表的表信息给定链接服务器表的所有列或指定列的信息，每列一行19.3.4获得关于链接服务器的信息第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作在链接服务器上执行查询使用链接服务器执行链接服务器查询执行直接传递查询19.4在链接服务器上执行查询使用链接服务器SQLServer如何优化远程查询SQLServer从原始分布式查询中析取只访问提供程序数据源中的远程表的语法元素，然后在提供程序上执行简化后的查询引用链接服务器上的对象当进行分布式查询的时候，必须以四部分名称引用链接对象：链接服务器名称.目录名称.架构名称.对象名称19.4.1使用链接服务器参数描述链接服务器名称包含分布式查询所引用对象的链接服务器名目录名称包含分布式查询所引用对象的目录名，相当于数据库架构名称包含分布式查询所引用对象的架构名，是属于特定用户的对象的集合，相当于对象拥有者名称对象名称需要访问的对象使用链接服务器（续）允许的Transact-SQL语句：当使用链接SQLServer时，可在链接数据上执行带WHERE子句或JOIN子句的SELECT语句INSERT、UPDATE和DELETE语句不允许的Transact-SQL语句在链接服务器上使用CREATE、ALTER或DROP语句链接表中某个大对象列在选择列表中且包括ORDERBY子句的SELECT语句READTEXT、WRITETEXT和UPDATETEXT语句19.4.1使用链接服务器执行链接服务器查询当查询链接服务器上对象的时候，应该使用完全合法的四部分名称来引用对象SELECTCompanyNameFROMAccountingServer.NorthwindRemote.dbo.SuppliersSELECTCompanyName,PhoneINTOPhoneList

FROMAccountingServer.NorthwindRemote.dbo.Suppliers示例1示例219.4.2执行链接服务器查询SELECT*FROMOPENQUERY

(AsiaServer,'SELECTProductID,Royalty

FROMNorthwind.dbo.ProductInfo')执行直接传递查询当查询链接服务器的时候，可以使用OPENQUERY函数在链接服务器上执行直接传递查询

语法：OPENQUERY(链接服务器名称,‘查询字符串')使用OPENQUERY函数进行直接传递查询可以在SELECT语句中用OPENQUERY函数的结果代替表名的位置若OLEDB提供程序支持，还可以用OPENQUERY函数的结果作为INSERT、UPDATE或DELETE语句的目标表直接传递查询示例19.4.3执行直接传递查询示例第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作在链接服务器上执行存储过程19.5在链接服务器上执行存储过程在链接服务器上执行存储过程，可允许连接到一个SQLServer的客户端执行另一个SQLServer上的存储过程，而不用建立到那个SQLServer的客户端连接

语法：EXECUTE服务器名称.数据库名称.拥有者名称.存储过程名称客户端连接的服务器接受客户端请求，将请求发送到链接服务器。EXECUTE语句必须包含链接服务器的名称链接服务器处理请求，将结果返回给原来的服务器，后者再接着把结果传递给客户端客户端或服务器上的应用程序都可以发起链接存储过程请求第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作管理分布式事务分布式事务是涉及来自两个或多个源的资源的事务使用MSDTC管理分布式事务Microsoft分布式事务处理协调器(MSDTC)在所有参与事务的服务器间协调分布式事务的提交。服务器可以包括SQLServer以及中间层组件可在SQLServer存储过程中使用MSDTC协调多台运行SQLServer的计算机或SQLServer与链接服务器间的事务可将运行SQLServer的远程计算机添加到分布式事务中使用组件服务管理分布式事务使用组件服务以部署和管理分布式事务。其底层实现机制是MSDTC。在中间层的组件可以参与分布式事务19.6管理分布式事务管理分布式事务（续）分布式数据的处理SQLServer提供了链接服务器、RPC和两阶段提交协议等特性，使用户可以很容易地管理和使用分布式环境里的数据分布式事务的两阶段提交RPC：是可以从远程服务器调用的存储过程，允许服务器到服务器通讯RPC也可以被MSDTC服务协调以确保事务的ACID属性默认在事务的上下文中不执行RPC也可以通过定义和访问链接服务器的方式管理分布式数据19.6管理分布式事务第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作修改链接服务器上的数据当要修改链接服务器上的数据的时候，必须进行分布式事务执行BEGINDISTRIBUTEDTRANSACTION语句或者从客户端应用程序调用API函数使用分布式事务BEGINDISTRIBUTEDTRANSACTION语句不能嵌套ROLLBACKTRANSACTION语句回滚整个分布式事务不支持保存点。若SQLServer回滚了一个分布式事务，则整个事务回滚到分布式事务的起始点，无论是否有保存点当执行链接服务器间的分布式事务的时候，必须设置XACT_ABORT会话选项19.7修改链接服务器上的数据第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作使用分区视图分区视图的要求分区视图的工作原理实现分布式分区视图分区数据的考虑事项19.8使用分区视图分区视图的要求可伸缩性增加更多硬件到单个服务器在多个独立计算机间划分工作负荷和数据库分区视图：使用视图在多个数据库或SQLServer实例间分区数据分区视图的优点不同表的结果可以被组合成为一个结果集，对用户来说好像是一个表，称为分区视图数据的位置对应用程序来说是透明的数据库作为单个实体被编程19.8.1分区视图的要求

分区视图的工作原理19.8.2分区视图的工作原理分区视图分区视图允许将大型表中的数据水平拆分成较小的成员表。每个成员表与原表的格式相同，但只有部分行包含成员表的服务器称为成员服务器。每个成员服务器包含一个成员表和一个分布式分区视图访问任一成员服务器上的视图都可得到完整的数据，就像原表在每个服务器上都有一个完整副本一样本地和分布式分区视图如果分区视图所引用的所有成员表都在同一服务器上，则该视图是本地分区视图如果成员表在多台服务器上，则该视图是分布式分区视图分区视图的工作原理（续）19.8.2分区视图的工作原理实现分区视图所需要的SQLServer特性特性带来的好处视图允许用户以一个表的形式查看所有分区表CHECK约束定义和强制分区的完整性分布式查询查询和更新分区数据INSTEADOF触发器管理对视图的更新实现分布式分区视图设置分布式分区视图的步骤创建多个数据库，每个数据库在不同的运行SQLServer实例的成员服务器上通过在各个成员服务器上创建表来水平分割表在每个成员服务器上创建链接服务器定义，这用来向每个成员服务器发送分布式查询在每个成员服务器上，使用UNIONALL集合运算符创建分区视图，以组合每个成员表的所有行。每个视图应使用相同的名称，以便引用分布式分区视图名称的查询可以在任一成员服务器上运行123419.8.3实现分布式分区视图分区数据的考虑事项设计时的考虑事项单独分区原始数据库中的表，以使最相关的数据放置在同一个成员服务器上最小化对其他成员服务器上数据的需求。分布式查询应该只占百分之二十，或更少在相同成员服务器上放置完整记录。应将数据行和它们引用的外键行放在相同成员服务器上为了平均分布工作负荷，应该在最能平均分布数据到各个分区表的列上定义分区19.8.4分区数据的考虑事项分区数据的考虑事项（续）可以使用多种方式将数据分布在所有成员数据库的不同表中。应该考虑分区若干表在每个成员数据库中完整复制其他表在原始服务器中剩下一些表不作变动分区的规则分区表与原始表的格式必须相同。表必须包括相同数量的列，并且列的属性也要相同分区范围不能重叠在每个成员表中必须使用CHECK约束来强制值的分区范围19.8.4分区数据的考虑事项第19章多服务器编程分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作推荐操作设置链接服务器以远程执行存储过程或分布式查询限制访问链接资源对频繁的远程数据访问，使用链接服务器避免在不同服务器上设置重复的登录账户偶尔的远程数据访问，使用特殊查询选择适当的列以定义分区19.9推荐操作实验

使用分布式数据目标设置链接服务器并建立安全性查询链接服务器上的数据从链接服务器导入数据练习1设置链接服务器练习2

查询远程数据可选管理分布式事务60分钟回顾学习完本章后，将能够：描述分布式查询编写访问存储在远程SQLServer2000或OLEDB数据源上数据的特殊查询设置链接服务器环境以访问存储在远程SQLServer2000或OLEDB数据源上的数据编写访问链接服务器上数据的查询执行远程服务器或链接服务器上的存储过程管理分布式事务使用分布式事务修改分布式数据使用分区视图来提升性能随堂练习1 使用下列选项中的哪一种方法可以将一个SQLServer2000导出的文本格式的数据文件中的数据和数据格式，利用编程的方法，导入到一个SQLServer2000数据库中的数据表？使用SQL-DMO和MicrosoftVisualBasicScriptingEdition创建Table对象。使用Table对象的ImportData方法使用SQL-DMO和MicrosoftVisualBasicScriptingEdition创建Table对象。使用Table对象的CopyData属性随堂练习1使用DTS和MicrosoftVisualBasicScriptingEdition创建Package对象。为文本文件创建一个Connection对象。在Package对象中添加BulkInsertTask对象，使用Package对象的Excute方法导入数据使用DTS和MicrosoftVisualBasicScriptingEdition创建Package对象。为文本文件创建一个Connection对象。在Package对象中添加ExcuteSQLTask2对象，使用Package对象的Excute方法导入数据随堂练习2在一个文本文件中有使用逗号和换行符分隔开的数据，如下所示：111,’T1’,4,’Car’,11120112,‘T2’,5,’Jeep’,12000113,’T3’,2,‘Taxi’,11000导入到一个SQLServer2000数据库中的数据表结构中，表结构如下所示：随堂练习2（续）请问应当使用下列哪种方式最为方便？使用bcp工具，使用–t选项使用BULKINSERT语句，使用FIRE_TRIGGERS参数使用SQL-DMOBulkCopy2对象，设置TableLock属性为TRUE使用DTS创建两个数据转换任务并制定好文本列到数据库列的映射随堂练习3 机器A和机器B都各有一个SQLServer2000数据库。机器A是一台在线的交易服务器，记录本月的交易数据。机器B在机器A上有一个连接。每个月底，机器A将所有的交易数据发送给机器B作为存档。现在想要在机器A上查询机器B中数据库Archive中的数据表History中的信息，请问应如何使查询？EXECsp_addlinkedserver‘MachineB’,’SQLServer’GOSELECTCustomIDFROMMachineB.Archive.dbo.HistoryWHERETypeID=21随堂练习3（续）SELECTCustomIDFROMOPENROWSET(‘SQLOLEDB’ ‘MachineB’,’password’SELECT CustomID FROMdbo.HistoryWHERETypeID=21

C.

SELECTCustomIDFROM MachineB.Archive.dbo.HistoryWHERE TypeID=2