SQL 2008 T-Prep 上課心得(三)

三、四兩天開發的課程由 Simon Sabin 來上

2008 提供的日期/時間格式

資料型態

格式

範圍

精確度

所占空間

使用者自訂局部秒精確度

時區位移

time hh:mm:ss[.nnnnnnn] 00:00:00.0000000 ~  23:59:59.9999999 100 nanoseconds 3 ~ 5
date YYYY-MM-DD 0001-01-01 ~  9999-12-31 1 天 3
smalldatetime YYYY-MM-DD hh:mm:ss 1900-01-01 ~ 2079-06-06 1 minute 4
datetime YYYY-MM-DD hh:mm:ss[.nnn] 1753-01-01 ~ 9999-12-31 0.00333 second 8
datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn] 0001-01-01 00:00:00.0000000 ~ 9999-12-31 23:59:59.9999999 100 nanoseconds 6 ~ 8
datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
 
0001-01-01 00:00:00.0000000 ~ 9999-12-31 23:59:59.9999999 (in UTC) 100 nanoseconds 8 ~ 10
  • 變動的精確數可以節省空間
  • 時間與日期分開,在利用 between and 取間隔時可方便許多 
  • 舊的時間函數可以使用新的資料型態,提供新的時間函數以取得更精確的時間

資料表型態的參數

  • 可以先定義資料表 Type,再宣告該型態的變數
    CREATE TYPE mytab AS TABLE (id int);
    DECLARE @t mytab;
  • 傳遞資料表型態的參數是唯讀的

T-SQL 語法增強

  • 可以一行指令同時宣告變數與初始化值
    DECLARE @i int = 4
  • C 格式的累加運算子,以下範例 @i 會變成 256,因為  2*2 -> 4*4 -> 16*16
    DECLARE @i INT=2
    SELECT TOP 3 @i*=@i FROM sys.objects
    SELECT @i
  • 單句話新增多筆記錄
    CREATE TABLE #t(c1 int,c2 nvarchar(10))
    INSERT #t VALUES(1,’a’),(2,’b’)
  • SELECT c1,c2,c3
    FROM #t
    JOIN
    (
      VALUES (1, ‘aa’), (3, ‘cc’)
    ) t (c3, c4)
    ON #t.c1 = t.c3;

Grouping Sets
符合 ANSI 的標準語法,可以搭配 Group By 子句,取得以往 Rollup 或 Cube 的效果。例如:

SELECT Country,TitleOfCourtesy,COUNT(EmployeeID) 彙總結果 FROM Employees
GROUP BY Grouping Sets(Country,TitleOfCourtesy,(Country,TitleOfCourtesy))
Order By Country DESC,TitleOfCourtesy

結果:
image

Merge 語法

  • 來源與目的資料比較後,以一句話完成新增、修改、刪除等動作,如同單一句話自動會有交易,透過 Merge 語法所有資料更新的動作都包在一個交易內,不像以往需明確定義交易,然後包裝所有變更。
  • 與 ANSI SQL 2006 相容並與以延伸
  • 若用 UPDATE FROM JOIN 語法變更資料,若來源端有兩筆以上紀錄對應到目的端,則以哪一筆記錄更新將無法推測,Merge 語法則直接回傳錯誤

CREATE TABLE tbSource(C1 INT PRIMARY KEY,C2 NVARCHAR(10))
GO
INSERT tbSource VALUES(1,N’甲’),(2,N’乙’)
GO
SELECT * INTO tbDest FROM tbSource

DELETE tbSource WHERE c1=1
UPDATE tbSource SET c2=N’乙2′ WHERE C1=2
INSERT tbSource VALUES(3,N’丙’)

