目前分類:MS SQL (7)

瀏覽方式: 標題列表 簡短摘要

假設我們有一個文字檔, 要寫入 table: StList 中, 且 StList schema 如下:

CREATE TABLE StList
(
 StFName varchar(50) NOT NULL,
 StLName varchar(50) NOT NULL,
 StEmail varchar(100) NOT NULL
)
go

 

文字檔型態一:

Kelly,Reynold,kelly@reynold.com
John,Smith,bill@smith.com
Sara,Parker,sara@parker.com

這個例子較簡單, 因此我們直接用下述語法:

BULK INSERT StList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = ',')

 

文字檔型態二:

"Kelly","Reynold","kelly@reynold.com"
"John","Smith","bill@smith.com"
"Sara","Parker","Parker"

這個例子就比較麻煩了, 若直接用以下語法,

BULK INSERT StList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = '","')

因為每個欄位的資料前後都有 double quote, 若直接用 "," 分開, 將造成"最前面"和"最後面"的double quote 拿不掉, 也就是會變成:

"Kelly Reynold kelly@reynold.com"
"John Smith bill@smith.com"
"Sara Parker Parker"

可改用以下程式處理:

BULK INSERT StList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR = '","')

UPDATE StList SET StFName = SUBSTRING(StFName,2,DATALENGTH(StFName)-1)    -- "KellyKelly

UPDATE StList SET StEmail = SUBSTRING(StFName,1,DATALENGTH(StEmail )-1)    -- kelly@reynold.com"kelly@reynold.com

 

 

cbw0731 發表在 痞客邦 PIXNET 留言(0) 人氣()

T-SQL 裡的 Error Handle 比較少人提到, 其實在撰寫 stored procedure 可以幫助除錯, 也可以自訂回饋給前端的訊息.

基本語法:

BEGIN TRY

    :

END TRY

BEGIN CATCH

    :

END CATCH

.TRY 及 CATCH 區塊必須搭配使用。

.TRY 及 CATCH 區塊間不可有其他的 T-SQL

.在 TRY 區塊間的 T-SQL,若發生錯誤,則會跳至 CATCH 區塊執行,然後中斷;若沒有錯誤,則會跳過 CATCH 區塊,然後繼續其後的敍述。

 

錯誤處理, 可用函數:

int ERROR_LINE();    --傳回發生錯誤的行數

BEGIN CATCH

PRINT N'Error Line: ' + CAST(ERROR_LINE() AS nvarchar(100));

END CATCH

 

int ERROR_NUMBER();    --傳回系統錯誤代號

(早期的 MS SQL 版本,可用 @@ERROR)

BEGIN CATCH

IF ERROR_NUMBER() = 220   

PRINT N'The number is too large to carry';  

END CATCH

 

nvarchar ERROR_MESSAGE();    --傳回系統錯誤訊息

 

int ERROR_SEVERITY();

ps: 這並非 Error 的嚴重程度, 只是標明 Error 的分類而已.

 

最後, 擷取自 MSDN 上的範例:

USE AdventureWorks;
GO

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO


參考資料:

http://www.functionx.com/sqlserver/Lesson09.htm

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHT/tsqlref9/html/50228f2f-6949-4d2e-8e43-fad11bf973ab.htm

cbw0731 發表在 痞客邦 PIXNET 留言(0) 人氣()

 

原文: Linked Server performance tips

 

重點摘要:

透過linked server 所執行的遠端存取/交易,可能耗用大量的頻寬,所以在使用上,要注意能避免的overhead 要盡量避免。

  

當查詢裡包括遠端主機的資料時,用linked server 將比使用 OPENROWSET OPENDATASOURCE 來得有效率。

  

當需要遠端主機回傳資料時,盡量讓資料先在遠端過濾完畢後再回傳;亦即,只取回必要的資料就好,以避免額外的網路傳輸。OPENQUERY 可作到這樣的效果。

 

