Microsoft SQL Server 2005技术内幕： T-SQ程序设计笔记

第一章数据类型相关问题

在线上环境更改数据类型是非常危险的

使用 SET STATISTICS IO ON 来检查是否有I/O操作，如果没报告I/O操作，那么可以确定不需要访问基表，执行速度会非常快，

例如，把varchar列的大小改为更大的值将不涉及对基本数据的I/O操作，更改会瞬间完成，如果缩短varchar列的长度需要访问基表数据，对大表操作时会花费很多时间

尽量不要使用SSMS来更改架构，使用这些工具会带来不必要的操作：比较创建一个新表，复制数据，删除原表，把新表重名为原来的表名，

好习惯是使用TSQL代码来执行架构更改！！

datetime数据类型

datetime在内部使用2个4字节来存储，一共需要8字节，前4个字节表示年月日，后4个字节表示一天中的时间

SET DATEFORMAT对日期处理的影响

SET DATEFORMAT DMY

USE [sss]
INSERT INTO [dbo].[counttb]
        ( [id], [TESTDATE] )
VALUES  ( 100, -- id - int
          '2012-12-3 10:20:10'  -- TESTDATE - datetime
          )

SELECT   CAST(CONVERT(CHAR(8), [TESTDATE], 112) AS DATETIME) FROM [dbo].[counttb]
SELECT   * FROM [dbo].[counttb]

查询session级别的set 选项

DBCC USEROPTIONS

SQLSERVER根据当前session的语言设置进行转换，session的语言由登录login的默认语言决定，也可以使用set languagesession选项重新设置，通过使用set dateformat选项，指定字符d,m,y的组合，也可以控制如何解释由数字和分隔符组成的datetime字面值

SET DATEFORMAT DMY
SET DATEFORMAT YDM
.
.
.
.
.
.

sqlserver有两种datetime的字面格式独立于任意设置

依赖ODBC api的格式

datetime的数据类型四舍五入

datetime函数的表达式返回的一些结果依赖于特定的语言，如果session的语言设置为us_english，那么datename返回的是Wednesday

getdate()返回服务器本地的datetime值，getutcdate()返回当前的通用协调时间，他是根据服务器本地时间和设置的时区计算得出的，

current_timestamp是ansi形式的getdate（）！！

当仅指定日期时，你可以忽略时间部分，当转换到datetime时，该值包含时间部分，但是该部分表示午夜
当只包含时间部分的字符串转换到datetime时，sqlserver把日期部分设置为基本日期1900-1-1

从getdate（）仅提取日期部分用作默认值

SELECT DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
SELECT CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME)

前者最高粒度是秒，后者是毫秒

2014-09-13 00:00:00.000

datetime相关查询问题
解决闰年问题

标识重叠 identifying overlay

分组重叠 grouping overlay

在sqlserver2000中可以使用派生表derived table代替CTE，派生表即子查询

标识星期数（identifying weekday）

datepart标识星期数依赖于datefirst，还有语言设置langguage setting

星期数的坐标轴是循环的，这一特点为反比关系的计算增加了些难度，数学中有完整的学科可以处理循环坐标轴，例如，下面是几个基于星期数坐标轴的计算
1+1=2
7-1=1
7-1=6
1-1=7

datefirst的设置值和datepart函数返回的星期数之间存在反比关系！！

SELECT (weekday,GETDATE()+@@DATEFIRST-n)

1900年1月1日是星期一
那么问题可以这样解决

实际上这种方法就是以1900年1月1日为标准，任何事物都会有一个标准，设定一个标准

USE [AdventureWorks]

SELECT orderid ,
orderdate
FROM orcers
WHERE DATEDIFF(DAY, '19000102', orderdate) % 7 = 0

使用参照日期的方法

1900年1月1日 --星期一

ISO周

包含边界（inclusive），不包含边界（non-inclusive）

工作日 working day

生成日期序列
你可能会用到两个日期之间的所有可能的日期序列，例如，这种日期序列可以用于填充SSAS中的时间维度time dimension

USE [sss]
SET NOCOUNT ON
CREATE TABLE nums
    (
      n INT NOT NULL
            PRIMARY KEY
    )
DECLARE @max AS INT
DECLARE @rc AS INT
SET @max = 1000000
SET @rc = 1

INSERT  INTO nums
VALUES  ( 1 )
WHILE @rc * 2 <= @max
    BEGIN 
        INSERT  INTO nums
                SELECT  n + @rc
                FROM    nums
        SET @rc = @rc * 2
    END

INSERT  INTO nums
        SELECT  n + @rc
        FROM    nums
        WHERE   n + @rc <= @max

SELECT  *
FROM    [dbo].[nums]

-----------------------------------------
DECLARE @s AS DATETIME
DECLARE @e AS DATETIME
SET @s = '20060101'
SET @e = '20061231'
SELECT  @s + n - 1 AS dt
FROM    [dbo].[nums]
WHERE   n <= DATEDIFF(DAY, @s, @e) + 1

字符相关问题

模式匹配，分析，区分大小写

sqlserver使用like和patindex支持模式匹配

tsql还不支持正则表达式regular expression，ansi通过similar to运算符支持正则表达式

否定逻辑reverse logic比使用肯定逻辑positive logic 更有效

--CHECK匹配序列号
--序列号固定
CHECK(sn LIKE '[0-9][0-9][0-9][0-9][0-9]')
--序列号不固定
CHECK(sn LIKE REPLICATE('[0-9]',LEN(SN)))

--否定逻辑
CHECK(sn NOT LIKE '%[^0-9]%')

--join的时候还可以用like来join 条件
SELECT  ip ,
        CAST(SUBSTRING(ip, s1, l1) AS TINYINT) AS o1 ,
        CAST(SUBSTRING(ip, s2, l2) AS TINYINT) AS o2 ,
        CAST(SUBSTRING(ip, s3, l3) AS TINYINT) AS o3 ,
        CAST(SUBSTRING(ip, s4, l4) AS TINYINT) AS o4
FROM    IPs
        JOIN IPPatterns ON ips.ip LIKE IPPatterns.pattern
ORDER BY o1 ,
        o2 ,
        o3 ,
        o4

还可以使用SELECT PARSENAME()的方法来解析ip地址

SELECT PARSE()

bin二进制排序规则都是区分大小写的

大写小写的二进制，ascii都不同

--如果改变排序规则的话会使用表扫描，这样会降低效率
USE [sss]
SELECT  *
FROM    [dbo].[aaa]
WHERE   [name] COLLATE latin1_general_cs_as = 'sdfsdf'

--where里面的搜索条件可以搜索两次,第一次指定排序规则,影响执行计划走索引查找,select运算符会有感叹号
USE [sss]
SELECT  *
FROM    [dbo].[aaa]
WHERE   [name] COLLATE latin1_general_cs_as = 'sdfsdf'
        AND [name] = 'sdfsdf'

大型对象lob

通过引入bulk行集提供程序（bulk rowset provider），对从文件中加载数据的支持也得到加强，

这个新的提供程序允许你使用bulk引擎高效地将文件数据加载为行集，sqlserver6.5的时候已经有bulk程序！！

通过使用max说明符，varchar，nvarchar，varbinary，sqlserver存储这些值可以达到lob支持的最大容量，即2GB

SQLSERVER提供了max列的write方法来修改字符串中的部分数据，而不需要加载整个字段值

试想一下，整个字段值2GB的话，那么读取字段值和写入字段值将会很慢，但是通常我们的业务一个字段很少有2GB数据

一般有100MB就不错了，所以代码中总是set xx=xx，而不是xx.write()

UPDATE  [dbo].[aaa]
SET     [name].WRITE('SDF', 9, 3)
WHERE   [a] = 1

wirte()方法类似于lob数据的，readtext，writetext

USE [AdventureWorks]
SELECT shipperid,companyname,phone
FROM OPENROWSET(BULK 'C:\TEMP\SHIPPER.TXT',FORMATFILE='C:\TEMP\SHIPPERS.FMT')AS S

OPENROWSET的bulk提供程序

bulk行集提供程序加载数据可以使用表提示
keepidentity
keepdefaults
ignore_constraints
ignore_triggers
tablock

USE [tempdb]
CREATE TABLE shippers
    (
      shipperid INT NOT NULL
                    PRIMARY KEY ,
      companyname NVARCHAR(40) NOT NULL ,
      phone NVARCHAR(24) NOT NULL
                         CHECK ( phone NOT LIKE '%[^0-9()]%' )
    )

INSERT  INTO [dbo].[shippers] WITH ( IGNORE_CONSTRAINTS )
        SELECT  *
        FROM    OPENROWSET(BULK 'C:\TEMP\SHIPPER.TXT',
                           FORMATFILE= 'C:\TEMP\SHIPPERS.FMT') AS S

INSERT  INTO [dbo].[shippers] WITH ( IGNORE_TRIGGERS )

INSERT  INTO [dbo].[shippers] WITH ( KEEPIDENTITY )

INSERT  INTO [dbo].[shippers] WITH ( KEEPDEFAULTS )

USE [tempdb]
CREATE TABLE student(id INT PRIMARY KEY,name NVARCHAR(200))
CREATE TABLE score(scoreId INT ,level INT)



--外键约束如下:比如student表id位主键,score为子表scoreId为外键
alter table score ADD CONSTRAINT FK_Score_ScoreId foreign key(scoreId) references student(id);

INSERT [dbo].[student]
        ( [id], [name] )
VALUES  ( 1, -- id - int
          N'lily'  -- name - nvarchar(200)
          )

--外键约束 报错
INSERT [dbo].[score]
        ( [scoreId], [level] )
VALUES  ( 2, -- scoreId - int
          2  -- level - int
          )

--方法一
ALTER TABLE [dbo].[score] NOCHECK CONSTRAINT  FK_Score_ScoreId
ALTER TABLE [dbo].[score] NOCHECK CONSTRAINT ALL


--方法二
DELETE FROM [dbo].[score]

ALTER TABLE [dbo].[score] CHECK CONSTRAINT ALL


INSERT [dbo].[score] WITH (IGNORE_CONSTRAINTS)
        ( [scoreId], [level] )
VALUES  ( 2, -- scoreId - int
          2  -- level - int
          )
----消息 8171，级别 16，状态 1，第 1 行
--对象 'dbo.score' 上的提示 'ignore_constraints' 无效。

INSERT [dbo].[student] WITH (IGNORE_CONSTRAINTS)
        ( [id], [name] )
VALUES  ( 2, -- id - int
          N'tom'  -- name - nvarchar(200)
          )
消息 8171，级别 16，状态 1，第 1 行
对象 'dbo.student' 上的提示 'ignore_constraints' 无效。

表提示 IGNORE_CONSTRAINTS不行的

隐式转换

tsql表达式的结果数据类型result datatype是由表达式中具有最高优先级的操作数的数据类型决定的，

在msdn论坛里的case when里返回的数据的数据类型导致不能执行动态sql！！

字符串varchar的优先级比interger低

case表达式的结果数据类型由then子句的结果表达式中具有优先级最高的数据类型决定，不论实际返回到哪个表达式

SELECT  CASE WHEN 1 > 1 THEN 10
             WHEN 1 = 1 THEN 'abc'
             WHEN 1 < 1 THEN 10.1
        END
--消息 8114，级别 16，状态 5，第 1 行
--从数据类型 varchar 转换为 numeric 时出错。

sql2005 技术内幕 TSQL程序设计 P37

有两种方法解决这个问题 CASE 语句的结果表达式问题
1、把索引结果表达式显式转换为都可以转换的通用类型，但是会发生功能错误，例如比较和排序时
2、把所有结果表达式转换为SQL_VARIANT,他是一个通用类型，所有基本类型（base type）都可以在其中保存

SELECT  CASE WHEN 1 > 1 THEN CAST(10 AS SQL_VARIANT)
             WHEN 1 = 1 THEN CAST('abc' AS SQL_VARIANT)
             WHEN 1 < 1 THEN CAST(10.1 AS SQL_VARIANT)
        END

DECIMAL优先级比FLOAT低

SQL_VARIANT数据类型系列和数据类型层次结构
数据类型层次结构（datatype hierarchy）中，数据类型系列（datatype family）具有较高的优先级，他自己就可以确定该值优先级更高，

不管实际值是什么，所以下面的语句返回smaller，

因为float是属于近似数字（approximate numeric）数据类型系列，decimal属于精确数字（exact numeric）数据类型系列
在层次结构中，前者比后者级别更高

筛选表达式

如果表达式两侧的数据类型不同，不同版本的sqlserver的行为是不一样的，sqlserver7.0总是把标量表达式的数据类型转换为列的数据类型，这样该表达式就是一个SARG，允许利用col1上的索引

sqlserver2000改了这行为，标量表达式的数据类型优先级比列高，那么列类型要转换为标量表达式的类型，尽管有些时候优化使用常量扫描constant scan和nested loop运算符以允许使用索引，但是在有join个的情况下，t1.col1=t2.col2，但是如果处于同一个数据类型系列，int和decimal，优化器也不会假设两个索引具有相同的排序行为

这种情况下无法执行merge join

sql2005中收集字符串概要string summary统计信息，用于改进对包含任意通配符的like谓词的基数评估cardinality estimation

他收集字符列中子字符串的频率分布frequency distribution

为什麽关系数据库管理系统并没有强制主键，mysql默认加隐藏主键

对象关系映射工具ORM

使用UDT创建正则表达式

CREATE ASSEMBLY 导入数据库，注册程序集，该命令有一个PERMISSION_SET 子句，默认是safe级别

safe权限执行的程序集代码不能访问外部系统资源，比如文件，网络，环境变量，注册表
external_access允许程序集访问一些外部系统资源
unsafe级别允许不受限制的资源访问，包括sqlserver实例内部和外部的资源，并可调用非托管代码

CREATE ASSEMBLY complenumerCS
FROM 'C:\COMPLEXNUMBERCS\BIN\DEBUG\COMPLENX.DLL'
WITH PERMISSION_SET =SAFE

如果你需要一个支持UDT的聚合函数，必须自己创建他，这个聚合函数是UDA，用户定义聚合函数

accumulate：累积

--修改程序集以添加complex UDA
ALTER ASSEMBLY COMP
FROM ''

--创建聚合函数
CREATE AGGREGATE [dbo].complex_sum(@input compl)
RETURNS comp
EXTERNAL NAME comp

你可以看到，可以开发CLR UDA以支持CLR UDT

使用VB.NET创建UDT和UDA

XML数据类型

使用符合W3C标准的XQuery语言操作XML

XQuery语言包括

查询query方法，

获取值value方法，

检查是否存在exists方法，

修改一部分XML数据modify方法区别于覆盖全部数据，

把xml数据拆分到结果集中的多行nodes方法

Freinventing the wheel 重复发明轮子

使用xml的原因
DDL触发器里的EventInfo函数返回xml格式的事件信息，这样增加新事件将非常方便
还有执行计划set showplan_xml
adjacency list:邻接列表

.net应用程序中的对象支持两种持久化的方法，使用二进制序列化或XML序列化

如果visio文档保存为xml，你甚至可以使用全文索引来搜索visio文档中的数据

向存储过程传递一个数组参数的方法有3个
1、以逗号分隔的字符串形式数组

2、http://www.51cto.com/art/200805/75029.htm

