SQL SERVER 2005 新特性(T-SQL enhancement)

SQL SERVER 2005 新特性(T-SQL enhancement)
 
 
T-SQL Enhancements

1 TOP

TOP后面可以使用表达式,而sql 2k只能使用consts

e.g

DECLARE @MyTop INT
SET @MyTop = 15
SELECT TOP (@MyTop*2) * FROM CASES

2 Common Table Expressions (CTE)

e.g.

USE AdventureWorks
WITH EmployeeChart(EmployeeID, ManagerID, Title)
AS
(SELECT EmployeeID, ManagerID, Title
 FROM HumanResources.Employee
 WHERE EmployeeID = 3
 UNION ALL
SELECT L2.EmployeeID, L2. ManagerID, L2.Title
 FROM HumanResources.Employee AS L2
 JOIN EmployeeChart
  ON L2.ManagerID = EmployeeChart.EmployeeID)
SELECT * from EmployeeChart

注意：with 后面的表名后并不需要申明字段数据类型.

3 PIVOT and UNPIVOT

e.g.

--兼容性为90则能支持PIVOT子句
EXEC sp_dbcmptlevel 'master', '90';
GO

--采购订单表
declare @p table
(
 purchaseorderid int,
 employeeid varchar(20) ,
 vendorid varchar(10)
)

--测试数据
insert @p
select 1,'e1','v1'
union all
select 2,'e2','v2'
union all
select 3,'e2','v2'
union all
select 4,'e1','v1'

--SQL SERVER 2005
-- 按vendor分组下了几次订单针对特定雇员累计数,可以把pivot子句看成按分组条件，去聚集函数计算值如count，sum
-- 形式一般表现为 FOR子句后面的决定横排的字段 IN后面决定竖的记录数变成横的栏位数
-- PIVOT表示按for和in的条件横记录打成竖记录

SELECT vendorid, [e1] as emp1, [e2] as emp2
FROM
 (SELECT purchaseorderid, employeeid, vendorid
  FROM @p) p
PIVOT
(
COUNT (purchaseorderid)
FOR employeeid in
( [e1], [e2]  )
) as pvt
ORDER BY vendorid;

--SQL SERVER 2000 使用聚集函数的表达式形式

select  p.vendorid,
 count(case p.employeeid  when  ('e1') then p.purchaseorderid  else null end ) as emp1,
 count(case p.employeeid  when   ('e2') then p.purchaseorderid  else null end ) as emp2
from
 @p as p
group by p.vendorid

 

4 DDL Triggers
e.g.

CREATE TRIGGER NoTableUpdate
ON DATABASE FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'DROP TABLE and ALTER TABLE statement are not allowed'
ROLLBACK
可以看出 DDL trigger 能够阻止DROP TABLE and ALTER TABLE 等DDL的使用

If an ALTER TABLE or DROP TABLE statement is issued,
the NoTableUpdate trigger will print an error message and roll back the attempted DDL operation.

显示信息如下:
DROP TABLE and ALTER TABLE statement are not allowed
.Net SqlClient Data Provider: Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.

 

5 DML Output

e.g.

 CREATE TABLE Pivot1
(
  [Year]      SMALLINT,
  [Quarter]   TINYINT,
  Amount      DECIMAL(2,1)
)
GO
INSERT INTO Pivot1 VALUES (1990, 1, 1.1)
INSERT INTO Pivot1 VALUES (1990, 2, 1.2)
INSERT INTO Pivot1 VALUES (1990, 3, 1.3)
INSERT INTO Pivot1 VALUES (1990, 4, 1.4)
INSERT INTO Pivot1 VALUES (1991, 1, 2.1)
INSERT INTO Pivot1 VALUES (1991, 2, 2.2)
INSERT INTO Pivot1 VALUES (1991, 3, 2.3)
INSERT INTO Pivot1 VALUES (1991, 4, 2.4)
GO

-- 下面是用于创建旋转结果的 SELECT 语句：
--
-- SELECT Year,
--     SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
--     SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
--     SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
--     SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
-- FROM Northwind.dbo.Pivot
-- GROUP BY Year
-- 

DECLARE @MyOrderSumTVar TABLE
(
    year int,
    Quarter int,
    Amount  DECIMAL(2,1)
)

DELETE FROM pivot1
OUTPUT DELETED.* INTO @MyOrderSumTVar
SELECT * FROM @MyOrderSumTVar

注意：
OUTPUT DELETED.* INTO  clause specifies that all deleted rows will be output

新版本返回如下信息
 (8 row(s) affected)

OrderID     CustomerID  OrderYear
----------- ----------- -----------
100         1           2000
101         1           2000
102         1           2000
103         1           2001
104         1           2001
105         1           2002
106         1           2003
107         1           2004

(8 row(s) affected)

6 WAITFOR

7 New varchar(max) Data Type

varchar(max)可以视为text, ntext ,image的替代解决方法,是string和binary的混合,不支持pointer

e.g.
CREATE TABLE NewBLOB
(
    DataID INT IDENTITY NOT NULL,
    BLOBData VARCHAR(MAX) NOT NULL
)

8 Transaction Abort Handling

基本语法如下

BEGIN TRY
    <SQL Statements>
END TRY
BEGIN CATCH TRAN_ABORT
    <SQL Statements>
END CATCH

e.g.

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.

 
 SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure        ErrorLine   ErrorMessage                                                                                                                                                                                                                                                    
----------- ------------- ----------- -----------------
8134        16            1           NULL                  3           Divide by zero error encountered.

 9 ROW_NUMBER() OVER()
 USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0

下期推出新特性专题总结提纲,感谢继续关注!

1 SQL SERVER 2005 CLR
2 SQL SERVER 2005 XML
3 SQL SERVER Report services

 

转载于:https://www.cnblogs.com/tieminsa/archive/2006/06/28/437944.html