搜尋本站文章

2010-07-24

認識「同義字(SYNONYM)」

在 SQL Server 2005 上增加了「同義字(SYNONYM)」。

同義字是結構描述範圍物件的替代名稱。

用戶端應用程式可以利用同義字,使用單一部份名稱來參考基底物件,而不是使用兩部份、三部份或四部份名稱來參考基底物件。

同義字是具有下列用途的資料庫物件:

  • 對在本機或遠端伺服器上的另一個資料庫物件 (稱為基底物件) 提供別名。
  • 提供抽象層來保護用戶端應用程式,避免變更基底物件的名稱或位置。

例如,以位於伺服器 Server1 上 AdventureWorks2008R2 範例資料庫的 Employee 資料表為例。
若要從另一個伺服器 Server2 參考此資料表,用戶端應用程式使用的名稱必須包含四個部分:Server1.AdventureWorks.HumanResources.Employee。

另外,若是資料表的位置已變更 (例如,變更至另一個伺服器),則必須修改用戶端應用程式以反映該變更。

若要解決這些問題,您可以在 Server2 建立同義字 EmpTable,來代表在 Server1 的 Employee 資料表。
現在,用戶端應用程式只需要使用單一部份的名稱 EmpTable 來參考 Employee 資料表。

此外,如果 Employee 資料表的位置變更,您必須修改同義字 EmpTable,指向 Employee 資料表的新位置。
因為沒有 ALTER SYNONYM 陳述式,首先您必須卸除同義字 EmpTable,再以相同的名稱重新建立同義字,但要將同義字指向 Employee 的新位置。




請參考以下的範例程式碼:

--EX1. 橫跨資料庫使用「同義字(SYNONYM)」
--01 建立「同義字(SYNONYM)」
/*
在資料庫 AdventureWorks2008 內,替另外一個資料庫 Northwind 上的資料表 dbo.Orders ,建立「同義字(SYNONYM)」:dbo.nw_dbo_Orders。
*/
USE AdventureWorks2008
GO
IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'nw_dbo_Orders')
DROP SYNONYM [dbo].[nw_dbo_Orders]
GO
CREATE SYNONYM [dbo].[nw_dbo_Orders] 
FOR [Northwind].[dbo].[Orders]
GO

請參考下圖所示:




--02 查詢「同義字(SYNONYM)」:dbo.nw_dbo_Orders,但此「同義字」物件,一直被系統判斷為無效的物件,看來系統無法偵測「同義字」是否存在、正確等特性。
SELECT * FROM dbo.nw_dbo_Orders

請參考下圖所示:


--03 檢視「執行計畫」的差異:查詢「同義字」物件 vs. 使用三部分名稱(資料庫.結構描述.物件)
-- 結果:無論是查詢「同義字」或是資料表,都是耗用相同的成本(Subtree Cost)
SELECT * FROM dbo.nw_dbo_Orders
WHERE OrderID=10248
--
SELECT * FROM Northwind.dbo.Orders
WHERE OrderID=10248

請參考下圖所示:






同義字和基底物件之間只透過名稱繫結。
基底物件的存在性、類型及權限,全部會延遲到執行階段再檢查。

因此,和原始基底物件名稱相同的另一個物件,可以修改、卸除或卸除並取代基底物件。

例如,以同義字 MyContacts 為例,此同義字參考 Adventure Works 中的 Person.Person 資料表。
如果 Person 資料表被卸除並由名稱為 Person.Person 的檢視所取代,則 MyContacts 會變成參考 Person.Person 檢視。

同義字的參考不受結構描述的約束。
因此,隨時可以卸除同義字。

不過,如果卸除同義字,已卸除的同義字有可能會留下懸吊參考。
只有等到執行階段才會發現這種參考。

您可以使用同義字在數個 SQL 陳述式和運算式內容中取代其參考的基底物件。


下表包含這些陳述式及運算式內容的清單:
SELECT、INSERT、UPDATE、DELETE、EXECUTE、子 SELECT