SQL2008表值参数，表值参数(Table-valued parameter)是SQL Server 2008的一个新特性DECLARE @TV AS [OfficeLocation_Tabetype]
INSERT INTO @TV (Id, Shortname, Name) SELECT 12, 'ME1', 'Dubai'
INSERT INTO @TV (Id, Shortname, Name) SELECT 13, 'ME2', 'Tehran'
INSERT INTO @TV (Id, Shortname, Name) SELECT 17, 'EA1', 'Bombay'
INSERT INTO @TV (Id, Shortname, Name) SELECT 18, 'EA2', 'Karachi'
INSERT INTO @TV (Id, Shortname, Name) SELECT 3, 'NA3', 'NewYork'
INSERT INTO @TV (Id, Shortname, Name) SELECT 4, 'EU1', 'London'
exec usp_InsertProdLocation @TV

3、传递XML形式的数组，使用.nodes方法把值保存到关系形式

把XML文档导入到数据表中

SINGLE_BLOB

USE [sss]
CREATE TABLE VisioDocs
(
id INT NOT NULL ,
doc XML NOT NULL
)
INSERT INTO VisioDocs(id,doc)
SELECT 1 ,* 
FROM OPENROWSET(BULK,'C:\PRODUCTORM.VDX',SINGLE_BLOB)AS X

新的bulk行集提供程序加载文件数据的优势非常明显

.VALUE()方法返回一个标量值，所以他可以用于select列表

prolog ：开端；开场白

xml数据类型实际上是一个lob类型，每列数据最大可以达到2GB
你在xml列上创建的第一个索引是主XML索引primary xml index
该索引将分解xml值的持久表示

创建主xml索引后，再创建另外三种类型的辅助xml索引secondary xml index
path

value

property

必须先创建主索引，创建xml索引的表必须具有主键聚集索引！！

USE [sss]

CREATE TABLE VisioDocs
    (
      id INT NOT NULL ,
      doc XML NOT NULL
    )
INSERT INTO VisioDocs(id,doc)
SELECT 1 ,* 
FROM OPENROWSET(BULK,'C:\PRODUCTORM.VDX',SINGLE_BLOB)AS X

--创建聚集主键
ALTER TABLE VisioDocs ADD CONSTRAINT PK_VisioDocs PRIMARY KEY CLUSTERED(ID)

--先创建主xml索引
CREATE PRIMARY XML INDEX idx_xml_primary ON VisioDocs(doc)
--消息 6332，级别 16，状态 201，第 1 行
--表 'VisioDocs' 需要具有一个包含的列数小于 16 的聚集主键，才能为其创建主 XML 索引。
--符合两个要求：聚集，主键
--创建辅助xml path索引
CREATE XML INDEX idx_xml_path ON VisioDocs(doc)
USING XML INDEX idx_xml_primary FOR PATH

DROP INDEX idx_xml_primary ON idx_xml_primary

删除主xml索引，所有的辅助xml索引也将会自动删除

DROP INDEX idx_xml_primary ON idx_xml_primary

--获取架构集合的信息
SELECT  *
FROM    sys.[xml_schema_collections]
WHERE   [name] = 'contranotherarrtibutes'

--获取架构集合中的命名空间信息
SELECT  N.*
FROM    SYS.[xml_schema_namespaces] AS N
        JOIN SYS.[xml_schema_collections] AS C ON N.[xml_collection_id] = C.[xml_collection_id]
WHERE   C.[name] = 'contranotherarrtibutes'

--获取架构集合中的组件信息
SELECT  *
FROM    sys.[xml_schema_components] AS CP
        JOIN SYS.[xml_schema_collections] AS C ON CP.[xml_collection_id] = C.[xml_collection_id]
WHERE   C.[name] = 'contranotherarrtibutes'

约束输入的xml数据的合法性使用xml架构验证，再进一步约束要使用check约束

.NODES方法，当把xml值拆分到关系数据时这个方法非常有用.nodes方法的结果集包含原始xml实例的逻辑副本

.NODES方法返回xml值的副本，你必须用其他方法提取其中的标量值，表中每一行都要调用.nodes方法，那么apply运算符，

cross apply为基表的每一行调用from子句的.nodes方法

使用xml作为存储过程的参数

CREATE PROCEDURE [dbo].GetContacts
@inplist XML
AS 
SELECT C.*
FROM [dbo].Contact AS C
JOIN (SELECT D1.C1.VALUE('(./TEXT())[1]','NVARCHAR(50)')AS NAMENEEDED
FROM @INPLIST.NODES('/NAMES/NAMENEEDED') AS D1(C1)) AS D2
ON C.CONTACTNAME=D2.NAMENEEDED
GO

XQUERY的修改语句
.modify方法与varchar（max）的write方法类似

XQUERY的修改语句
insert
delete
replace value of

XQUERY的方法严格使用小写字母，因为他们是区分大小写的，就像xml中的任何数据一样
value
modify
nodes
path
property

--VisioDocs
UPDATE VisioDocs
SET doc.MODIFY ('')

第二章临时表和表变量

#T1_________________________00000000000001E这是表的内部名称，在代码中你应该引用创建他时使用的名称#T1

SET CONTEXT_INFO
将最多 128 字节的二进制信息与当前会话或连接关联。

http://msdn.microsoft.com/zh-cn/library/ms187768(SQL.90).aspx

DECLARE @BinVar varbinary(128)
SET @BinVar = CAST(REPLICATE( 0x20, 128 ) AS varbinary(128) )
SET CONTEXT_INFO @BinVar

SELECT CONTEXT_INFO() AS MyContextInfo;
GO

一旦超出动态批处理的范围，临时表就不再存在了

临时表的范围scope

跟踪临时表统计信息的基数（唯一值个数）

重编译阀值recompilation threshold

持久表的重编译阀值RT
如果n<=500,则RT=500;n=当编译查询计划时表的基数
如果n>500,则RT=500+0.20*n

临时表的重编译阀值RT：
n<6 则RT=6;
如果6<=n<=500,则RT=500;
如果n>500,则RT=500+0.20*n

优化器不会为表变量创建统计信息！！但是优化器会保存表变量的行计数信息，在sysindexes表和sys.partition表

你可以自己使用recompile提示强制重编译表变量

临时表名称解析 name resolve

proc1

proc2

在两个存储过程中命名相同名字的临时表，并在的存储过程1里调用存储过程2，报错
当存储过程2的临时表被缓存的时候，不会报错，当缓存失效，那么发生重编译的时候，还会继续报错

在动态SQL中修改临时表架构的解决办法

由于全局临时表是所有会话访问，你不能创建多个同名的全局临时表

创建一个不属于任何会话的全局临时表，这时，不论哪个会话打开或关闭，他总是存在，只有显式的删除他才会被移除。
你可以在一个特殊的存储过程中（使用sp_前缀，在master中创建）该存储过程使用startup选项标记该存储过程。sqlserver会在每次启动时调用启动过程startup procedure
而且，sqlserver为在启动过程中创建的全局临时表维护一个大于0的引用计数器，这样就确保sqlserver不会尝试去自动删除他

--创建不会自动删除的全局临时表 
USE [master]
GO
IF OBJECT_ID('dbo.sp_Globals') IS NOT NULL
DROP PROC dbo.sp_Globals
GO

CREATE PROC dbo.sp_Globals
AS
CREATE TABLE ##Globals
(
varname sysname NOT NULL
PRIMARY KEY ,
val SQL_VARIANT NULL
)
EXEC sys.[sp_procoption] @ProcName = N'sp_Globals', -- nvarchar(776)
@OptionName = 'startup', -- varchar(35)
@OptionValue = 'true' -- varchar(12)

##Globals是一个全局共享的临时表，可以维护跨会话的全局变量

表变量的限制条件

表变量只能创建primary key和unique约束
表变量创建后不能改变架构
sql2000不能对表变量执行select into和insert exec
sql2005 支持对表变量执行insert exec
不能使用表变量的别名名称例如select t.a from @tb as t
表变量不使用并行计划

SELECT [TABLE_NAME]
FROM [tempdb].[INFORMATION_SCHEMA].[TABLES]
WHERE [TABLE_NAME] LIKE '%#%'

DECLARE @T TABLE(col1 INT)
INSERT INTO @T VALUES(1)

SELECT [TABLE_NAME]
FROM [tempdb].[INFORMATION_SCHEMA].[TABLES]
WHERE [TABLE_NAME] LIKE '%#%'

#A1E86A5D是临时表名称

表变量范围只能在当前级别的当前批处理内

如果语句是外部事务的一部分，外部事务回滚后，对表变量已经完成的更改将不可撤销，表变量在这一点上是独一无二的

表变量涉及更小的日志操作和锁操作

spool数据，排序，行版本都放在tempdb

最理想的磁盘划分是
数据文件使用RAID10，事务日志使用RAID1

SQL2005的tempdb初始大小是8MB，并且不能改变initial size

《Tempdb initial size和dbcc shrinkfile》

http://www.cnblogs.com/stswordman/p/3358496.html

SQL2008才可以，那么下面这句话是不是有错

改变tempdb数据文件的size参数，当重启sql后，以定义的大小创建tempdb

SQL2012的tempdb初始大小还是8MB

sql重启之后保留tempdb的对象
方法一：在model库里创建
方法二：创建启动存储过程

sqlserver不会物化表表达式

在范围和可见性方面，派生表和CTE只对当前语句有效，而视图和UDF对所有有权限访问他们的用户有效

各种临时对象的比较

临时表

表变量

表表达式

-------------------------------------------------

范围/可见性

是否在tempdb中有物理显示

受外部事务回滚影响

是否支持统计信息/重编译/高效执行计划

表大小

使用XML的path方法会更快，只用了7秒钟

第三章游标

游标和关系模型的主要前提有冲突，使用游标，你可以应用过程逻辑代替基于集合的逻辑

游标可以接受有序数据作为输入，而查询只能接受关系输入

游标不好的地方有两个
1、游标强制优化器执行固定计划，并没有给优化器太多余地，至少没有基于集合的解决方案多
2、逐行操作产生大量开销

使用了FAST_FORWARD游标依然比基于集合的操作慢十倍

cold cache：冷缓存

warm cache：暖缓存

游标对于解决过程性的问题非常有用，这种问题要求你必须单独处理每一行

在测试该解决方案的性能，确保选中SSMS中的“执行后放弃结果选项”

SSMS的alwayson选项

阻止保存要求重新创建表的更改

执行后放弃结果

SQL-92查询标准

SELECT @@ROWCOUNT
SELECT ROWCOUNT_BIG

基于排序数据的游标解决方案也会受益于索引，如果没有索引，这种方案也非常慢
因为扫描数据后还需要对数据排序，因为排序的时间复杂度是0(n log n) ，而扫描仅仅是0(n)

按顺序访问

自定义聚合

解决连续聚合问题的基于集合的解决方案会多次扫描数据，而基于游标的方案只扫描一次数据

SUM(QTY) OVER (PARTITION BY EMPID ORDER BY ORDERMONTH) 实现连接聚合

SELECT  empid ,
        CONVERT(VARCHAR(7), ordmonth, 121) AS ordmonth ,
        qty ,
        SUM(qty) OVER ( PARTITION BY empid ORDER BY ordermonth ) AS runqty
FROM    emporders

between and 和大于小于的区别，大于小于更灵活，>= 或者>
ts>= starttime and st<endtime

匹配问题

第四章动态SQL

通常sp_executesql更具优势，因为他提供了输入/输出接口，而exec没有

下面的代码尝试在括号中调用QUOTENAME函数以引用对象名称
把代码构造到一个变量中，这样就不会受限制了

exec不提供接口

串联变量的内容存在性能方面的弊端，sqlserver将为每个唯一的查询字符串创建新的即席执行计划adhoc execution plan，

即使查询模式相同也是这样，执行计划不能缓存

SELECT  [cacheobjtype] ,
        [objtype] ,
        [usecounts] ,
        [sql]
FROM    sys.[syscacheobjects]
WHERE   [sql] NOT LIKE '%cache%'
        AND [sql] NOT LIKE '%sys.%'

因为exec（）没有输出，那么需要使用insert exec语法把输出插入到一个目的表

临时表在动态sql内部和外部都可以用，但是需要在外部create一下

USE [sss]
DECLARE @tablename AS NVARCHAR(128)
DECLARE @sql AS NVARCHAR(800)
DECLARE @cnt AS INT

SET @tablename = 'testrow'
SET @sql = 'insert into #T(cnt) select count(*) from ' + QUOTENAME(@tablename)

