SQL2005 学习笔记

SQL2005 学习笔记

1) varchar(MAX) nvarchar(MAX) nvarbinary(MAX).

• MAX 最大2G,不再限制于8K,替代text,ntext,image等数据类型，普通字符串函数都可以处理这些类型。
• 增加write，用于局部更新。

如 update tbtest set c1.write('test string',10,3) ;-- 在原字符串10位开始，用'test string'替换3个字符。
2) XML,最大2G.

2 索引

• ALTER INDEX .. DISABLE ，如果索引被DISABLE后必须需要重建(REBUILD)或是删除后再创建，否则引用索引数据错误。若是 Clustered Index 被 Disable，则整个数据表都不能用。

技巧：如果要暂停使用一个表，又不想删除他，就让Clustered Index 被 Disable。

• Alter index .. rebuild 重新建立指定索引。
• alter index .. reorganize 重新组织索引。
• 增强覆盖索引的功能，提升查询性能。 索引叶级加入与键值无关的表的其他字段，这些字段不能做排序等额外操作。

举例：
--创建覆盖索引
CREATE NONCLUSTERED INDEX idxCustomerID
ON Sales.Customer(CustomerID)
INCLUDE(AccountNumber,TerritoryID)
--上述索引完整覆盖了以下的查询，通过索引查询不需要查询原表
SELECT CustomerID,AccountNumber,TerritoryID FROM Sales.Customer
WHERE CustomerID BETWEEN 1 AND 100
3 Partion Table (分区存储的表)
新增Partion Table 来分区存储一个表，可以充分利用CPU资源和存储资源，分散访问，提高访问效率。
技巧： 现在处理分年度数据终于有了一个好的解决方案，原来拥有采用的年度账库的模式可以结束了。我的几个应用程序也可以在数据量和查询效率上找到了一个好的处理办法，有空了迁移到2005上，好多问题就好解决了。
(具体用法以后再写)。
4 DDL 触发器。
SQL2005终于有了这些数据定义语言（DDL）触发器,现在写数据操作日志就方便多了。
2大类：

• 针对整个服务器的触发器--ON server

create_login /create_endpoit等操作的触发器。

• 针对数据库级的触发器---on database

create_table/create_user /create_schema .
DDL的触发器之间不能有先后的依存关系。
DDL的触发器默认和触发它的语法在同一个事务中，可以通过rollback来恢复以前的操作。
技巧1：使用触发器搭配Eventdata()函数来记录数据库操作日志。
技巧2：终于有简单的办法来防止用户误删除表了，在触发器检查详细判断条件来决定是否接受用户的操作指令。

二、新的DML语法。
1. CTE(Common Table Expression)
可以当作临时的视图表，具备了原来衍生表的特点，但是在一个批处理查询数据中可以重复使用，因为 CTE 的定义只执行一次，就可以重复在语法中使用，所以较有效率。使用方法和表一样。
技巧: 原来需要使用临时表、自定义函数表、游标实现的功能，现在可以通过CTE轻松实现，且效率更高。

• 建立一个CTE

WITH Sales_cte (ProductID,CountProduct)
AS
(SELECT ProductID,Count(ProductID) FROM [Order Details] GROUP BY ProductID)
SELECT * FROM Sales_cte

• 单一批处理内多个CTE 定义，但要以逗号隔开,后面的CTE可以使用前面定义的CTE名称。

WITH
cteSales(ProductID,CountProduct)
AS
(SELECT ProductID,Count(ProductID) FROM [Order Details] GROUP BY ProductID)
,
cteStatistics(MaxCount,MinCount,AvgCount,DiffCount)
AS
(SELECT Max(CountProduct),Min(CountProduct),Avg(CountProduct),Max(CountProduct)-Min(CountProduct)
FROM cteSales)

• CTE递归功能使用，解决原来诸如器材配件需要量计算类的问题。