當遠端 DB 與近端 DB character set sort order (collation) 相同時,可將 SP_SERVEROPTION "collation compatible"選項打開,可增加效能;尤其當使用 WHERE 時,若選項不開啓,則 MS SQL 必須將遠端所有資料傳輸至近端後才能比對;若開啓,則可在遠端比對。

 

遠端主機最好與近端主機接同一台 switch,或至少在同一個 subnet 裡。

 

當以 Enterprise Manager Management Studio 建立一個 linked server 時,在 Server Option tab 裡,可以考慮設定 Connection Timeout 以及 Query Timeout 的值;預設值 0 表示不會 time out,設定該值可以幫助我們找出效能不彰的 Query

 

 

 

在預設的情形下, MS SQL 會將查詢盡量放在遠端,並只取回必要的資料, 但在下述情形下, MS SQL 可能必須先將所有資料取回:

  • Data conversion operations (eg: cast/convert)
  • 當用到 bit timestamp uniqueidentifier 等資料型態
  • 用到 "TOP" keyword
  • INSERTS UPDATES DELETES
  • 所以最好避免執行上述的遠端查詢/交易

 

最後, 善用 Query Analyzer 查看 query plan 以確保大多數的資料查詢是在遠端上執行,而非近端 DB

cbw0731 發表在 痞客邦 PIXNET 留言(0) 人氣()

原文 - http://www.sqlteam.com/article/temporary-tables

暫存表(Temporary Tables)

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )
  • table name 前加入"#", 表示這是一個暫存表(temporary table)
  • 當session 關閉時, 這個table 將會自動drop
  • 好的寫作習慣, 應在暫存表使用完畢後, 下指令去 drop, 而不是讓系統自動回收
  • 暫存表是存在主機記憶體中, 因此存取速度較快
  • 暫存 table 的限制:
  • 暫存表存在於"tempdb"這個database 裡
  • 如果有兩個使用者建立同一個名字的暫存表, 則他們會各自擁有獨立的一份, 互相不會干擾.
  • 若stored procedure A 建立了一個暫存表, 並呼叫 stored procedure B, 則在 B 中可以存取這個暫存表
  • 如果在SQL Server Management Studio or Query Analyzer 中建立的暫存表, 會等到我們手動drop 去關閉session 才會消失

 

表格變數(Table Variables)

當我們使用 SQL Server 2000 或以後的版本, 則可以考慮使用 "Table Variables" (表格變數); 使用方式如下例:

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakName
FROM dbo.Yaks
WHERE YakType = 'Tibetan'

-- Do some stuff with the table

 

 

  • 它和暫存表類似, 但它更加彈性, 且不會存在於tempdb 中(完全存在於記憶體).
  • 使用完畢後, 不須手動去 drop它

 

兩者的使用時機

 

  • 當暫存的資料筆數小於100筆時, 使用表格變數, 否則, 可使用暫存表, 因為針對表格變數, SQL Server 不會去解析/最佳化它的效能.
  • 當我們須要對表格建立索引(Index)時, 則必須使用暫存表.
  • 在使用暫存表時, 最好能在建立後一併建立索引, 這能增加效能 (SQL Server 2005後, 這方面已改善, 所以可以不建索引; 但建立它仍是一個好習慣)

 

全域暫存表(Global Temporary Tables)

在表格名字前面, 加入兩個"#", 比如"##YakHerders", 則表示這是個全域暫存表, 也就是說, 這個表格和一般表格一樣, 可以被所有連線(connections/sessions)使用; 在SQL Server 中, 這樣的應用並不多見.

cbw0731 發表在 痞客邦 PIXNET 留言(1) 人氣()

原文 - http://www.sqlteam.com/article/sql-server-versions

 

SQL Server 版本一覽 (2010/1/18為止)

 

