ページとエクステントについて

SQL　Serverの基本についてです。開発備忘録では有りませんがとりあえずメモっておきます。

SQL Server のデータ ストレージの基本単位はページです。
エクステントは、物理的に連続する8ページをまとめたもので、すべてのページは、エクステントに格納されます。

[ページ]
　ページの概要は以下のとおりです。
　　・SQL Server では、ページのサイズは 8 KB です。
　　・ページの先頭には 96 バイトのヘッダーがあり、
　　　ページ番号、ページの種類、ページ上の空き容量、そのページを所有しているオブジェクトのアロケーション ユニット ID
　　　が格納されています。
　　・ページの種類は、
　　　　データ・・・・・・・・・・・・すべてのデータが含まれるデータ行。
　　　　インデックス・・・・・・・・インデックスのエントリ。
　　　　テキスト／イメージ・・・LOB (ラージ オブジェクト) データ型、データ行が 8 KB を超える場合の可変長データ列
　　　　の他、各種マップページやページ空き容量ページが有るそうです。
　　・データ行はヘッダーの直後から始まり、ページ上に連続的に配置されます。
　　・ページの末尾から行オフセット テーブルが始まります。
　　　各行オフセット テーブルにはページ上の 1 行につき 1 つのエントリが格納されます。
　　　各エントリには、その行の最初のバイトがページの先頭からどれだけ離れているかが記録されます。
　　　行オフセット テーブル内のエントリは、ページ上の行と逆の順序になっています。
　　・行は複数のページにまたがることができません。
　　・更新操作に基づいてレコードが大きくなると、大きなレコードが別のページに動的に移動されます。
　　・１つのデータ行が 8 KB を超える場合・・・、行の一部をその行のページから移動させます。

　基本的に、1レコードが8Kを超えるような大きなデータを扱わない限り、意識する必要は有りません。
　が、超えてしまう場合、どうなるのか？？
　　・SQL Server 2008 の varchar 型、nvarchar 型、varbinary 型、sql_variant 型、または CLR ユーザー定義型
　　　の列を含むテーブルでは、この制限が緩和されます。
　　　これらの列の長さは、単独の場合は引き続き 8,000 バイトに制限されますが、組み合わせた場合は 8,060 バイトの
　　　制限を超えることができます。
　　・組み合わせた長さがこの制限を超えると、データベース エンジンにより、最大幅のレコード列が ROW_OVERFLOW_DATA
　　　アロケーション ユニット内の別のページに移動されます。
　　　元のページには、24 バイトのポインタが維持されます。
　　・レコードが短くなる更新操作が発生すると、レコードが IN_ROW_DATA アロケーション ユニット内の元のページに移動する
　　　ことがあります。
　　・varchar(MAX)は最大格納サイズが２Gです。この場合はエクステントをチェインして格納するそうです。

　大きなレコードある場合の注意
　　・行オーバーフロー データを含む大きなレコードで、クエリを実行したり並べ替えや結合などの他の選択操作を実行すると、
　　　処理に時間がかかります。これは、これらのレコードが非同期にではなく同期的に処理されるためです。
　　・設計時に行オーバーフロー データの多くの行にクエリが頻繁に実行される可能性が高い場合は、いくつかの列を別の
　　　テーブルに移動して、テーブルのサイズを正規化することを検討します。
　　　これにより、非同期結合操作でクエリを行えるようになります。
　　・クラスタ化インデックスのインデックス キーには、ROW_OVERFLOW_DATA アロケーション ユニットに既存のデータを
　　　持つ varchar 型の列を含めることはできません。クラスタ化インデックスが varchar 型の列に作成され、
　　　既存のデータが IN_ROW_DATA アロケーション ユニットにある場合に、データを行外に押し出すような挿入処理や
　　　更新処理をその列に対して行うと失敗します。

［エクステント］
　　・エクステントは、DBMSが領域を管理する際の基本単位です。
　　・1 つのエクステントは物理的に連続した 8 ページ、つまり 64 KB です。
　　・単一エクステントは、単一のオブジェクトに所有され、所有しているオブジェクトだけがエクステント内の 8 ページすべてを
　　　使用できます。大きなテーブルはエクステントを独占します。
　　・混合エクステントは最大 8 つのオブジェクトによって共有されます。エクステント内の各 8 ページを、それぞれ異なる
　　　オブジェクトが所有できます。小さなテーブルは、複数で１つのエクステントを共有します。

大きなデータを格納する時はパフォーマンスが悪くなる可能性が有るので、そういう場合は、
　　・正規化をする。
　　・LOBデータをファイルシステムに移動
などで、データをコンパクトにすることが肝要です。

なんとなく、まとめてみました。。。

varcharとchar

テーブル設計をする時、varchar（ｎvarchar）かcha(nchar)rか迷う時ありませんか？

私の場合、感覚で決めてしまっているので、良い習慣ではありません。
そこで、少しまとめてみようと思いました。

[格納サイズ]
　charは設定したバイト数がそのまま格納サイズになります。
　varcharは入力されたバイト数+2バイトが格納サイズになります。

