欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server 2008 新特性 总结复习(一)

程序员文章站 2022-05-28 12:15:36
1. tvp, 表变量,临时表,cte 的区别 tvp和临时表都是可以索引的,总是存在tempdb中,会增加系统数据库开销,而表变量和cte只有在内存溢出时才会被写入tem...
1. tvp, 表变量,临时表,cte 的区别
tvp和临时表都是可以索引的,总是存在tempdb中,会增加系统数据库开销,而表变量和cte只有在内存溢出时才会被写入tempdb中。对于数据量大,并且反复使用,反复进行查询关联的,建议使用临时表或tvp,数据量小,使用表变量或cte比较合适

2. sql_variant 万能类型
可以存放所有数据类型,相当于c#中的object数据类型

3. datetime, datetime2, datetimeoffset
datetime 时间有效期较小,在1753-1-1 之前就不能使用了,精度为毫秒级别,而datetime2 数据范围相当于c#中的datetime ,精度达到了秒后面小数点后7位,datetimeoffset则是考虑是时区的日期类型

4. merge的用法
语法很简单就不说了,主要是处理两张表某些字段对比后的操作,需注意 when not matched (by target) 与 when not matched by source的区别,前者是是针对对比后目标表不存在的记录,可以选择insert操作,而后者则是针对对比后目标表多出来的记录,可以选择delete或update操作
5. rowversion 类型
代替以前的timestamp,时间戳,8字节二进制值,常用来进行解决并发操作的问题

6. sysdatetime()
返回datetime2类型,精度比datetime高

7. with cube , with rollup , grouping sets 运算符
都可与group by 后连用,with cube 表示汇总所有级别的组合,with rollup 则是按级别汇总,从下面的代码可以详细看出区别。注意,汇总行,null可以看成所有值
而grouping sets运算符,则仅返回每个分组*汇总行,在查询汇总行中 可使用grouping(字段名) = 1来判断,该运算符可和rollup, cube连用,表示按照grouping by sets和按照rollup/cube处理的结果集union all
示例代码如下:
复制代码 代码如下:

with cube, with rollup
--示例代码
declare @t table(goodsname varchar(max) ,sku1name varchar(max) , sku2name varchar(max), qty int)
insert @t select '凡客tx','红色','s',1
insert @t select '凡客tx','黑色','s',2
insert @t select '凡客tx','白色','l',3
insert @t select '京东村山','白色','l',4
insert @t select '京东村山','红色','s',5
insert @t select '京东村山','黑色','l',6
insert @t select '亚马逊拖鞋','白色','l',7
insert @t select '亚马逊拖鞋','红色','s',8
select * from @t
select goodsname,sku1name,sku2name,sum(qty) sumqty
from @t
group by goodsname,sku1name,sku2name with rollup
order by goodsname,sku1name,sku2name

select goodsname,sku1name,sku2name,sum(qty) sumqty
from @t
group by goodsname,sku1name,sku2name with cube
order by goodsname,sku1name,sku2name
-----------------------
declare @t table(goodsname varchar(max) ,sku1name varchar(max) , sku2name varchar(max), qty int)
insert @t select '凡客tx','红色','s',1
insert @t select '凡客tx','黑色','s',2
insert @t select '凡客tx','白色','l',3
insert @t select '京东村山','白色','l',4
insert @t select '京东村山','红色','s',5
insert @t select '京东村山','黑色','l',6
insert @t select '亚马逊拖鞋','白色','l',7
insert @t select '亚马逊拖鞋','红色','s',8

--grouping sets 运算符
select goodsname,sku1name,sku2name, sum(qty) from @t group by grouping sets(goodsname,sku1name,sku2name)
select goodsname, sku1name, sku2name ,sum(qty) from @t
group by grouping sets(goodsname), rollup(sku1name,sku2name)
order by goodsname,sku1name,sku2name
select goodsname, sku1name, sku2name ,sum(qty) from @t
group by rollup(goodsname,sku1name,sku2name)
order by goodsname,sku1name,sku2name
select case when grouping(goodsname) = 1 then '[all]' else goodsname end goodsname,
case when grouping(sku1name) = 1 then '[all]' else sku1name end sku1name,
case when grouping(sku2name) = 1 then '[all]' else sku2name end sku2name ,sum(qty) from @t
group by grouping sets(goodsname), rollup(sku1name,sku2name)
order by goodsname,sku1name,sku2name

