AI 助理
备案控制台
开发者社区 数据库 文章 正文

SQL SERVER 2005 T_SQL新的特性以及解决并发

2017-11-26 1342
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
简介：
一.修改语句的增强
UPDATE test SET tname.WRITE( ' one hundred and two ', 9, 3) WHERE tid = 102

在2005中增强了update方法，这是修改test表的列tname WRITE方法是把tname这个列中从字符串9开始 把3个长的字符串改为one hundred and two

二、异常的捕获
可以捕获过去会导致批处理终止和事务的错误，但是不能处理连接中断错误和硬件错误等
-- Using the try..catch.. construct and invoking a run-time error
SET XACT_ABORT of 这个打开捕获异常的开关 ，默认是关闭的
BEGIN TRY
BEGIN TRAN
INSERT INTO score VALUES ( 102, 78)
INSERT INTO score VALUES ( 107, 76) /* Foreign Key Error */
INSERT INTO score VALUES ( 103, 81)
COMMIT TRAN
PRINT ' Transaction committed '
END TRY
BEGIN CATCH
ROLLBACK
PRINT ' Transaction rolled back '
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
GO
三、快照隔离
写入程序不会影响读取程序，可对事务冲突的检测
create database demo2 建立数据库
go
use demo2
alter database demo2 set allow_snapshot_isolation on 打开快照隔离开关，默认是关闭的
create table test
( tid int not null primary key,
tname varchar( 50) not null
)
insert into test values( 1, ' version1 ')
insert into test values( 2, ' version2 ')

-- connection 1

use demo2
begin tran
update test set tname = ' version3 ' where tid = 2
select * from test

-- connection 2
use demo2
set transaction isolation level snapshot
select * from test

-- it will ok, you can see it
四 、top语句的增强
可以是数字表达式，一返回要通过通过查询影响的行输或者百分比还可以是更具情况使用变量和子查询
可以在delete、update和insert中使用top选项，更好的代替set rowcount选项，使之更为有效。
-- create a table and insert some data
use demo
go
CREATE TABLE toptest (column1 VARCHAR( 150))
go
INSERT INTO toptest VALUES( ' t1 ')
INSERT INTO toptest VALUES( ' t2 ')
INSERT INTO toptest VALUES( ' t3 ')
INSERT INTO toptest VALUES( ' t4 ')
INSERT INTO toptest VALUES( ' t5 ')
INSERT INTO toptest VALUES( ' t6 ')
INSERT INTO toptest VALUES( ' t7 ')
INSERT INTO toptest VALUES( ' t8 ')
select * from toptest
go

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

-- declare 3 variables
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT
-- set values
SET @a = 10
SET @b = 5
SELECT @c = @a / @b
-- use the calculated expression
SELECT TOP( @c) * FROM toptest
-- use a SELECT statement as expression
SELECT TOP( SELECT COUNT( *) FROM toptest2) *
FROM toptest

-- DML top
DELETE TOP( 2) toptest where column1 > ' t6 '
-- this sets 't1' and 't2' to 'hi'
UPDATE TOP( 2) toptest SET column1 = ' hi ' where column1 <= ' t2 '

SELECT * FROM toptest
五、output
引入一个新的output字句 可以使您从修改语句（elete、update和insert）中将数据返回到表变量中
语法：output <dml_select_list > into @table_variable
可以通过应用插入的表和删除的表来访问被修改的行的旧 /新映象，起方式于访问触发器类似，在insert语句中，只能访问插入的表，update和delete也一样。访问临时表
-- create table and insert data
use demo
go
CREATE TABLE tt
(id INT IDENTITY, c1 VARCHAR( 15))
go
INSERT INTO tt VALUES ( ' r1 ')
INSERT INTO tt VALUES ( ' r2 ')
INSERT INTO tt VALUES ( ' r5 ')
INSERT INTO tt VALUES ( ' r6 ')
INSERT INTO tt VALUES ( ' r7 ')
INSERT INTO tt VALUES ( ' r8 ')
INSERT INTO tt VALUES ( ' r9 ')
INSERT INTO tt VALUES ( ' r10 ')

