データベース スナップショット概要

データベース スナップショットについてのメモ

データベースの「ある時点」の読取り専用コピー

○メリット１

　ミラーリングした際に、通常は待機側（復元状態）データベースの
　内容を見ることは出来ないが、データベーススナップショットを
　作成すれば、読み取り専用サーバーとして使うことが出来る。

○メリット２
　テスト時に使える。

　テスト開始前の状態のデータベーススナップショットを
　作成した状態でテストを実行、結果を確認後に
　データベーススナップショットの時点の状態に戻す。

NTFSの特徴

MCPの勉強でNTFSがでてきたので、その特徴をメモしておく。

【NTFSの特徴】
・アカウントごとにアクセス権が設定できる
・ファイルシステムレベルで暗号化ができる
・ジャーナリングファイルシステム
　（クラッシュ時の復旧をファイル更新履歴を使って行なう）

最後（直前）に追加されたデータのIDを取得する

追加したデータのIDを別のテーブルにも設定する場合など、
直前に追加されたID（identity）の値を知りたい時には

@@identity

の値を取得する。

関連リンク：
@@IDENTITY (Transact-SQL)

【Transact-SQL】日付の加算・減算（その１）

--日付の加算・減算を行なう。
--１日を１とした場合の各時間の値
--　３時間＝0.125
--　６時間＝0.25
--　９時間＝0.375
--１２時間＝0.5

DECLARE @date DATETIME
SET @date = CONVERT(DATETIME, '2008/10/10 12:00:00')

PRINT @date + 0.5
PRINT @date + 0.375
PRINT @date + 0.25
PRINT @date + 0.125
PRINT @date
PRINT @date - 0.125
PRINT @date - 0.25
PRINT @date - 0.375
PRINT @date - 0.5

--【実行結果】
-- 10 11 2008 12:00AM
-- 10 10 2008 9:00PM
-- 10 10 2008 6:00PM
-- 10 10 2008 3:00PM
-- 10 10 2008 12:00PM
-- 10 10 2008 9:00AM
-- 10 10 2008 6:00AM
-- 10 10 2008 3:00AM
-- 10 10 2008 12:00AM

【SQL】文字数をあわせるため不足分を０で埋める

--取得した日付を年月日に分解する。
--分解した際の文字数が足らない場合は左側を０で埋める。
DECLARE @date datetime
SET @date = getdate()

--分解した値を必要な文字数のVARCHARにキャストするのがポイント
PRINT right('00' + CONVERT(varchar(4),DATEPART(YEAR,@date)),4)
PRINT right('00' + CONVERT(varchar(2),DATEPART(MONTH,@date)),2)
PRINT right('00' + CONVERT(varchar(2),DATEPART(DAY,@date)),2)
PRINT right('00' + CONVERT(varchar(2),DATEPART(HOUR,@date)),2)
PRINT right('00' + CONVERT(varchar(2),DATEPART(MINUTE,@date)),2)
PRINT right('00' + CONVERT(varchar(2),DATEPART(SECOND,@date)),2)

--実行結果 : 値が一桁の場合には左側に０が入っている。
--2008
--08
--19
--20
--18
--01

【SQL】重複のあるデータをHaving句で取得

--HAVING句を使ってidに重複のあるデータのみ取得する。
--概要・・・
--①id ごとに件数を集計する。(GROUP BY)
--②集計した結果が１より大きいものを選択する。(HAVING)
--テーブルtbl_dataの構成例
--[id] AS int
--[data] AS varchar(50)

SELECT [id]
FROM [tbl_data]
GROUP BY [id]
HAVING COUNT(*) > 1

【Transact-SQL】引数で値を返すストアドプロシージャの作成

--引数で値を返すストアドプロシージャの作成
CREATE PROCEDURE st_get_output_data
(
@inArg1 varchar(16)
,@outArg1 varchar(32) OUTPUT
)
AS
BEGIN
SET @outArg1='@inArg1 is ' + @inArg1
RETURN 0
END

