SQL Server 2008 亮点

Hiosh 2008-05-14 03:55:35

加精

SQL Server 2008 提供的日期/时间格式
[img=http://p.blog.csdn.net/images/p_blog_csdn_net/hiosh/372304/o_SQL%20%e6%97%b6%e9%97%b4%e6%a0%bc%e5%bc%8f.JPG] [/img]

变动的精确数可以节省空间
时间与日期分开，在利用 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

单句话新增多笔记录
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 的效果。例如：

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

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

Order By Country DESC,TitleOfCourtesy

结果：
[img=http://p.blog.csdn.net/images/p_blog_csdn_net/hiosh/372304/o_11.jpg] [/img]

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

[img=http://p.blog.csdn.net/images/p_blog_csdn_net/hiosh/372304/o_22.jpg] [/img]

空间数据格式
提供两种数据型态
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 ;

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

[img=http://p.blog.csdn.net/images/p_blog_csdn_net/hiosh/372304/o_33.jpg] [/img]

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