SQL Server 2008
10.00.27.57 SQL Server 2008 SP1 CU6 18 Jan 2010
10.00.2746 SQL Server 2008 SP1 CU5 24 Nov 2009
10.00.2734 SQL Server 2008 SP1 CU4 22 Sept 2009
10.00.2723 SQL Server 2008 SP1 CU3 21 July 2009
10.00.2714 SQL Server 2008 SP1 CU2 18 May 2009
10.00.2710 SQL Server 2008 SP1 CU1 16 Apr 2009
10.00.2531 SQL Server 2008 SP1 7 Apr 2009
10.00.1828 SQL Server 2008 RTM CU9 18 Jan 2009
10.00.1823 SQL Server 2008 RTM CU8 16 Nov 2009
10.00.1818 SQL Server 2008 RTM CU7 21 Sept 2009
10.00.1812 SQL Server 2008 RTM CU6 21 July 2009
10.00.1806 SQL Server 2008 RTM CU5 18 May 2009
10.00.1798 SQL Server 2008 RTM CU4 17 Mar 2009
10.00.1787 SQL Server 2008 RTM CU3 19 Jan 2009
10.00.1779 SQL Server 2008 RTM CU2 17 Nov 2008
10.00.1763 SQL Server 2008 RTM CU1 22 Sept 2008
10.00.1600 SQL Server 2008 RTM 6 Aug 2008
SQL Server 2005
9.00.4285 SQL Server 2005 SP3 CU8 16 Feb 2010
9.00.4273 SQL Server 2005 SP3 CU7 21 Dec 2009
9.00.4266 SQL Server 2005 SP3 CU6 19 Oct 2009
9.00.4230 SQL Server 2005 SP3 CU5 17 Aug 2009
9.00.4226 SQL Server 2005 SP3 CU4 16 June 2009
9.00.4220 SQL Server 2005 SP3 CU3 21 Apr 2009
9.00.4211 SQL Server 2005 SP3 CU2 17 Feb 2009
9.00.4207 SQL Server 2005 SP3 CU1 20 Dec 2008
9.00.4053 SQL Server 2005 SP3 GDR (Security Update) 13 Oct 2009
9.00.4035 SQL Server 2005 SP3 16 Dec 2008
9.00.3356 SQL Server 2005 SP2 CU17 21 Dec 2009
9.00.3355 SQL Server 2005 SP2 CU16 19 Oct 2009
9.00.3330 SQL Server 2005 SP2 CU15 18 Aug 2009
9.00.3328 SQL Server 2005 SP2 CU14 16 June 2009
9.00.3225 SQL Server 2005 SP2 CU13 21 Apr 2009
9.00.3315 SQL Server 2005 SP2 CU12 17 Feb 2009
9.00.3310 SQL Server 2005 Security Update 10 Feb 2009
9.00.3301 SQL Server 2005 SP2 CU11 15 Dec 2008
9.00.3294 SQL Server 2005 SP2 CU10 20 Oct 2008
9.00.3282 SQL Server 2005 SP2 CU9 18 Aug 2008
9.00.3257 SQL Server 2005 SP2 CU8 16 June 2008
9.00.3239 SQL Server 2005 SP2 CU7 14 April 2008
9.00.3233 SQL Server 2005 QFE Security Update 8 July 2008
9.00.3228 SQL Server 2005 SP2 CU6 18 Feb 2008
9.00.3215 SQL Server 2005 SP2 CU5 17 Dec 2007
9.00.3200 SQL Server 2005 SP2 CU4 15 Oct 2007
9.00.3186 SQL Server 2005 SP2 CU3 20 Aug 2007
9.00.3175 SQL Server 2005 SP2 CU2 28 June 2007
9.00.3161 SQL Server 2005 SP2 Cumulative Update 1 (CU1)  
9.00.3152 SQL Server 2005 SP2 Cumulative Hotfix 7 Mar 2007
9.00.3077 SQL Server 2005 Security Update 10 Feb 2009
9.00.3054 KB934458 - Also read Bob Ward's post on SP2. 5-Apr-07
9.00.3042.01 SQL Server 2005 "SP2a" 5-Mar-07
9.00.3042 SQL Server 2005 SP2 Feb-07
9.00.2047 SQL Server 2005 SP1  
9.00.1399 SQL Server 2005 RTM Nov-05
SQL Server 2000
8.00.2039 SQL Server 2000 SP4  
8.00.760 SQL Server 2000 SP3  
8.00.534 SQL Server 2000 SP2 30 Nov 2001
8.00.384 SQL Server 2000 SP1  
8.00.194 SQL Server 2000 RTM  
SQL Server 7
7.00.1063 SQL Server 7.0 SP4  
7.00.961 SQL Server 7.0 SP3 15 Dec 2000
7.00.842 SQL Server 7.0 SP2 20 Mar 2000
7.00.699 SQL Server 7.0 SP1 July 1999
7.00.623 SQL Server 7.0 / MSDE 1.0 RTM  
SQL Server 6.5
6.50.416 SQL Server 6.5 with Service Pack 5a  
6.50.415 SQL Server 6.5 with Service Pack 5  
6.50.281 SQL Server 6.5 with Service Pack 4  
6.50.258 SQL Server 6.5 with Service Pack 3  
6.50.240 SQL Server 6.5 with Service Pack 2  
6.50.213 SQL Server 6.5 with Service Pack 1  
6.50.201 SQL Server 6.5 RTM  
  • 查詢版本的指令