CREATE TABLE #T ( CNT INT )
EXEC(@SQL)
SET @CNT = ( SELECT CNT   FROM   [#T] )
SELECT  @cnt


SELECT  COUNT(*)
FROM    TESTROW

尽管技术上，sp_executesql的输入代码字符串是NTEXT类型
所以sp_executesql中执行的查询字符串被限制为Unicode字符串nvarchar支持的最大长度：4000个字符，

而exec支持常规字符串varchar，允许最大8000个字符

在sql2005中支持varchar（max）和nvarchar（max），支持最大2GB大小
varchar（max）支持二十亿个字符
nvarchar（max）支持十亿个字符

EXEC AT：sql2005引入了EXEC AT语法，允许你在linked server执行动态sql

在sql2000的时候只能使用openquery和openrowset来在linked server执行代码
openquery有很多限制，输入字符串不能是变量，不接受参数

EXEC(
'select productid,productname,unitprice
from products
where productid=?;',3) AT dojo

对access数据库执行transform查询

sp_executesql比exec引入晚一些，他主要为重用执行计划提供更好的支持

SELECT [cacheobjtype] ,
[objtype] ,
[usecounts] ,
[sql]
FROM sys.[syscacheobjects]
WHERE [sql] NOT LIKE '%cache%'
AND [sql] NOT LIKE '%sys.%'
AND [sql] NOT LIKE '%sp_executesql%'

查询执行计划缓存，sqlserver只生成了一个prepared plan，而exec生成了3个adhoc plan

把二进制值转换为字符串的函数

DECLARE @b AS VARBINARY(1000)
SET @b = 0x0123456
SELECT  SYS.[fn_varbintohexstr](@b)

--内部批处理不影响外部批处理的数据库上下文
USE [AdventureWorks]
DECLARE @db AS NVARCHAR(200)
SET @db=QUOTENAME('sss')
EXEC('USE'+@DB+';EXEC(''SELECT DB_NAME()'');')

使用动态sql的场景

sql2005联机丛书中的索引碎片整理的示例代码，在msdn上面也有

碎片级别大于30%重建索引，小于30%重组索引

30%是为了演示而任意选择的，你应该根据查询性能和有效的维护周期使用自己的临界值，维护窗口

使用触发器的inserted表和sp_executesql存储过程可以把方法看起来像excel电子表格

SELECT COLUMNS_UPDATED()

SELECT COLUMNS_UPDATED()函数检查表中的列是否有被修改，该函数是一个位图bitmap，

其中每一位表示一列，对于update语句，如果某列包含在update语句的set子句中，则相应的位为开启状态1，对于insert语句，所有的列都为开启状态

http://www.users.drew.edu/SKASS/SQL/INFIX.SQL.TXT

http://www.users.drew.edu/SKASS/

动态sql 的动态筛选器

动态sql可以获得高效的执行计划，把输入定义为参数，甚至可以高效地重用执行计划

create proc usp_getorders

with recompile

拼接sql，where1=1

SELECT  [cacheobjtype] ,
        [objtype] ,
        [usecounts] ,
        [sql]
FROM    sys.[syscacheobjects]

动态pivot和unpivot，处理数量未知的被旋转元素

--检查 @col是否存在SQL注入尝试
--当示例扩展到用于串联字符串时使用
--
IF UPPER(@col) LIKE UPPER(N'%0x%')
    OR UPPER(@col) LIKE UPPER(N'%;%')
    OR UPPER(@col) LIKE UPPER(N'%''%')
    OR UPPER(@col) LIKE UPPER(N'%--%')
    OR UPPER(@col) LIKE UPPER(N'%/*%*/%')
    OR UPPER(@col) LIKE UPPER(N'%exec%')
    OR UPPER(@col) LIKE UPPER(N'%xp[_]%')
    OR UPPER(@col) LIKE UPPER(N'%sp[_]%')
    OR UPPER(@col) LIKE UPPER(N'%select%')
    OR UPPER(@col) LIKE UPPER(N'%insert%')
    OR UPPER(@col) LIKE UPPER(N'%update%')
    OR UPPER(@col) LIKE UPPER(N'%delete%')
    OR UPPER(@col) LIKE UPPER(N'%truncate%')
    OR UPPER(@col) LIKE UPPER(N'%create%')
    OR UPPER(@col) LIKE UPPER(N'%alter%')
    OR UPPER(@col) LIKE UPPER(N'%drop%')
--其他一些用于SQL注入的字符串
    BEGIN
        RAISERROR('possible sql injection attempt',16,1)
        RETURN
    END

这里使用[INFORMATION_SCHEMA].[COLUMNS]来动态获取表的各个列名

SELECT  *
FROM    [INFORMATION_SCHEMA].[COLUMNS]
WHERE   [TABLE_SCHEMA] = 'dbo'
        AND [TABLE_NAME] = 'pvtcustorders'
        AND [COLUMN_NAME] NOT IN ( 'cusid' )

防止sql注入
1、减少攻击面，不开xp_cmdshell等功能

2、使用足够权限的登录用户连接数据库，不要用高权限，例如sa
在sql2005中，你可以模拟用户，甚至可以在服务器上把新的凭据credential应用于动态调用的代码，于此同时，这也为黑客打开了一个全新的机会之窗，动态sql可以在模拟用户的凭据下运行，甚至不需要该用户有执行存储过程的直接权限

3、使用模式匹配检查输入参数，例如单引号，破折号等

4、限制输入的长度

5、使用存储过程

6、避免使用动态SQL

7、当你引用输入时，不要直接使用，应该使用QUOTENAME()函数，如果想更安全
用CHAR(39)+ CHAR(39)代替CHAR(39)

--SQL注入
DECLARE @lastname AS NVARCHAR(40) 
DECLARE @sql AS NVARCHAR(200)
SET @lastname = N'''drop database sss'
SET @sql = N'select * from employees where lastname=' + QUOTENAME(@lastname,
                                                              '''') + ';'
PRINT @sql
select * from employees where lastname='''drop database sss';

--没有SQL注入
DECLARE @lastname AS NVARCHAR(40) 
DECLARE @sql AS NVARCHAR(200)
SET @lastname = N'lily'
SET @sql = N'select * from employees where lastname=' + QUOTENAME(@lastname,
                                                              '''') + ';'
PRINT @sql
select * from employees where lastname='lily';

第五章视图

1、不能在视图定义中使用order by，除非定义中包含top或for xml说明符
2、所有的结果列必须有名称
3、所有结果列的名称必须是唯一的

EXISTS只关心行是否存在，而不引用行的属性，select 列表可以被完全忽略
下面代码运行时不会有任何错误

IF EXISTS(SELECT 1/0) PRINT 'no error'
no error

视图中的order by

创建视图时，sqlserver会保存元数据信息在系统表中，包括列名，视图定义，安全，依赖等信息
基表的架构更改不影响视图的元数据信息，更改基表架构后，使用EXEC sys.[sp_refreshview] @viewname = N'' -- nvarchar(776)刷新视图的元数据信息是个好习惯

EXEC sys.[sp_refreshview] @viewname = N'' -- nvarchar(776)

使用下面的动态sql刷新没有绑定架构的视图schema-bound

USE [sss]
SELECT N'EXEC sys.sp_refreshview ' + QUOTENAME(VIEW_NAME, '''') + ';' AS CMD
FROM ( SELECT QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) AS VIEW_NAME
FROM [INFORMATION_SCHEMA].[VIEWS]
) AS V
WHERE OBJECTPROPERTY(OBJECT_ID([VIEW_NAME]), 'IsSchemaBound') = 0


--SELECT * FROM [INFORMATION_SCHEMA].[VIEWS]
SELECT OBJECTPROPERTY(OBJECT_ID('ABC'), 'IsSchemaBound')

SELECT SIGN()

SIGN函数在输入为正时返回1，输入为0时返回0，输入为负时返回-1，输入NULL返回NULL

更新视图 update对基表进行

行级安全

Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005

修改视图有下列限制

只要视图有一列不能隐式获取值，就不能向视图插数据

视图是join 的话，修改只能修改一端

不能修改视图的计算列，聚合，标量表达式

如果在创建或修改视图时指定了with check option选项，与视图查询筛选器有冲突的insert或update语句将拒绝

对视图定义了instead of触发器，违反上面这些限制的数据修改语句可以被执行

SELECT  keycol ,
        datacol
FROM    [dbo].userdata
WHERE   loginname = SUSER_NAME()
DENY SELECT ,INSERT,UPDATE,DELETE ON [dbo].userdata TO PUBLIC
GRANT SELECT ,INSERT,UPDATE,DELETE ON [dbo].userdata TO PUBLIC

视图选项

encryption选项
未指定encryption选项，sqlserver会在[sys].[sql_modules](在sql2000中是sys.[syscomments])以纯文本形式保存用于定义对象/程序主体的语句

所以sys.[sp_refreshview] 存储过程内部执行的是sys.sp_refreshsqlmodule_internal

EXEC sys.[sp_refreshview] @viewname = N'' -- nvarchar(776)

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO
create procedure sys.sp_refreshview
@viewname    nvarchar(776)
as
declare @ret int
exec @ret = sys.sp_refreshsqlmodule_internal @viewname, N'OBJECT', 1 -- viewonly
return @ret
GO

schemabinding选项

如果视图已经存在，使用ALTER VIEW命令比删除视图后重建视图更明智，因为ALTER VIEW可以保持权限！！

在子查询中使用了常量1代替*,这样就符合了schemabinding选项的要求

check option

没有check option的话，有join约束也不用怕

有check option的话，insert插入就会失败，一定要两表都要有数据

view_metadata

在sql2000的企业管理器里的视图图形界面修改视图数据，使用sql profiler来跟踪发现企业管理器实际上是对基表进行操作

但是在sql2005里的SSMS则会失败

USE [AdventureWorks]
CREATE VIEW Vcustswithorders
WITH ENCRYPTION,SCHEMABINDING,VIEW_METADATA
AS 
SELECT [Sales].[CustomerAddress]
FROM [Person].[Address] AS c
JOIN b AS b
ON [AddressID].sdf=[c].sdf
WITH CHECK OPTION 
GO

索引视图

SCHEMABINDING选项创建索引视图，必须为对象名称使用两部分命名约定，并在select列表中显式指定列名称

sqlserver企业版和开发版功能一样

如果使用企业版，直接查询基表的语句跟定义索引视图的sql语句一样，那么sqlserver会查询索引视图

如果你使用非SQLSERVER企业版或开发版，默认情况下，即使你直接查询视图，也不会考虑使用索引视图，这时，要使用索引视图，

你必须指定NOEXPAND提示

integrity rule完整性规则

当允许多个null，但是不想允许有重复值的列的时候可以考虑使用索引视图来做约束

为什麽使用索引视图可以减少I/O，首先索引视图就是基表，在定义索引视图的时候，索引视图的列的数量一般会比定义他的基表的列数量要少，

这样的话，那么索引视图的页面一页能容纳更多的数据行，这样的话，读取索引视图的I/O就会比定义他的基本要少！！

第六章用户定义函数

sql2000引入UDF 用户定义函数

内联表值函数跟视图非常相似，唯一不同是他可以接收参数

创建函数时可以指定RETURNS NULL ON NULL INPUT或者CALLED ON NULL INPUT(默认值)
RETURNS NULL ON NULL INPUT：函数的参数为null时返回null
CALLED ON NULL INPUT：无论是否为null都调用函数

创建函数时指定SCHEMABINDING和RETURNS NULL ON NULL INPUT选项是一个好习惯

SCHEMABINDING：防止删除基础对象和修改引用列

当操作的主要成本来自基于集合的操作而不是过程计算时，TSQL-UDF比CLR-UDF快

聚合串联（aggregate concatenation）技术

字符串串联

串联订单ID

在sql2005你可以使用for xml path，并用一个空字符串作为其输入来完成字符串串联

UDA：用户定义聚合函数

sql2000中扩展了tsql的语法，将查询结果作为xml输出
for xml raw
for xml auto
for xml explicit

sql2005 for xml改进
利用type选项，for xml可以从使用 for xml的select语句中输出，他还允许将select...for xml
的结果嵌套在另一个select语句中
利用新选项for xml path 可以更容易整理数据，生成基于元素的xml

xpath 测试函数
data
comment
node
text
处理指令

精通SQL Server2008程序设计笔记

SET NOCOUNT ON
SELECT CustomerID,
(SELECT CAST(OrderID AS VARCHAR(10))+';' AS [text()]
FROM dbo.Orders AS O
WHERE O.CustomerID=C.CustomerID
ORDER BY OrderID
FOR XML PATH('')) AS Orders
FROM dbo.Customers AS C

在SSMS中启用“执行后放弃结果”选项，这样你的统计结果将不包含生成输出所花费的时间

USE [sss]
--开启执行查询后放弃结果
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
SELECT  *
FROM    [dbo].[aaa]
SET STATISTICS TIME OFF



 --SQL Server 执行时间:
 --  CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。

--不开启执行查询后放弃结果
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
SELECT  *
FROM    [dbo].[aaa]
SET STATISTICS TIME OFF

 --SQL Server 执行时间:
 --  CPU 时间 = 0 毫秒，占用时间 = 4 毫秒。

内联表达式

如果你在查询中使用内联表达式实现计算并避免使用UDF，通常会好些

在约束中使用UDF

索引准则（indexing guideline）
在计算列上创建UDF约束，那么在创建UDF时需要使用SCHEMABINDING,计算列必须具有确定性和精确的，并且要指定持久的

CREATE TABLE a
(id AS MIN(1) PERSISTED)

后续添加计算列

--添加计算列
ALTER TABLE [dbo].[aaa] ADD col1 AS MIN([a]) CONSTRAINT UQ_AAA_COL1 UNIQUE

ALTER TABLE [T1]
ADD COL2 AS FN_ADD(KEYCOL) PERSISTED NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY

persisted选项是sql2005中新增的，在sql2000中，要在计算列上创建主键约束，你必须使用isnull函数封装对UDF的调用

ALTER TABLE [T1]
ADD COL2 AS ISNULL(FN_ADD(KEYCOL),0) 
CONSTRAINT PK_T1 PRIMARY KEY

TSQL不擅长处理：复杂计算，遍历，过程逻辑，字符串处理，操作系统资源访问等，交由CLR来处理

TSQL是一种声明式语言declarative language

CLR开发正则表达式

具有确定性

计算列不具有确定性

http://support.microsoft.com/kb/2380361

开启CLR选项意味着整个SQL实例都可以运行CLR程序集，这会有风险
在create assembly的时候指定permission_set，选项设置为safe，external_access，unsafe

safe：默认
external_access：以sqlserver服务账户身份运行并访问此账户的外部资源
unsafe：在sqlserver进程空间中执行任意操作，会破坏clr和sqlserver的安全系统
只有sysadmin角色才能修改unsafe程序集

如果你修改了程序集，你需要执行alter assembly或drop，create assembly命令来重新把IL代码加载到数据库

注册CLR函数

你可以在网上找到更多的正则表达式：http://www.regexlib.com/

正则表达式大全网站

检查E-mail地址是否有效

写出更健壮正则表达式的网站

http://www.regular-expressions.info/

匹配jpg文件的正则表达式，包含在创建表的check约束里面

显式转换隐式转换
.NET 本机类型（native type）和.NET SQL类型

建议在CLR对象的.NET代码中使用.NET 的SQL类型而不要使用本机类型
sql类型SqlString
本机类型string

显式转换和隐式转换的性能差别不大

C#里面sqlstring和string的比较

sql 签名 signature

sql签名的tsql实现是sqlserver cat的成员 stuart ozer

http://www.microsoft.com/about/legal/en/us/Copyright/Default.aspx

C#代码的数据类型sql类型不是本机类型与TSQL代码的数据类型的对应

存入数据库
            myCommand.Parameters.Add("@VC_A_SNNO", SqlDbType.VarChar).Value = VC_A_SNNO;
            myCommand.Parameters.Add("@VC_OC_UserName", SqlDbType.VarChar).Value = VC_OC_UserName;
            myCommand.Parameters.Add("@VC_OC_Company", SqlDbType.VarChar).Value = VC_OC_Company;
            myCommand.Parameters.Add("@VC_A_CardNO", SqlDbType.VarChar).Value = VC_A_CardNO;
            myCommand.Parameters.Add("@CardType", SqlDbType.Int).Value = CardType;
            myCommand.Parameters.Add("@VC_A_AppendType", SqlDbType.VarChar).Value = VC_A_AppendType;
            myCommand.Parameters.Add("@VC_TicketType", SqlDbType.VarChar).Value = VC_TicketType;
            myCommand.Parameters.Add("@VC_TicketNO", SqlDbType.VarChar).Value = VC_TicketNO;
            myCommand.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = StartDate;
            myCommand.Parameters.Add("@EndDate", SqlDbType.VarChar).Value = EndDate;
            myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Value = TotalRecords;
            myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;

            myConnection.Open();
            PageSetInfo _CT_AppendSetInfo = new PageSetInfo();



从数据库取出
     private CT_AppendInfo Populate(SqlDataReader dr)
        {
            CT_AppendInfo _CT_AppendInfo = new CT_AppendInfo();

            _CT_AppendInfo.VC_A_SNNO = Convert.ToString(dr["VC_A_SNNO"]);
            _CT_AppendInfo.VC_A_AppendType = Convert.ToString(dr["VC_A_AppendType"]);
            _CT_AppendInfo.VC_A_CardNO = Convert.ToString(dr["VC_A_CardNO"]);
            _CT_AppendInfo.I_A_CardType = Convert.ToInt32(dr["I_A_CardType"]);
            _CT_AppendInfo.I_A_CardTypeName = Filter.CardTypeToStr(_CT_AppendInfo.I_A_CardType);
            _CT_AppendInfo.I_A_PointToOil = Convert.ToInt32(dr["I_A_PointToOil"]);
            _CT_AppendInfo.VC_TicketType = Convert.ToString(dr["VC_TicketType"]);
            _CT_AppendInfo.VC_TicketNO = Convert.ToString(dr["VC_TicketNO"]);
            _CT_AppendInfo.DE_A_BAmount = Convert.ToDecimal(dr["DE_A_BAmount"]);
            _CT_AppendInfo.DE_A_AppendAmount = Convert.ToDecimal(dr["DE_A_AppendAmount"]);
            _CT_AppendInfo.DE_A_AAmount = Convert.ToDecimal(dr["DE_A_AAmount"]);
            _CT_AppendInfo.D_A_AppendDateTime = Convert.ToDateTime(dr["D_A_AppendDateTime"]);
            _CT_AppendInfo.VC_A_Remark = Convert.ToString(dr["VC_A_Remark"]);
            _CT_AppendInfo.VC_A_OperatorNO = Convert.ToString(dr["VC_A_OperatorNO"]);

            return _CT_AppendInfo;
        }

在字符串处理方面，.NET代码比TSQL代码更快，尤其是两个版本实现的是同一个算法，你马上就能看到他们在字符串处理方面的性能差异

clr版本比T-SQL版本快100倍

clr函数

CREATE FUNCTION fn_RegExReplace(@input AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME xxx.fn_RegExReplace

你可能想创建一些有广泛用途的函数
你可以使用同义词功能和model数据库的功能

在每个数据库中创建一个该函数的同义词，就可以调用该函数，而不需要在每个库中创建该函数

USE [AdventureWorks2008]
CREATE SYNONYM [dbo].fn_RegExReplace
FOR clutit.dbo.fn_RegExReplace

在model数据库中创建该函数，就可以在新建数据库和tempdb中定义好该函数

表值UDF
内联表值 UDF INLINE TABLE-VALUED UDF
多语句表值UDF MULTISTATEMENT TABLE-VALUED UDF
CLR表值UDF

内联表值UDF跟视图是很相似的，但是内联表值UDF可以输入参数

内联表值udf也是展开udf的定义并生成一个访问基表的执行计划
不同于标量udf和多语句表值udf，不能在内联udf里使用begin/end块

CREATE FUNCTION [dbo].fn_GetCusorders ( @cid AS NCHAR(5) )
RETURNS TABLE
AS 
RETURN
SELECT *
FROM [Department]
GO

可以修改内联UDF，最终修改的还是基表

UPDATE fn_GetCusorder(N'alfki') SET shipvia=2

当然你有权限的话，也可以执行删除操作

DELETE FROM fn_GetCusorder(N'alfki') WHERE YEAR(orderdata)=1997

拆分数组 split array

使用这个拆分函数来拆分字符串，可以缓存执行计划，而用动态sql的话不会缓存执行计划

函数头把FillRowMethodName属性设置为"ArrSplitFillRow"
ArrSplitFillRow是一个方法，他把输入的对象转换为字符串，函数头还用TableDefinitin属性定义了输出表的架构，只在使用VS自动部署该函数时才需要该属性，如果你用TSQL手工部署这个函数，不需要指定这个属性

stringsplitoptions.removeemptyentries选项，使返回值不包含为空字符串的数字元素

CLR版的字符串拆分函数比TSQL版的字符串拆分函数快1倍！

多语句表值UDF是一种返回表变量的函数

sqlserver对内联udf像视图，对多语句表值udf像存储过程，你不能对多语句表值udf进行update，delete

多语句表值UDF

sql2000版本

CREATE FUNCTION [dbo].fn_subordinates ( @mgrid AS INT )
RETURNS @subs TABLE
    (
      empid INT NOT NULL
                PRIMARY KEY NONCLUSTERED ,
      mgrid INT NULL ,
      empname VARCHAR(255) NOT NULL ,
      salary MONEY NOT NULL ,
      lvl INT NOT NULL ,
      UNIQUE CLUSTERED ( lvl, empid )
    )
AS
    BEGIN
        DECLARE @lvl AS INT;
        SET @lvl = 0
    END
RETURN;

sql2005版本

CREATE FUNCTION [dbo].fn_subordinates ( @mgrid AS INT )
RETURNS  TABLE
AS
RETURN
WITH subsCTE AS (XX)
SELECT * FROM subsCTE
GO;

cte体中的第二个查询包含一个指向自身的递归引用

递归调用最好用CTE，不断获取下层

逐行调用udf

不确定性函数，当用相同的参数多次调用他时，他不保证返回相同的输出
例如 rand() getdate（）函数

rand() getdate（）函数这些不确定性函数在查询中只被调用一次，而不是每行都调用，唯一的例外是newid（）函数

USE [sss]

SELECT  RAND() AS rnd ,
        GETDATE() AS dt ,
        NEWID() AS guid ,
        [name]
FROM    [dbo].[aaa]

如果想每行都调用rand函数可以使用udf

可以使用偏方，创建一个视图，该视图调用rand（）函数，然后在udf中查询该视图

sql2000不允许在udf中调用不确定函数
sql2005检查udf的属性以区分他是否会产生副作用，sql2005更宽容一些

在sql2000中你不能在udf中调用getdate函数
sql2005中可以在udf中调用getdate函数

第七章存储过程

存储过程允许有副作用（side effect）也就是收他可以修改表中数据和修改对象架构

用户定义存储过程

调用存储过程最好加架构名前缀
解析顺序
当前库sys架构
调用方默认架构
当前库的dbo架构

如果存储过程未指定架构名称，这些连接会因为要获取编译锁（compile lock）而互相阻塞

sql2005 技术内幕存储引擎笔记

编译锁
support.microsoft.com/?id=263889

SELECT [resource_type] FROM sys.[dm_tran_locks]
SR （subresource）
如果锁存在于一个数据库资源上，SR表示下列之一
完全数据库级锁
容量操作锁
如果锁存在于表资源上，SR表示下列之一
完全表级锁（默认）
更新统计锁
编译锁
如果锁存在于索引资源上，SR表示下列之一：
完全索引锁（默认）
索引ID锁
索引名字锁

只能使用存储过程，而不能直接查询表数据能控制权限

DENY SELECT ON [dbo].[aaa] TO [db_accessadmin]
GRANT EXECUTE ON [dbo].[usp_GetFreeSpace] TO user1

安全模型

使用分号是一个好习惯，分号为所有语句添加后缀，避免歧义

微软强烈建议不要使用SP_前缀创建你的自己的存储过程！！

特殊存储过程sp_开头

以sp_开头的表名，即使切换数据库，也可用找到该表，但是在sql2012里测试已经不行了

USE [sss]
CREATE TABLE dbo.sp_Globs
    (
      var_name sysname NOT NULL
                       PRIMARY KEY ,
      val SQL_VARIANT NULL
    )
INSERT dbo.sp_Globs
SELECT 'nih','1'

USE [tempdb]
SELECT * FROM dbo.sp_Globs

--消息 208，级别 16，状态 1，第 2 行
--对象名 'dbo.sp_Globs' 无效。


USE [AdventureWorks]
SELECT * FROM dbo.sp_Globs

--消息 208，级别 16，状态 1，第 2 行
--对象名 'dbo.sp_Globs' 无效。

系统存储过程

系统存储过程是微软内置在产品中的存储过程，在sql2000中，系统存储过程位于master数据库，以sp_开头，并标记为“system”（MS Shipped）
在sql2005中，系统存储过程在物理上位于一个内部隐藏的资源数据库resource库中，逻辑上存在于每个数据库中

SELECT [is_ms_shipped] FROM sys.[objects]
SELECT [is_ms_shipped] FROM sys.[triggers]
SELECT [is_ms_shipped] FROM sys.[procedures]

特殊存储过程（使用sp_前缀，在master中创建）也被标记为系统存储过程，并具备一些独特的行为，当sqlserver的安装程序运行安装脚本以创建系统存储过程时，安装脚本使用未公开的存储过程sp_MS_marksystemobject把这些特殊存储过程标记为系统存储过程

不应该在产品环境中使用sp_MS_marksystemobject存储过程把用户存储过程标记为系统存储过程

在sql 2008r2安装包里找不到sp_MS_marksystemobject存储过程的脚本

--sql2005里面直接报没有sp_MS_marksystemobject文本
USE [master]
EXEC sys.[sp_helptext] @objname = N'sp_MS_marksystemobject'
--消息 15197，级别 16，状态 1，过程 sp_helptext，第 91 行
--没有对象 'sp_MS_marksystemobject' 的文本。

--sql2012里面不能够使用ctrl+T的方式显示文本，只能用网格显示sp_MS_marksystemobject存储过程文本
--然后拷到新建查询窗口里，分析语法和用sql prompt
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO

--执行想要的xp_cmdshell语句
Exec xp_cmdshell 'query user'
GO


USE [sss]
CREATE TABLE tbtxt(txt NVARCHAR(MAX))

INSERT INTO tbtxt  EXEC sys.[sp_helptext] @objname = N'sp_MS_marksystemobject'

SELECT [txt] FROM [tbtxt]

exec master.. xp_cmdshell 'bcp " select txt from [sss].[dbo].[tbtxt] " queryout d:\sp_MS_marksystemobject.txt -c -U "sa" -P "ttttttt"'

sp_MS_marksystemobject存储过程内容

---------------------------- sp_MS_marksystemobject -----------------------------                                                    
-- FOR INTERNAL USE ONLY ... DO NOT DOCUMENT --                                                    
--#NAME?                                                    
----    groups (starfigther, davinci, replication) use it for different things                                                
--#NAME?                                                    
--    not have this bit set.  Use at your own risk.                                                
--                                                       
                                                    
CREATE PROCEDURE sys.sp_MS_marksystemobject
    @objname NVARCHAR(517) ,  -- 517 is max for two part name                                                
    @namespace VARCHAR(10) = NULL        --#NAME?                                    
AS
    SET NOCOUNT ON                                                    
                                                    
    DECLARE @objid INT                                            
                                                    
    DECLARE @owner sysname ,
        @ns INT ,            --#NAME?                    
        @object sysname                                    
                                        
    -- ============================================================                                                
    -- DEVNOTE:                                                
    --    x_eonc_TrgOnServer        = 20,    // Namespace for Triggers on the Server                                
    --    x_eonc_TrgOnDatabase    = 21,    // Namespace for Triggers on Databases                                    
                                                    
    --#NAME?            0        // Standard Namespace                            
    -- ============================================================                                                
                                        
    IF NOT ( @namespace IS NULL )
        BEGIN                                                
                                                    
            SELECT  @ns = ( CASE LOWER(@namespace)
                              WHEN 'server' THEN 20
                              WHEN 'database' THEN 21
                            END )                                    
        END                                                
    ELSE
        BEGIN                                                
            SET @ns = 0 -- null means standard namespace                                            
        END                                                
                                        
                                                    
    IF @ns IS NULL
        BEGIN                                            
            RAISERROR('sp_MS_marksystemobject: Invalid NameSpace ''%ls''',0,1,@namespace)                                                RETURN (1)                                            
        END                                                
                                    
    -- CHECK THE OBJECT NAME --                                                    
                                                    
    SELECT  @object = PARSENAME(@objname, 1) ,
            @owner = PARSENAME(@objname, 2)                                        
                                                
    -- ============================================================                                                
    -- DEVNOTE:                                                
    --    x_eonc_TrgOnServer        = 20,    // Namespace for Triggers on the Server                                
    --    x_eonc_TrgOnDatabase        = 21,    // Namespace for Triggers on Databases                                
    -- ============================================================                                                
                                                    
    IF @ns = 20
        OR @ns = 21
        BEGIN                                                
            IF NOT ( @owner IS NULL )
                BEGIN                                            
            --#NAME?                                        
                    RAISERROR(1094,-1,-1)                                        
                    RETURN (1)                                        
                END                                            
        END                                                
                                        
                                                    
    IF @ns = 20
        BEGIN                                                
                                                    
            SELECT  @objid = object_id
            FROM    sys.server_triggers
            WHERE   name = @objname                                            
        END                                                
    ELSE
        IF @ns = 21
            BEGIN                                                
                                                    
                SELECT  @objid = object_id
                FROM    sys.triggers
                WHERE   name = @objname
                        AND parent_class = 0                                            
            END                                                
                                                    
        ELSE
            BEGIN                                                
                SELECT  @objid = OBJECT_ID(@objname, 'local')                                            
            END                                                
                                            
                                                    
    -- CHECK THE OBJECT OWNER (MUST BE DBO) --                                                    
    -- DO THE UPDATE --                                                    
                                                    
    BEGIN TRAN                                                
    IF NOT ( @objid IS NULL )
        BEGIN                                                
            IF @ns = 0
                BEGIN                                            
            EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)                                        IF @@error <> 0
                        SELECT  @objid = NULL                                    
                    ELSE
                        BEGIN                                        
                            IF @owner IS NULL
                                SELECT  @owner = N'dbo'                                
                                                
                EXEC %%Object( Owner = @owner, Name = @object ).SetSystem()                                    
                                                
                        END                                        
                END                                            
                                                    
            ELSE
                BEGIN                                            
                                                    
            EXEC %%TriggerEx(MultiName = @objname).ValidateAndSetSystem(                                        
                                                    
                                                    ID = @objid, NameSpaceClass = @ns)
                END                                            
                                                    
        END                                                
                                                
    IF @objid IS NULL
        BEGIN                                                
            RAISERROR('sp_MS_marksystemobject: Invalid object name ''%ls''',0,1,@objname)                                                                        
            COMMIT TRAN                                            
            RETURN (1)                                            
        END                                                
                                                    
                                                    
    COMMIT TRAN                                                
    RETURN (0)    --#NAME?

其他类型存储过程

1、临时存储过程#或##
2、扩展存储过程：允许使用C语言利用开放数据库服务open data service ODS API创建外部程序
sql2005支持扩展存储过程只是为了向后兼容，在以后sql版本中将不再支持，现在你可以使用CLR存储过程

存储过程接口，即输入，输出参数

SET NOCOUNT ON

SET NOCOUNT ON选项，一些数据库接口，例如OLEDB，会把该消息作为一个行集，你得到的第一个结果集将是关于受影响行数的消息，而你只想获取查询的结果集

存储过程的参数：命名格式和非命名格式

为存储过程的参数指定默认值

CREATE PROC usp_getaaa
(
@name AS NVARCHAR(20) = 'sdf969'
)
AS
SET NOCOUNT ON
SELECT *
FROM [dbo].[aaa]
WHERE [name] = @name
GO

EXEC usp_getaaa

或者

使用默认值

EXEC usp_getaaa DEFAULT

使用命名赋值格式是一个好习惯

EXEC usp_getaaa @name='sdf969'

存储过程的输出参数跟C语言的指针和C# ref类型一样从输入一直到输出

ADO编程逐个接收结果集，使用recordset对象的nextrecordset属性
而print和raiserror命令的输出，他们都由客户端通过接口结构接收，例如，ADO中的ERRORS集合

ADO.NET中使用SqlCommand对象，CommandType.StoredProcedure

ADO.NET中使用SqlCommand对象，CommandType.StoredProcedure
ADO.NET中的SqlDataReader类的NextResult方法遍历返回的行集
SqlCommand对象的Parameters集合接收输出参数

SqlDataReader类的RecordsAffected属性，用于获取更新，插入，删除的行数，如果是select语句，不能使用该属性

严重级别大于10的错误，他们表示非常严重的错误

SqlExceptoin对象
SqlErrors对象

SqlErrors对象的number表示错误号，message表示错误消息

使用SqlConnection对象的InfoMessage事件获取存储过程中的警告

TSQL 中的print语句：可以使用SqlConnection对象的InfoMessage事件获取这类输出，或者使用try catch中catch块读取输出

DBCC输出：dbcc tableresults选项，使用SqlDataReader对象读取输出，就像读取行集一样
或者使用使用SqlConnection对象的InfoMessage事件获取输出

XML输出：SqlCommand对象的ExecuteXmlReader方法或者SqlDataReader对象

用户定义数据类型UDTs：ADO.NET完全支持UDT，

利用SqlDataReader获取行集架构：使用SqlDataReader的GetSchemaTable方法后去表架构

存储过程的解析阶段

如果对象根本不存在，sqlserver却可以创建存储过程，并且把解析过程推迟到调用存储过程时再执行，这种技术叫延迟名称解析（deferred name resolution）

没有对象名的时候，创建存储过程不会检查对象名和对象的列名，存在对象的时候创建存储过程会检查对象名和对象的列名

创建存储过程usp_Proc2，引用已存在的T1表不存在的col2列，解析过程不会被推迟到运行时执行，因为T1表已经存在，但存储过程无法被创建

sqlserver通过计算查询优化阀值（threshold for optimization）来限制优化器只生成适合数量的执行计划供选项

优化器使用基数（cardinality）和密度（density）信息评估他要使用的访问方法的成本

在一个大型表里面，索引的级别可能有三，四级

一共是10次逻辑读2（seek）+2*4（lookups）=10

流程逻辑（flow logic）

修改存储过程，使用RECOMPILE，避免执行计划的不理想

USE [sss]
GO

ALTER procedure [dbo].[sp_MSupd_dboaaa] 
@c1 int = null,@c2 nvarchar(50) = null,@pkc1 int
,@bitmap binary(1)
WITH RECOMPILE
as
begin

end

当指定RECOMPILE 你找不到缓存的执行计划

SELECT * FROM sys.[syscacheobjects]
WHERE [sql] NOT LIKE '%cache%'
AND [sql] LIKE '%sp_MSupd_dboaaa%'

sql2000的话，使用下面语句

SELECT * FROM [master].[dbo].[syscacheobjects]
WHERE [sql] NOT LIKE '%cache%'
AND [sql] LIKE '%sp_MSupd_dboaaa%'

--sql2005支持语句级重编译 ,那么在要重编译的那个语句指定OPTION(RECOMPILE)就可以了
ALTER procedure [dbo].[sp_MSupd_dboaaa] 
 @c1 int = null,@c2 nvarchar(50) = null,@pkc1 int
,@bitmap binary(1)
as
begin
update [dbo].[aaa] set 
 [name] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [name] end
OPTION(RECOMPILE)
END


--sql2000不支持语句级重编译 ,只能在存储过程定义添加WITH RECOMPILE，这样会影响存储过程里面的所有语句！！
ALTER procedure [dbo].[sp_MSupd_dboaaa] 
 @c1 int = null,@c2 nvarchar(50) = null,@pkc1 int
,@bitmap binary(1)
WITH RECOMPILE
as
begin

end

重编译

《Microsoft SQL Server 2005技术内幕查询、调整和优化笔记》

KEEP PLAN：正确性

KEEPFIXED PLAN：最佳性

SET CONCAT_NULL_YIELDS_NULL ON：表示当你用任意字符串和NULL串联时，得到的结果都为null

除了执行计划，sqlserver还会缓存查询结果的set选项

SELECT [setopts] FROM sys.[syscacheobjects]

当建立数据库连接时，客户端接口工具通常会更改一些set选项的状态，不同的客户端接口更改不同的set选项，

形成不同的执行环境，如果使用多个数据库接口工具连接数据库，而且他们拥有不同环境，你将无法重用其他应用程序的计划！！

参数嗅探

优化器无法“嗅探”出变量的内容，因此，优化器必须要猜测变量的值

解决参数嗅探的方法一:使用内联表达式代替变量，该表达式引用输入参数，而不是变量

解决参数嗅探的方法二:使用根存储过程（stub procedure），也就是创建两个存储过程，第一个存储过程

sql2005为你提供了一个新的查询提示OPTIMIZE FOR，该提示允许你为sqlserver提供一个字面值，

用于表示具有代表性的变量值，如果你知道变量通常是一个高选择性的值，可以提供一个字符串'9999991231'

USE [sss]
GO
/****** Object:  StoredProcedure [dbo].[usp_getaaa]    Script Date: 2014/9/18 7:24:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_getaaa]
    (
      @name AS NVARCHAR(20) = 'sdf969'
    )
AS
    SET NOCOUNT ON
    SELECT  *
    FROM    [dbo].[aaa]
    WHERE   [name] = @name
    OPTION(OPTIMIZE FOR(@name='sdf969'))


EXEC [usp_getaaa]

execute as

存储过程和基对象属于同一个架构
操作是静态的（不是有动态sql）
操作语句是dml（select，insert，update，delete）语句，或者执行另一个存储过程

sql2005可以吧存储过程安全上下文设置为其他用户的上下文，

就像其他用户在运行存储过程一样，创建存储过程时，你可以指定下列选项之一的execute as子句

1、caller（默认值）调用者的安全上下文
2、self创建或修改存储过程用户的安全上下文
3、owner数据库所有者的安全上下文
4、‘username’指定用户名称的安全上下文

此外，拥有模拟权限的用户可以提交一个独立的execute as<选项>命令以模拟另一个实体（登录用户或数据库用户）。

执行后，就好像当前会话的安全上下文被更改为所模拟实体的安全上下文

参数化排序

sqlserver更加表达式的结果中优先级最高的数据类型确定case表达式结果的数据类型，而不是按实际返回结果的数据类型确定，这就意味着，如果case表达式的一个then子句返回varchar（30），而另一个返回int值，表达式的结果将总是int，因为int的优先级比varchar高，如果实际返回varchar（30）值sqlserver将尝试转换他，如果该值不可转换，将产生一个运行时错误，如果可以转换，他被转换为int型，当然，可能会导致和原始值不一样的排序行为

解决方案一：把返回值都转换为SQL_VARIANT

解决方案二：使用case when

解决方案三：使用动态sql

如果未验证输入可能会产生所有权链（ownership chaining）和sql注入问题

解决方案四：每种可能的排序方法都创建一个静态查询存储过程

动态Pivot

PIVOT运算符不支持*作为聚合函数的输入，所以把count（*）改为count（1）

sql注入动态pivot

--检查输入的列是否存在

USE [sss]
IF COLUMNPROPERTY(OBJECT_ID('testrow'),'id','ColumnId') IS NOT NULL 
PRINT 'not null'

SELECT OBJECT_ID('testrow')
SELECT COLUMNPROPERTY(OBJECT_ID('testrow'),'id','ColumnId')

防范sql注入的几种措施
1、限制输入参数大小
2、只接收数据库存在的对象
3、代码引用列名称和对象时使用QUOTENAME()，并用方括号作为分隔标识符
4、检查变量，使用like语句匹配

创建视图获取别的库的数据，这样相对安全

要允许EXTERNAL_ACCESS和UNSAFE程序集运行，你需要把数据库选项TRUSTWORTHY设置为ON

ALTER DATABASE [sss] SET TRUSTWORTHY ON

TRUSTWORTHY 数据库属性用于指明 SQL Server 的实例是否信任该数据库以及其中的内容。默认情况下，除 msdb 数据库之外的所有系统数据库都将 TRUSTWORTHY 设置为 OFF。对于 model 和 tempdb 数据库，不能更改此值。
1. 可以通过ALTER DATABASE语句来修改数据库的TRUSTWORTHY属性（注意：必须是 sysadmin 固定服务器角色的成员才能设置此选项。）
ALTER DATABASE databasename SET TRUSTWORTHY ON
ALTER DATABASE databasename SET TRUSTWORTHY OFF
2.若要查看数据库TRUSTWORTHY属性的状态，可以查看sys.databases视图的is_trustworthy_on列。
查看具体数据库的TRUSTWORTHY属性：
select is_trustworthy_on from sys.databases where name=databasename
查看当前实例中所有数据库的TRUSTWORTHY属性：
select is_trustworthy_on,name from sys.databases
 
参考资料：
http://msdn.microsoft.com/en-us/library/ms187861.aspx

一个更为安全的解决方案是使用强名称钥密钥文件（strong-named key file）为程序集签名或使用证书为授权码（authenticode）签名，

强名称或证书是sqlserver内部创建的非对称密钥或证书，对应一个拥有EXTERNAL_ACCESS assembly权限或UNSAFE assembly权限的登录

USE [master]
GO
---从已签名的程序创建非对称密钥
--注意：必须使用强名称密钥文件为程序集签名
CREATE ASYMMETRIC KEY CLRutilitikey
FROM EXECUTABLE FILE='C:\CLUSIT\CLRUTILITIES.DLL'
--创建登录并授予其external_access权限
CREATE LOGIN clrutilitylogin FROM ASYMMETRIC KEY CLRutilitikey
GRANT EXTERNAL ACCESS ASSEMBLY TO clrutilitylogin

程序集安全的更详细信息：http://msdn.microsoft.com/en-us/library/ms345106.aspx

所有的.net程序集都包含元数据，用于描述在程序集中定义的所有类型（类和结构），包括所有public方法和属性，.net中system.reflection命名空间的一些类和接口提供已加载类型的管理视图（managed view）.NET 反射(http://www.cnblogs.com/yaozhenfa/p/CSharp_Reflection_1.html)

使用Reflector for .NET反编译.NET程序集 www.aisto.com/roeder/dotnet

(http://www.red-gate.com/products/dotnet-development/reflector/)被redgate收购

http://blogs.msdn.com/b/sqlclr/archive/2005/11/21/495438.aspx
提供了一个CLR DDL触发器，这个触发器在执行CREATE ASSEMBLY语句时触发，他会自动地注册程序集中的所有CLR对象，包括UDT,UDA,UDF,SP和触发器

We use reflection to look through the assembly. ：我使用反射去查找程序集

CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY

CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY

对此进行分级
较差较差凑合凑合平均平均良好良好优秀优秀 Miles Trochesset Tue, Nov 22 2005 12:43 AM 1
This code is provided "AS IS" with no warranties, and confers no rights.

I've made some small modifications to comply with standard naming conventions on 11/30/2005.

Save the following into AutoRegisterTrigger.cs

--------------------------------------------------------

using System;
using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Xml;
using System.Reflection;
using System.Text.RegularExpressions;

namespace Microsoft.SqlServer.Sample

{

    public class AutoRegister
    {
        [SqlTrigger(Name = "AutoRegister", Event = "FOR CREATE_ASSEMBLY", Target = "DATABASE")]
        public static void AutoRegisterTrigger()
        {
            // Checks that we are executing this trigger after a create assembly
            SqlTriggerContext triggContext = SqlContext.TriggerContext;
            if (triggContext.TriggerAction != TriggerAction.CreateAssembly)
            {
                throw new Exception("This trigger is only meaningful for CREATE ASSEMBLY.");
            }

            // Retrieves the name of the assembly being registered
            String asmName = null;
            XmlReader eventreader = triggContext.EventData.CreateReader();
            while (eventreader.Read())
            {
                if (eventreader.Name.Equals("ObjectName"))
                {
                    asmName = eventreader.ReadElementContentAsString();
                    break;
                }
            }
            if (asmName == null)
            {
                throw new Exception("Could not find the assembly name.");
            }

            // Retrieve the clr name of the assembly
            String ClrName = null;
            using (SqlConnection myConnection = new SqlConnection("Context connection = true"))
            {
                myConnection.Open();
                using (SqlCommand myCommand = new SqlCommand())
                {
                    myCommand.Connection = myConnection;
                    myCommand.CommandText = "SELECT clr_name FROM sys.assemblies WHERE name = @AsmName";
                    myCommand.Parameters.Add("@AsmName", SqlDbType.NVarChar);
                    myCommand.Parameters[0].Value = asmName;
                    ClrName = (String)myCommand.ExecuteScalar();
                }
            }

            // Load the assembly from SQL database to memory
            Assembly a = Assembly.Load(ClrName);

            // Iterating over types in assembly
            Type[] types = a.GetTypes();
            foreach (Type type in types)
            {

                try
                {
                    // Iterating over custom attributes of type
                    Attribute[] Type_attributes = Attribute.GetCustomAttributes(type);
                    foreach (Attribute att in Type_attributes)
                    {
                        if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute"))
                        {
                            RegisterUDT(asmName, type, (SqlUserDefinedTypeAttribute)att);
                        }
                        else if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlUserDefinedAggregateAttribute"))
                        {
                            RegisterUDA(asmName, type, (SqlUserDefinedAggregateAttribute)att);
                        }
                    }
                }
                catch (AutoRegisterException e)
                {
                    SqlContext.Pipe.Send("[" + asmName + "].[" + type.Name + "] : " + e.Message);
                }

                // Iterate over public static methods
                MethodInfo[] methodInfos = type.GetMethods();
                foreach (MethodInfo methodInfo in methodInfos)
                {
                    if (methodInfo.IsPublic && methodInfo.IsStatic)
                    {
                        try
                        {
                            Object[] Method_attributes = methodInfo.GetCustomAttributes(false);
                            foreach (Attribute att in Method_attributes)
                            {
                                if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlProcedureAttribute"))
                                    RegisterProcedure(asmName, type, (SqlProcedureAttribute)att, methodInfo);
                                if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlTriggerAttribute"))
                                    RegisterTrigger(asmName, type, (SqlTriggerAttribute)att, methodInfo);
                                if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlFunctionAttribute"))
                                    RegisterUDF(asmName, type, (SqlFunctionAttribute)att, methodInfo);
                            }

                        }
                        catch (AutoRegisterException e)
                        {
                            SqlContext.Pipe.Send("[" + asmName + "].[" + type.Name + "].[" + methodInfo.Name + "] : " + e.Message);
                        }
                    }
                }
            }
        }

        private static void RegisterUDT(String asmName, Type type, SqlUserDefinedTypeAttribute sqlUserDefinedTypeAttribute)
        {
            String Name = sqlUserDefinedTypeAttribute.Name;
            if (Name == null) Name = type.Name;

            // Check that the name is valid
            if (!IsValid(Name))
                throw new InvalidNameException(Name);

            // Check if the object is already registered
            if (IsRegistered(Name))
                throw new ObjectRegisteredException(Name);

            String CommandString = "CREATE TYPE [" + Name + "] EXTERNAL NAME [" + asmName + "].[";
            if (type.Namespace != null) CommandString += type.Namespace + ".";
            CommandString += type.Name + "]";

            Register(CommandString);
        }

        private static void RegisterUDA(String asmName, Type type, SqlUserDefinedAggregateAttribute sqlUserDefinedAggregateAttribute)
        {
            String Name = sqlUserDefinedAggregateAttribute.Name;
            if (Name == null) Name = type.Name;

            // Check that the name is valid
            if (!IsValid(Name))
                throw new InvalidNameException(Name);

            // Check if the object is already registered
            if (IsRegistered(Name))
                throw new ObjectRegisteredException(Name);

            String CommandString = "CREATE AGGREGATE [" + Name + "]";
            String CommandString_return = "";
            bool hasAccumulateMethod = false;

            // Iterate over public methods to find parameter and return type
            MethodInfo[] methodInfos = type.GetMethods();
            foreach (MethodInfo methodInfo in methodInfos)
            {
                if (methodInfo.Name.Equals("Accumulate"))
                {
                    // Add parameter
                    ParameterInfo[] pis = methodInfo.GetParameters();
                    if (pis.Length != 1)
                        throw new InvalidNumberOfArgumentsException();

                    ParameterInfo pi = pis[0];
                    char[] refchar = { '&' }; // double check that this is a unicode array
                    String param_type;

                    // if parameter is passed by reference as in (@i INT OUTPUT) display an error
                    if (pi.ParameterType.ToString().LastIndexOfAny(refchar) != -1)
                        throw new InvalidArgumentByReferenceException(pi.ParameterType.ToString());

                    param_type = NETtoSQLmap(pi.ParameterType.ToString());
                    if (param_type == null)
                        throw new InvalidTypeException(pi.ParameterType.ToString());

                    // Check the validity of the parameter strings (we construct param_type no need to validate it)
                    if (!IsValid(pi.Name))
                        throw new InvalidNameException(pi.Name);

                    CommandString += " (@" + pi.Name + " " + param_type + ")";
                    hasAccumulateMethod = true;
                }
                else if (methodInfo.Name.Equals("Terminate"))
                {
                    String return_type = NETtoSQLmap(methodInfo.ReturnType.ToString());
                    if (return_type == null)
                        throw new InvalidReturnTypeException(methodInfo.ReturnType.ToString());
                    CommandString_return = " RETURNS " + return_type + " "; // we construct return_type no need to validate it
                }
            }

            if (CommandString_return.Equals(""))
                throw new MissingTerminateMethodException();

            if (!hasAccumulateMethod)
                throw new MissingAccumulateMethodException();

            CommandString += CommandString_return;
            CommandString += "EXTERNAL NAME [" + asmName + "].[";
            if (type.Namespace != null) CommandString += type.Namespace + ".";
            CommandString += type.Name + "]";

            Register(CommandString);
        }

        private static void RegisterProcedure(String asmName, Type type, SqlProcedureAttribute sqlProcedureAttribute, MethodInfo methodInfo)
        {
            String Name = sqlProcedureAttribute.Name;
            if (Name == null) Name = methodInfo.Name;

            // Check that the name is valid
            if (!IsValid(Name))
                throw new InvalidNameException(Name);

            // Check if the object is already registered
            if (IsRegistered(Name))
                throw new ObjectRegisteredException(Name);

            String CommandString = "CREATE PROCEDURE [" + Name + "]";
            CommandString += GetParameterString(methodInfo);
            CommandString += " AS EXTERNAL NAME [" + asmName + "].[";
            if (type.Namespace != null) CommandString += type.Namespace + ".";
            CommandString += type.Name + "].[" + methodInfo.Name + "]";

            Register(CommandString);
        }

        private static void RegisterTrigger(String asmName, Type type, SqlTriggerAttribute sqlTriggerAttribute, MethodInfo methodInfo)
        {
            String Name = sqlTriggerAttribute.Name;
            if (Name == null) Name = methodInfo.Name;

            // Check that the name is valid
            if (!IsValid(Name))
                throw new InvalidNameException(Name);

            // Check if the object is already registered
            if (IsRegistered(Name))
                throw new ObjectRegisteredException(Name);

            // Check that the target is valid
            String Target = sqlTriggerAttribute.Target;
            if (Target == null)
                throw new NoTargetException();
            if (!IsValid(sqlTriggerAttribute.Target))
                throw new InvalidNameException(sqlTriggerAttribute.Target);

            // Check that the event is valid
            String Tr_event = sqlTriggerAttribute.Event;
            if (Tr_event == null)
                throw new NoEventException();
            if (!IsValid(sqlTriggerAttribute.Event))
                throw new InvalidNameException(sqlTriggerAttribute.Event);

            String CommandString = "CREATE TRIGGER [" + Name + "] ";
            CommandString += "ON " + Target + " WITH EXECUTE AS CALLER ";
            CommandString += Tr_event + " AS EXTERNAL NAME [" + asmName + "].[";
            if (type.Namespace != null) CommandString += type.Namespace + ".";
            CommandString += type.Name + "].[" + methodInfo.Name + "]";

            Register(CommandString);
        }

        private static void RegisterUDF(String asmName, Type type, SqlFunctionAttribute sqlFunctionAttribute, MethodInfo methodInfo)
        {
            String Name = sqlFunctionAttribute.Name;
            if (Name == null) Name = methodInfo.Name;

            // Check that the name is valid
            if (!IsValid(Name))
                throw new InvalidNameException(Name);

            // Check if the object is already registered
            if (IsRegistered(Name))
                throw new ObjectRegisteredException(Name);

            String CommandString = "CREATE FUNCTION [" + Name + "]";
            CommandString += GetParameterString(methodInfo);
            String return_type = NETtoSQLmap(methodInfo.ReturnType.ToString());
            if (return_type == null)
                throw new InvalidReturnTypeException(methodInfo.ReturnType.ToString());

            CommandString += "RETURNS " + return_type + " ";
            CommandString += "AS EXTERNAL NAME [" + asmName + "].[";
            if (type.Namespace != null) CommandString += type.Namespace + ".";
            CommandString += type.Name + "].[" + methodInfo.Name + "]";

            Register(CommandString);
        }

        // could we use a solution similar to Xiaowei's question to make this more robust
        private static String NETtoSQLmap(String typeName)
        {
            switch (typeName)
            {
                case "System.Data.SqlTypes.SqlBoolean": return "bit";
                case "System.Data.SqlTypes.SqlBinary": return "varbinary"; // other choices: binary, image, timestamp,
                case "System.Data.SqlTypes.SqlByte": return "tinyint";
                case "System.Data.SqlTypes.SqlChars": return "nvarchar"; //other choices: char, nchar, text, ntext, varchar
                case "System.Data.SqlTypes.SqlDateTime": return "smalldatetime"; // other choice: datetime
                case "System.Data.SqlTypes.SqlDecimal": return "decimal";
                case "System.Data.SqlTypes.SqlDouble": return "float";
                case "System.Data.SqlTypes.SqlGuid": return "uniqueidentifier";
                case "System.Data.SqlTypes.SqlInt16": return "smallint";
                case "System.Data.SqlTypes.SqlInt32": return "int";
                case "System.Data.SqlTypes.SqlInt64": return "bigint";
                case "System.Data.SqlTypes.SqlMoney": return "money"; // other choice smallmoney
                case "System.Data.SqlTypes.SqlSingle": return "real";
                case "System.Data.SqlTypes.SqlString": return "nvarchar"; //other choices: char, nchar, text, ntext, varchar
                case "System.Data.SqlTypes.SqlXml": return "xml";
                default:
                    {
                        switch (typeName.ToLower())
                        {
                            case "short": return "smallint";
                            case "int": return "int";
                            case "long": return "bigint";
                            case "bool": return "bit";
                            case "boolean": return "bit";
                            case "float": return "float";
                            case "single": return "single";
                            default: return null;
                        }
                    }
            }
        }

        // Check if the object is already registered, return true if it is, false otherwise
        private static bool IsRegistered(String name)
        {
            bool b;

            using (SqlConnection myConnection = new SqlConnection("context connection = true"))
            {
                myConnection.Open();
                using (SqlCommand myCommand = new SqlCommand())
                {
                    myCommand.Connection = myConnection;
                    myCommand.CommandText = "SELECT * FROM sys.objects WHERE name = @Name";
                    myCommand.Parameters.Add("@Name", SqlDbType.NVarChar);
                    myCommand.Parameters[0].Value = name;
                    if (myCommand.ExecuteScalar() != null) b = true;
                    else b = false;
                }
                return b;
            }

        }

        private static void Register(String commandString)
        {
            using (SqlConnection myConnection = new SqlConnection("context connection = true"))
            {
                myConnection.Open();
                using (SqlCommand comm = new SqlCommand(commandString, myConnection))
                {
                    try
                    {
                        comm.ExecuteNonQuery();
                        SqlContext.Pipe.Send("AutoRegister: " + commandString);
                    }
                    catch (SqlException e)
                    {
                        SqlContext.Pipe.Send(e.Message);
                    }
                }
            }
        }

        /* We assume namespace, class and method names are valid strings for
         * TSQL statements (alphanumeric or underscore only) since the
         * compiler accepted them and so is the assembly name since
         * it was provided by the DBA */
        private static bool IsValid(String s)
        {
            return Regex.IsMatch(s, "[^_.[:alnum:]]");
        }

        private static String GetParameterString(MethodInfo methodInfo)
        {
            String s_params = "";

            // Add parameters
            ParameterInfo[] pis = methodInfo.GetParameters();
            bool empty = true;
            char[] refchar = { '&' }; // double check that this is a unicode array
            String param_type;
            foreach (ParameterInfo pi in pis)
            {
                // add OUTPUT if parameter is passed by reference as in (@i INT OUTPUT)
                int ix = pi.ParameterType.ToString().LastIndexOfAny(refchar);
                if (ix != -1)
                {
                    param_type = NETtoSQLmap(pi.ParameterType.ToString().Substring(0, ix));
                    if (param_type == null)
                        throw new InvalidTypeException(pi.ParameterType.ToString().Substring(0, ix));
                    param_type += " OUTPUT";
                }
                else
                {
                    param_type = NETtoSQLmap(pi.ParameterType.ToString());
                    if (param_type == null)
                        throw new InvalidTypeException(pi.ParameterType.ToString());
                }

                // Check the validity of the parameter strings (we construct param_type no need to validate it)
                if (!IsValid(pi.Name))
                    throw new InvalidNameException(pi.Name);

                if (empty)
                {
                    s_params += " (@" + pi.Name + " " + param_type;
                    empty = false;
                }
                else
                    s_params += ", @" + pi.Name + " " + param_type;
            }
            if (!empty)
                s_params += ") ";

            return s_params;
        }
    }

    internal class AutoRegisterException : ApplicationException
    {
        internal AutoRegisterException(String Message)
            : base(Message + " UDX not registered.")
        {
        }
    }

    internal class InvalidTypeException : AutoRegisterException
    {
        internal InvalidTypeException(String sType)
            : base("Argument type " + sType + " cannot be mapped to a SQL Type.")
        {
        }

    }

    internal class InvalidReturnTypeException : AutoRegisterException
    {

        internal InvalidReturnTypeException(String sType)
            : base("Return type " + sType + " cannot be mapped to a SQL Type.")
        {
        }
    }

    internal class ObjectRegisteredException : AutoRegisterException
    {
        internal ObjectRegisteredException(String Name)
            : base(Name + " is already registered.")
        {
        }
    }

    internal class InvalidNameException : AutoRegisterException
    {
        internal InvalidNameException(String Name)
            : base(Name + " is not a valid name.")
        {
        }
    }

    internal class NoEventException : AutoRegisterException
    {
        internal NoEventException()
            : base("Missing the 'Event' argument for the SqlTriggerAttribute constructor.")
        {
        }
    }

    internal class NoTargetException : AutoRegisterException
    {
        internal NoTargetException()
            : base("Missing the 'Target' argument for the SqlTriggerAttribute constructor.")
        {
        }
    }

    internal class InvalidNumberOfArgumentsException : AutoRegisterException
    {
        internal InvalidNumberOfArgumentsException()
            : base("Wrong number of arguments.")
        {
        }
    }

    internal class InvalidArgumentByReferenceException : AutoRegisterException
    {
        internal InvalidArgumentByReferenceException(String Argument)
            : base("Expected argument " + Argument + "passed by value, passed by reference instead.")
        {
        }
    }

    internal class MissingTerminateMethodException : AutoRegisterException
    {
        internal MissingTerminateMethodException()
            : base("The user defined aggregate is missing a 'Terminate' method.")
        {
        }
    }

    internal class MissingAccumulateMethodException : AutoRegisterException
    {
        internal MissingAccumulateMethodException()
            : base("The user defined aggregate is missing an 'Accumulate' method.")
        {
        }
    }
}
-------------------------------------