8. 一些快捷的语法 例如 declare @id int = 0

虽然有时很快捷,但dba不建议这样使用,declare @id = select top 1 id from 表名,建议声明和查表赋值分开

9. 公用表达式 cte

特点:可嵌套使用,代替联接表中的子查询,结构层次更加清晰,也可用来递归查询,另外通过巧妙的常量列控制递归层次

示例代码如下:
复制代码 代码如下:

使用cte

--公用表达式cte common table expression

--用cte实现递归算法

create table employeetree(
employee int primary key,
employeename nvarchar(50),
reportsto int
)

insert into employeetree values(1,'richard',null)
insert into employeetree values(2,'stephen',1)
insert into employeetree values(3,'clemens',2)
insert into employeetree values(4,'malek',2)
insert into employeetree values(5,'goksin',4)
insert into employeetree values(6,'kimberly',1)
insert into employeetree values(7,'ramesh',5)

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

--确定哪些员工向stephen报告的递归查询
with employeetemp as
(
select employee, employeename, reportsto from employeetree where employee = 2
union all
select a.employee, a.employeename, a.reportsto from employeetree as a
inner join employeetemp as b on a.reportsto = b.employee
)
select * from employeetemp where employee <> 2 --option(maxrecursion 2)


--不报错设置级联关联递归
with employeetemp as
(
select employee, employeename, reportsto,0 as sublevel from employeetree where employee = 2
union all
select a.employee, a.employeename, a.reportsto,sublevel+1 from employeetree as a
inner join employeetemp as b on a.reportsto = b.employee
)
select * from employeetemp where employee <> 2 and sublevel <=2 --option(maxrecursion 2)

10. pivot 与 unpivot

前者用在行转列,注意:必须用聚合函数与pivot一起使用,计算聚会时将不考虑出现在值列中的任何空值;一般情况下,可以用列上的子查询来替换pivot语句,但是这样做效率不高

后者用在列转行,注意:如果某些列中有null值,将会被过滤掉,不产生新行;语法上for前指定的新列,对应原表指定列名中的值,for后指定的新列对应原表指定列名中的标题的值

两者都有的共性:语法上最后必须要有别名;in里面指定的列类型必须是一致的。

示例代码如下:
复制代码 代码如下:

pivot与unpivot

--关于pivot的操作

create table #test
(
name varchar(max),
score int
)

insert into #test values ('张三','97')
insert into #test values ('李四','28')
insert into #test values ('王五','33')
insert into #test values ('神人','78')

--name score
--张三 97
--李四 28
--王五 33
--神人 78

--行转列
select --'成绩单' as scorename ,
[张三], [李四], [王五]
from #test
pivot (avg(score) for name in ([张三], [李四], [王五])) b


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

create table vendoremployee(
vendorid int,
emp1order int,
emp2order int,
emp3order int,
emp4order int,
emp5order int,
)

go

insert into vendoremployee values(1,4,3,5,4,4)
insert into vendoremployee values(2,4,1,5,5,5)
insert into vendoremployee values(3,4,3,5,4,4)
insert into vendoremployee values(4,4,2,5,4,4)
insert into vendoremployee values(5,5,1,5,5,5)

select * from vendoremployee

----------------
--列转行

select * from (
select vendorid,[emp1order],[emp2order],[emp3order],[emp4order],[emp5order] from vendoremployee) as unpiv
unpivot (orders for elyid in ([emp1order],[emp2order],[emp3order],[emp4order],[emp5order])) as child
order by elyid

select * from vendoremployee
unpivot (orders for elyid in ([emp1order],[emp2order],[emp3order],[emp4order],[emp5order])) as child
order by elyid

select * from vendoremployee unpivot ( orders for [操作员名字] in ([emp1order],[emp2order],[emp3order],[emp4order],[emp5order]))