[2008] MS SQL 所提供類似ORACLE之RowId 功能 [%%physloc%%]

對於ORACLE使用者而言，許多情況下ROWID指令是相當必要且簡便之指令，如刪除重複資料時。MSSQL 2008版本起已具有類似ORACLE ROWID指令的%%physloc%%函數（為Unleashed指令，不保證未來版本支援），可取得資料列（Row）的物理位址，格式為Binary(8)，其中1~4表示Page No，5~6代表File no，7~8為Slot No。並提供fn_PhysLocFormatter純量函數及fn_PhysLocCracker資料表函數以供取得其位置：

ü   sys.fn_physlocformatter純量函數，可將%%physloc%%物理位置轉換為(file:page:slot)格式字串。

ü   fn_PhysLocCracker資料表函數，可將物理位置拆解為file/page/slot置於不同欄位中。

 建立資料

SELECT * INTO Test

FROM (VALUES ('A')

            , ('B')

            , ('C')

            , ('A')

    ) AS Temp(VAL)

測試-1： fn_PhysLocFormatter

SELECT A.*

, %%physloc%% AS [%%physloc%%]

, sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]

FROM Test A 

      

          

測試-2：fn_PhysLocCracker

SELECT A.*

   , %%physloc%% AS [%%physloc%%]

   , C.file_id

   , C.page_id

   , C.slot_id

FROM Test A 
     CROSS APPLY fn_PhysLocCracker(%%physloc%%) C

註：後續文章再介紹CROSS APPLY之用法及如何開發此類型資料表函數。

      

          

測試3-查詢：(刪除, 修改類似)

SELECT A.*

FROM Test A

WHERE %%physloc%% = 0xC100000001000100

      

[2008] VALUES增強功能（Table Value Constructor）

VALUES關鍵字常見應用於INSERT指令上，以指定（暫存）將存入資料表之資料，MSSQL 2008版本起增強VALUES之功能及應用範圍，先前版本僅支援一筆資料之操作，此版本起開始支援多筆模式，可於INSERT字句中同時指定存入多筆資料，並擴大應用範圍於SELECT、UPDATE、DELETE或MERGE INTO等語法中使用，資料庫將VALUES關鍵字所列舉之資料列產生並暫存於虛擬資料表中，提供後續INSERT、SELECT（或相關）等參照使用。

下表整理出可能之應用情境，過去多筆資料上之應用常需搭配UNION ALL語法產生或組合所需，2008版本則可用VALUES功能達成所需。

功能	SQL2008以下版本	SQL2008版本
Insert 1筆	INSERT … VALUES	INSERT … VALUES
Insert 多筆	INSERT … SELECT [UNION ALL]	INSERT … VALUES
暫存資料	SELECT [COLUMN LIST] UNION ALL SELECT [COLUMN LIST]	VALUES

前後版本對應語法及說明，整理列於下表:

功能	SQL2008以下	SQL2008(含)以上	用途/說明
Insert All	INSERT INTO #Seasons (Season, Disp) SELECT 'Spring', '春天' UNION ALL SELECT 'Summer', '夏天' UNION ALL SELECT 'Autumn', '秋天' UNION ALL SELECT 'Winter', '冬天'	INSERT INTO #Seasons VALUES ('Spring', '春天')       , ('Summer', '夏天')       , ('Autumn', '秋天')       , ('Winter', '冬天');	DML指令中同時INSERT多筆資料。 測試前需建立#Seasons CREATE TABLE #Seasons ( Season   varchar(20), Disp     varchar(20) );
Temp Table	SELECT 'Spring' Season , '春天' Disp UNION ALL SELECT 'Summer', '夏天' UNION ALL SELECT 'Autumn', '秋天' UNION ALL SELECT 'Winter', '冬天'	SELECT * FROM       (VALUES ('Spring', '春天')          , ('Summer', '夏天')          , ('Autumn', '秋天')          , ('Winter', '冬天')    ) AS Seasons(Season, Disp) ORDER BY Disp	以VALUES產生暫存資料表。