[SQL Server] 2012 データベース レベルでの FILESTREAM ファイル グループの指定

SQL Server 2012の機能のFILETABLEを使用するためには、
予め「インスタンス レベルでの FILESTREAM の有効化」 を実施した後、
データベース レベルでの FILESTREAM ファイル グループの指定が必要になります。
FILESTREAM は特殊なファイル グループを使用するので、データベースの作成時に少なくとも 1 つのファイル グループに対して CONTAINS FILESTREAM 句を指定する必要があるからです。

新規でDATABASEを作成する場合は、CREATE DATABASEからも指定できます。
下の例は、TEST2というDATABASEを作成し、FileStreamGroup1 というFILESTREAM ファイル グループを指定しました。
CREATE DATABASE TEST2
ON
PRIMARY ( NAME = TEST2,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TEST2.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = TEST2fs,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\filestream2’)
LOG ON ( NAME = TEST2log1,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TEST2log1.ldf’)
GO

既存のDATABASEに追加する場合は、ALTER DATABASEを使用します。
下の例は、TEST3という既存のDATABASEに対し、
■FileStreamGroup3 というFILESTREAM ファイル グループを指定し、
ALTER DATABASE TEST3
ADD FILEGROUP FileStreamGroup3 CONTAINS FILESTREAM

■そのファイルストリームにfilestream3というファイルを追加しましましす。
ALTER DATABASE TEST3
ADD FILE
( NAME = ‘TEST3fs’,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\filestream3’
)
TO FILEGROUP FileStreamGroup3

ALTER DATABASEを使用しないで、データベースのプロパティ設定画面からも指定することもできます。
まずは、目的のDATABASEのプロパティ画面を開きます。
■ファイルグループを追加

1)左側メニューの「ファイルグループ」をクリック
2)FILESTREAMの追加をクリック
3)名前を入力
4)ファイルグループを追加できたので、次のファイル指定をするために左側メニューの「ファイル」をクリック
■ファイルの追加

1)データベースファイルを追加する為に、右下の「追加」をクリック
2)新たに行が追加されるので、その行のファイルの種類をFILESTREAMに指定
3)ファイルグループの指定は、先程上で作成したファイルグループを選択
4)パスを指定
5)最後に「OK」をクリック

以上で、DATABASEにFILESTREAM ファイル グループを指定することができました。

[SQL Server] 2012 FILETABLEを使うためにFILESTREAMの有効化

SQL Server 2012の機能のFILETABLEを使用するためには、
予め「インスタンス レベルでの FILESTREAM の有効化」 が必要になります。

その設定方法の備忘録です。

  1. [スタート] ボタンをクリックし、[すべてのプログラム]、[Microsoft SQL Server コードネーム 2012]、[構成ツール] の順にポイントして、[SQL Server 構成マネージャー] をクリックします。
  2. サービスの一覧で、[SQL Server のサービス] を右クリックし、[開く] をクリックしす。
  3. [SQL Server 構成マネージャー] スナップインで、FILESTREAM を有効にする ためにSQL Server のインスタンスを探します。画像では、上から3番目のSql Server(MSSQLSERVER)
  4. インスタンスを右クリックし、[プロパティ] をクリックしSql Server(MSSQLSERVER)のプロパティ画面を開きます。
  5. [SQL Server のプロパティ] ダイアログ ボックスで、[FILESTREAM] タブをクリックします。
  6. [Transact-SQL アクセスに対して FILESTREAM を有効にする] チェック ボックスをオンにします。
  7. Windows から FILESTREAM データの読み取りと書き込みを行う場合は、[ファイル I/O ストリーム アクセスに対して FILESTREAM を有効にする] をクリックします。 Windows 共有の名前を [Windows 共有名] ボックスに入力します。
  8. この共有に格納された FILESTREAM データにリモート クライアントからアクセスする必要がある場合は、[リモート クライアントに FILESTREAM データへのストリーム アクセスを許可する] を選択します。
  9. [適用] もしくは[OK] をクリックします。Sql Server(MSSQLSERVER)のプロパティ画面の設定はここまでです。
  10. SQL Server Management Studioを開き、[新しいクエリ] をクリックしてクエリ エディターを表示します。
  11. クエリ エディターで、次の Transact-SQL コードを入力します。
    Transact-SQLEXEC sp_configure filestream_access_level, 2
    RECONFIGURE
  12. [実行] をクリックします。
  13. SQL Server サービスを再開します。

以上で、「インスタンス レベルでの FILESTREAM の有効化」が出来ました。
尚、データベース レベルで以下の設定が必要です。
  ・データベース レベルでの FILESTREAM ファイル グループの指定
  ・データベース レベルでの非トランザクション アクセスの有効化
  ・データベース レベルでの FileTable のディレクトリ指定

[SQL Server] ページとエクステントについて

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

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

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

[ページ]
 ページの概要は以下のとおりです。
  ・SQL Server では、ページのサイズは 8 KB です。
  ・ページの先頭には 96 バイトのヘッダーがあり、
   ページ番号、ページの種類、ページ上の空き容量、そのページを所有しているオブジェクトのアロケーション ユニット ID
   が格納されています。
  ・ページの種類は、
    データ・・・・・・・・・・・・すべてのデータが含まれるデータ行。
    インデックス・・・・・・・・インデックスのエントリ。
    テキスト/イメージ・・・LOB (ラージ オブジェクト) データ型、データ行が 8 KB を超える場合の可変長データ列
    の他、各種マップページやページ空き容量ページが有るそうです。
  ・データ行はヘッダーの直後から始まり、ページ上に連続的に配置されます。
  ・ページの末尾から行オフセット テーブルが始まります。
   各行オフセット テーブルにはページ上の 1 行につき 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)は最大格納サイズが2Gです。この場合はエクステントをチェインして格納するそうです。

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

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

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

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

