分类: C/C++
2008-04-03 17:54:22
INSERT INTO T1 VALUE(3)返回连接1,再次运行脚本1。如果检查表ErrorLog内容,会看到一个主键违规错误已被记录。转到连接2,运行下面命令以删除刚插入的行:
DELETE FROM T1 WHERE col1 = 3要测试锁超时错误,在连接2中运行下面代码:
BEGIN TRAN UPDATE T1 SET col1 = 1返回连接1,再次运行脚本1。大约30秒后,你会得到一个错误。检查ErrorLog内容,会发现一个锁超时错误已被记录。转到连接2,执行ROLLBACK命令回滚事务。
DECLARE @i AS INT BEGIN TRAN SET @i = 1 WHILE @i <= 2 BEGIN UPDATE T2 SET col1 = 2 WAITFOR DELAY ''''00:00:10'''' SELECT * FROM T1 WAITFOR DELAY ''''00:00:05'''' SET @i = @i + 1 END ROLLBACK转到连接1,运行脚本1代码,然后立即在连接2中运行代码。约一分钟后,你会看到连接1发生错误。检查ErrorLog内容,注意到表中有死锁错误后两个重试尝试和第三个未导致错误的成功尝试。查询ErrorLog表,检查它的内容。
CREATE DATABASE testdb GO USE testdb ALTER DATABASE testdb SET ALLOW_SNAPSHOT_ISOLATION ON CREATE TABLE T1 ( keycol INT NOT NULL PRIMARY KEY, datacol VARCHAR(10) NOT NULL ) INSERT INTO T1 VALUES(1, ''''Version1'''')在连接1中执行下列代码,它开启一个事务并改变datacol值为“Version2”:
USE testdb BEGIN TRAN UPDATE T1 SET datacol = ''''Version2'''' WHERE keycol = 1 SELECT * FROM T1转到连接2,运行下列代码,它设置会话隔离级为SNAPSHOT并取回T1的内容:
USE testdb SET TRANSACTION ISOLATION LEVEL SNAPSHOT SELECT * FROM T1注意你取回的是“Version1”,即使连接2已将它改变为“Version2”(但是还没有提交改变)。
SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT * FROM T1转到连接2,执行UPDATE:
UPDATE T1 SET datacol = ''''Version3'''' WHERE keycol = 1返回连接1,设法update你以前获取的同样数据,这些数据已在连接2中被修改:
UPDATE T1 SET datacol = ''''Version4'''' WHERE keycol = 1你应该得到一个错误,它通知你SQL Server不能使用SNAPSHOT隔离级去存取数据库testdb的表T1和应重试你的事务。
WAITFOR(Yukon中另一增强允许返回从数据操纵语言(INSERT,UPDATE,DELETE)而不只是SELECT语句的输出。新的OUTPUT子句允许请求列的旧或新数据通过引用INSERTED和DELETED表被返回,类似在触发器中引用它们。你甚至能指定INTO子句并定向其输出到一个表变量。另一增强是允许在修改语句里指定READPAST提示,这样就可跳过锁定行。) [,TIMEOUT ]
USE tempdb CREATE TABLE MsgQueue ( msgid INT NOT NULL IDENTITY PRIMARY KEY, msgdata VARCHAR(15) NOT NULL )打开一个或多个连接,在每个连接中运行下列代码以定时插入新消息到表中:
SET NOCOUNT ON USE tempdb WHILE 1 = 1 BEGIN INSERT INTO MsgQueue VALUES(''''Msg'''' + CAST(CAST(RAND()*1000000000 AS INT) AS VARCHAR(10))) WAITFOR DELAY ''''00:00:01'''' END接着需打开数个其他新连接,并在每个连接中运行Figure 4中代码以模拟对新到达消息的处理。
SELECT col1, col2, col3 FROM OPENROWSET(BULK ''''c:\temp\textfile1.txt'''', FORMATFILE = ''''c:\temp\textfile1.fmt'''') AS C(col1, col2, col3)除了FORMATFILE选项,还能在OPENROWSET函数里指定下列选项:CODEPAGE、DATAFILETYPE、FIELDTERMINATOR、FIRSTROW、LASTROW和ROWTERMINATOR。也可以使用INSERT SELECT来有效地载入数据到表中和为装载选项指定表提示:
INSERT INTO MyTable WITH (BULK_CHECK_CONSTRAINTS) SELECT col1, col2, col3 FROM OPENROWSET(BULK ''''c:\temp\textfile1.txt'''', FORMATFILE = ''''c:\temp\textfile1.fmt'''') AS C(col1, col2, col3)能指定表提示的其他选项包括:BULK_BATCHSIZE, BULK_FIRE_TRIGGERS, BULK_KEEPIDENTITY, BULK_KEEPNULLS, BULK_KILOBYTES_PER_BATCH, BULK_MAXERRORS,和ROWS_PER_BATCH。
UPDATE LOBs SET clob_col = (SELECT clob_data FROM OPENROWSET(BULK ''''c:\temp\textfile1.txt'''', SINGLE_CLOB) AS C(clob_data)) WHERE keycol = 1SINGLE_NCLOB选项通知SQL Server大对象是字符格式的。类似地,SINGLE_CLOB指定大对象为规则字符格式,SINGEL_BLOB为二进制格式。返回列的名称为BulkColumn,然而如上一代码片段所示,可以为它指定自己的列名。
USE AdventureWorks DECLARE @n AS BIGINT SET @n = 5 SELECT TOP (@n) * FROM SalesOrderHeader AS SOH ORDER BY OrderDate, SalesOrderID排序时同种情况下SalesOrderID优先。类似地,下面例子示范如何使用PERCENT选项返回最先满足请求百分比的订单:
DECLARE @p AS FLOAT SET @p = 0.01 SELECT TOP (@p) PERCENT * FROM SalesOrderHeader AS SOH ORDER BY OrderDate, SalesOrderID如果SalesOrderHeader表包含31519行,31519×0.0001结果向上取整为4, 你会得到4行结果。
SELECT * INTO MySalesOrderHeader FROM SalesOrderHeader CREATE UNIQUE CLUSTERED INDEX idx_uc_OrderDate_SalesOrderID ON MySalesOrderHeader(OrderDate, SalesOrderID)要以批处理量为1000的方式删除所有订单年份比2003年早的行,用下列代码:
WHILE 1 = 1 BEGIN DELETE TOP (1000) FROM MySalesOrderHeader WHERE OrderDate < ""20030101"" IF @@rowcount < 1000 BREAK ENDSQL Server比使用SET ROWCOUNT更有效优化此代码。现在可以删除MySalesOrderHeader表:
DROP TABLE MySalesOrderHeaderAPPLY算子
CREATE TABLE Arrays ( arrid INT NOT NULL IDENTITY PRIMARY KEY, array VARCHAR(7999) NOT NULL ) INSERT INTO Arrays VALUES('''''''') INSERT INTO Arrays VALUES(''''10'''') INSERT INTO Arrays VALUES(''''20,40,30'''') INSERT INTO Arrays VALUES(''''-1,-3,-5'''')接着,创建fn_splitarr表值函数,它接受一队列为参数并返回一个包含个体元素和他们的位置的表(见Figure 5)。要测试函数,运行此代码:
SELECT * FROM fn_splitarr(''''20,40,30'''')输出应与下面相似:
pos value --- ----- 1 20 2 40 3 30现在,用CROSS ARRAY算子对表Arrays中每行调用函数:
SELECT A.arrid, F.* FROM Arrays AS A CROSS APPLY fn_splitarr(array) AS F然后对照下面检查输出:
arrid pos value ----- --- ----- 2 1 10 3 1 20 3 2 40 3 3 30 4 1 -1 4 2 -3 4 3 -5注意,表Arrays中arrid为1的行没有返回,因为函数对其返回空集。如果要返回表Arrays中所有行,且不管函数是否为它们返回行,使用OUTER APPLY。
SELECT * FROM Arrays WHERE (SELECT SUM(value) FROM fn_splitarr(array)) <= 10通用表表达式(Common Table Expressions )和递归查询
SELECT B.ProductID, P.Name, SUM(B.Qty) AS TotalQty FROM BOMCTE AS B JOIN Product AS P ON P.ProductID = B.ProductID GROUP BY B.ProductID, P.Name ORDER BY B.ProductID;如果怀疑循环和想限制递归调用的次数,可紧跟在外部查询指后定MAXRECURSION选项:
WITH... outer_query OPTION(MAXRECURSION 30)当CTE超过指定限制,此选项使SQL Server抛出错误。当没有指定此选项时,SQL Server默认设置为100。如果不想由限制,必须指定其为0。注意可编写检查循环关系的自定义代码,但这超出本文的范围。
USE AdventureWorks SELECT SOH.SalesPersonID, YEAR(SOH.OrderDate) AS OrderYear, SOD.OrderQty * SOD.UnitPrice AS OrderValue FROM SalesOrderHeader AS SOH JOIN SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderIDSQL Server计算出,“GROUP BY”列清单应是输入表中列清单,它们没有被合计函数或IN子句里PIVOT算子显示引用。因此,你不会在隐式GROUP BY列清单中得到不需要的列,你需要为PIVOT算子提供一个输入表,它仅包含对合计函数、IN子句和隐式GROUP BY感兴趣的列。这可以通过使用CTE或导出表获取,其包含返回只感兴趣列的上一查询。
SELECT * FROM SalesPivoted UNPIVOT(OrderValue FOR OrderYear IN([2001], [2002], [2003], [2004])) AS UUNPIVOT不会返回含有NULL值单元的行。要清空刚才在数据库中已建立的表和索引,运行下列代码:
DROP INDEX SalesOrderHeader.idx_nc_OrderDate DROP TABLE SalesPivoted触发器和通知
CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE AS RAISERROR(''''Not allowed to drop tables.'''', 10, 1) ROLLBACK -- For debug PRINT ''''DROP TABLE attempt in database '''' + DB_NAME() + ''''.'''' PRINT EventData() GO可以定义触发器对指定DDL事件触发,这些事件有CREATE_TABLE、DROP_TABLE、ALTER_TABLE、CREATE_VIEW等等,或者,如果想触发器对数据库中所有DDL事件进行触发,可指定DDL_DATABASE_LEVEL_EVENTSXUAN。触发器内,可调用EventData函数以返回启动触发器的有关进程和活动的信息。可检查此函数返回的XML并作相应处理。
CREATE TABLE TestDROP(col1 INT) INSERT INTO TestDROP VALUES(1)接着,尝试删除表:
DROP TABLE TestDROPDROP企图被捕获,一条信息被打印以指示不允许删除此表。同样,为调试所需,EventData函数的返回值以XML格式被打印出。(实际上,触发器内可检查XML数据,它包含很多有用信息,以决定哪个过程行为最适合你需要。例如,可防止一天中某时刻删除某表。)触发器回滚活动,因而表没有从数据库中删除。要删除触发器,执行下列语句:
DROP TRIGGER prevent_drop_table ON DATABASE也可以创建捕获服务级别事件的触发器。例如,下面触发器捕获登录操作事件,如创建、改变或删除一次登录。
CREATE TRIGGER audit_ddl_logins ON ALL SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN AS PRINT ''''DDL LOGIN took place.'''' PRINT EventData() GO此例中,当事件发生后触发器仅仅打印通告信息和事件的详细信息。但是,当然,可检查事件详细内容并作相应的处理。要测试触发器,运行下列代码,然后看看结果:
CREATE LOGIN login1 WITH PASSWORD = ''''123'''' ALTER LOGIN login1 WITH PASSWORD = ''''xyz'''' DROP LOGIN login1一DDL登录事件被识别,然后事件数据以XML格式产生。如果你愿意,可检查事件数据和审查对你重要的信息。
DROP TRIGGER audit_ddl_logins ON ALL SERVER总结