Select @@version

查詢結果如下(以我的環境為例):

Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86)

Dec 17 2008 15:19:45

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

 
  • 查詢相關資料的系統 sp:
exec master..xp_msver

 

cbw0731 發表在 痞客邦 PIXNET 留言(0) 人氣()

原文出處 : http://www.ithome.com.tw/plog/index.php?op=ViewArticle&articleId=6287&blogId=620

淺談SQL Server的鎖定原理
neo_lin_42 | 02 Oct, 2006 10:21

在說明SQL Server的鎖定原理之前,首先來看一個基本的問題,為什麼要使用鎖定?我們可以從逆向思考的方式來回答這個問題,也就是不使用鎖定,會出現什麼問題,特別是當多人同時存取資料時,若不使用鎖定,可能會發生lost update、dirty read、nonrepeatable read與phantoms read等問題。這也是為什麼要使用鎖定的主要原因。

談到鎖定,就不得不談到交易處理(transaction)。所謂交易處理是指在一個工作單元中,執行的一系列資料操作。ATM跨行轉帳就是一個很典型的例子,這個工作單元中包含從轉出帳戶扣款與將金額存入轉入帳號兩個操作,這兩個操作必須有一致的結果,不是全部成功,確認最終狀態;就是全部失敗,回復原始狀態,沒有部分成功,部分失敗的情況,只有轉出沒有轉入或只有轉入沒有轉出都是不允許的。也就是交易處理具有不可分割性(Atomicity)、一致性(Consistency)、隔離性(Isolation)以及持久性(Durability)等特性。而鎖定機制便是為了實現交易的隔離性,好像此時資料庫系統是專屬你一個人的。

鎖定的範圍
在SQL Server可以鎖定哪些資源?依據鎖定的範圍可以分成RID 列級鎖定、Key 索引鎖定、Page 頁級鎖定、Extent 擴展鎖定(每8個Page為1個extent,用於SQL Server儲存空間的分配)、Table 表級鎖定,以及Database 資料庫級鎖定。

鎖定的範圍越小,耗用的資源越大;鎖定的範圍越大,耗用的資源越小。列級鎖定可以增加同時線上(concurrent)數量,但卻增加資源花費;頁級或表級鎖定則正好相反。SQL Server會衡量目前的concurrent數量與系統資源,自動管理鎖定的範圍,同時為了獲得最佳的鎖定效能,SQL Server提供一種動態鎖定的機制,也就適當列級鎖定耗用的資源達到某個程度時,SQL Server會自動將列級鎖定升級成頁級或表級鎖定。