MERGE tbDest D
  USING tbSource s
  ON D.c1 = S.c1
  WHEN MATCHED THEN –修改
    UPDATE SET D.c2 = S.c2
  WHEN SOURCE NOT MATCHED THEN  –刪除
     DELETE
  WHEN TARGET NOT MATCHED THEN  –新增
    INSERT VALUES(c1, c2)
    OUTPUT $action, INSERTED.c1 [New c1],
                    INSERTED.c2 [New c2],
                    DELETED.c1  [Original c1],
                    DELETED.c2  [Original c2];

image

空間資料格式

  • 提供兩種資料型態
    • Geometry:平面地球
    • Geography:圓地球
  • 特殊資料型態是以 SQLCLR UDT 的方式實做
    • 使用 "." 來存取屬性,或是呼叫執行個體的函數
    • 使用 "::" 來呼叫靜態函數
    • 函數屬性名稱要區分大小寫
  • 結合微軟 Virtual Earth 建立 SQL 空間資料的範例網頁:http://mikeo.co.uk/demo/sqlspatial/default.aspx

Hierarchyid

  • 以 SQLCLR UDT 實做的特殊資料型態
  • 儲存有階層性的資料,便利維護樹狀結構,例如檔案架構、組織階層
  • 提供 GetRoot、GetLevel、IsDescendant、GetDescendant、GetAncestor、Reparent 等方法

— Step 1: 建立有階層特徵的資料表
—         HierarchyID 可比較,因此可當作主鍵
CREATE TABLE tbEmployee
(
   OrgNode HierarchyID PRIMARY KEY CLUSTERED,
   OrgLevel AS OrgNode.GetLevel(),
   EmployeeID int UNIQUE NOT NULL,
   EmpName nvarchar(20) NOT NULL) ;
GO

— Step 2: 建立 breadth-first 索引,也就是相同父親的資料放在一起
—         以數值 OrgLevel 放在前面,然後才是結點
CREATE UNIQUE INDEX EmployeeOrgNc1
ON tbEmployee(OrgLevel, OrgNode) ;
GO

— Step 3: 載入資料
— 載入根結點
INSERT tbEmployee(OrgNode, EmployeeID, EmpName)
VALUES (hierarchyid::GetRoot(), 1, N’甲’) ;
GO

SELECT OrgNode.ToString() [文字描述階層],
OrgNode, OrgLevel, EmployeeID, EmpName
FROM tbEmployee ;

image
GO

–透過 GetDescendant 函數建立第一個子結點
DECLARE @Manager hierarchyid
SET @Manager = (SELECT OrgNode FROM tbEmployee WHERE EmployeeID = 1)

–加入子結點,因為是第一個子結點,所以不需要算位置
INSERT tbEmployee (OrgNode, EmployeeID, EmpName)
VALUES
(@Manager.GetDescendant(NULL, NULL), 12, N’乙’) ;
GO

SELECT OrgNode.ToString() AS  [文字描述階層],
OrgNode, OrgLevel, EmployeeID, EmpName
FROM tbEmployee ;
GO

image

— Step 4: 建立新增節點的共用預存程序
CREATE PROC AddEmp(@mgrid int, @empid int, @e_name nvarchar(20))
AS
BEGIN
   — mOrgNode 父節點
   — lc 該父節點的最後一個子結點
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid
   SELECT @mOrgNode = OrgNode
   FROM tbEmployee
   WHERE EmployeeID = @mgrid

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode)
      FROM tbEmployee
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ; –傳回上一階,相同父節點的最大子結點

      INSERT tbEmployee(OrgNode, EmployeeID, EmpName)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name) –將新增節點加在父節點最大的孩子旁
   COMMIT
END ;
GO

EXEC AddEmp 12, 121, N’丙’;
EXEC AddEmp 12, 122, N’丁’;
EXEC AddEmp 1, 13, N’戊’;
EXEC AddEmp 121, 1211, N’己’;
EXEC AddEmp 13, 131, N’庚’;
GO

SELECT OrgNode.ToString() AS  [文字描述階層],
OrgNode, OrgLevel, EmployeeID, SPACE(OrgNode.GetLevel()*5) + EmpName
FROM tbEmployee;

image

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: