SQL Server 2008新特性総括復習(一)
8175 ワード
1.TVP、テーブル変数、テンポラリテーブル、CTEの違い
TVPとテンポラリ・テーブルはインデックス可能で、常にtempdbに存在し、システム・データベースのオーバーヘッドが増加しますが、テーブル変数とCTEはメモリオーバーフロー時にのみtempdbに書き込まれます.データ量が多く、かつ繰り返し使用し、クエリー関連を繰り返し行う場合は、一時テーブルまたはTVPを使用することを推奨し、データ量が小さく、テーブル変数またはCTEを使用するのが適切である
2. sql_variant万能タイプ
すべてのデータ型を格納できます.C#のobjectデータ型に相当します.
3. datetime, datetime2, datetimeoffset
datetimeの有効期間は小さく、1753-1-1までは使用できません.精度はミリ秒レベルですが、datetime 2のデータ範囲はC#のdatetimeに相当し、精度は秒後の小数点以下7桁に達し、datetimeoffsetはタイムゾーンを考慮した日付タイプです.
4.MERGEの使い方
文法は簡単ではありませんが、主に2つのテーブルのいくつかのフィールドを比較した後の操作を処理します.when not matched(by target)とwhen not matched by sourceの違いに注意してください.前者は比較後のターゲットテーブルが存在しない記録に対して、insert操作を選択することができます.後者は比較後のターゲットテーブルが多く出た記録に対して、deleteまたはupdate操作を選択することができます.
5.rowversionタイプ
従来のtimestamp,タイムスタンプ,8バイトバイナリ値の代わりに,同時操作の問題を解決するためによく用いられていた.
6. Sysdatetime()
datetime 2タイプを返します.精度はdatetimeより高いです.
7.with cube,with rollup,grouping sets演算子
いずれもgroupbyと併用でき、with cubeはすべてのレベルを要約する組合せを表し、with rollupはレベル別に要約され、以下のコードから違いが詳しくわかります.注意、要約行、nullはすべての値と見なすことができます.
一方grouping sets演算子は、各パケットの上位要約行のみを返し、クエリー要約行ではgrouping(ワードセグメント名)=1を使用して判断できます.この演算子はrollup、cubeと連用でき、grouping by setsとrollup/cubeで処理された結果セットunion allを表します.
サンプルコードは次のとおりです.
8.Declare@id int=0などのショートカット構文
ショートカットの場合もありますが、DBAでは推奨されていません.Declare@id=select top 1 id fromテーブル名は、宣言とルックアップの割り当てが別々であることを推奨します.
9.共通表現CTE
特徴:ネストして使用でき、結合テーブルのサブクエリの代わりに、構造階層がより明確になり、再帰クエリにも使用できます.また、巧みな定数列で再帰階層を制御します.
サンプルコードは次のとおりです.
10.pivotとunpivot
前者は行で列を回します.注意:PIVOTと一緒に集約関数を使用する必要があります.パーティーを計算するときは、値列に表示される空の値は考慮されません.一般的には、pivot文を列のサブクエリで置き換えることができますが、効率的ではありません.
後者はカラムの移行に使用されます.注意:一部のカラムにnull値がある場合は、フィルタリングされ、新しいローは生成されません.文法上、Forの前に指定された新しい列は、元のテーブルに対応して列名の値を指定し、Forの後に指定された新しい列は、元のテーブルに対応して列名の見出しの値を指定します.
両方に共通性があります.文法的には最後に別名が必要です.INで指定したカラムタイプは一致している必要があります.
サンプルコードは次のとおりです.
TVPとテンポラリ・テーブルはインデックス可能で、常にtempdbに存在し、システム・データベースのオーバーヘッドが増加しますが、テーブル変数とCTEはメモリオーバーフロー時にのみtempdbに書き込まれます.データ量が多く、かつ繰り返し使用し、クエリー関連を繰り返し行う場合は、一時テーブルまたはTVPを使用することを推奨し、データ量が小さく、テーブル変数またはCTEを使用するのが適切である
2. sql_variant万能タイプ
すべてのデータ型を格納できます.C#のobjectデータ型に相当します.
3. datetime, datetime2, datetimeoffset
datetimeの有効期間は小さく、1753-1-1までは使用できません.精度はミリ秒レベルですが、datetime 2のデータ範囲はC#のdatetimeに相当し、精度は秒後の小数点以下7桁に達し、datetimeoffsetはタイムゾーンを考慮した日付タイプです.
4.MERGEの使い方
文法は簡単ではありませんが、主に2つのテーブルのいくつかのフィールドを比較した後の操作を処理します.when not matched(by target)とwhen not matched by sourceの違いに注意してください.前者は比較後のターゲットテーブルが存在しない記録に対して、insert操作を選択することができます.後者は比較後のターゲットテーブルが多く出た記録に対して、deleteまたはupdate操作を選択することができます.
5.rowversionタイプ
従来のtimestamp,タイムスタンプ,8バイトバイナリ値の代わりに,同時操作の問題を解決するためによく用いられていた.
6. Sysdatetime()
datetime 2タイプを返します.精度はdatetimeより高いです.
7.with cube,with rollup,grouping sets演算子
いずれもgroupbyと併用でき、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]))