6 SQL Server 2008 T-SQL語句技術(shù)

1、SQL Server 2008 T-SQL語句技術(shù) Transaction-SQL 概要 SQL Server 2005 T-SQL技術(shù) 特殊技巧 SQL Server 2008 T-SQL新體驗(yàn) SQL Server 2005 T-SQL技術(shù) 內(nèi)容 XML數(shù)據(jù)類型 統(tǒng)一化的大對(duì)象 自定義編號(hào) 通用語言表達(dá)式 分割數(shù)組 排名 錯(cuò)誤處理 透視-PIVOT操作符 其他新操作符 XML數(shù)據(jù)類型 原生支持的數(shù)據(jù)類型 可以在表,變量,參數(shù)中使用 使用內(nèi)置的方法查詢和修改XML - 在表定義中使用 CREATE TABLE NewTable (Col1 int primary key, Col2 xml

2、) - 作為本地變量使用 declare data xml - 作為存儲(chǔ)過程參數(shù)使用 CREATE PROCEDURE SaveData(doc xml) AS . 統(tǒng)一化的大對(duì)象 varchar(max) nvarchar(max) varbinary(max) 自定義編號(hào) Identity是屬于單一數(shù)據(jù)表 僅能在INSERT之后取出新的值 但是INSERT失敗,卻無還原取出的值 無法關(guān)閉與開啟,僅能刪除數(shù)據(jù)行 Why Not Identity? 自定義編號(hào) 在單一數(shù)據(jù)表中維護(hù)最新號(hào)碼 使用 UPDATE語句累加單一值 主要是依靠默認(rèn)的鎖定機(jī)制 Blocking Sequence CREAT

3、E TABLE dbo.SyncSeq(val INT); INSERT INTO dbo.SyncSeq VALUES(0); GO CREATE PROC dbo.usp_SyncSeq val AS INT OUTPUT AS UPDATE dbo.SyncSeq SET val = val = val + 1; GO 自定義編號(hào) 每一個(gè)數(shù)據(jù)表使用identity 行進(jìn)行維護(hù) 新增數(shù)據(jù)之后使用scope_identity回傳數(shù)值 Nonblocking Sequence CREATE TABLE dbo.AsyncSeq(val INT IDENTITY); GO CREATE PROC

4、 dbo.usp_AsyncSeq val AS INT OUTPUT AS BEGIN TRAN SAVE TRAN S1; INSERT INTO dbo.AsyncSeq DEFAULT VALUES; SET val = SCOPE_IDENTITY() ROLLBACK TRAN S1; COMMIT TRAN GO 通用語言表達(dá)式 遞歸的CTE A recursive CTE includes a reference to itself within the CTE query 可以直接訪問自己 非遞歸的CTE A nonrecursive CTE does not include