--ストアドプロシージャの実行
DECLARE @RC int
DECLARE @inArg1 varchar(16)
DECLARE @outArg1 varchar(32)

SET @inArg1 = 'test data'

EXECUTE @RC = [test].[dbo].[st_get_output_data]
@inArg1
,@outArg1 OUTPUT

SELECT @outArg1

--実行結果
--@inArg1 is test data

【Transact-SQL】複数行を返すテーブル値関数を作成する

--複数行を返すテーブル値関数を作成する
CREATE FUNCTION tfn_get_table_data
(
@inID AS int
)
RETURNS @wk TABLE
--戻り値のテーブル定義
(
outID int
)
AS
BEGIN
--条件式（IF,ELSE）、WHILE文も使うことが出来る。

--呼出元に返す値をテーブルにINSERTする。
INSERT INTO @wk(outID)VALUES(@inID)
--処理の最後はRETURNで終了させる。
RETURN
END

【Transact-SQL】四捨五入・切捨て（ROUND関数）

--切捨て
PRINT '--------------------'
PRINT '切捨て'
PRINT '--------------------'
PRINT ROUND(555.55,2,1) --小数点第二位まで
PRINT ROUND(555.55,1,1) --小数点第一位まで
PRINT ROUND(555.55,0,1) --
PRINT ROUND(555.55,-1,1)--１の位
PRINT ROUND(555.55,-2,1)--１０の位
--四捨五入の確認
PRINT '--------------------'
PRINT '四捨五入の確認'
PRINT '--------------------'
PRINT ROUND(555.54,1) --小数点第二位が４で切り捨て
PRINT ROUND(555.55,1) --小数点第二位が５で切り上げ


【結果】
--------------------
切捨て
--------------------
555.55
555.50
555.00
550.00
500.00
--------------------
四捨五入の確認
--------------------
555.50
555.60

【SQL】table1に存在しないIDのデータをtable2より取得する

--【table1に存在しないIDのデータをtable2より取得する】
--　table2とtable1をLEFT JOINで連結し、
--　table2から、tabel1.id=table2.idの条件でtable1.idがNULLのデータを取得する
--例）データ
------table1 | table2
--[id] 　1　 　|　 1
--[id] 　2 　　| 　2
--[id] 　3 　　| 　3
--[id] 　4 　　| 　4
--[id] NULL |　 5
--[id] NULL |　 6
-- LEFTJOINなので、table1に存在しないidはNULLになる

SELECT DEST.[id],DEST.[key]
FROM [test].[dbo].[table2] DEST
LEFT JOIN [test].[dbo].[table1] SRC
ON DEST.[id]=SRC.[id]
WHERE SRC.[id] IS NULL

【結果】
id key
----------- ----------
5 data_2_5
6 data_2_6

(2 行処理されました)

【SQL】重複した行を取り出す

--テーブルからフィールドAの値が重複しているデータを取り出す。
--手順：
--①GROUP BYでフィールドAを指定し、HAVING句を用いて
--　COUNT()の結果が２以上のデータのリストを取得する。
--　例）リストの取得方法
SELECT [key]
FROM [test].[dbo].[tbl_union1]
GROUP BY [key]
HAVING COUNT([key])>1

--②①で取得したリストに入っているデータをテーブルから取得する。
SELECT [key]
FROM [test].[dbo].[tbl_union1]
WHERE [key] in
(SELECT [key]
FROM [test].[dbo].[tbl_union1]
GROUP BY [key]
HAVING COUNT([key])>1)

【結果】
key
----------
union1_3
union1_3
union1_3

(3 行処理されました)

【Transact-SQL】条件式とループ処理（IF-ELSE, WHILE)

--WHILEによるループ処理とIF-ELSEによる条件分岐
DECLARE @cnt INT
SET @cnt = 0