鎖定的類型

SQL Server鎖定可以分成基本鎖定與特殊鎖定兩大類。基本鎖定又可分成共用鎖定(shared lock)與獨占鎖定(exclusive lock) 。一般而言,當Select資料時會使用共用鎖定,Insert、Update和Delete資料時會使用獨佔鎖定。特殊鎖定又可分成意圖鎖定(Intent lock)、更新鎖定(Update lock)、綱要鎖定(Schema lock)與大量新鎖定(Bulk update lock)。接下來,對常用的鎖定類型,簡單說明如下:

共用鎖定
當讀取資料時,SQL Server會使用共用鎖定,即使尚未結束,其他交易也可以獲得共用鎖定,也就是說多個交易可以同時讀取table、page、key和row。

獨佔鎖定
當修改資料時,SQL Server會使用獨佔鎖定。在交易結束之前,其他交易的鎖定請求都會被拒絕。一個資源只能有一個獨佔鎖定,當一個交易對某個資源進行獨占鎖定時,其他交易無法讀取該資源,由此可知,這種鎖定會限制了同時線上數量。
更新鎖定是共用鎖定與獨占鎖定的混合體。在修改之前會使用共用鎖定,其他交易可以讀取被鎖定的資料,但不可以修改。一旦開始修改時,就變成了獨占鎖定,其他交易無法讀取和更新被鎖定的資料,直到交易結束。

一個鎖定可以與另一個鎖定同時使用,這種特性稱為鎖定的相容性。相反的,若一個交易正在操作而鎖定資料,另一個交易必須等前一個交易結束後,才能繼續,則稱為不相容。相容性越好,表示支援的同時線上數量越大。哪些鎖定式相容的?哪些鎖定又是不相容的?一般的原則如下:
共用鎖定可以與獨占鎖定之外的其他鎖定相容。多個交易可以在相同資料上獲得共用鎖定,但沒有一個交易可以在已經獲得共用鎖定的資料上獲得獨佔鎖定。

獨占鎖定不可以其他鎖定相容。

更新鎖定只能與共用鎖定相容。

我們可以使用交易的隔離級別來設定鎖定的策略,以防止前面提到多人存取所可能出現的問題。SQL Server提供下列4種交易隔離級別,其特性簡單說明如下。
read uncommitted 會出現dirty read、non-repeatable read與phantoms等問題。
read committed 會出現non-repeatable read與phantoms等問題。
repeatable read 會出現phantoms的問題。
serializable 無

若未指定SQL Server預設使用read committed。在SQL Server 2005,提供了另外兩個基於資料版本的隔離級別,snapshot與Read Committed using Statement-level Snapshot(RCSI),使得讀取與更新的操作不會互相影響,加快交易的執行速度。

除了隔離級別的鎖定策略之外,您也可以使用鎖定提示來控制資料表的鎖定行為,以及覆蓋目前交易的隔離級別的鎖定策略。當隔離級別的鎖定策略與鎖定提示相衝突時, SQL Server是採用級別越小越優先的處理原則。例如,使用serializable隔離級別,若在交易中使用with (nolock)讀取資料,SQL Server會允許其他交易讀取資料。可用的表級鎖定提示摘要如下:
holdlock:當交易開始之後,交易結束之前,不允許其他交易讀取被holdlock的資料。
nolock:不使用任何的鎖定。
使用rowlock、paglock,tablock與tablockx來指定資料表的鎖定類型和大小。例如使用tablockx表示當讀取資料列時,在資料列所在的資料表上使用獨占索定。
readpast:忽略鎖定的資料列
updlock:當讀取資料使用更新鎖定,而不是預設的共用鎖定。當交易尚未結束前,不允許其他交易更新被updlock的資料,可避免non-repeatable read。