5、 a reference to itself within the CTE query 其實(shí)類 似是一個(gè)視圖 分割數(shù)組 情境 問題:將數(shù)組中的每一個(gè)元素分成單一值 情境: 從異構(gòu)系統(tǒng)或是電子表格中所導(dǎo)入的數(shù)組格式數(shù)據(jù) 處理使用逗號(hào)分隔的字符串?dāng)?shù)據(jù) 分割數(shù)組解決方案 處理的三大步驟: 產(chǎn)生復(fù)制 隔離每一個(gè)元素 計(jì)算每一個(gè)位置 SELECT arrid, n - LEN(REPLACE(LEFT(string, n), , ) + 1 AS pos, SUBSTRING(string, n, CHARINDEX(, string + , n) - n) AS val FROM Arrays JOI

6、N Nums ON n = LEN(string) AND SUBSTRING(, + string, n, 1) = , ORDER BY arrid, pos; 排名 SalesOrderID SubTotal RowNumber Rank DenseRank NTile - - - - - 73976 7.28 1 1 1 1 65101 6.28 2 2 2 1 65814 6.28 3 2 2 1 72585 4.99 4 4 3 2 56410 3.99 5 5 4 2 56411 3.99 6 5 4 2 51885 2.29 7 7 5 3 51886 2.29 8 7 5 3

7、 52031 2.29 9 7 5 3 SQL Server 2005 支持排名函數(shù): DENSE_RANK NTILE ROW_NUMBER RANK 排名-指導(dǎo)原則 給行編號(hào) 對(duì)大結(jié)果集分頁支持 剔除重復(fù)數(shù)據(jù) 通過OrderBy來分層 錯(cuò)誤處理 TRYCATCH TRY 塊包含了要保護(hù)的事務(wù) CATCH 塊處理錯(cuò)誤 CREATE PROCEDURE dbo.AddData a int, b int AS BEGIN TRY INSERT INTO TableWithKey VALUES (a, b) END TRY BEGIN CATCH SELECT ERROR_NUMBER() Err

8、orNumber, ERROR_MESSAGE() Message END CATCH BEGIN TRY - INSERT INTO . END TRY SELECT * FROM TableWithKey - NOT ALLOWED BEGIN CATCH - SELECT ERROR_NUMBER() END CATCH BEGIN TRY BEGIN TRAN INSERT . INSERT . COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN SELECT MESSAGE_NUMBER. END CATCH SET XACT_ABORT ON

9、 BEGIN TRY BEGIN TRAN . COMMIT TRAN END TRY BEGIN CATCH IF (XACT_STATE() = -1 - uncommitable ROLLBACK TRAN ELSE IF (XACT_STATE() = 1- commitable COMMIT TRAN END CATCH BEGIN TRY . END TRY BEGIN CATCH INSERT INTO ErrorLog VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE() END CATCH Create CATCH block

10、immediately after TRY 1 Roll back failed transactions in CATCH 2 Consider using XACT_ABORT ON and XACT_STATE 3 Capture ERROR_xxx information if required4 錯(cuò)誤處理指導(dǎo)原則 Create CATCH block immediately after TRY Roll back failed transactions in CATCH Consider using XACT_ABORT ON and XACT_STATE Capture ERROR

11、_xxx information if required The unpivot query 反透視查詢 An unpivot query retrieves data that is stored in a way similar to a spreadsheet and displays it in a normalized fashion The pivot query 透視查詢 A pivot query retrieves normalized data and displays it in a way similar to how data appears in a spreads

12、heet 什么是透視查詢? 透視 不支持動(dòng)態(tài) PIVOT的IN (數(shù)據(jù)行列表),當(dāng)透過PIVOT將數(shù)據(jù)輸 出時(shí)，在IN的清單必須是固定的字段列表，不可 以透過子查詢或是變動(dòng)字段進(jìn)行處理 旋轉(zhuǎn)與匯總數(shù)據(jù)行必須是在輸入數(shù)據(jù)表之中 僅可以旋轉(zhuǎn)一個(gè)數(shù)據(jù)行 PIVOT 操作符的限制 透視 使用動(dòng)態(tài)SQL指令 使用FOR XML PATH技術(shù)將旋轉(zhuǎn)數(shù)據(jù)行進(jìn)行鏈接 使用TABLE表達(dá)式處理基礎(chǔ)數(shù)據(jù)行 使用鏈接數(shù)據(jù)技術(shù)處理單一旋轉(zhuǎn)資料行的限制 回避限制 透視 建立存儲(chǔ)過程簡(jiǎn)單化處理 參數(shù): Putting it All Together CREATE PROC dbo.sp_pivot query AS NV

13、ARCHAR(MAX), on_rows AS NVARCHAR(MAX), on_cols AS NVARCHAR(MAX), agg_func AS NVARCHAR(257), agg_col AS NVARCHAR(MAX) 其他新操作符 Apply OUTPUT 特殊技巧 使用CHECKSUM與NEWID產(chǎn)生隨機(jī)數(shù)數(shù)據(jù) 數(shù)據(jù)取樣可以使用SELECT搭配TABLESAMPLE 另外的選擇CHECKSUM與NEWID -查詢數(shù)據(jù)取出10筆隨機(jī)數(shù)資料 SELECT TOP 10 TransactionID,TransactionDate FROM Production.Transactio

14、nHistory ORDER BY CHECKSUM(NEWID() 根據(jù)指定的字節(jié)數(shù)抓取字符串 SUBSTRING會(huì)回傳字符數(shù) 無法根據(jù)字節(jié)數(shù)進(jìn)行回傳 -測(cè)試使用字節(jié)數(shù)擷取字符串 USE master DECLARE s VARCHAR(40) SET s=阿輝一號(hào) SELECT SUBSTRING(s,1,2) 阿輝 SELECT CONVERT(VARBINARY(MAX),s) -轉(zhuǎn)換成VARBINARY(MAX) 0 xAAFCB16AA440B8B9 SELECT SUBSTRING(CONVERT(VARBINARY(MAX),s),1,2) -根據(jù)VARBINARY(MAX)抓

15、取指定字節(jié)數(shù) 0 xAAFC SELECT CONVERT(VARCHAR(40),SUBSTRING(CONVERT(VARBINARY(MAX),s),1,2) -轉(zhuǎn)成原始VARCHAR類型 阿 在數(shù)據(jù)表觸發(fā)器中仿真數(shù)據(jù)指針的功能 利用累計(jì)的功能產(chǎn)生字符串 可以搭配動(dòng)態(tài)SQL字符串 -建立觸發(fā)器針對(duì)新增數(shù)據(jù)逐筆執(zhí)行存儲(chǔ)過程 CREATE TRIGGER tr1 ON Books AFTER INSERT AS DECLARE sql VARCHAR(max) SET sql= -根據(jù)INSERTED的緩存數(shù)據(jù)產(chǎn)生對(duì)應(yīng)的語句 SELECTsql=sql+EXEC usp_Process +

16、BookCode + ; FROM Inserted -顯示字符串 SELECT sql 顯示組合完成的字符串 -動(dòng)態(tài)執(zhí)行字符串 EXEC (sql) GO CONTEXTINFO決定用戶信息 解決三層式體系結(jié)構(gòu)中無法抓到特定前端機(jī)器的問 題 SET CONTEXT_INFO的語句 -將指定的信息，儲(chǔ)存到context_info數(shù)據(jù)行 DECLARE bi VARBINARY(MAX) SET bi=CONVERT(VARBINARY(MAX),Lewis Yang) SET CONTEXT_INFO bi -使用sys.dm_exec_sessions SELECT CONVERT(VARC

17、HAR(MAX),CONTEXT_INFO),CONTEXT_INFO,* FROM sys.dm_exec_sessions where session_id=spid SQL Server 2008 T-SQL新體驗(yàn) 內(nèi)容 時(shí)間日期數(shù)據(jù)類型 HierachyID數(shù)據(jù)類型 表值參數(shù) Merge語句 對(duì)象依賴關(guān)系 Grouping Set 行構(gòu)造器 其他新特性 日期與時(shí)間數(shù)據(jù)類型 比 datetime 類型更大的范圍、更加精確datetime2 datetime2 的日期部分date datetime2 的時(shí)間部分time datetime2 + 時(shí)區(qū)datetimeoffset DECLAR

18、E newDateTime datetime2 SET newDateTime = SYSDATETIME() PRINT newDateTime - OUTPUT - 2008-02-24 20:50:46.7892464 DECLARE newDate date SET newDate = SYSDATETIME() PRINT newDate - OUTPUT - 2008-02-24 DECLARE newTime time(3) SET newTime = SYSDATETIME() PRINT newTime - OUTPUT - 20:50:46.789 DECLARE newD

19、ateTime datetimeoffset SET newDateTime = SYSDATETIMEOFFSET() PRINT newDateTime - OUTPUT - 2008-01-24 20:50:46.7892464 -08:00 HierarchyID 數(shù)據(jù)類型 用來表示一個(gè)層次結(jié)構(gòu)當(dāng)中的節(jié)點(diǎn) 可以搭配CTE處理層次化數(shù)據(jù) 成員： GetAncestor 返回代表該 hierarchyid 節(jié)點(diǎn)第 n 代前輩的 hierarchyid。 GetDescendant 返回該 hierarchyid 節(jié)點(diǎn)的子節(jié)點(diǎn)。 GetLevel 返回一個(gè)整數(shù)，代表該 hierarchyid

20、 節(jié)點(diǎn)在整個(gè)層次結(jié)構(gòu) 中的深度。 GetRoot 返回該層次結(jié)構(gòu)樹的根 hierarchyid 節(jié)點(diǎn)。靜態(tài)。 IsDescendant 如果傳入的子節(jié)點(diǎn)是該 hierarchyid 節(jié)點(diǎn)的后代，則 返回 true。 Parse 將層次結(jié)構(gòu)的字符串表示轉(zhuǎn)換成 hierarchyid 值。靜態(tài)。 Reparent 將層次結(jié)構(gòu)中的某個(gè)節(jié)點(diǎn)移動(dòng)另一個(gè)位置。 ToString 返回包含該 hierarchyid 邏輯表示的字符串。 表值參數(shù) 表值參數(shù) 用戶定義的Table類型 有利于批量數(shù)據(jù)在客戶端與服務(wù)器間傳遞 常見的應(yīng)用場(chǎng)合 在客戶端與服務(wù)器間傳遞大批量的數(shù)據(jù) 對(duì)數(shù)據(jù)集進(jìn)行業(yè)務(wù)邏輯處理 數(shù)據(jù)挖掘、E

21、TL等應(yīng)用 MERGE語句 混合了UPDATE、DELETE和INSERT 根據(jù)一個(gè)源數(shù)據(jù)集對(duì)一個(gè)目標(biāo)數(shù)據(jù)集進(jìn)行處理 與SQL-2006兼容 常見的應(yīng)用場(chǎng)合 傳統(tǒng)OLTP應(yīng)用的中的插入或更新操作(UPSERT) 數(shù)據(jù)倉庫的更新 兩張數(shù)據(jù)表之間的同步 對(duì)象依賴關(guān)系 發(fā)現(xiàn)對(duì)象間的依賴關(guān)系 存儲(chǔ)過程、表、視圖、函數(shù)、觸發(fā)器、用戶定義類型等 架構(gòu)綁定對(duì)象以及非綁定對(duì)象 常見的應(yīng)用場(chǎng)合 尋找某對(duì)象多依賴的所有對(duì)象 尋找依賴于某對(duì)象的所有對(duì)象 尋找依賴于另一個(gè)數(shù)據(jù)庫的所有對(duì)象 尋找使用四部命名進(jìn)行分布式查詢的對(duì)象 Grouping Set 對(duì)GROUP BY子句的擴(kuò)展 可在同一查詢中定義多個(gè)分組 生成一個(gè)等效于由多個(gè)簡(jiǎn)單 GROUP BY 子句的 UNION ALL 生成的結(jié)果集 簡(jiǎn)化并加速報(bào)表和聚合查詢 行構(gòu)造器 在VALUES的子句中，提供多筆數(shù)值 INSERT INTO Employees(EmpName,Age,EmpId) OUTPUT INSERTED.唯一標(biāo)識(shí)符 INTO TID VALUES (阿南, 25, 5) , (阿麗, 30, 6) , (阿強(qiáng), 9