-- make a table variable to hold the results of the OUTPUT clause
DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR( 15))
DELETE tt
OUTPUT DELETED.id, DELETED.c1 INTO @del
WHERE id < 3
SELECT * FROM @del
GO

六、函数
ROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序
SELECT orderid,qty,
ROW_NUMBER() OVER( ORDER BY qty) AS rownumber,
RANK() OVER( ORDER BY qty) AS rank,
DENSE_RANK() OVER( ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty
七、通用表达式 cte 临时命名的结果集
USE AdventureWorks
GO
WITH SalesCTE(ProductID, SalesOrderID)
AS
(
SELECT ProductID, COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT * FROM SalesCTE
-- --


-- Using CTEs Recursively
use demo
go
CREATE TABLE CarParts
(
CarID int NOT NULL,
Part varchar( 15),
SubPart varchar( 15),
Qty int
)
GO
INSERT CarParts
VALUES ( 1, ' Body ', ' Door ', 4)
INSERT CarParts
VALUES ( 1, ' Body ', ' Trunk Lid ', 1)
INSERT CarParts
VALUES ( 1, ' Body ', ' Car Hood ', 1)
INSERT CarParts
VALUES ( 1, ' Door ', ' Handle ', 1)
INSERT CarParts
VALUES ( 1, ' Door ', ' Lock ', 1)
INSERT CarParts
VALUES ( 1, ' Door ', ' Window ', 1)
INSERT CarParts
VALUES ( 1, ' Body ', ' Rivets ', 1000)
INSERT CarParts
VALUES ( 1, ' Door ', ' Rivets ', 100)
INSERT CarParts
VALUES ( 1, ' Door ', ' Mirror ', 1)
go
select * from CarParts
go

WITH CarPartsCTE(SubPart, Qty)
AS
(
-- Anchor Member (AM):
-- SELECT query that doesn’t refer back to CarPartsCTE
SELECT SubPart, Qty
FROM CarParts
WHERE Part = ' Body '
UNION ALL
-- Recursive Member (RM):
-- SELECT query that refers back to CarPartsCTE
SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
FROM CarPartsCTE
INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
WHERE CarParts.CarID = 1
)
-- outer query
SELECT SubPart, SUM(Qty) AS TotalNUM
FROM CarPartsCTE
GROUP BY SubPart
八、新的关系运算符
pivot 把行转回为列
unpivot 把列转回为行
use demo
go

create table orders
(Customer varchar( 10) not null,
product varchar( 20) not null,
quantity int not null)
go
insert orders values( ' Mike ', ' Bike ', 3)
insert orders values( ' Mike ', ' Chain ', 2)
insert orders values( ' Mike ', ' Bike ', 5)
insert orders values( ' Lisa ', ' Bike ', 3)
insert orders values( ' Lisa ', ' Chain ', 3)
insert orders values( ' Lisa ', ' Chain ', 4)
insert orders values( ' Lisa ', ' Bike ', 2)

select * from orders

select * from orders
pivot ( sum(quantity) for product in ( [ Bike ], [ Chain ])) as a
use demo
go
CREATE TABLE SALES1
(
[ Year ] INT,
Quarter CHAR( 2),
Amount FLOAT
)
GO
INSERT INTO SALES1 VALUES ( 2001, ' Q1 ', 80)
INSERT INTO SALES1 VALUES ( 2001, ' Q2 ', 70)
INSERT INTO SALES1 VALUES ( 2001, ' Q3 ', 55)
INSERT INTO SALES1 VALUES ( 2001, ' Q3 ', 110)
INSERT INTO SALES1 VALUES ( 2001, ' Q4 ', 90)
INSERT INTO SALES1 VALUES ( 2002, ' Q1 ', 200)
INSERT INTO SALES1 VALUES ( 2002, ' Q2 ', 150)
INSERT INTO SALES1 VALUES ( 2002, ' Q2 ', 40)
INSERT INTO SALES1 VALUES ( 2002, ' Q2 ', 60)
INSERT INTO SALES1 VALUES ( 2002, ' Q3 ', 120)
INSERT INTO SALES1 VALUES ( 2002, ' Q3 ', 110)
INSERT INTO SALES1 VALUES ( 2002, ' Q4 ', 180)
GO

SELECT * FROM SALES1
PIVOT
( SUM (Amount) -- Aggregate the Amount column using SUM
FOR [ Quarter ] -- Pivot the Quarter column into column headings
IN (Q1, Q2, Q3, Q4)) -- use these quarters
AS P
GO

select * into temp1 from orders
pivot ( sum(quantity) for product in ( [ Bike ], [ Chain ])) as a

select * from temp1

select customer, product,quantity
froam temp1
unpivot(quantity for product in ( [ Bike ], [ Chain ])) as a
九、ddl触发器
use demo
go
CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE
AS
RAISERROR( ' Not allowed to drop tables. ', 10, 1)
PRINT ' DROP TABLE attempt in database ' + DB_NAME() + ' . '
PRINT CONVERT ( nvarchar ( 1000),EventData())
ROLLBACK
GO
-- test
CREATE TABLE TestDROP(col1 INT)
go
INSERT INTO TestDROP VALUES( 1)

drop talbe testdrop

-- Server
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
PRINT ' DDL LOGIN took place. '
PRINT CONVERT ( nvarchar ( 1000),EventData())
GO

-- test
CREATE LOGIN login1 WITH PASSWORD = ' 123 '
ALTER LOGIN login1 WITH PASSWORD = ' xyz '
DROP LOGIN login1
丰富的数据类型 Richer Data Types

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

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

错误处理 Error Handling

1、新的异常处理结构

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

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

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

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

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

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

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

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

SELECT * FROM student
SELECT * FROM score

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

SELECT * FROM score
GO

快照隔离 Snapshot Isolation

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

-- 连接一

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

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

TOP 增强功能

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

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

OUTPUT

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

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

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

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

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

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

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

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

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

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

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

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

SELECT * FROM toptest

排序函数 Ranking Functions

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

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

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

通用表表达式 Common Table Expressions

通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集。在他们的简单形式中，您可以将CTE视为类似于视图和派生表混合功能的改进版本。在查询的FROM子句中引用CTE的方式类似于引用派生表和视图的方式。只须定义CTE一次，即可在查询中多次引用它。在CTE的定义中，可以引用在同一批处理中定义的变量。但是CTE的真正威力在于它们的递归功能，即CTE可以包含对它们自身的引用。

视图、派生表和CTE内部的查询的一般形式

1、视图
CREATE VIEW <view_name >( <column_aliases >) AS <view_query >

2、派生表
SELECT * FROM ( <derived_table)query >) AS <dericed_table_alias >( <column_aliases >)

3、CTE
WITH <cte_alias >( <column_aliases >)
AS
{
<cte_query >
)
SELECT * FROM <cte_alias] >
在关键字WITH之后，为CTE提供一个别名，并且为它的结果列提供一个可选的别名列表；编写CTE的主体；然后从外部查询中引用它。