then run the following TSQL batch
-------------------------------------

CREATE ASSEMBLY AutoRegisterAsm
FROM -- path to your dll goes here
WITH PERMISSION_SET = SAFE
GO

CREATE TRIGGER AutoRegisterTrigger
ON DATABASE
FOR CREATE_ASSEMBLY
AS EXTERNAL NAME AutoRegisterAsm.[Microsoft.SqlServer.Sample.AutoRegister].AutoRegisterTrigger
GO

ENABLE TRIGGER AutoRegisterTrigger ON DATABASE
GO
- Miles Trochesset, Microsoft SQL Server

View Code

GetMethods方法获取所有public方法

利用反射还可以获取程序集的其他元数据信息

反射和正则表达式命名空间

using System.Reflection;
using System.Text.RegularExpressions;

第八章触发器

dml触发器：after触发，instead of触发器

sql2005不支持select触发器，行级（row level）触发器和before触发器，instead of触发器和before触发器最接近

after触发器

dml after触发器只能在持久表上建立，不能在视图和临时表上建立

表上的约束违反了，那么该表的触发器不会执行

after触发器按语句触发，不是按行触发

你可以对对象的每种语句crud建立多个after触发器，如果触发，他们将一个接一个执行，你可以使用
EXEC sys.[sp_settriggerorder]存储过程标记一个触发器最先执行还是最后执行