Sys.synonyms 目錄檢視含有一個關於給定資料庫中各個同義字的項目。
此目錄檢視會公開同義字中繼資料,例如同義字的名稱與基底物件的名稱。

透過擴充屬性的運用,您可以將描述性或指示性文字、輸入遮罩以及格式化規則新增為同義字的屬性。
由於屬性儲存在資料庫中,因此讀取屬性的所有應用程式都能夠以同樣的方式評估物件。

若要尋找同義字基底物件的基底類型,請使用 OBJECTPROPERTYEX 函數。



注意事項:
不支援函數基底物件的四部份名稱。
同義字不可為另一個同義字的基底物件,而且同義字不可以參考使用者自訂的彙總函式。

只有同義字擁有者、db_owner 的成員或 db_ddladmin 的成員,才可授與同義字的權限。
您無法參考位於連結伺的服器上的同義字。

您無法在 DDL 陳述式中參考同義字。


同義字不是結構描述繫結性質,因此,下列結構描述繫結的運算式內容無法參考同義字:
CHECK 條件約束、計算資料行、預設運算式、規則運算式、結構描述繫結的檢視、結構描述繫結的函數



參考資料:
CREATE SYNONYM (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms177544.aspx

2010-07-05

SQL Server 2000、2005、2008 與 2008 R2 產品技術支援的終止日期

 

以下整理了 SQL Server 2008 R2、2008、2005、2000、7.0、6.5 版本,其主流技術支援的終止日期。

建議作為選擇 SQL Server 版本的參考。

SQL Server 版本

主流支援的終止日期

2008 與 2008 R2

2014/01/14

2005

2011/04/12

2000

2008/04/08

7.0

2005/12/13

6.5

2002/01/01

 


「微軟產品技術支援週期」的 10 年支援,包括 5 年主流支援和 5 年延伸支援。
在後 5 年的延伸支援階段,支援範圍主要在安全性更新,協助企業維護系統的安全性,避免系統弱點造成資安風險。

主流支援

主流支援是產品支援週期的第一個階段。
在符合 所支援的service pack 等級下,主流支援包含:

  • 問題數支援 (免費的問題支援,付費的問題支援,依時數計價的付費支援, 保證宣告支援)
  • 安全性更新的支援
  • 能夠要求非安全性的 hotfixes

延伸支援

主流支援之後為延伸支援階段,提供於商業與開發人員產品。
在符合 所支援的 service pack 等級下,延伸支援包含:

  • 付費的支援
  • 安全性更新支援 (不額外收費)
  • 非安全性的 hotfix 支援需要購買另一份延伸 Hotfix 支援的合約( 亦可單次計費)

請注意:

  • Microsoft 在延伸支援的階段並無法提供warranty支援、設計變更或新功能等服務。
  • 消費者、硬體或多媒體產品並無延伸支援的服務。 


參考資料:
SQL Server 產品技術支援週期(lifecycle)
http://sharedderrick.blogspot.com/2010/06/sql-server-lifecycle.html

Microsoft 支援週期
http://support.microsoft.com/lifecycle/

Microsoft 技術支援週期準則常見問答集
http://support.microsoft.com/gp/lifepolicy

搜尋Microsoft 產品技術支援週期:SQL Server
http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=SQL+Server&Filter=FilterNO

檢視其支援週期資訊 -- 全產品
http://support.microsoft.com/gp/lifeselect

2010-07-03

[SSIS]:設定封裝執行「記錄(Logging)」的「文字檔的 SSIS 記錄提供者」,讓其產生的檔案名稱能夠依據系統時間自動變更

SQL Server Integration Services 包括可用於在封裝、容器和工作中實作「記錄(Logging)」的「記錄提供者(Log Provider)」。
使用記錄,可以擷取有關封裝的執行階段資訊,藉此幫助您在每次執行封裝時對其進行稽核和疑難排解。
例如,記錄可以擷取執行封裝之操作員的名稱,以及封裝開始和結束的時間。


在使用記錄提供者的文字檔,依據預設值是一個固定的檔案名稱。
但在某些環境下,若希望能夠自定其檔案名稱,會是更加彈性的作法。


實作練習

目標:
設定封裝執行「記錄(Logging)」的文字檔,其檔名能夠依據系統時間自動變更。

實作步驟:

工作1:設定封裝的「記錄(Logging)」

步驟01. 在封裝內,點選「控制流程」。

步驟02. 在上方的工具選單,選擇「SSIS」\「記錄」。
步驟03. 在「設定記錄」視窗,點選「提供者與記錄」頁籤。

步驟04. 在「加入新記錄」區域,在「提供者類型」方塊,下拉選取「文字檔的 SSIS 記錄提供者」。
再點選「加入」。

步驟05. 在「選取用於容器的記錄」區域,在「名稱」方塊,勾選「文字檔的 SSIS 記錄提供者」。
步驟06. 在「組態」方塊,點選下拉,選擇「新增連接」。

步驟07. 在「檔案連接管理員編輯器」視窗, 在「使用類型」方塊,下拉選擇「建立檔案」。
步驟08. 在「方塊」,點選「瀏覽」,設定要此記錄檔案要存放的路徑與完整檔名。

步驟09. 點選「確定」,關閉「檔案連接管理員編輯器」視窗。

工作2:設定文字檔的連線字串
步驟01. 在下方的「連接管理員」,點選先前所建立的連接,滑鼠右鍵,選擇「屬性」。
步驟02. 在此連接的「屬性」視窗, 點選「Expressions」的...圖示。
請參考下圖所示:




步驟03. 在「屬性運算式編輯器」視窗,在「屬性」區域,下拉選取「ConnectionString」。
請參考下圖所示:



步驟04. 在「屬性」區域,點選...圖示。
步驟05. 在「運算式產生器」視窗,在中間的「運算式」區域,輸入以下的範例程式碼。
請參考下圖所示



--EX1. 完整檔案名稱的範例:C:\myAdmin\2010070315312540.log
-- 精確度到年、月、日、小時、分、秒、0.01 秒。
"C:\\myAdmin\\"+ SUBSTRING( (DT_WSTR,30) GETDATE(),1, 4 )+SUBSTRING( (DT_WSTR,30) GETDATE(),6, 2 )+SUBSTRING( (DT_WSTR,30) GETDATE(),9, 2 )+ SUBSTRING( (DT_WSTR,30) GETDATE(),12, 2 )+SUBSTRING( (DT_WSTR,30) GETDATE(),15, 2 )+SUBSTRING( (DT_WSTR,30) GETDATE(),18, 2 )+SUBSTRING( (DT_WSTR,30) GETDATE(),21, 2 )+".log"

--EX2. 完整檔案名稱的範例:C:\myAdmin\20100704094021.log
-- 精確度到年、月、日、小時、分、「10 秒」。
"C:\\myAdmin\\"+ SUBSTRING( (DT_WSTR,30) GETDATE(),1, 4 )+SUBSTRING( (DT_WSTR,30) GETDATE(),6, 2 )+SUBSTRING( (DT_WSTR,30) GETDATE(),9, 2 )+ SUBSTRING( (DT_WSTR,30) GETDATE(),12, 2 )+SUBSTRING( (DT_WSTR,30) GETDATE(),15, 2 )+SUBSTRING( (DT_WSTR,30) GETDATE(),18, 1 )+".log"

--EX3. 完整檔案名稱的範例:C:\myAdmin\2010110511164.log
-- 精確度到年、月、日、小時、分、「10 秒」。
"C:\\myAdmin\\"+ REPLACE( REPLACE( REPLACE( SUBSTRING( (DT_WSTR, 30)  GETDATE(), 1, 18 ) , "-", "" ), " ", "" ), ":", "" ) +".log"

上述的範例,其精確度是到「0.01 秒」。
步驟06. 點選「評估運算式」,可以預覽產生的完整檔案名稱等資料。
步驟07. 點選「確定」,關閉「運算式產生器」視窗。


步驟08. 點選「確定」,關閉「屬性運算式編輯器」視窗。
步驟09. 執行此封裝。


步驟10. 執行「檔案總管」,檢查所產生的檔案。
請參考下圖所示:








但比較麻煩的是,每次執行此封裝,都會產生個記錄檔案。
前兩個檔案,僅有檔頭,沒有放資料。
第三個檔案才有存放記錄的資料。


這可能是因為精確度到「0.01 秒」的緣故。
若是調整其精確度為「10 秒」,並加上自定的字串,或許就可以避開這類的問題。

2010-07-01

使用 ROUND、CEILING、FLOOR 函數,執行四捨五入到整數、無條件捨去到整數、無條件進位到整數、無條件捨去到指定的有效位數

若要對數值資料,執行四捨五入到整數、無條件捨去到整數、無條件進位到整數、無條件捨去到指定的有效位數等作業,可以使用 ROUND、CEILING、FLOOR 函數來達到需求。

請參考以下的範例程式碼:


-- 01 建立範例資料表
USE tempdb
GO
CREATE TABLE math01
(rid int IDENTITY(1,1), decData decimal(5,2) )
GO
INSERT math01 VALUES(10.44)
INSERT math01 VALUES(10.55)
INSERT math01 VALUES(10.66)
GO

-- 02 查詢資料表
SELECT * FROM math01





--03 使用 ROUND、CEILING 函數,執行四捨五入到整數、無條件捨去到整數、無條件進位到整數
SELECT rid, decData N'原始資料',
ROUND(decData,0) N'ROUND() 四捨五入到整數',
ROUND(decData,0,1) N'ROUND() 無條件捨去到整數',
CEILING(decData) N'CEILING() 無條件進位到整數'
FROM math01




--04 使用 ROUND、FLOOR 函數,執行無條件捨去到整數、無條件捨去到指定的有效位數
SELECT rid, decData N'原始資料',
FLOOR(decData) N'FLOOR() 無條件捨去到整數',
ROUND(decData,0,1) N'ROUND() 無條件捨去到整數',
ROUND(decData,1,1) N'ROUND() 無條件捨去到小數點第一位數'
FROM math01





但要請您留意的是 CEILING 與、 FLOOR 函數,只能處理到「整數」。

無法如同 ROUND 函數一般,自行指定要處理的有效位數。



CEILING (Transact-SQL)
傳回大於或等於指定數值運算式的最小整數。


ROUND (Transact-SQL)
傳回數值,捨入到指定的長度或有效位數。

語法:
ROUND ( numeric_expression , length [ ,function ] )
ROUND 一律傳回值。如果 length 是負的,且大於小數點前面的位數,ROUND 會傳回 0。

參數說明:

numeric_expression
這是精確數值或近似數值資料類型類別目錄的運算式,但 bit 資料類型除外。

length
這是 numeric_expression 捨入的有效位數。
length 必須是 tinyint、smallint 或 int 類型的運算式。
當 length 是正數時,numeric_expression 會捨入到 length 所指定的十進位數。
當 length 是負數時,numeric_expression 會依照 length 所指定,在小數點左側捨入。

function
這是要執行的作業類型。function 必須是 tinyint、smallint 或 int。
當省略 function,或其值為 0 (預設值) 時,會捨入 numeric_expression。
當指定 0 以外的值時,會截斷 numeric_expression。



FLOOR (Transact-SQL)
傳回小於或等於指定數值運算式的最大整數。
CEILING 函數會傳回大於或等於指定的數值運算式之最小整數。

FLOOR 函數會傳回小於或等於指定的數值運算式之最大整數。
例如,以數值運算式 12.9273 為例,CEILING 會傳回 13,FLOOR 會傳回 12。


參考資料:
CEILING (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms189818.aspx

ROUND (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms175003.aspx

FLOOR (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms178531.aspx

CEILING 與 FLOOR 的比較
http://msdn.microsoft.com/zh-tw/library/ms190927.aspx

認識 ROUND 函數,以 SQL Server 2008 為例
http://sharedderrick.blogspot.com/2009/06/round-sql-server-2008.html