SQL Server 2005中的T-SQL增强

丰富的数据类型 Richer Data Types 

1、varchar(max)、nvarchar(max)和varbinary(max)数据类型最多可以保存2GB的数据，可以取代text、ntext或image数据类型。
CREATE TABLE myTable
(
    id INT,
    content VARCHAR(MAX)
)

2、XML数据类型
XML数据类型允许用户在SQL Server数据库中保存XML片段或文档。

错误处理 Error Handling

1、新的异常处理结构

2、可以捕获和处理过去会导致批处理终止的错误
前提是这些错误不会导致连接中断（通常是严重程度为21以上的错误，例如，表或数据库完整性可疑、硬件错误等等。）。

3、TRY/CATCH 构造
SET XACT_ABORT ON
   BEGIN TRY
     <core logic>
   END TRY
   BEGIN CATCH TRAN_ABORT
     <exception handling logic>
   END TRY

@@error may be quired as first statement in CATCH block

4、演示代码
USE demo
  GO
  --创建工作表

  CREATE TABLE student
  ( 
      stuid INT NOT NULL PRIMARY KEY,
      stuname VARCHAR(50)
  )

  CREATE TABLE score
  (
      stuid INT NOT NULL REFERENCES student(stuid),
      score INT
  )
GO

INSERT INTO student VALUES (101,''''zhangsan'''')
INSERT INTO student VALUES (102,''''wangwu'''')
INSERT INTO student VALUES (103,''''lishi'''')
INSERT INTO student VALUES (104,''''maliu'''')

--调用一个运行时错误
SET XACT_ABORT OFF
  BEGIN TRAN
     INSERT INTO score VALUES (101,90)
     INSERT INTO score VALUES (102,78)
     INSERT INTO score VALUES (107, 76) /* 外键错误 */
     INSERT INTO score VALUES (103,81)
     INSERT INTO score VALUES (104,65)
  COMMIT TRAN
  GO

  SELECT * FROM student
  SELECT * FROM score

--使用TRY...CATCH构造，并调用一个运行时错误
SET XACT_ABORT OFF
  BEGIN TRY
    BEGIN TRAN
       INSERT INTO score VALUES (101,90)
       INSERT INTO score VALUES (102,78)
       INSERT INTO score VALUES (107, 76) /* 外键错误 */
       INSERT INTO score VALUES (103,81)
       INSERT INTO score VALUES (104,65)
    COMMIT TRAN
    PRINT ''''事务提交''''
  END TRY
  BEGIN CATCH
    ROLLBACK
     PRINT ''''事务回滚''''
        SELECT ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_MESSAGE() as ErrorMessage;
   END CATCH
GO

SELECT * FROM score
GO

快照隔离 Snapshot Isolation

1、写入程序不会阻碍读取程序
2、Snapshot isolation must be enabled for DB
      ALTER DATABASE 数据库 SET allow_snapshot_isolation ON
3、Snapshot isolation must be enabled for connection
      Set transaction isolation level snapshot
4、UPDATE transactions keep old versions of data in a linked list
5、新的隔离级别提供了以下优点：
　 1) 提高了只读应用程序的数据可用性
　 2) 允许在OLTP环境中执行非阻止读取操作
　 3) 可对写入事务进行自动的强制冲突检测
6、演示代码
CREATE DATABASE demo2
GO
USE demo2
   ALTER DATABASE demo2 SET allow_snapshot_isolation ON
   CREATE TABLE test
   (
     tid INT NOT NULL primary key,
     tname VARCHAR(50) NOT NULL
   )
  INSERT INTO test VALUES(1,''''version1'''')
  INSERT INTO test VALUES(2,''''version2'''')

--连接一

USE demo2
BEGIN TRAN
   UPDATE test SET tname=''''version3'''' WHERE tid=2
   SELECT * FROM test

--连接二
USE demo2
   SET transaction isolation level snapshot
   SELECT * FROM test 

TOP 增强功能

1、TOP 增强
可以指定一个数字表达式，以返回要通过查询影响的行数或百分比，还可以根据情况使用变量或子查询。
可以在DELETE、UPDATE和INSERT查询中使用TOP选项。

2、更好地替换SET ROWCOUNT选项，使之更为有效。

OUTPUT

1、SQL Server 2005引入一个新的OUTPUT子句，以使您可以冲修改语句(INSERT、UPDATE、DELETE)中将数据返回到表变量中。

2、新的OUTPUT子局的语法为：
OUTPUT <dml_select_list> INTO @table_variable
可以通过引用插入的表或删除的表来访问被修改的行的旧/新影象，其方式与访问触发器类似。在INSERT语句中，只能访问插入的表。在DELETE语句中，只能访问删除的表。在UPDATE语句中，可以访问插入的表和删除的表。

3、代码演示
USE demo
GO
CREATE TABLE tt
(
  id INT IDENTITY,
  c1 VARCHAR(15)
)
GO

INSERT INTO tt VALUES (''''r1'''')
INSERT INTO tt VALUES (''''r2'''')
INSERT INTO tt VALUES (''''r5'''')
INSERT INTO tt VALUES (''''r6'''')
INSERT INTO tt VALUES (''''r7'''')
INSERT INTO tt VALUES (''''r8'''')
INSERT INTO tt VALUES (''''r9'''')
INSERT INTO tt VALUES (''''r10'''')

DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))
DELETE tt
OUTPUT DELETED.id, DELETED.c1 INTO @del
WHERE id < 3
SELECT * FROM @del
GO
-----------------------------------------------
USE demo
GO
CREATE TABLE toptest (column1 VARCHAR(150))
GO
INSERT INTO toptest VALUES(''''t1'''')
INSERT INTO toptest VALUES(''''t2'''')
INSERT INTO toptest VALUES(''''t3'''')
INSERT INTO toptest VALUES(''''t4'''')
INSERT INTO toptest VALUES(''''t5'''')
INSERT INTO toptest VALUES(''''t6'''')
INSERT INTO toptest VALUES(''''t7'''')
INSERT INTO toptest VALUES(''''t8'''')
SELECT * FROM toptest
GO

CREATE TABLE toptest2 (column2 VARCHAR(150))
GO
INSERT INTO toptest2 VALUES(''''c1'''')
INSERT INTO toptest2 VALUES(''''c2'''')

--声明3个变量
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT

--赋值
SET @a = 10
SET @b = 5
SELECT @c = @a/@b

--使用计算表达式
SELECT TOP(@c) * FROM toptest

--使用SELECT语句作为条件
SELECT TOP(SELECT COUNT(*) FROM toptest2) *
FROM toptest

--指出top
DELETE TOP(2) toptest where column1>''''t6''''

--更新top
UPDATE TOP(2) toptest SET column1 = ''''hi'''' where column1<=''''t2''''

SELECT * FROM toptest

排序函数 Ranking Functions

1、SQL Server引入几个新的排序函数：如ROW_NUMBER、RANK、DENSE_RANK等。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。

2、排序函数都遵循类似的语法模式：
()OVER
([PARTITION BY]
ORDER BY)
该函数只能在查询的两个子句中指定 - 在SELECT子句或ORDER BY子句中。以下详细讨论不同的函数。 

3、ROW_NUMBER
ROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序
USE demo
GO
CREATE TABLE rankorder
(
 orderid INT,
 qty INT
)
GO
INSERT rankorder VALUES(30001,10)
INSERT rankorder VALUES(10001,10)
INSERT rankorder VALUES(10006,10)
INSERT rankorder VALUES(40005,10)
INSERT rankorder VALUES(30003,15)
INSERT rankorder VALUES(30004,20)
INSERT rankorder VALUES(20002,20)
INSERT rankorder VALUES(20001,20)
INSERT rankorder VALUES(10005,30)
INSERT rankorder VALUES(30007,30)
INSERT rankorder VALUES(40001,40)
GO
SELECT orderid,qty,
  ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
  RANK()       OVER(ORDER BY qty) AS rank,
  DENSE_RANK() OVER(ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty

通用表表达式 Common Table Expressions 

通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集。在他们的简单形式中，您可以将CTE视为类似于视图和派生表混合功能的改进版本。在查询的FROM子句中引用CTE的方式类似于引用派生表和视图的方式。只须定义CTE一次，即可在查询中多次引用它。在CTE的定义

[1] [2]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18