MS SQL Server 2008新增功能示例

 

 

---------------------cte通用表达式示例-----------------------------

with
obj as (
 select type from sys.objects
)
select * from obj
pivot(
 count(type)
 for type
 in(u,v,p)
)p
-----------------------------------

;with
obj as (
 select type from sys.objects
),p as(
 select *
 from obj
 pivot
 (
  count(type)
  for type
  in(u,v,p)
 )p
)
select *
from p
unpivot(
 count
 for type
 in(u,v,p)
)up
-----------------------------------
select u,v,p from sys.objects
pivot(
 count(type)
 for type
 in(u,v,p)
)p

-----------------------------------
select SUM(case when type =N'U' then 1 else 0 end) as Uds, SUM(case when type =N'V' then 1 else 0 end) as Vds,SUM(case when type =N'P' then 1 else 0 end) as Pds
from sys.objects

 

 

Declare @tbname sysname,@sql varchar(100),@sqlN nvarchar(4000)
Set @sqlN = 'select top 1 @tbname = name From sys.objects'
Set @sql = 'select top 1 @tbname = name From sys.objects'
--Exec sp_executesql @sqlN,N'@tbname sysname Output',@tbname output
Exec (@sql)
select @tbname

--1\HAVING

select cnt = COUNT(OBJECT_ID)
from sys.objects
where object_id<10
having COUNT(object_id) <>10

select OBJECT_ID,
  cnt = COUNT(*)
From sys.columns
where OBJECTPROPERTY(object_id,'Istable') =1
group by object_id having COUNT(*) > 10

--2、Rollup 为每级数据生成合计和小计

with
TB as
(
 select Item = 'Table',Color = 'Read',Quantity = 124 union all
 select Item = 'Table',Color = 'Blue',Quantity = 23 union all
 select Item = 'Chair',Color = 'Read',Quantity = 101 union all
 select Item = 'Chair',Color = 'Blue',Quantity = 34
)
select Item,color,Quantity = SUM(Quantity)
From TB
Group by Item,Color
 with Rollup
 

--Grouping 函数来判断结果中的NULL

with
TB as
(
 select Groups = 'a',Item = 'Table',Color = 'Read',Quantity = 124 union all
 select Groups = 'b',Item = 'Table',Color = 'Blue',Quantity = 23 union all
 select Groups = 'b',Item = 'cup',Color = 'Green',Quantity = 35 union all
 select Groups = 'a',Item = 'Chair',Color = 'Read',Quantity = 101 union all
 select Groups = 'a',Item = 'Chair',Color = 'Blue',Quantity = 34
)
select Groups,Item,color,Quantity = SUM(Quantity),
  Groups_Flag = GROUPING(Groups),
  Item_Flag = GROUPING(Item),
  Color_Flag = GROUPING(Color) 
From TB
Group by Groups,Item,Color
 with Rollup
 

 

 

with
TB as
(
 select Groups = 'a',Item = 'Table',Color = 'Read',Quantity = 124 union all
 select Groups = 'b',Item = 'Table',Color = 'Blue',Quantity = 23 union all
 select Groups = 'b',Item = 'cup',Color = 'Green',Quantity = 35 union all
 select Groups = 'a',Item = 'Chair',Color = 'Read',Quantity = 101 union all
 select Groups = 'a',Item = 'Chair',Color = 'Blue',Quantity = 34
)
select Groups,Item,color,Quantity = SUM(Quantity),
  Groups_Flag = GROUPING(Groups),
  Item_Flag = GROUPING(Item),
  Color_Flag = GROUPING(Color) 
From TB
Group by Groups,Item,Color
 with Rollup

 

 

 

with
TB as
(
 select Groups = 'a',Item = 'Table',Color = 'Read',Quantity = 124 union all
 select Groups = 'b',Item = 'Table',Color = 'Blue',Quantity = 23 union all
 select Groups = 'b',Item = 'cup',Color = 'Green',Quantity = 35 union all
 select Groups = 'a',Item = 'Chair',Color = 'Read',Quantity = 101 union all
 select Groups = 'a',Item = 'Chair',Color = 'Blue',Quantity = 34
)

select
  Groups = case
    when GROUPING(Color) = 0 then Groups
    when GROUPING(Groups) = 1 then '总计'
    else '' end,
  Item = case
    when GROUPING(Color) = 0 then Item
    when GROUPING(Item) = 1 and GROUPING(Groups) = 0 then Groups +'小计'
    else '' end,
  Color = case
    when Grouping(Color) = 0 then Color
    when GROUPING(Color) = 1 and GROUPING(Item) = 0 then Item +'小计'
    else '' end,
  Quantity = SUM(Quantity)
  
From tb
Group by Groups,Item,Color
with Rollup
Having GROUPING(Item) = 0 or GROUPING(Groups) = 1

 

ROW_NUMBER (Transact-SQL)

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

 

 

---------------------top示例-----------------------------

declare @tb table (ID int)
Insert top (5) @tb
select OBJECT_ID from sys.objects
order by object_id

Delete top (@@ROWCOUNT - 2)
From @tb

Update top (Case @@ROWCOUNT when 3 then 2 else 0 end) A set id = id +1
from @tb A

select * from @tb

with
LIB as
(
 select type = 's',showcount = 2 union all
 select type = 'v',showcount = 1 union all
 select TYPE_ID = 'p',showcount = 3
)
select LIB.*,o.*
from LIB
 cross apply
 (
  select top (LIB.showcount)
    name,create_date
  from sys.objects
  order by create_date
 ) as o

 

--索引功能增强

Create Index IX_col on dbo.tb_indexTest(col)

go

--禁用索引

Alter Index IX_col on dbo.tb_indexTest Disable
go

--禁用所有索引（包括主键）

Alter Index all on dbo.tb_indexTest Disable

go

--禁用主键后，访问基础表会失败

select * from tb_indexTest

go

--启用索引

Alter Index all on dbo.tb_indexTest Rebuild
go

--重建组织索引 IX_COl

Alter Index IX_Col on dbo.tb_indexTest REORGANIZE
GO