4、演示代码
USE AdventureWorks
GO
WITH SalesCTE(ProductID, SalesOrderID)
AS
(
SELECT ProductID, COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT * FROM SalesCTE

Recursive CTEs 递归的通用表表达式

递归的CTE是根据至少两个查询（或者称为两个成员）构建的，一个是非递归查询，也成为固定成员，只能调用一次，另外一个是递归查询，也成为递归成员（RM），可以反复调用，直到查询不再返回行。查询由UNION ALL运算符连接为一个单独的CTE。

-- 使用递归的通用表表达式
USE demo
GO
CREATE TABLE CarParts
(
CarID INT NOT NULL,
Part VARCHAR( 15),
SubPart VARCHAR( 15),
Qty INT
)
GO
INSERT CarParts VALUES ( 1, ' Body ', ' Door ', 4)
INSERT CarParts VALUES ( 1, ' Body ', ' Trunk Lid ', 1)
INSERT CarParts VALUES ( 1, ' Body ', ' Car Hood ', 1)
INSERT CarParts VALUES ( 1, ' Door ', ' Handle ', 1)
INSERT CarParts VALUES ( 1, ' Door ', ' Lock ', 1)
INSERT CarParts VALUES ( 1, ' Door ', ' Window ', 1)
INSERT CarParts VALUES ( 1, ' Body ', ' Rivets ', 1000)
INSERT CarParts VALUES ( 1, ' Door ', ' Rivets ', 100)
INSERT CarParts VALUES ( 1, ' Door ', ' Mirror ', 1)
GO
SELECT * FROM CarParts
GO

WITH CarPartsCTE(SubPart, Qty)
AS
(
-- 固定成员 (AM):
-- SELECT查询无需参考CarPartsCTE
SELECT SubPart, Qty
FROM CarParts
WHERE Part = ' Body '
UNION ALL
-- 递归成员 (RM):
-- SELECT查询参考CarPartsCTE
SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
FROM CarPartsCTE
INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
WHERE CarParts.CarID = 1
)
-- 外部查询
SELECT SubPart, SUM(Qty) AS TotalNUM
FROM CarPartsCTE
GROUP BY SubPart

新的关系运算符 PIVOT /UNPIVOT /APPLY

1、PIVOT
PIVOT运算符将行旋转为列，并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者是CTE只返回所关注的列。

2、UNPIVOT
UNPIVOT运算符执行与PIVOT运算符相反的操作；他将列旋转为行了。

3、APPLY
APPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY，其方式与使用JOIN关系运算符类似。APPLY具有两种形式： CROSS APPLY和OUTER APPLY。

演示：

USE demo
GO

CREATE TABLE orders
(
Customer VARCHAR( 10) NOT NULL,
product VARCHAR( 20) NOT NULL,
quantity INT NOT NULL
)
GO
INSERT orders VALUES( ' Mike ', ' Bike ', 3)
INSERT orders VALUES( ' Mike ', ' Chain ', 2)
INSERT orders VALUES( ' Mike ', ' Bike ', 5)
INSERT orders VALUES( ' Lisa ', ' Bike ', 3)
INSERT orders VALUES( ' Lisa ', ' Chain ', 3)
INSERT orders VALUES( ' Lisa ', ' Chain ', 4)
INSERT orders VALUES( ' Lisa ', ' Bike ', 2)

SELECT * FROM orders

SELECT * FROM orders
PIVOT ( SUM(quantity) FOR product IN ( [ Bike ], [ Chain ])) AS a
USE demo
GO
CREATE TABLE SALES1
(
[ Year ] INT,
Quarter CHAR( 2),
Amount FLOAT
)
GO
INSERT INTO SALES1 VALUES ( 2001, ' Q1 ', 80)
INSERT INTO SALES1 VALUES ( 2001, ' Q2 ', 70)
INSERT INTO SALES1 VALUES ( 2001, ' Q3 ', 55)
INSERT INTO SALES1 VALUES ( 2001, ' Q3 ', 110)
INSERT INTO SALES1 VALUES ( 2001, ' Q4 ', 90)
INSERT INTO SALES1 VALUES ( 2002, ' Q1 ', 200)
INSERT INTO SALES1 VALUES ( 2002, ' Q2 ', 150)
INSERT INTO SALES1 VALUES ( 2002, ' Q2 ', 40)
INSERT INTO SALES1 VALUES ( 2002, ' Q2 ', 60)
INSERT INTO SALES1 VALUES ( 2002, ' Q3 ', 120)
INSERT INTO SALES1 VALUES ( 2002, ' Q3 ', 110)
INSERT INTO SALES1 VALUES ( 2002, ' Q4 ', 180)
GO

SELECT * FROM SALES1
PIVOT
( SUM (Amount) -- 使用SUM聚合数量列
FOR [ Quarter ] -- PIVOT Quarter 列
IN (Q1, Q2, Q3, Q4)) -- 使用季节
AS P
GO

SELECT * INTO temp1 FROM orders
PIVOT ( sum(quantity) FOR product IN ( [ Bike ], [ Chain ])) AS a

SELECT * FROM temp1

SELECT customer, product,quantity
FROM temp1
UNPIVOT(quantity FOR product IN ( [ Bike ], [ Chain ])) AS a
-- --------------------------------------------------
USE demo
GO
CREATE TABLE Arrays
(
aid INT NOT NULL IDENTITY PRIMARY KEY,
array VARCHAR( 7999) NOT NULL
)
GO
INSERT INTO Arrays VALUES( '')
INSERT INTO Arrays VALUES( ' 10 ')
INSERT INTO Arrays VALUES( ' 20,40,30 ')
INSERT INTO Arrays VALUES( ' -1,-3,-5 ')
GO
CREATE FUNCTION function1( @arr AS VARCHAR( 7999))
RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
AS
BEGIN
DECLARE @end AS INT, @start AS INT, @pos AS INT
SELECT @arr = @arr + ' , ', @pos = 1,
@start = 1, @end = CHARINDEX( ' , ', @arr, @start)
WHILE @end > 1
BEGIN
INSERT INTO @t VALUES( @pos, SUBSTRING( @arr, @start, @end - @start))

SELECT @pos = @pos + 1,
@start = @end + 1, @end = CHARINDEX( ' , ', @arr, @start)
END
RETURN
END

-- 测试
SELECT * FROM function1( ' 200,400,300 ')
GO

SELECT A.aid, F. *
FROM Arrays AS A
CROSS APPLY function1(array) AS F
GO
SELECT A.aid, F. *
FROM Arrays AS A
OUTER APPLY function1(array) AS F
GO

DDL触发器 DDL Triggers

SQL Server 2005可以就整个服务器或数据库的某个范围为DDL事件定义触发器。也可以为单个DDL语句（例如：CREAT_TABLE、DROP_TABLE等）或者为一组语句（例如：指定DDL_DATABASE_LEVEL_EVENTS想要触发器触发数据库所有DDL事件）定义DDL触发器。

在DDL触发器内部，可以通过访问eventdata()函数获得与激发该触发器的事件有关的数据。该eventdata()函数返回有关事件的xml数据。

DDL触发器特别有用的方案包括DDL更改的完整性检查、审核方案以及其他方案。

代码演示：

USE demo
GO
CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE
AS
RAISERROR( ' 没有删除表的权限. ', 10, 1)
PRINT ' 尝试在数据库 ' + DB_NAME() + ' 中删除表. '
PRINT CONVERT ( nvarchar ( 1000),EventData())
ROLLBACK
GO
-- 测试
CREATE TABLE TestDROP(col1 INT)
GO
INSERT INTO TestDROP VALUES( 1)

DROP TABLE testdrop

-- Server
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
PRINT ' 发生DDL LOGIN. '
PRINT CONVERT ( nvarchar ( 1000),EventData())
GO

-- 测试
CREATE LOGIN login1 WITH PASSWORD = ' 123 '
ALTER LOGIN login1 WITH PASSWORD = ' xyz '
DROP LOGIN login1
SQL Server 2005 在Transaction -SQL上所做的改进反映了其更好地满足了ANSI - 99 SQL规范的要求以及客户的需求。

create proc [ dbo ]. [ Name_Add ]
@Name varchar( 50)
as
begin

begin tran
insert Names (Name)
select ( @Name) where not exists ( select NameId from Names with( HOLDLOCK) where Name = @Name)
commit tran

select NameId,Name from Names with(nolock) where Name = @Name
end

要点：检查，加锁，插入值在一句sql中完成.这样再大的并发也不怕了.



本文转自高海东博客园博客，原文链接：http://www.cnblogs.com/ghd258/archive/2005/10/19/257914.html，如需转载请自行联系原作者
文章标签：
云数据库 RDS SQL Server 版
SQL
数据库
数据格式
XML
OLTP
关键词：
SQL特性
云数据库RDS SQLServer版sql
SQL并发
SQL server
云数据库RDS SQLServer版特性
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
老朱教授
目录
相关文章
java冯坚持
|
2月前
|
SQL 存储 数据库
SQL学习一：ACID四个特性，CURD基本操作，常用关键字，常用聚合函数，五个约束，综合题
这篇文章是关于SQL基础知识的全面介绍，包括ACID特性、CURD操作、常用关键字、聚合函数、约束以及索引的创建和使用，并通过综合题目来巩固学习。
java冯坚持
45 1
Codelinghu
|
2月前
|
SQL 数据库
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
Codelinghu
341 0
游客j4mujezz7vm7y
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
游客j4mujezz7vm7y
38 0
pbeskoyq7ffm4
|
4月前
|
SQL 运维 监控
SQL Server 运维常用sql语句（二）
SQL Server 运维常用sql语句（二）
pbeskoyq7ffm4
40 3
pbeskoyq7ffm4
|
4月前
|
SQL XML 运维
SQL Server 运维常用sql语句（三）
SQL Server 运维常用sql语句（三）
pbeskoyq7ffm4
29 1
vohelon
|
4月前
|
SQL 关系型数据库 MySQL
SQL数据库和 SQLserver数据库
【8月更文挑战第19天】SQL数据库和 SQLserver数据库
vohelon
69 2
土木林森
|
4月前
|
Java 应用服务中间件 Maven
从零到英雄：一步步构建你的首个 JSF 应用程序，揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准，用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets，便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用，包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
土木林森
107 0
土木林森
|
4月前
|
SQL 关系型数据库 MySQL
【超全整理】SQL日期与时间函数大汇总会：MySQL与SQL Server双轨对比教学，助你轻松搞定时间数据处理难题！
【8月更文挑战第31天】本文介绍了在不同SQL数据库系统（如MySQL、SQL Server、Oracle）中常用的日期与时间函数，包括DATE、NOW()、EXTRACT()、DATE_ADD()、TIMESTAMPDIFF()及日期格式化等，并提供了具体示例。通过对比这些函数在各系统中的使用方法，帮助开发者更高效地处理日期时间数据，满足多种应用场景需求。
土木林森
538 0
土木林森
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL：主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异，为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统，均使用SQL作为查询语言，但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同，帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言，但每个系统都有独特的语法规则，了解这些差异有助于提升开发效率和项目成功率。
土木林森
474 0
土木林森
|
4月前
|
SQL 数据库 开发者
SQL事务处理与并发控制：保障数据一致性的关键——深入探索ACID原则、锁定与乐观并发控制策略，以及高级事务管理技巧
【8月更文挑战第31天】在数据库管理和应用开发中，确保数据一致性至关重要。SQL事务处理和并发控制是实现这一目标的关键技术，它们保证了多用户同时访问和修改数据时数据库的一致性和准确性。事务处理遵循ACID原则（原子性、一致性、隔离性和持久性），并发控制则通过锁定和乐观并发控制等策略管理多用户访问，防止数据冲突。本文将深入探讨这些技术的原理与应用，帮助开发者更好地保护数据。
土木林森
69 0

热门文章

最新文章

  • 1
    多台SQLServer数据实时同步，syncnavigator使用
  • 2
    阿里云服务器怎么搭建sql server（即 MS SQL）？
  • 3
    SQLServer CDC数据通过Kafka connect实时同步至分析型数据库 AnalyticDB For PostgreSQL及OSS
  • 4
    使用批处理文件命令行方式快速启动和停止IIS、SqlServer
  • 5
    [推荐] (SqlServer)批量清理指定数据库中所有数据
  • 6
    数据库SQL Server与C#中数据类型的对应关系
  • 7
    SQL Server 查询性能优化 相关文章
  • 8
    SQLSERVER数据库分离作用及附加方法
  • 9
    第十二章——SQLServer统计信息（2）——非索引键上统计信息的影响
  • 10
    SQL Server: Get table primary key and Foreign Key using sql query
  • 1
    SQL脚本相除
    70
  • 2
    0基础学习SQL注入之万能账号密码（BUUctf例题-[极客大挑战 2019]EasySQL1）
    660
  • 3
    SQL脚本利用带关联子查询Update语句更新数据
    135
  • 4
    10分钟了解Flink SQL使用
    390
  • 5
    SQL脚本把多行SQL数据变成一条多列数据
    118
  • 6
    mybatis动态sql
    44
  • 7
    基于若依的ruoyi-nbcio流程管理系统修改代码生成的sql菜单id修改成递增id（谨慎修改，大并发分布式有弊端）
    112
  • 8
    SQL脚本字符串替换
    74
  • 9
    你写的每条SQL都是全表扫描吗
    183
  • 10
    数据库SQL语言实战（六）
    54

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。