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 

结果：
 

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;