一．新型超大数据类型 Large-Value Data Types
CREATE TABLE MyPublications
(PublicationID int,
Abstract
nvarchar(max),
Publication varbinary(max));
Use .WRITE (expression, @Offset, @Length) to update the word
‘writer
’ (@Offset=20, @Length=6) with
‘author
’ in the Abstract column
UPDATE MyPublications
SET Abstract .WRITE (N
‘author', 20, 6)
WHERE PublicationID = 1;
二．先进的错误处理 Error Handling
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
{ sql_statement | statement_block }
END CATCH
[ ; ]
一：
BEGIN TRY
-- Divide-by-zero error shifts control flow to the CATCH block
SELECT 1/0;
END TRY
BEGIN CATCH
-- Retrieve error information
SELECT ERROR_NUMBER() AS ErrNumber,
ERROR_SEVERITY() AS ErrSeverity,
ERROR_STATE() AS ErrState,
ERROR_PROCEDURE() AS ErrProc,
ERROR_LINE() AS ErrLine,
ERROR_MESSAGE() AS ErrMessage;
END CATCH;
二：
USE AdventureWorks;
GO
CREATE PROCEDURE usp_GetErrorInfo AS
SELECT ERROR_NUMBER() AS ErrNumber,
ERROR_SEVERITY() AS ErrSeverity,
ERROR_STATE() as ErrState,
ERROR_LINE() as ErrLine,
ERROR_PROCEDURE() as ErrProc,
ERROR_MESSAGE() as ErrMessage;
GO
BEGIN TRY
BEGIN TRANSACTION;
-- Generate a constraint violation error
DELETE FROM Production.Product WHERE ProductID = 980;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
EXECUTE usp_GetErrorInfo;
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH;
三．通用表表达式 Common Table Expressions
WITH <cte_alias>(<column_aliases>)
AS
(
<cte_query_definition>
)
SELECT *
FROM <cte_alias>
WITH mid_cte
AS
(
SELECT ((MAX(value)
– MIN(value)) / 2 ) AS midval
FROM Invoices
)
SELECT
CASE
WHEN value > mid_cte.midval THEN 0
ELSE 1
END AS half,
Invoices.*
FROM Invoices, mid_cte
ORDER BY half;
WITH <cte_alias>(<column_aliases>)
AS
(
<cte_query_definition> -- Anchor member is defined
UNION ALL
<cte_query_definition> -- Recursive member is defined
-- referencing cte_alias
)
SELECT *
FROM <cte_alias>
-- Returns all employees reporting to Employee with EmployeeID=109
WITH EmpCTE (EmployeeID, ManagerID, Title)
AS
(
SELECT EmployeeID, ManagerID, Title
FROM HumanResources.Employee
WHERE EmployeeID = ‘109’
UNION ALL
SELECT E.EmployeeID, E.ManagerID, E.Title
FROM HumanResources.Employee AS E
JOIN EmpCTE AS M
ON E.ManagerID = M.EmployeeID
)
SELECT * FROM EmpCTE
四．DDL 触发器 DDL Triggers
CREATE TRIGGER trg_disallow_create_table
ON DATABASE
FOR CREATE_TABLE
AS
PRINT 'CREATE TABLE Issued.
’;
SELECT EVENTDATA().value('(/EVENT_INSTANCE/
TSQLCommand/CommandText)[1]','nvarchar(max)');
TSQLCommand/CommandText)[1]','nvarchar(max)');
RAISERROR ('New tables cannot be created in this database.', 16, 1);
ROLLBACK;
;
五．新的排序函数 Ranking Functions
SELECT
ROW_NUMBER() OVER(ORDER BY City) AS Row_Num,
RANK() OVER(ORDER BY City) AS Rank,
RANK() OVER(PARTITION BY City
ORDER BY LastName) AS Part_Rank,
DENSE_RANK() OVER(ORDER BY City) AS Dense_Rank,
NTILE(4) OVER(ORDER BY City, ) AS NTile_4,
LastName, FirstName, City
FROM Employees
ORDER BY City, LastName
六．新的关系运算符 PIVOT/UNPIVOT/APPLY
实例演示
APPLY
七．新的结果集运算符 EXCEPT and INTERSECT
八．OUTPUT在DML中的特别运用
九．TOP 增强功能
十．T-SQL和CLR的集成
什么是SQLCLR?
SQLCLR概述
990
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