[ASP.NET] Interop.ActiveDsの読込エラー

エラー対応の開発備忘録です。

[環境]
 windows7(32bit版)でVS2010を使用してWebアプリを開発。
 Windows Server 2003(64bit版)で稼働させる。

[現象]
 デバッグも終わり、サーバへリリースしたところ、下のエラーが発生。
  「ファイルまたはアセンブリ ‘Interop.ActiveDs’、またはその依存関係の 1 つが読み込めませんでした。間違ったフォーマットのプログラムを読み込もうとしました。」

[対応]
 まず、’Interop.ActiveDs’ってなんだっけ?から
 名前からして、ActiveDirectoryか?
 とりあえず、それらしいものを参照から外そうかと思いましたが、使用しているので。。。無理^^;

 で、ネット検索!
 さっぱりわからないのですが、どうやらWOW64が関係していそう。
 そこで、ターゲットCPUを64bitに変更してみることに。
 1)現在はAnyCPU
 2)▼をクリックして、「構成マネージャー」を表示
 3)Any CPU →x64に・・・・選択肢にない!?
 4)<新規作成..>を選択して作成することに。 設定のコピー元って?。。。
   わからなかったのでとりあえず<空>で
 5)OKをクリックして、Any CPU →x64に
 6)再コンパイル
 7)実行して動作を確認
 8)再度リリース!
 9)無事、動作を確認^^

結果、良くわからないままですが、なんとか動きましたとさ^^

[SQL Server] varcharとchar

varcharとchar

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

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

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

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

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

  私はvarcharに1票入れます。

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

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

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

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

[SQL Server] ユーザ定義関数について

ユーザ定義関数について

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

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

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

1)スカラー値関数
スカラー値関数は、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

2)テーブル値関数
ユーザー定義テーブル値関数は、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

[ふと思ったこと] TeamViewerとは?

本日、久々にTeamViewerというアプリの話を耳にしました。

このアプリは、PCを遠隔操作するためのものです。
個人利用ならただで使用できます。

詳細な使用方法は、TeamViewerで検索するといろいろ出てくるので、困ることはないと思います。

なかなか、使用する機会はないのだけど、面白いアプリです。
ちなみに、iPhoneからでも遠隔操作できるみたいです。

[ASP.NET] FileUpload Web サーバー コントロールを使用してファイルをアップロードする

Webアプリケーションでファイルを取得するプログラムの備忘録です。

  1. FileUpload コントロールをページに追加します。
     セキュリティ上の理由から、ファイル名を FileUpload コントロールにあらかじめロードすることはできません。
  2. ページの Load イベントなどのイベントのハンドラでは、次の処理を行います。
    1. FileUpload コントロールがアップロードされたファイルを持つことを、その HasFile プロパティをテストすることで確認します。
    2. ファイルの名前または MIME の種類を確認し、受け入れるファイルをユーザーがアップロード済みであることを確認します。
        MIME の種類を確認するには、FileUpload コントロールの PostedFile プロパティとして公開されている HttpPostedFile オブジェクトを取得します。
        その後、ポストされたファイルの ContentType プロパティを確認することによって MIME の種類を取得できます。
    3. 指定した場所にファイルを保存します。
        HttpPostedFile オブジェクトの SaveAs メソッドを呼び出すことができます。
        または、HttpPostedFile オブジェクトの InputStream プロパティを使用してアップロードされたファイルをバイト配列またはストリームとして管理することもできます。

例)このコードではアップロードされたファイルの拡張子を、指定した拡張子リストと照らし合わせ、OKであれば保存処理へ進みます。
  保存は現在の Web サイトのUploadedImagesフォルダを指定しています。
  アップロードされたファイルは、クライアントコンピュータに存在していたときと同じ名前で保存されます。変更も可能です。
  HttpPostedFile オブジェクトのFileNameプロパティはファイルのクライアント コンピュータでの絶対パスを返すため、FileUpload コントロールのFileNameプロパティを使用します。

  また、クライアントから送信できるファイルの大きさは、既定サイズで4,096KB(4MB)です。
  この値は、MaxRequestLength値を変更することで変えられます。
    configSection.MaxRequestLength = 2048

  但し、MaxRequestLengthを超えたファイルを送った場合、ブラウザが真っ白になるかHTTPエラーになったはずです。。。
  なので、エラーや真っ白にしたくない場合、2Mまでとしたい場合は、余分に5M程度を設定しておき、一旦受け取った後PostedFile.ContentLengthでファイルサイズを確認し、
  2M以内なら保存、2Mを超えるようならエラーメッセージを出すなどのユーザインターフェースにした方が良いです。
  
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If IsPostBack Then

        Dim path As String = Server.MapPath(“~/UploadedImages/”)
        Dim fileOK As Boolean = False

        If FileUpload1.HasFile Then
            Dim fileExtension As String
            fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower()

            Dim allowedExtensions As String() = {“.jpg”, “.jpeg”, “.png”, “.gif”}
            For i As Integer = 0 To allowedExtensions.Length – 1
                If fileExtension = allowedExtensions(i) Then
                   fileOK = True
                End If
            Next

            If fileOK Then
                Try
                    FileUpload1.PostedFile.SaveAs(path & FileUpload1.FileName)
                    Label1.Text = “File uploaded!”
                Catch ex As Exception
                    Label1.Text = “File could not be uploaded.”
                End Try
            Else
                Label1.Text = “Cannot accept files of this type.”
            End If
        End If
    End If
End Sub