DECLARE @CarID INT
SET @CarID=1;
WITH CarParts(SubPart,Qty,lvl) AS
(
SELECT SubPart, Qty, 0
FROM Car_Parts
WHERE Part=N'车身' AND Car_ID=@CarID
UNION ALL
SELECT Car_Parts.SubPart, CarParts.Qty*Car_Parts.Qty, CarParts.lvl+1
FROM CarParts INNER JOIN Car_Parts
ON CarParts.SubPart=Car_Parts.Part --与前一段定义发生递归
WHERE Car_Parts.Car_ID=@CarID
)
SELECT * FROM CarParts
OPTION(MAXRECURSION 3)

2 取得排名和顺序的函数：
以下4个函数搭配over 使用， ：
RANK: 排名位置，不连续, 相同名字占号。
DENSE_RANK: 排名位置，连续。
ROW_NUMBER: 依照群组显示每条记录在该群组中顺序位置。
NTILE: 依照指定分组数量将结果集切分，并显示纪录在各组的的排名。
partition by 子句决定排名的纪录如何分组。
举例：

--依照产品类型分开编的记录编号
SELECT PSC.Name Category,p.Name Product,p.ListPrice,
ROW_NUMBER() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice DESC) AS Row
FROM Production.Product p
JOIN Production.ProductSubCategory PSC
ON p.ProductSubCategoryID=PSC.ProductSubCategoryID
ORDER BY Category,ListPrice DESC

-- 通过 ROW_NUMBER 函数搭配子查询完成数据分页的动作(哈哈！现在分页读取数据简单多了)
CREATE PROC spGetPages2 @iRowCount INT,@iPageNo INT
AS
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID ASC) RowNum,
* FROM Production.Product ) OrderData
WHERE RowNum BETWEEN @iRowCount*(@iPageNo-1)+1 AND @iRowCount*@iPageNo
ORDER BY ProductID ASC

--依照各产品的价格排名，但名次是连续的，不会因为相同名次有 N 个，
--下一个名次就跳至 N+1 名之后
SELECT Name ,ListPrice,
DENSE_RANK() OVER(ORDER BY ListPrice DESC) AS Rank
FROM Production.Product
--依照产品类型分开编的记录编号
--各类产品依照标价的大小排序，并切成十群，标示每一群的编号
SELECT NTILE(10) OVER(PARTITION BY PC.Name ORDER BY P.ListPrice DESC) AS PriceBand,
pc.Name Category,p.Name Produc,p.ListPrice
FROM Production.Product p
JOIN Production.ProductSubCategory PSC
ON p.ProductSubCategoryID=PSC.ProductSubCategoryID
JOIN Production.ProductCategory pc
ON PSC.ProductCategoryID=pc.ProductCategoryID
ORDER BY Category,ListPrice DESC

3 结构化错误处理
begin try
...{SQL语句}
end try
begin catach
...{错误处理语句}
end catch
新增 系统函数来取得相关的信息进行处理，可以重复调用（值相同，不同于@@error公共变量）：
error_number
error_serverity
error_state
error_message

4 APPLY 操作符。
5 PIVOT 和UNPIVOT. 数据转置和反转置的操作.
6 OUTPUT 语法。 insert delete update中搭配output子句，相当于原来编写触发器来取得更新数据内容的方式。
举例：
CREATE TABLE TestTbl (a INT NOT NULL IDENTITY(1,1),b INT)
GO
INSERT TestTbl VALUES (1)
INSERT TestTbl VALUES (2)
SELECT * FROM TestTbl
GO
DECLARE @InsertOutput TABLE(a INT,B INT)
--将插入的记录输出
INSERT TestTbl
OUTPUT INSERTED.a, INSERTED.b INTO @InsertOutput
VALUES (3)
SELECT * FROM @InsertOutput
GO
DECLARE @UpdateOutput TABLE(oldA INT,oldB INT,A INT,B INT)
UPDATE TestTbl
SET b=5
OUTPUT DELETED.*,INSERTED.* INTO @UpdateOutput
WHERE a=3
SELECT * FROM @UpdateOutput
GO
7 参照完整性增强：
当外键对应的主数据表中的字段值被修改或删除时，对delete /update外键操作增加了set default 和set null 2个功能。