WHILE @cnt < style="color: rgb(0, 153, 0);">--ループ処理の中身はBEGIN-ENDで囲む
BEGIN
IF @cnt = 0
--条件によって実行される処理が複数の場合はBEGIN-ENDで囲む
BEGIN
Print '複数行実行開始'
Print '@cnt = 0'
Print '複数行実行終了'
END
ELSE IF @cnt = 1
BEGIN
Print '複数行実行開始'
Print '@cnt = 1'
Print '複数行実行終了'
END
--条件によって実行される処理が１つの場合はBEGIN-ENDは必要ない
ELSE IF @cnt = 2
Print '@cnt = 2'
ELSE
Print 'Else'
SET @cnt = @cnt +1
END -- End of WHILE Statement

【結果】
複数行実行開始
@cnt = 0
複数行実行終了
複数行実行開始
@cnt = 1
複数行実行終了
@cnt = 2
Else
Else

【SQL】Datetime型の日付データを年月日に分割する

--Datetime型の日付データを年月日に分割する
DECLARE @currdate Datetime

SET @currdate = getDate()

Print @currdate
Print DATEPART(YEAR,@currdate)
Print DATEPART(MONTH,@currdate)
Print DATEPART(DAY,@currdate)
Print DATEPART(HOUR,@currdate)
Print DATEPART(MINUTE,@currdate)
Print DATEPART(SECOND,@currdate)


【結果】
04 30 2008 7:26PM
2008
4
30
19
26
29

【MSSQL】新規にINSERTしたレコードのIDを取得する方法

--新規にINSERTしたレコードのIDを取得する方法
--INSERTを実行した直後に@@IDENTITYの値を取得する。

INSERT INTO [dbo].[TEST]
([data1])
VALUES('test value')


Print 'New ID is ' + CONVERT(VARCHAR,@@IDENTITY)

【SQL】トリム処理

--文字列の先頭・末尾にあるスペースを取り除く
DECLARE @value varchar(32)
SET @value = ' test '

print '-' + @value + '-'
print '-' + LTRIM(@value) + '-'
print '-' + RTRIM(@value) + '-'
print '-' + LTRIM(RTRIM(@value)) + '-'

【結果】
- test -
-test -
- test-
-test-

【SQL】NULL値の扱い方１（比較方法）

--NULL値の扱い方１（比較方法）
--①間違ったNULL値の比較方法
IF @value = NULL
print 'null'
ELSE
print 'not null'

--②正しいNULL値の比較方法
IF @value IS NULL
print 'null'
ELSE
print 'not null'

--NULL値でなければ①の比較方法で比較できる。
SET @value = ''
IF @value = ''
print 'value is empty'
ELSE
print 'value is not empty'

【結果】
start of test1
test
end of test1
start of test2

end of test2
not null
null
value is empty

【SQL】キャストの方法（CONVERT関数 数字から文字）

キャストの方法（CONVERT関数 数字から文字）

DECLARE @value int
SET @value = 2
--文字＋数字で出力すると、数字で出力される。
print '0' + @value
--キャストすると文字として出力される。
print '0' + CONVERT(varchar,@value)

--結果
2
02

【MSSQL】DDLによる資格情報の追加

資格情報はSQLServerが内部的に使用する認証情報で、外部にあるリソースにアクセスする場合に利用される。

--資格情報　credent
--ログイン名 TEST1

CREATE CREDENTIAL credent
WITH
IDENTITY = 'TEST1' --Windowsユーザー名などを指定

--新しくデータベースにログインプリンシパルを作成する場合
CREATE LOGIN TEST1
WITH
PASSWORD='test', --ログインパスワード
CREDENTIAL = credent --資格情報名

--既存のログインプリンシパルを変更する場合
ALTER LOGIN TEST1
WITH
CREDENTIAL=credent --資格情報名

--資格情報を削除する場合
DROP CREDENTIAL credent

ただし、資格情報が使用されている場合は実行時に以下のエラーが発生する。

メッセージ 15541、レベル 16、状態 1、行 8
資格情報 'credent' を削除できません。この資格情報は、サーバー プリンシパルで使用されています。