EXEC sys.[sp_settriggerorder] @triggername = N'', -- nvarchar(517)
@order = '', -- varchar(10)
@stmttype = '', -- varchar(50)
@namespace = '' -- varchar(10)

inserted表和deleted表都没有索引！

用RAID10把日志放在多个磁盘上是个好主意

把inserted表或deleted表的数据放到临时表，并在临时表上建立索引会使表扫描快一些，因为
inserted表和deleted表都没有索引

CREATE TRIGGER tri_abc ON [testrow]
FOR UPDATE
AS
SELECT *
INTO #I
FROM [Inserted]
CREATE UNIQUE CLUSTERED INDEX IDX_TESTROW ON [#I](KEYCOL)

多行修改会出现逻辑错误

用畅亮导数据那种方法来获取下一行

使用游标的方法操作触发器的inserted表和使用临时表的方法速度比较

识别触发器类型，建立update insert，delete触发器

CREATE TRIGGER tri_abc ON [testrow]
    FOR INSERT, UPDATE, DELETE
AS
    DECLARE @rc AS INT
    SET @rc = @@ROWCOUNT;

    IF @rc = 0
        BEGIN
            PRINT 'no rows affected'
            RETURN;
        END

    IF EXISTS ( SELECT  *
                FROM    [Inserted] )
        BEGIN 
            IF EXISTS ( SELECT  *
                        FROM    [Deleted] )
                BEGIN 
                    PRINT 'update identified'
                END
            ELSE
                BEGIN
                    PRINT 'insert identified'
                END
        END
    ELSE
        BEGIN 
            PRINT 'delete identified'
        END

优化器支持短路功能（short-circuiting）只要找到一行，不需要再扫描其他行就可以确定该表是否包含行了

在sql2000中，如果你使用count(*)聚合检查是否存在行，IF (SELECT COUNT(*) FROM [testrow])>0，这个方法成本很高，

优化器通常会扫描该表上的最窄索引（narrowest index）的叶级，也就是说最好的方法是使用EXIST，sql2005中IF (SELECT COUNT(*) FROM [testrow])>0会使用短路，

sql2005一般会生成跟EXIST一样的执行计划

--扫描最窄索引（narrowest index）来执行SELECT COUNT(*) 
SELECT COUNT(*) FROM [testrow]

禁用触发器

ALTER TABLE [testrow] DISABLE TRIGGER ALL

对特殊语句不引发触发器

SELECT OBJECT_ID()要确保对象名称限定数据库，如果连接另一个数据库没有使用tempdb前缀，将总是返回null

应该不是获取当前会话的上下文信息

SELECT CONTEXT_INFO()

[context_info]会话上下文是sql2005新增的，数据类型是varbinary(128)

SELECT [context_info] FROM sys.[sysprocesses] WHERE [spid]=xx

你可以利用会话的上下文信息在代码的不同级别中通讯，我们的例子中是调用批处理和触发器之间通讯，你可以把上下文信息看作是一个全局会话变量

设置CONTEXT_INFO()信号阻止触发器

触发器嵌套和递归

最大32层递归限制

递归需要终止检查（termination check）以防止递归一直运行

ALTER DATABASE [sss] SET RECURSIVE_TRIGGERS ON

update（）函数和columns_updated（）函数

如果你像检查empid列是否被修改，执行

IF UPDATE(empid)

COLUMNS_UPDATED（）函数返回位图和自己的位掩码bitmask

SELECT SUBSTRING(COLUMNS_UPDATED(),(@i-1)/8+1,1)

动态sql创建一个100列的表

识别被修改的列，根据列位置和[INFORMATION_SCHEMA].[COLUMNS]表做join

当触发器回滚，那么inserted表和deleted表也会回滚，这时候你可以使用第二章的表变量保存inserted表和deleted表的内容，绕过22条军规

instead of 触发器
原语句永远到达不了目标对象，原语句会由触发器代码替换他，instead of触发器跟before触发器不一样

你需要修改自己的代码，然后重新提交原始操作，inserted和deleted保存的是假设要更改的数据不是实际更改的数据

你可以作弄的你同事，在表上创建一个insert，update，delete的instead of触发器，在触发器中不执行任何操作，当人对表执行修改时候总是发现数据没有变化，他会非常郁闷

不像after触发器，instead of触发器不仅可以在表创建还可以在视图上建立

每个对象只能创建一个这样的触发器，INSTEAD OF触发器不会递归触发

--after触发器
CREATE TRIGGER tri_abc ON [testrow]
FOR INSERT,UPDATE,DELETE
AS


--INSTEAD OF触发器
CREATE TRIGGER tri_abc ON [testrow]
INSTEAD OF DELETE 
AS

逐行触发器：游标

INSTEAD OF触发器应用于视图

CREATE TRIGGER trg_VorderTotals_ioi ON [testrow]
    INSTEAD OF UPDATE
AS
    IF @@ROWCOUNT = 0
        RETURN
    IF UPDATE(oid)
        BEGIN 
            RAISERROR('update to the orderid colunm are not allowed',16,1)
            ROLLBACK TRAN
            RETURN
        END

update函数和columns_updated函数一般只用在触发器代码里面

自动处理序列

--使用 INSTEAD OF触发器实现自增序列
USE [sss]
CREATE TABLE [dbo].Sequence(val INT NOT NULL)
INSERT INTO Sequence VALUES(0)



--假设val=5
UPDATE [Sequence] SET [val]=5


CREATE TRIGGER trg_t1_assign_key ON [T1] INSTEAD OF INSERT
AS
DECLARE @rc AS INT
DECLARE @key AS INT
SET @rc=@@ROWCOUNT
IF @rc=0 RETURN
--比如插入3行，那么@rc=3 update从左到右 @key=val=5，val=val+@rc=8，即@key=5，val=8

--更新序列
UPDATE Sequence SET @key=val,val=val+@rc

INSERT INTO T1(KEYCOL,DATACOL)
SELECT @KEY+ROW_NUMBER()OVER (ORDER BY CONST) ,DATACOL
FROM (SELECT 1 AS CONST,DATACOL FROM INSERTED) AS I

INSERT INTO T1(KEYCOL,DATACOL)
SELECT 6,DATACOL
SELECT 7,DATACOL
SELECT 8,DATACOL

--CONST这里随便就可以了，使用ROW_NUMBER()OVER主要是为了获得从5开始的增长，增长3 ，由于插入3行，触发3次，那么
--第一次 5+1
--第二次 5+2
--第三次 5+3
SELECT @KEY+ROW_NUMBER()OVER (ORDER BY CONST) ,DATACOL
FROM (SELECT 1 AS CONST,DATACOL FROM INSERTED) AS I

DDL触发器，sqlserver仅支持after ddl触发器

通过eventdata函数获取引发触发器的事件信息

使用XQuery表达式获取eventdata数据：

xml_value.value('(<path_to_element>)[1]',<sql_type>)

<path_to_element>是你要提取的属性路径

CREATE TRIGGER trg_create_table_with_pk ON DATABASE FOR  CREATE_TABLE
AS

使用OBJECTPROPERTY()判断表是否包含主键

数据库级触发器

审核DDL事件，创建AuditDDLEvents表和trg_audit_ddl_event触发器

创建ddl触发器监控ddl事件

USE [sss]
CREATE TABLE AuditDDLEvents
    (
      lsn INT NOT NULL
              IDENTITY ,
      posttime DATETIME NOT NULL ,
      eventtype sysname NOT NULL ,
      loginname sysname NOT NULL ,
      schemaname sysname NOT NULL ,
      objectname sysname NOT NULL ,
      targetobjecdtname sysname NOT NULL ,
      eventdata XML NOT NULL ,
      CONSTRAINT pk_auditddlevents PRIMARY KEY ( lsn )
    )


CREATE TRIGGER trg_audit_ddl_events ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
AS
    DECLARE @eventdata AS XML
    SET @eventdata = EVENTDATA()

    INSERT  INTO AuditDDLEvents
            ( [posttime] ,
              [eventtype] ,
              [loginname] ,
              [schemaname] ,
              [objectname] ,
              [targetobjecdtname] ,
              [eventdata]
            )
    VALUES  ( @eventdata.value('(/event_instance/posttime)[1]', 'varchar(23)') ,
              @eventdata.value('(/event_instance/eventtype)[1]', 'sysname') ,
              @eventdata.value('(/event_instance/loginname)[1]', 'sysname') ,
              @eventdata.value('(/event_instance/schemaname)[1]', 'sysname') ,
              @eventdata.value('(/event_instance/objectname)[1]', 'sysname') ,
              @eventdata.value('(/event_instance/targetobjecdtname)[1]',
                               'sysname') ,
              @eventdata
            )

查询过去24小时对数据库所做的DDL

SELECT  [posttime] ,
        [eventtype] ,
        [loginname] ,
        [eventdata].value('(/event_instance/tsqlcommand/commandtext)[1]',
                          'nvarchar(max)')
FROM    [AuditDDLEvents]
WHERE   [posttime] > GETDATE() - 1
ORDER BY [posttime]

服务器级触发器

CREATE TRIGGER trg_audit_ddl_events ON ALL SERVER
    FOR DDL_LOGIN_EVENTS
AS
    DECLARE @eventdata AS XML
    SET @eventdata = EVENTDATA()

    INSERT  INTO AuditDDLEvents
            ( [posttime] ,
              [eventtype] ,
              [loginname] ,
              [schemaname] ,
              [objectname] ,
              [targetobjecdtname] ,
              [eventdata]
            )
    VALUES  ( @eventdata.value('(/event_instance/posttime)[1]', 'varchar(23)') ,
              @eventdata.value('(/event_instance/eventtype)[1]', 'sysname') ,
              @eventdata.value('(/event_instance/loginname)[1]', 'sysname') ,
              @eventdata.value('(/event_instance/schemaname)[1]', 'sysname') ,
              @eventdata.value('(/event_instance/objectname)[1]', 'sysname') ,
              @eventdata.value('(/event_instance/targetobjecdtname)[1]',
                               'sysname') ,
              @eventdata
            )

服务器触发器还是数据库触发器，都会有安全问题，例如尝试创建一个名为[l<gin]的登录名，你会发现显示在审核表中的登录名是l<gin

CLR触发器

使用触发器发送Email邮件是一个糟糕的示例，因为触发器是事务的一部分，在事务期间资源会被锁定，根据执行的操作和隔离级别，锁可以被一直保持到事务结束

记住，触发器是事务的一部分，你要对触发器里面的代码非常谨慎，确保他执行得尽可能的快！！

通过RAISERROR记录到日志的错误信息被限制为440个字节

总之尽量避免使用CLR编写触发器！！

sqlserver中的CLR代码总是在进程帐户的上下文中被调用，如果你想以调用用户的标识调用执行sqlserver外部操作的CLR代码，必须通过SqlContext对象的WindowsIdentity属性获取一个模拟标记（impersonation token）模拟令牌。WindowsIdentity属性返回一个WindowsIdentity对象的实例。他表示调用者的Windows（OS）标识（如果客户端使用sqlserver身份认证者为空）
要访问该属性，必须把程序集标记为EXTERNAL_ACCESS或UNSAFE权限集

unsafe权限集还可以调用非托管代码！！

visual studio自动部署CLR程序集，CLR触发器

注册C#版触发器

CREATE TRIGGER trg_T1_iud_GenericDMLAudit
ON [dbo].T1 FOR INSERT,UPDATE,DELETE
AS 
EXTERNAL NAME CUL.CLU.TRG_GENERIC

在sql2000中，只有在redo和undo两个操作都已完成之后，数据库才变为可用状态，而在sql2005，redo阶段完成后数据库立即可用

sqlserver在失败时并不是都可以自动回滚事务，例如，默认情况下，违反约束或锁超时到期会让事务保持打开状态。

你可以编写错误处理代码以决定显式回滚该事务还是提交该事务。如果你想让所有错误都可以回滚事务，

把XACT_ABORT会话选项设置为ON，当使用错误处理时，XACT_ABORT会话选项会有不同的结果

锁

最小的数据粒度级别是行级！！

也就是说，如果一个进程保持某资源任何模式的锁，其他进程将无法获取该资源的排他锁

因此该模式被称为排他锁

当sqlserver编译批处理查询的时候，使用架构稳定锁锁住表架构

SELECT * FROM [dbo].[testrow] WITH(XLOCK,UPDLOCK)

查询 sys.[dm_exec_connections]连接视图得到发生冲突的连接的信息

SELECT  *
FROM    sys.[dm_exec_connections]
WHERE   [session_id] IN ( 63, 67 )

在sql2000中术语连接和会话的含义是一样的，然而，在sql2005中支持多个活动结果集MARS,

一个连接可以包含多个异步运行的活动会话，因此，sql2005把他们放到两个视图中

sys.[dm_exec_connections]：用于连接
sys.[dm_exec_sessions]：用于会话

MARS里面，会话只有一个，但是会话里面有多个连接

sys.[dm_exec_requests]

在sql2000中，你可以使用DBCC INPUTBUFFER(87)和sys.[fn_get_sql]和 sys.[dm_exec_connections] 做join获得会话代码

--系统进程的sql代码是看不到的
DBCC INPUTBUFFER(20)

--用户进程可以看到代码
DBCC INPUTBUFFER(87)

SELECT [session_id] ,
[text] ,
[most_recent_sql_handle]
FROM sys.[dm_exec_connections]
CROSS APPLY sys.[fn_get_sql]([most_recent_sql_handle])

在sql2005中，使用

SELECT [session_id] ,
[text] ,
[most_recent_sql_handle]
FROM sys.[dm_exec_connections]
CROSS APPLY sys.[dm_exec_sql_text]([most_recent_sql_handle]) AS ST
WHERE [session_id] IN ( 67, 68 )

--设置锁超时为3秒
SET LOCK_TIMEOUT 3000
SELECT * FROM [dbo].[testrow]

设置锁永远不超时，设置lock_timeout为-1

--设置锁超时
SET LOCK_TIMEOUT -1

达到5000个锁，sqlserver会提升为表锁，如果锁升级失败，这时每增加1250个锁，sqlserver会再次进行锁升级（lock escalation）

在数据库仓库中，在ETL的时候，可以把数据库设置为READ_WRITE,ETL完毕之后，把数据库设置为READ_ONLY,这样可以减少开销，得到更高性能

halloween protect

脏读
更新丢失
不可重复读
幻读：

幻读：当在同一个事务中，delete where xx=xx 和select where xx=xx
where 筛选器是相同的时候，就有可能产生幻读

隔离级别

sqlserver设置的会话级别和查询级别设置隔离级别

--会话级别
SET TRAN ISOLATION LEVEL READ COMMITTED


--查询级别
SELECT * FROM [dbo].[testrow] WITH(READUNCOMMITTED)
SELECT * FROM [dbo].[testrow] WITH(READCOMMITTED)
SELECT * FROM [dbo].[testrow] WITH(REPEATABLEREAD)
SELECT * FROM [dbo].[testrow] WITH(SERIALIZABLE)

WITH(READUNCOMMITTED)和WITH(NOLOCK)等价

sql2000中只能在select语句使用
sql2005中可以在查询和修改语句中使用(READPAST)

SELECT * FROM [dbo].[testrow] WITH(READPAST)

UPDATE [dbo].[testrow] WITH (READPAST) SET [id]= 1

已提交读：使用spool防止重复读，使用键范围锁防止幻读

可重复读

可重复读：共享锁一直保持到事务被终止，更新丢失不会在可重复读级别上发生，但是容易造成死锁

可重复读：还是会发生幻读

捕获死锁，重新提交事务：牺牲的进程会得到错误1205，他可以重新提交该事务！！

可串行读：加键范围锁（要有索引），防止幻读

mysql：gap锁

新的隔离级别

快照和已提交读隔离级别

连接链表linked list

使用快照隔离级别，读数据时不申请共享锁，而且永远不会与修改数据的进程发生冲突

快照隔离级别：影响范围事务

SELECT * FROM sys.[dm_tran_version_store]

快照链接列表清理进程大概每分钟运行一次，他从链接列表中移除不需要的行版本，但是他只移除链接列表中链表尾部附近的部分（最老的版本），

这意味着长时间运行的事务可能会阻止清理最旧版本之前的中间版本，即使这些版本不再需要，这种清理机制跟事务日志的记录清理机制类似

快照隔离级别防止更新冲突：你可能会在同一事务中访问数据并根据访问到的数据执行计算，如果你在第一次读取数据以及尝试修改他之间有另外一个进程修改该数据，sqlserver将检测到这种更新冲突并中断事务，如果需要，你可以重新提交事务，这时他将依赖数据的最新状态

快照隔离级别并不适合修改操作频繁的环境，这种情况下可能产生更多更新冲突，他更适合主要读环境

已提交读隔离级别不检测更新冲突

已提交读的行版本不会轻易地增长得很长，因为行版本可以很快地从链接列表中被移除

已提交读隔离级别使用WITH(READCOMMITTED)提示可以使语句处于已提交读隔离级别，从而申请共享锁！！

保存点

mysql 的保存点：SAVEPOINT

http://imysql.com/2014/09/18/mysql-faq-a-savepoint-identifier-bug.shtml

sqlserver保存点：SAVE TRAN

sqlserver不支持真正意义上的嵌套事务，当你在事务内提交一个rollback tran，sqlserver回滚最外层begin tran之后的所有操作，

如果你在已存在的事务内提交一个begin tran语句，并不会打开新的事务，sqlserver只是增加内部计数器，通过@@TRANCOUNT函数可以查询该计数器。

commit tran语句会把计数器减一，只有最外层的commit tran才会真正提交事务，并把计数器减为0，sqlserver把通过begin tran语句打开的事务级数限制为32
sqlserver支持保存点，他允许你撤销事务中的部分操作，为此，你需要执行save tran<保存点名>语句用于标记一个保存点，

然后执行rollback tran<保存点名>以撤销保存点以后执行的操作

在上一章使用触发器自己做自增序列，在触发器生成自增序列期间，锁会一直保持到事务结束

不像表变量，identity列的递增不是外部事务的一部分！！

向只有一个自增列插入数据

USE [sss]

CREATE TABLE identitydefaulttest(id INT IDENTITY)

INSERT INTO identitydefaulttest DEFAULT VALUES
GO 10

SELECT * FROM identitydefaulttest

死锁

SET DEADLOCK_PRIORITY HIGH

重试事务，处理死锁

跟踪标志1204，1222启动sqlserver
http://msdn.microsoft.com/en-us/library/ms178104(sql90).aspx

lock:deadlock chain 每个死锁链中的进程都产生该事件，他允许你标识死锁进程的进程id并关注他们在跟踪中的操作

deadlock graph 这是sql2005新增的事件，他生成包含死锁信息的xml值

添加索引防止死锁

第十章错误处理

sql2000提供的最主要的错误处理手段只有一个@@error方法

sql2000中处理不确定性函数比较麻烦

DECLARE @err INT
DECLARE @rc INT
SET @err=@@ERROR
SET @rc=@@ROWCOUNT
PRINT @err
PRINT @rc
0
--1 ：正确应该是1，因为PRINT @err、SET @err=@@ERROR

sql2000中的错误处理还有其他一些局限，除了你可以从@@error方法获取错误号外，没有任何其他可用信息，没有消息，没有严重级别，没有状态

参观mvp网站，主要关注sqlserver的错误处理

http://www.sommarskog.se/

sql2005中的错误处理

除了安全级别为20或更高级别的错误（如硬件错误）不能捕获，其他所有错误都可以捕获

--新的错误函数代替@@函数
SELECT ERROR_NUMBER()
SELECT ERROR_MESSAGE()
SELECT ERROR_SEVERITY()
SELECT ERROR_STATE()
SELECT ERROR_LINE()
SELECT ERROR_PROCEDURE()

如果你想把错误返回给调用者，必须显式调用RAISERROR命令

--检查是否是更新丢失和死锁
IF ERROR_NUMBER()  IN(1205,3960)

sql2005除了几个最为严重的错误以外，他可以捕获所有错误，通过错误相关函数，他提供了大量有用信息

三种事务状态
1、未打开的
2、打开的可提交
3、打开的不可提交

第十一章 service broker

service broker：顺序的，异步的，可靠的消息传递

消息

service broker消息体的数据类型好似VARBINAYR(MAX),最大达到2GB

消息头包含消息类型

消息类是sqlserver的数据类型，所以不用担心超出sqlserver的数据类型

CREATE MESSAGE TYPE [//microsoft.com/inventory/additen]

XML分析器

SELECT * FROM sys.[service_message_types]

约定

CREATE CONTRACT []

约定也使用类似URL的名称格式，这是因为他也是作为消息头的一部分被发送，约定名称也使用二进制排序规则，输时候要注意！！

--系统中的约定
SELECT * FROM sys.[service_contracts]

--描述消息和约定关系的视图
SELECT * FROM sys.[service_contract_message_usages]

--查询包含数据库中所有消息类型的列表，他们使用的约定，由哪些端点发送

SELECT c.[name] AS contract ,
m.[name] AS messagetyep ,
CASE WHEN [is_sent_by_initiator] = 1
AND [is_sent_by_target] = 1 THEN 'any'
WHEN [is_sent_by_initiator] = 1 THEN 'initiatior'
WHEN [is_sent_by_target] = 1 THEN 'target'
END AS sentby
FROM sys.[service_message_types] AS m
JOIN sys.[service_contract_message_usages] AS u ON m.[message_type_id] = u.[message_type_id]
JOIN sys.[service_contracts] AS c ON c.[service_contract_id] = u.[service_contract_id]
ORDER BY c.[name] ,
m.[name]

initiatior：发送者
target：接收者
any：既是发送者也是接受者

如果你使用service broker处理DML时没有指定消息类型或约定，将使用default

在最底层，service broker是功能完备的数据库队列，service broker队列是隐藏的表，他由service broker代码进行管理，service broker使用自己的锁架构以最大化队列的性能

service broker队列在内部表中存储信息，内部表和普通表类似，放在msdb数据库里

service broker队列在内部表中存储信息，内部表和普通表类似

--查看内部表的语句
SELECT q.[name] AS queuename ,
i.[name] AS intelnalname
FROM sys.[service_queues] AS q
JOIN sys.[internal_tables] AS i ON q.[object_id] = i.[parent_object_id]

大部分的service broker操作会在队列的行上保持锁，所以查看队列时加nolock ，select * from queuename with(nolock)

队列是唯一实际存储数据的service broker对象

--创建队列时可以指定文件组，以指定消息放在哪个文件组，这样可以降低主文件组的开销
CREATE QUEUE dbo.InventoryQueue
WITH ACTIVATION(MAX_QUEUE_READERS=2,
EXECUTE AS SELF)
ON PRIMARY

你可以使用service broker临时存储的消息

SELECT * FROM [sys].[transmission_queue]

每个数据库只有一个[sys].[transmission_queue]

目标队列所在数据库的service broker被禁止，这通常是由于附加或恢复数据库时未使用 ENABLE_BROKER

--还原数据库时指定WITH ENABLE_BROKER
RESTORE DATABASE [sss] FROM DISK='' WITH ENABLE_BROKER

--附加数据库时指定WITH ENABLE_BROKER
CREATE DATABASE [Barefoot.Opinion.9299] ON 
( FILENAME = N'E:\DataBase\Bxx.mdf' ),
( FILENAME = N'E:\DataBase\Bxx.ldf' )
FOR ATTACH WITH ENABLE_BROKER
GO

SELECT [transmission_status] FROM [sys].[transmission_queue]
[transmission_status] 列将表明故障诊断的最重要源头，[transmission_status] 列表明哪里出了问题

服务

服务仅仅是会话端点的名称！！

CREATE SERVICE [//microsoft.com/] ON QUEUE dbo.InventoryQueue ([//microsoft.com/inventory])

sys.[services]

--下面查询列出服务名称和用于接收该服务的目标消息队列名称
SELECT  s.[name] ,
        q.[name]
FROM    sys.[services] AS s
        JOIN sys.[service_queues] AS q ON s.[service_queue_id] = q.[object_id]

--下面查询显示约定，队列，服务三者的关系
SELECT  s.[name] AS servicename ,
        q.[name] AS queuename ,
        c.[name] AS contractname
FROM    sys.[services] AS s
        JOIN sys.[service_queues] AS q ON s.[service_queue_id] = q.[object_id]
        JOIN sys.[service_contract_usages] AS u ON s.[service_id] = u.[service_id]
        JOIN sys.[service_contracts] AS c ON u.[service_contract_id] = c.[service_contract_id]

对话的组成部分
标识消息的消息类型
约定：定义端点可以发送哪些消息类型
队列：在每个端点保存消息
服务：联接所有端点元素

对话

@dialog是输出变量，他返回UNIQUEIDENTIFIER类型的对话句柄，用于在其他service broker的DML命令中引用该对话

BEGIN DIALOG CONVERSATION @dialog
FROM SERVICE []
TO SERVICE '' --长度为25的字符串
ON CONTRACT []
WITH ENCRYPTION=OFF,LIFETIME=3600

CONTRACT子句限制可以发送的消息类型以及哪个端点可以发送哪种消息类型，如果忽略CONTRACT子句，将使用default约定，default约定只允许端点发送default类型的消息

结束对话，传递@dialog参数，@dialog是输出变量，他返回UNIQUEIDENTIFIER类型的对话句柄，用于在其他service broker的DML命令中引用该对话

END CONVERSATION @dialog

--指定一个错误号，发送到目标端，而不是发送结束会话消息，下面是使用错误选项结束对话的例子
END CONVERSATION @dialog WITH ERROR=31427
DESCRIPTION ='invalid '


--发现孤立对话，使用WITH CLEANUP把他们清理掉
END CONVERSATION @dialog WITH CLEANUP

批量清理孤立会话，避免在生产系统中使用

ER状态的意思

DECLARE @handle AS UNIQUEIDENTIFIER
DECLARE conv CURSOR
FOR
    SELECT  [conversation_handle]
    FROM    sys.[conversation_endpoints]
    WHERE   [state] = 'ER'
OPEN CONV
FETCH NEXT FROM CONV INTO @handle
WHILE @@FETCH_STATUS = 0
    BEGIN
        END CONVERSATION @HANDLE WITH CLEANUP
        FETCH NEXT FROM CONV INTO @handle
    END
CLOSE CONV
DEALLOCATE CONV

会话端点

--存储会话状态
SELECT * FROM sys.[conversation_endpoints]

远程数据库的service_broker_guid列来填充GUID

这里没有关于两个端点的网络地址信息，这样可以在对话的生存期中把端点移动到不同的网络位置而不影响消息的传递，转移活动对话端点是一个非常强大的功能，他使service broker系统具有很大的灵活性和伸缩性

会话组

每个会话关联一个会话组，锁定该会话组，这样就不必再处理多线程的应用程序相关问题了

当创建一个会话端点后，将生成一个GUID,并向[sys].[conversation_groups]视图插入一行，生成的GUID保存到[conversation_group_id]列

SELECT * FROM [sys].[conversation_groups]

conversation_group_id作为外键

SELECT  *
FROM    [sys].[conversation_groups] AS grp
        JOIN sys.[conversation_endpoints] AS ept ON [ept].[conversation_group_id] = [grp].[conversation_group_id]

BEGIN DIALOG CONVERSATION @dialog
FROM SERVICE []
TO SERVICE ''
ON CONTRACT []
WITH LIFETIME=3600,RELATED_CONVERSATION=@shop,RELATED_CONVERSATION_GROUP=@shopgrp

receive命令从目标队列提取一条消息，如果队列retention选项是off，则receive命令转化为delete...output命令

发送消息

SEND ON CONVERSATION @dialog
MESSAGE TYPE [//microsoft.com/invetn](@message_body)

接收命令

RECEIVE TOP(1)
@message_type=[message_type_name],
@message_body=message_body,
@diaglog=[conversation_handle]
FROM dbo.inventoryqueue

指定会话组id，接收指定会话组的会话

RECEIVE TOP(1)
@message_type=[message_type_name],
@message_body=message_body,
@diaglog=[conversation_handle]
FROM dbo.inventoryqueue
WITH conversation_group_id=@CGID

同步接收相同会话消息，而不是异步，等待目标端返回确认消息

RECEIVE TOP(1)
@message_type=[message_type_name],
@message_body=message_body,
@diaglog=[conversation_handle]
FROM dbo.inventoryqueue
WITH CONVERSATION_HANDLE=@dialog

waitfor语句等待2秒后没有消息返回即返回0行

WAITFOR(RECEIVE TOP(1)
@message_type=[message_type_name],
@message_body=message_body,
@diaglog=[conversation_handle]
FROM dbo.inventoryqueue),TIMEOUT 2000

send和receive关键字之前的命令必须以分号结束，但waitfor语句中的receive语句是个例外

简单对话，库存示例

有害消息

处理有害消息的最好办法是预防，service broker检测到队列中的一行发生了5次回滚，将禁用该队列以阻止有害消息

ALTER DATABASE [sss] SET TRUSTWORTHY ON

对称密钥加密也称为共享安全（shared secret）加密

在sql2005中，所有的网络连接都经过端点处理：有效的端点类型包括：TSQL,SOAP,数据库镜像，service broker

CREATE ENDPOINT xx
SELECT * FROM sys.[endpoints]

创建端点

CREATE ENDPOINT inven STATE = STARTED AS TCP ( LISTENER_PORT=4030 ) FOR
    SERVICE_BROKER ( AUTHENTICATION= WINDOWS, ENCRYPTION= SUPPORTED )

端口使用哪个都无所谓，只要大于1024就可以了

--创建服务器端点inven ，使用Windows身份验证方式
CREATE ENDPOINT inven STATE = STARTED AS TCP ( LISTENER_PORT=4030 ) FOR
    SERVICE_BROKER ( AUTHENTICATION= WINDOWS, ENCRYPTION= SUPPORTED )

--授予网络服务【inven】 connec权限
GRANT CONNECT ON ENDPOINT::inven
TO [NT AUTHORITY\NETWORK SERVICE]

任何人都没必要使用代理登录（proxy login）进入该实例，所以最大程度的保证安全

路由

2232是端口号，是对方创建端点的时候指定的端口号

CREATE ROUTE inventory_route WITH
SERVICE_NAME='//microsoft.com',
ADDRESS='TCP://MYPC11:2232'

SELECT * FROM sys.[routes]

使用sql profiler监视service broker消息的发送，你会注意到该消息会定时向目标发送，但总是因重复而被拒收

使用sql profiler监视service broker连接

查询SELECT * FROM sys.[dm_broker_connections]视图看是否建立了broker网络连接

SELECT * FROM sys.[dm_broker_connections]

--指定BROKER_INSTANCE就可以将消息路由到哪个数据库


CREATE ROUTE maunfacturing WITH
SERVICE_NAME='',
BROKER_INSTANCE='A31CDE6F-E987-4C8B-8BC4-0565F289A0DB',
ADDRESS='TCP://MYPC11:3833'

SELECT DB_NAME([database_id]) AS dbname,[service_broker_guid] FROM sys.[databases]

msdb数据库中的路由决定如何处理传入的消息

创建数据库时会自动创建一个名为AutoCreateLocal的路由

service broker会在msdb库的SELECT * FROM [msdb].SYS.[routes]查找与传入消息的服务名称匹配的路由，转发队列，转发路由是在msdb库创建的

--设置转发队列为50MB内存

ALTER ENDPOINT inventory FOR SERVICE_BROKER
(MESSAGE_FORWARDING=ENABLED,MESSAGE_FORWARD_SIZE=50)

SELECT * FROM sys.[endpoints]

service broker属性

service broker结合了CLR集成，数据库镜像，新的XML特征

在触发器内，临时表会被回滚，而表变量不会被回滚

测试

USE [sss]
GO
CREATE TRIGGER trig_testrollback ON [dbo].[testrow]
    FOR INSERT
AS
    BEGIN 
        CREATE TABLE #TESTTB ( ID INT )
        DECLARE @TB TABLE ( ID INT )

        BEGIN TRAN
        INSERT  INTO [#TESTTB]
                ( [ID] )
        VALUES  ( 1  -- ID - int
                  )
        INSERT  INTO @TB
                ( [ID] )
        VALUES  ( 1  -- ID - int
                  )
        ROLLBACK TRAN

        SELECT  *
        FROM    @TB
        SELECT  *
        FROM    [#TESTTB]

    END 

----------------------------------------------
INSERT INTO [dbo].[testrow]
        ( [id] )
VALUES  ( 1212  -- id - int
          )
--执行insert的时候会把临时表进行回滚,而没有把表变量进行回滚

(1 行受影响)

(1 行受影响)

(1 行受影响)
消息 208，级别 16，状态 0，过程 trig_testrollback，第 21 行
对象名 '#TESTTB' 无效。

posted @ 2014-09-10 10:05 桦仔阅读(2412) 评论(2) 编辑收藏举报

刷新页面返回顶部

Microsoft SQL Server 2005技术内幕： T-SQ程序设计 笔记

Microsoft SQL Server 2005技术内幕： T-SQ程序设计 笔记

公告

Microsoft SQL Server 2005技术内幕： T-SQ程序设计笔记

Microsoft SQL Server 2005技术内幕： T-SQ程序设计笔记