　このことから、
　　・列データ エントリが類似したサイズになると想定される場合は、char を使用します。
　　・列データ エントリのサイズがある程度異なると想定される場合は、nvarchar を使用します。

[プログラムから見た取扱のしやすさ]
　プログラミングの理由で、DB設計が影響を受けることは余り好ましくないというのが前提。でも少しだけ。
　　SQLで取り扱う場合、データ長を合わせてあると思いますので、問題に成りませんが、
　　・charはデータ長が足らない場合は空白で埋めるため、使用する際に空白が不要な場合はトリムする必要がある。
　　・上と同じ理由から、検索する際も空白で埋めてからDBへ渡す必要がある。
　　・データ長が将来拡張することが微妙に予定されている場合、varcharの方が考慮しなくて済みそう。

　　私はvarcharに1票入れます。

[パフォーマンス]
　検索パフォーマンスについては、実際さっぱり判りません＾＾；
　基本的にはDBがメモリに読み込むデータ長と検索対象のテーブルのレコード長の方がより影響が大きいと考えられます。
　結果、どちらを採用しても最適化する工程が必要ということです。

　更新パフォーマンスについてもほぼ同じ考えです。
　１つの列だけでパフォーマンスは決まらないので、全体で考える必要があると思いますので。
　charの場合、not　NULLにしておけば、データ更新時にあふれが発生しませんので少し有利かともおもいます。
　varcharの場合は、更新時にデータがきちきちに詰まっているとあふれが発生します。
　その際は、別のブロックに移動するためパフォーマンスが低下する可能性が高くなります。

　一度に大きなデータを処理するなら、考慮する必要が有るかも知れません。
　しかし、そうなるとやはり全体最適化となるので。。。良く分かりません＾＾；

結果は、varcharが基本で入力データが固定長の場合のみcharが良いかな。。。と思います。

ユーザ定義関数について

SQL Serverでは、ユーザ定義関数と予め組み込まれたシステム関数がサポートされます。
今日の開発備忘録は、ユーザ定義関数です。

ユーザ定義関数は、もう少し細かく分けることができ、スカラー値関数とテーブル値関数と呼ばれています。
機能は、読んで字の如く「スカラー値」を返す関数と、「テーブル値」を返す関数となります。

どちらの関数も、CREATE FUNCTION句で関数名を宣言し、RETURNS句で戻り値を宣言し、RETURNで値を返します。
少しですが違うので具体的に見ていきます。

１）スカラー値関数
スカラー値関数は、MSDNではスカラー関数と記載されているのスカラー関数が正しいのかもしれません。
この関数の戻り値には、text、ntext、image、cursor、および timestamp 以外の任意のデータ型を指定できます。
多くは、数値(int)だったり名称(varchar)だったりだと思います。
先ほども書きましたがこの関数は、RETURNS 句で返す型を定義します。
RETURNで定義した型の単一のデータ値を返します。

具体的には、以下のような感じです。

--スカラー関数FN_TEST1が有れば消す
if OBJECT_ID (N'FN_TEST1',N'FN') is NOT NULL
DROP FUNCTION FN_TEST1
go

--テスト用テーブルT_FN_TESTが有れば消す
if OBJECT_ID (N'T_FN_TEST',N'U') is NOT NULL
Drop table T_FN_TEST
go

--テスト用テーブルT_FN_TEST作成＆データセット
create table T_FN_TEST(
paramID int,
v_num int
)

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

go

--スカラー関数FN_TEST1を作成
CREATE FUNCTION FN_TEST1
(
@param1 int
)
RETURNS int
AS
BEGIN
DECLARE @ret int;

SELECT @ret = SUM(v_num)
FROM T_FN_TEST t
WHERE t.paramID >= @param1

IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END

go

--スカラー関数FN_TEST1の呼び出しテスト
declare @rv int
select @rv = [dbo].[FN_TEST1](3)
print @rv
go

--テスト用テーブルT_FN_TESTを消す
drop table T_FN_TEST

--スカラー関数FN_TEST1を消す
DROP FUNCTION FN_TEST1

２）テーブル値関数
ユーザー定義テーブル値関数は、table データ型を返します。
この関数もRETURNS句で戻り値を宣言しますが、
・インライン　テーブル値関数
・複数のステートメントのテーブル値関数
で宣言とロジックが少し異なります。
ちなみに、table データ型の値を返すこの関数は、ビューとストアードプロシージャの中間のようなツールです。
ビューに比べると、まず同じようにFROM句に使用できる。ビューよりも高度SQLロジックが組める。
ストアードプロシージャに比べると、ストアードプロシージャはFROM句に使用できないので決定的に違います。
ただ、テーブル値関数は、既存のデータに関してUPDATE句は使用できませんしCREATE句も使用できません。
あくまでも参照用ってことですね。関数内で宣言したRETURNS句テーブルに関してはUPDATEもできます。

では、具体的な宣言方法へ

A.インライン　テーブル値関数
RETURNS TABLEと宣言し、SELECT　SQLを書くだけです。
この場合、SELECTで抽出したテーブルの定義がそのまま値として返ります。

