SQL Server 2008 亮点

SQL Server 2008 提供的日期/时间格式


变动的精确数可以节省空间
时间与日期分开，在利用 between and 取间隔时可方便许多 
旧的时间函数可以使用新的数据型态，提供新的时间函数以取得更精确的时间

数据表型态的参数
可以先定义数据表 Type，再宣告该型态的变量
CREATE TYPE mytab AS TABLE (id int);
DECLARE @t mytab;
传递数据表型态的参数是只读的

T-SQL 语法增强
可以一行指令同时宣告变量与初始化值

SQL code

   
   

    
    
    
    DECLARE
    
     
    
    @i
    
     
    
    int
    
     
    
    =
    
     
    
    4
    
     
   
   

C 格式的累加运算符，以下范例 @i 会变成 256，因为  2*2 -> 4*4 -> 16*16

SQL code

   
   

    
    
    
    DECLARE
    
     
    
    @i
    
     
    
    INT
    
    =
    
    2
    
    

    
    SELECT
    
     
    
    TOP
    
     
    
    3
    
     
    
    @i
    
    *=
    
    @i
    
     
    
    FROM
    
     sys.objects

    
    SELECT
    
     
    
    @i
    
     
   
   

单句话新增多笔记录
DECLARE @t TABLE (id int, name varchar(20));
INSERT INTO @t VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue');

Grouping Sets
符合 ANSI 的标准语法，可以搭配 Group By 子句，取得以往 Rollup 或 Cube 的效果。例如：

SQL code

   
   

    
    
    
    SELECT
    
     Country,TitleOfCourtesy,
    
    COUNT
    
    (EmployeeID) 汇总结果 
    
    FROM
    
     Employees 

    
    GROUP
    
     
    
    BY
    
     
    
    Grouping
    
     Sets(Country,TitleOfCourtesy,(Country,TitleOfCourtesy))

    
    Order
    
     
    
    By
    
     Country 
    
    DESC
    
    ,TitleOfCourtesy 

   
   

结果：


Merge 语法
来源与目的数据比较后，以一句话完成新增、修改、删除等动作，如同单一句话自动会有交易，透过 Merge 语法所有数据更新的动作都包在一个交易内，不像以往需明确定义交易，然后包装所有变更。

与 ANSI SQL 2006 相容并与以延伸
若用 UPDATE FROM JOIN 语法变更数据，若来源端有两笔以上纪录对应到目的端，则以哪一笔记录更新将无法推测，Merge 语法则直接回传错误

SQL code

   
   

    
    
    
    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
    
    ]
    
    ; 


   
   

空间数据格式
提供两种数据型态
Geometry：平面地球
Geography：圆地球

特殊数据型态是以 SQLCLR UDT 的方式实做
使用 "." 来存取属性，或是呼叫实例的函数
使用 "::" 来呼叫静态函数
函数属性名称要区分大小写
结合微软 Virtual Earth 建立 SQL 空间数据的范例网页： http://mikeo.co.uk/demo/sqlspatial/default.aspx

Hierarchyid
以 SQLCLR UDT 实做的特殊数据型态
储存有阶层性的数据，便利维护树状结构，例如档案架构、组织阶层
提供 GetRoot、GetLevel、IsDescendant、GetDescendant、GetAncestor、Reparent 等方法

SQL code

   
   

    
    
    
    --
    
     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 ;
   
   

SQL code

   
   

    
    
    
    --
    
    透过 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 ;
   
   

SQL code

   
   

    
    
    
    --
    
     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;