您可以根據個別的需要來組合這些鎖定提示,以獲得更加靈活、更有彈性的應用。例如,with (paglock, holdlock)表示在資料列所在的資料頁上使用hold lock。

有關SQL Server的鎖定原理,拉哩拉喳地介紹到此,相信大家對SQL Server的鎖定已有初步的認識。至於進一步的實作細節,還是那一句老話,多翻翻SQL Server的線上叢書囉!!

cbw0731 發表在 痞客邦 PIXNET 留言(0) 人氣()

Dirty Read (以下簡稱DR)在MS SQL 的Help 裡解釋如下:
"第二筆交易選擇的資料列已經被其他交易更新時,會發生未確認依存性 (Uncommitted Dependency)。此時,第二筆交易讀取的是尚未認可且可能被更新資料列的交易變更之資料。

例如,有一位編輯人員正在修改一份電子文件。進行變更時,第二位編輯人員複製了包含目前所有變更的文件,並將文件散發給預期的讀者。此時,第一位編輯人員認為截至目前的變更均有誤,所以移除了原先的變更後儲存文件。散發的文件包含不存在的修改,但這些修改不應該存在。如果能在第一位編輯人員決定不再進行變更後才允許其他人員讀取修改過的文件,即可避免這個問題。"

坦白說, 我一向看不太懂這些詏口的翻譯文, 不過大致可以推斷出, 當 DR 發生時, 表示我們的select 的資料, "可能"是錯的.

想對MS SQL 的鎖定機制有進一步認識的人, 可以參考這一篇

為了避免這種錯誤發生, 我們只有運用lock 的機制去避免, 但這往往必須用系統效能的降低來交換, 只不過, 在一個設計良好的系統中, 這種兩難可以達成一個平衡(trade-off balance).

但如果是發生在一個已經每天都滿載甚至超載(overload)的MS SQL 資料庫上呢? 我們會發現, 由於大量的Lock, 將會導致系統始終遊走於崩潰邊緣!

我在 sql-server-performance.com 這個網站上翻閱了一些文章, 其中這篇寫得不錯; 作者針對上述問題(row lock/page lock/table lock/...), 提供了兩點方案:
1) turn option "NOLOCK" on while SELECT
2) turn option "ROWLOCK" on while UPDATE and DELETE


關於"NOLOCK"及"ROWLOCK", 在此不再詳述, MSDN 裡都翻得到.

由於這種方法可能會造成DR, 筆者也特別說明, 進行重要的交易(如會計結帳), 不可用此方案.

以一個 ERP 而言, 絕大多數(往往超過50%)的查詢或報表, 並不一定需要"最及時"(updated)的資料; 如果我要查目前的庫存量, 那麼30秒前的庫存, 和目前的庫存, 即使有所差異, 那差異量和整月的交易量比起來也是微乎其微; 諸如此類的select statement, 其實是可以忽略 DR 的風險! 如果我們對這些 sql statement 加上 "NOLOCK"及"ROWLOCK", 可以使 DB 的效能超死回生, 那麼何妨一試!?

曾與人爭論"資料正確性"與"效能", 孰重孰輕!? 理論上, 提供100% 的資料正確性是MIS 的天職, 但實務上若遇到設計不佳的 DB 存取方法, 常造成使用者操作時要忍受無窮無盡的等待, 三不五時的"作業逾時",換算成全公司浪費的人力, 絕對比DR 的成本更加驚人 (何況前者是"一定會有"的顯性成本, 後者只是潛在成本),遑論使用者對系統的抗拒感造成日後的怒氣爆發! MIS 宜從多個角度思考何者為佳(對公司最好), 而非固執地抱著理論不放.

cbw0731 發表在 痞客邦 PIXNET 留言(0) 人氣()

找更多相關文章與討論