--スカラー関数FN_TEST2が有れば消す
if OBJECT_ID (N'FN_TEST2',N'IF') is NOT NULL
DROP FUNCTION dbo.FN_TEST2
go

--テスト用テーブルT_FN_TESTが有れば消す
if OBJECT_ID (N'T_FN_TEST',N'U') is NOT NULL
Drop table T_FN_TEST
go

--テスト用テーブルT_FN_TEST作成＆データセット
create table T_FN_TEST(
paramID int,
v_num int
)

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

go

--テーブル値関数FN_TEST2の作成
CREATE FUNCTION dbo.FN_TEST2
(
@param1 int
)
RETURNS TABLE
AS
RETURN
(
SELECT * from T_FN_TEST t
where t.paramID > @param1
)
go

--スカラー関数FN_TEST2の呼び出しテスト
select * from dbo.FN_TEST2(2)

select sum(v_num) from dbo.FN_TEST2(2)
--テスト用テーブルT_FN_TESTを消す
drop table T_FN_TEST

--スカラー関数FN_TEST2を消す
DROP FUNCTION FN_TEST2

B.複数のステートメントのテーブル値関数
複数の処理で返す値を整えていきます。
テーブルAから10行入れて、テーブルBから更に10行追加して、最後に一部の項目を更新するなどなど。
以下のように宣言します。

--スカラー関数FN_TEST3が有れば消す
if OBJECT_ID (N'FN_TEST3',N'TF') is NOT NULL
DROP FUNCTION dbo.FN_TEST3
go

--テスト用テーブルT_FN_TESTが有れば消す
if OBJECT_ID (N'T_FN_TEST',N'U') is NOT NULL
Drop table T_FN_TEST
go

--テスト用テーブルT_FN_TEST作成＆データセット
create table T_FN_TEST(
tableID int,
R1 int,
R2 int,
R3 int,
R4 int,
R5 int,
R6 int
)

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

go

--テーブル値関数FN_TEST3の作成
CREATE FUNCTION dbo.FN_TEST3
(
@param1 int,
@param2 int,
@param3 int,
@param4 int
)
RETURNS @rtnTable TABLE
(
R1 int NOT NULL,
R2 int,
R3 int,
R4 int,
R5 int,
R6 int
)
AS
begin
insert into @rtnTable
SELECT t1.tableID, t1.R2, t1.R3, t1.R4, null, null from T_FN_TEST t1
where t1.tableID > @param1

update @rtnTable
set R5=R2 * @param2

insert into @rtnTable
SELECT t2.tableID, t2.R2, t2.R3, t2.R4, null, null from T_FN_TEST t2
where t2.tableID > @param3

update @rtnTable
set R6=R3 * @param4

return
end
go

--スカラー関数FN_TEST3の呼び出しテスト
select * from dbo.FN_TEST3(1,2,3,4)

select sum(R2) from dbo.FN_TEST3(1,2,3,4)
--テスト用テーブルT_FN_TESTを消す
drop table T_FN_TEST

--スカラー関数FN_TEST3を消す
DROP FUNCTION FN_TEST3

「SQL SERVER 2012」が発売されました。
TechNetでは事前にダウンロードが出来ましたので、私も早速ダウンロードをしてましたが。。。インストールできるPCが無い＾＾；
今のところ特に使用する予定は無いけど、新しい物は試したくなりますよね。

「SQL　SERVER　2008　R2」を削除してインストールする予定です＾＾

本番データをちょっと更新・・・でも、バックアップしないと
という時に。
バックアップが既に存在していればスキップ。存在してなければ作成。

–ちょっと便利な日付付きバックアップ
declare @exec_str varchar(256)
declare @targettable char(20)
set @targettable = ‘Table1’ + convert(char(8) ,getdate(), 112)

if not exists(select * from sysobjects where name=@targettable)
 begin
  print ‘未作成’
  set @exec_str=’select * into ‘ + @targettable + ‘ from dbo.Table1’
  print @exec_str
  exec (@exec_str)
  –直接実行でも可
  –exec (‘select * into ‘ + @targettable + ‘ from dbo.Table1’)
 end
else
 begin
  print ‘作成済’
 end

[SQL SERVER] 変数を使用したテーブルの利用方法

ちょっと便利なSQLを紹介します。
変数を使用したテーブルです。テーブル変数とでもいうのかな？
ちょっとしたテストで良く使っています。
覚えておくととっても便利です。

ブラウザで表示すると、シングルコーテションがおかしくなるかも。
コピペするときには修正してください。

--変数テーブルの作成&INSERT
declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)

declare @tbl2 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000010','2',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl2 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)
--変数テーブルを使用する例（SELECT)
select *
from @tbl1 S1
join @tbl2 S2
on S1.A_CD=S2.a_cd
and S2.b_kbn=S2.b_kbn
--変数テーブルの値を更新する（UPDATE)
update @tbl1 set c_kbn+=1
--確認用SELECT
select * from @tbl1
--変数テーブルの値を削除する（DELETE)
delete from @tbl1 where b_kbn='1'
--確認用SELECT
select * from @tbl1

もちろん変数なので同じセッション中のみ有効です。
また、セッションが切れたら無くなります。