【MSSQL】DDLによるログインの追加と削除（SQLサーバー認証）

SQLサーバー上のTESTデータベースに
SQLサーバー認証のログインTest_Userを作成する。
（SQLサーバー認証はWindows以外のOSから接続するような場合に使われる認証方法)

USE TEST --データベースの選択

CREATE LOGIN [Test_User]
WITH PASSWORD =N'test' --ログインパスワード
,DEFAULT_DATABASE=test; --デフォルトのデータベースの設定

【MSSQL】DDLによるログインの追加と削除（Windows認証）

WindowsOSのユーザー"Test_User"をSQL Serverに追加する。

CREATE LOGIN [OU812\Test_User]
FROM WINDOWS
WITH DEFAULT_DATABASE =[test];

Windowsユーザーアカウントの指定方法を間違えると以下のエラーが発生した。

CREATE LOGIN [Test_User]　--（←コンピュータ名が無い）
FROM WINDOWS
WITH DEFAULT_DATABASE =[test];

メッセージ 15407、レベル 16、状態 1、行 2
'Test_User' は有効な Windows NT 名ではありません。完全な名前 を指定してください。


Test_Userが存在しない場合はクエリーを実行すると以下のエラーが発生する。

メッセージ 15401、レベル 16、状態 1、行 1
Windows NT ユーザーまたはグループ 'OU812\Test_User' が見つかりませんでした。名前を再確認してください。


追加したログインを削除するにはDROP loginnameを使う。

DROP [OU812\Test_User]

【MSSQL】bcpコマンドの使い方

bcpコマンドを使って

テーブル←→ファイル、
クエリー実行結果→ファイル

の操作を行うことができる。

bcp DB名.スキーマ.テーブル名 [in/out] "ファイル名" -T -t; -c

bcp “クエリー文字列” queryout “ファイル名” --T --t; -c

その他、bcpコマンドのオプションは以下のとおり

C:\WINDOWS\system32>bcp
使用法: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m 最大エラー数] [-f フォーマット ファイル] [-e エラー ファイル]
[-F 先頭行] [-L 最終行] [-b バッチ サイズ]
[-n ネイティブ型] [-c 文字型] [-w UNICODE 文字型]
[-N text 以外のネイティブ型を保持]
[-V ファイル フォーマットのバージョン] [-q 引用符で囲まれた識別子]
[-C コード ページ指定子] [-t フィールド ターミネータ] [-r 行ターミネータ]
[-i 入力ファイル] [-o 出力ファイル] [-a パケット サイズ]
[-S サーバー名] [-U ユーザー名] [-P パスワード]
[-T 信頼関係接続] [-v バージョン] [-R 地域別設定有効]
[-k NULL 値を保持] [-E ID 値を保持]
[-h "読み込みヒント"] [-x XML フォーマット ファイルを生成]

【MSSQL】ログファイルとデータファイル

データファイル＝*.mdf（プライマリデータファイル）　
　　　　　　　　　　　　　　（セカンダリデータファイルは*.ndfを推奨）
ログファイル＝*.ldf

①データの参照時の動作
　１．データの参照（SELECT）
　２．データファイルからバッファキャッシュへのデータ読み取り
　３．バッファキャッシュのデータが参照元に返される。

　・バッファキャッシュは物理メモリに保持される。
　・バッファキャッシュからのデータの読み取りを
　　「論理読み取り」と呼ぶ。

②データの追加、更新時の動作

　１．データの更新（INSERT,UPDATE)
　２．バッファキャッシュの内容変更
　３．ログキャッシュからログファイルへの「ログの先行書き込み」
　４．チェックポイント時にバッファキャッシュから
　　　データファイルへの物理書き込み

　・データファイルとログファイルを物理的に異なるディスクに割り当てると
　　I/Oが競合しないため処理速度の向上が期待できる。
　　（デフォルトでは同じディレクトリ）
　　データの保護のためにも分けたほうがよい。