SQL SERVER 2005中同義字執行個體

標籤：style   blog   http   color   使用   os   strong   資料   

 

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

在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 RiverResearch
go
Use RiverResearch
go

然後分別為兩個資料庫建立模式以及表，預存程序如下

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 [email protected]goCreate 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 [email protected]goUse 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 [email protected]Go 

 這裡，在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

 

 

 

 

 

 

---------------------->>>