SQL SERVER 2005中同义词实例

在SQL SERVER 2005中，终于出现了同义词了，大大方便了使用。下面举个小例子说明

同义词是用来实现下列用途的数据库对象：

为本地或远程服务器上的另一个数据库对象（称为“基对象”）提供备选名称。

提供一个提取层，该层防止客户端应用程序的基对象的名称或位置被更改。

例如，名为 Server1 的服务器上有 Adventure Works 的 Employee 表。若要从另一台服务器 Server2 引用此表，客户端应用程序必须使用由四个部分构成的名称 Server1.AdventureWorks.Person.Employee。另外，如果更改了表的位置（例如，更改到另一台服务器上），那么需要修改客户端应用程序以反映所做的更改。 之前在http://www.cnblogs.com/jackyrong/archive/2006/06/15/426304.html中已经归纳了一些特性，现在在举出例子，例子来自老外的。 首先建立两个数据库Create Database RiverResearchgoUse RiverResearchgo

然后分别为两个数据库建立模式以及表，存储过程如下

Use RainbowResearchgoCreate Schema RaingoCreate Schema SnowgoCreate table Rainbowresearch.Rain.Cities (id int, City varchar(200))goinsert into Rainbowresearch.Rain.Cities; select 1, 'Hongkong'insert into Rainbowresearch.Rain.Cities; select 2, 'Tokyo'insert into Rainbowresearch.Rain.Cities; select 3, 'Beijing'insert into Rainbowresearch.Rain.Cities; select 4, 'Taipei'insert into Rainbowresearch.Rain.Cities; select 5, 'Seoul'insert into Rainbowresearch.Rain.Cities; select 6, 'Mumbai'goCreate procedure Rain.DisplayCities @id intasSelect City from Rainbowresearch.Rain.Cities where id=@idgoCreate table Rainbowresearch.Snow.Cities (id int, City varchar(200))goinsert into Rainbowresearch.Snow.Cities; select 1, 'Tokyo'insert into Rainbowresearch.Snow.Cities; select 2, 'Seoul'insert into Rainbowresearch.Snow.Cities; select 3, 'Moscow'insert into Rainbowresearch.Snow.Cities; select 4, 'NewYork'goCreate procedure Snow.DisplayCities @id intasSelect City from Rainbowresearch.Snow.Cities where id=@idgoUse RiverResearchgoCreate Schema RivergogoCreate table RiverResearch.River.Cities (id int, City varchar(200))goinsert into RiverResearch.River.Cities; select 1, 'Hongkong'insert into RiverResearch.River.Cities; select 2, 'Tokyo'insert into RiverResearch.River.Cities; select 3, 'Beijing'insert into RiverResearch.River.Cities; select 4, 'Taipei'insert into RiverResearch.River.Cities; select 5, 'Seoul'goCreate procedure River.DisplayCities @id intasSelect City from RiverResearch.River.Cities where id=@idGo这里，在RainbowResearch数据库里建立了两个模式，rain,snow,再分别在这两个模式下建立了city表，而在riversearch数据库里也建立了个模式river，也建立了表cities，就这么简单，不详细表述。之后，我们查询刚才建好的内容select * from RiverResearch.River.Citiesgoselect * from Rainbowresearch.Rain.Citiesgoselect * from Rainbowresearch.Snow.Citiesgo

最后，我们建立同义词use RainbowResearchgocreate SYNONYM; RiverCities for RiverResearch.River.Citiesgocreate SYNONYM; RainCities for Rainbowresearch.Rain.Citiesgocreate SYNONYM; SnowCities for Rainbowresearch.Snow.Citiesgouse RiverResearchgocreate SYNONYM; RiverCities for RiverResearch.River.Citiesgocreate SYNONYM; RainCities for Rainbowresearch.Rain.Citiesgocreate SYNONYM; SnowCities for Rainbowresearch.Snow.Citiesgo

看到了么，简单许多了再来查询一次，这次简单多了Use RiverResearchgoSelect * from RiverCitiesSelect * from RainCitiesSelect * from SnowCitiesGoUse RainbowResearchgoSelect * from RiverCitiesgoSelect * from RainCitiesgoSelect * from SnowCitiesgo

http://www.cnblogs.com/jackyrong/archive/2006/11/15/561287.html