SQL Server SET NOCOUNT

SET NOCOUNT

Transact-SQL ステートメントまたはストアド プロシージャで処理された行数を示すメッセージが結果セットの一部として返すか返さないかを設定します。

デフォルトでは SET NOCOUNT はOFFの状態です。
下の例で確認してください。

SQL Server SET NOCOUNT 実行例
SQL Server SET NOCOUNT 実行例

最初はデフォルトなので、そのままselect文やupdate文の処理行数を表示します。

次に SET NOCOUNT ON を実行したので、select文やupdate文共に処理行数の表示をしません。ただし @@ROWCOUNT は正常にカウントされています。

最後は SET NOCOUNT OFF を実行したので、再度select文やupdate文の処理行数を表示します。

このように SET NOCOUNT は、Transact-SQL ステートメントまたはストアド プロシージャで処理された行数を示すメッセージが結果セットの一部として返すか返さないかの設定ができます。

SET NOCOUNT ON

SET NOCOUNT ONが良く使われる場面は、ストアドプロシージャで処理を実行したときやクエリアナライザのメッセージタブに表示される「(– 件処理されました)」というメッセージ(DONE_IN_PROCメッセージ)を出さないようにできます。

ストアドプロシージャで各ステートメントの完了時にクライアントアプリケーションにステートメントが処理された行数をメッセージとして送っていると、自ら設定したPRINT文の表示するメッセージを見つけにくくなるばかりか、メッセージを処理するSMDBやサーバーからクライアントに送信するためネットワークに無駄な負荷を掛けます。
このため、ストアドプロシージャではSET NOCOUNT ONによるメッセージ抑制がパフォーマンス管理には有効と考えられています。

SET NOCOUNT OFF

上で示したメリットよりも、処理件数メッセージが送られて来る(表示される)方がメリットが高い場合は、デフォルトが OFF なので通常は何もしないで良いのですが、ON にした後に OFF にしたい場合などは、SET NOCOUNT OFF を実行します。

SQL Server DONE_IN_PROCメッセージ

DONE_IN_PROCメッセージ

SQL Server では、ストアドプロシージャに実行時に、既定で各ステートメントの完了時にクライアントアプリケーションにステートメントが処理された行数をメッセージとして送っています。

このステートメントが処理された行数メッセージのことをDONE_IN_PROCメッセージと呼んでいます。

通常、処理件数を把握する必要が無いケースが多く、このメッセージをOFFすることで、データベースの全体的なパフォーマンスを向上させ、ネットワークトラフィックを減らすことができます(実際にどの程度かわからないので効果的なのか明確に答えられませんが、このような考え方が主流です)。

DONE_IN_PROCメッセージをOFFにする方法

SET NOCOUNT ON を実行します。
「SET NOCOUNT」を必要なら参照してください。

SQL Server Create View

SQL Server Create View

新しいビュー(VIEW) を作ります。

ビューとは、テーブルの特定部分のデータを抜き出したり複数テーブルを結合し必要なデータセットを取得するための仮想的なテーブルです。
create table文で作成する物理的なテーブルとは異なり、ビューの実体はデータを持たちません。

構文

create table ビュー名 as selectステートメント

例文

従業員テーブルと部門テーブル、配属テーブルを結合させてあたかも1つのテーブルのように見せます。

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
   QUOTED_IDENTIFIER, ANSI_NULLS ON;

--従業員テーブルの作成とデータ挿入
create table EMPLOYEE_V (
EMPLOYEE_ID nchar(5)
, NAME nvarchar(20) NOT NULL
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
)
insert into [dbo].[EMPLOYEE_V] values('T0001','太郎','タロウ','1997-08-2','1','2013-03-30','2005347','住所','09001239876')
insert into [dbo].[EMPLOYEE_V] values('T0002','花子','ハナコ','1997-08-2','1','2013-03-30','2005347','住所','09001239876')
insert into [dbo].[EMPLOYEE_V] values('T0003','次郎','ジロウ','1997-08-2','1','2013-03-30','2005347','住所','09001239876')
insert into [dbo].[EMPLOYEE_V] values('T0004','三郎','サブロウ','1997-08-2','1','2013-03-30','2005347','住所','09001239876')


--部門テーブルの作成とデータ挿入
create table BUMON_V(
BUMON_CD nchar(3)
, BUMON_NAME nvarchar(20)
, primary key CLUSTERED (BUMON_CD)
)
insert into [dbo].[BUMON_V] values('001','営業部')
insert into [dbo].[BUMON_V] values('002','業務部')
insert into [dbo].[BUMON_V] values('003','製造部')
insert into [dbo].[BUMON_V] values('004','総務部')


--配属テーブルの作成とデータ挿入
create table HAIZOKU_V(
EMPLOYEE_ID nchar(5) 
, START_DATE date
, END_DATE date
, BUMON_CODE nchar(3)
, primary key CLUSTERED (EMPLOYEE_ID, START_DATE)
)
insert into [dbo].[HAIZOKU_V] values('T0001','2019/04/02','2020/06/09','004')
insert into [dbo].[HAIZOKU_V] values('T0002','2020/04/02','2020/06/09','002')
insert into [dbo].[HAIZOKU_V] values('T0003','2020/04/02',null,'002')
insert into [dbo].[HAIZOKU_V] values('T0004','2020/04/02',null,'002')
insert into [dbo].[HAIZOKU_V] values('T0001','2020/06/10',null,'001')
insert into [dbo].[HAIZOKU_V] values('T0002','2020/06/10',null,'003')

GO 

--Viewの作成
create View dbo.View_HAIZOKU
with schemabinding
as
select TH.EMPLOYEE_ID
, TH.BUMON_CODE
, TB.BUMON_NAME
, TE.NAME
, TE.KANANAME
 from dbo.HAIZOKU_V TH
inner join dbo.EMPLOYEE_V TE on TH.EMPLOYEE_ID = TE.EMPLOYEE_ID
inner join dbo.BUMON_V TB on TH.BUMON_CODE = TB.BUMON_CD
where TH.end_date is null

GO

--インデックの作成(従業員ID)
create UNIQUE CLUSTERED index IDX_View_HAIZOKU 
on View_HAIZOKU (EMPLOYEE_ID)

GO

--インデックの作成(部門コード)
create NONCLUSTERED index IDX_View_HAIZOKU2 
on View_HAIZOKU (BUMON_CODE)

GO

--ビューからデータを取得
select BUMON_NAME
, NAME
, KANANAME 
from View_HAIZOKU
order by BUMON_CODE

/*
drop view View_HAIZOKU
drop table EMPLOYEE_V
drop table BUMON_V
drop table HAIZOKU_V
*/

Viewの参照はテーブルと同じようにできます。
上の例の80-85行を参考にしてください。

ViewにINDEXを付ける

ViewにもINDEXを付けて高速化することができます。
上の例の68-76行を参考にしてください。
INDEXを作成するにはいくつ条件が有るので、エラーが出たらメッセージを確認して対応してください。

良く引っかかるのが次のことです。
1-3行のSET オプション。
Viewを作成する時に schemabinding キーワードを付ける必要があります。
上の例の場合、dboを付けてあるようにスキーマ名を付ける必要があります。
INDEXを付ける時に NONCLUSTERED で作成する前に、UNIQUE CLUSTERED で作成する必要があります。
他にも有るかも知れませんが、エラーが出たらその都度調べてください。

私はあまり View を使わないのでこの程度のことしかわかりません、参考になれば幸いです。

以上、「SQL Server Create View」でした。

SQL Server クラスタ化インデックス 非クラスタ化インデックス

SQL Server インデックスのクラスタ化とは

SQL Server でテーブル等にインデックスを作成する際に、クラスタ化インデックスにするか非クラスタ化インデックスにするか決めることができます。
その違いをメリット・デメリットを挙げて説明します。

その前にインデックス(INDEX)とは

その前にインデックスとは、検索対象のテーブルやビューに対して早く検索できるように、任意の列のデータもしくは複数の列のデータの組み合わせを基準にして、どのような順序で並んでいるかを管理している索引情報のことです。
インデックスを作る際に「この列の小さい順でインデックス作成」のように列や並びを指定してインデックスを作成作成します。

クラスター化インデックス

クラスタ化インデックスは、指定した条件で「実際のデータを並び替えて格納」します。
実際にデータを並び替えて格納しているため、テーブル毎にクラスター化インデックスは1つしか作成できません。
最もよく使われる検索条件に合わせてクラスタ化インデックスを作成します。

メリット

データが並び替えられて保存されているため、範囲データの抽出が速いです。
データが並び替えられて保存されているため、余分な領域を取りません。

デメリット

テーブル1つに対して1つしかクラスター化インデックスを作成できません。
データ挿入でデータ格納領域に対して溢れが起こった場合、その処理に時間が掛かります。

作成方法

インデックやPRIMARY KEYを作る時に「CLUSTERED」キーワードを使います。
例)create UNIQU CLUSTERED index インデック名
  primary key CLUSTERED (列名)

非クラスター化インデックス

非クラスタ化インデックスの場合は、テーブルの実際のデータの並び替えは実施せず(入れた順に格納)、インデックスで指定した列の値に対応するデータレコードへのポインタがインデックスファイルに格納されます。
ピンポイント検索は高速ですが、範囲検索はデータがバラバラに格納されているため、クラスタ化インデックスに比べ一般的に遅くなります。無いよりは早いですが……
メリット

1つのテーブルに複数のインデックスを作成できます。
データの挿入時、追記挿入になるためクラスタ化インデックスに比べて一般的に若干早くなります。

デメリット

上述の理由で範囲検索が遅いです。

まとめ

インデックスはデータ量が多くなると絶対に必要になります。
適切なインデックス作成は難しいですし、大規模システムでは特に当初の設計の見込み違いなどでデータ規模が変わるとインデックスを変える必要が出てくる可能性もあります。机上設計のみならず実地に試験をしてパフォーマンス管理を怠らないよに努めてください。

SQL Server tableを作成してみる

tableを作成してみる

データベースを作成し、ログインユーザーを作成登録したら、次はテーブルを作成してみましょう。 テーブルを作成する方法はいくつかあります。 今回は何も無いところから始めるため、新規にテーブルを作成してみます。

SQL文を発行してテーブルを作成してみる

testTable01と言う名前のテーブルを作成してましょう。 testTable01テーブルには販売日・商品名・販売数のデータを格納できるようにします。 カラムは以下のように設定します。 h-date date null無し p-name nvarchr(10) null無し h-num int null有り SQL文は以下の通りです。

create table testTable01(
 h-date date not null
, p-name nvarchar(10) not null
, h-num int null 
)

SSMSを 立ち上げます。
データベースを選択します。
メニューバーの「新しいクエリ」ボタンをクリックします。
SQL文を入力できるWindowが表示されます。

 

 

SSMSのテーブル作成機能を利用してテーブルを作成してみる

表示されたWindowに上のSQLを入力し、「!実行」ボタンをクリックします。「!実行」ボタンは「新しいクエリ(N)」の下あたりに有りますが、なければ探してください。

グラフィカルな操作でテーブルを作ることも出来ます。データベースを右クリックして「テーブル…」を選択すると MS ACCESS の操作イメージでテーブルが作成できます。

私のテストで使っている環境では『このバックエンド バージョンでは、データベース ダイアグラムまたはテーブルのデザインはサポートされていません。 (MS Visual Database Tools)』と表示されて使用できません。
バージョンを合わせると使用できるそうなのですが、SQL で書いてしまうので必要性を感じず放置したままになってます……
データベース ダイアグラムはリレーションを貼ったりするのに便利そうなのですが、これも私は使っていないので……安易に変更できるといろいろと問題が有りそうなので……

以上、「SQL Server tableを作成してみる」でした。
もう少し詳しい情報が知りたい場合は、「SQL Server Create Table」を参考にしてください。

SQL Server Create Table

Create Table テーブルを作成する

新しいテーブルを作成します。

構文

create table テーブル名 (
列名 データ型
, 列名 データ型
, 列名 データ型
, 列名 データ型
   ・
   ・
)

デフォルト値を設定したい場合は、
列名 データ型 default データ
のようにデータ型の後ろにデフォルト情報を追記します。

例文

良くある社員情報のテーブルを例に挙げて例文となるSQLを示します。
情報は「社員コード」「社員名」「社員カナ名」「生年月日」「性別」「入社年月日」「郵便番号」「住所」「電話番号」とします。

create table EMPLOYEE (
ID nchar(5)
, NAME nvarchar(20)
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
)

実行

テーブルの作成を実行し、正常に終了すると下のように「コマンドは正常に完了しました」と表示されます。

構文等の記述ミスがあると、メッセージ番号、レベル、状態、行の情報と大まかなエラー内容が表示されます。

例えば、IDの nchar を記述ミスして nhar とした場合、以下のようなエラーメッセージが表示されます。

メッセージ 2715、レベル 16、状態 6、行 1
列、パラメーター、または変数 #1: データ型 nhar が見つかりません。

Not NULL制約の追加

NOT NULL制約とは、テーブルにデータを追加(Insert)、更新(Update)する際、テーブル列に対する制約の一つで、その列に必ず「値」が登録される制約をとることです。

ここで言う「値」とは値が未設定の状態である「NULL」でない値のことです。
NULLとは、値が未設定の状態とも言えます。

NULLについては「データベースのNULL」に少しですが説明してありますので、NULLがピンとこない場合は参照してみてください。

構文 Not NULL制約付き

例えば上の EMPLOYEE テーブルにおいて、従業員の「NAME」の定義を NAME nvarchar(20) NOT NULL と指定し、「NAME」列のデータには必ず値を入れないと追加・更新できなようにします。

create table EMPLOYEE (
ID nchar(5)
, NAME nvarchar(20) NOT NULL
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
)

実際に登録してNAME列にデータを入れずに追加(Insert)してみます。

Create Table NOT NULL制約付き
Create Table NOT NULL制約付き

左のオブジェクト エクスプローラーで確認すると、NAME が「NULL以外」となっているのがわかります。
実際にNULLを入れようとしても、見ての通りエラーになります。

ちなみに2行目の「’’」は空文字のことで、長さ0文字の文字列になります。
プログラム言語のなかには空文字もNULLも同じ扱いにする言語があるかもしれませんが、データベースではNULLと空文字は明確に違うので注意してください。

下に2行目の「KANANAME」をNULLにして登録したものの結果を載せます。
空文字はブランク表示になっていますが、NULLはNULと表示されます。

NULLと空文字の違い
NULLと空文字の表示の違い

Select文で取得する時も
NULLなら Select * from EMPLOYEE where KANANAME is NULL
空文字なら Select * from EMPLOYEE where NAME=”
となります。

UNIQUE制約(列制約)

UNIQUE制約は列に設定し、その列に格納されるデータの中には同じコードや名称などが無い唯一のデータになるように制約を掛けることです。

上の従業員テーブルEMPLOYEEでは、IDがそれに当たることが多いです。
IDをUNIQUE設定すると同じIDは挿入できなくなります。
尚、NULL を格納することができ、またテーブルに複数列作成できます。

列にUNIQUE制約を行う構文

create table テーブル名 (
列名 データ型 [制約名] UNIQUE
)
制約名は付けなくても可。

create table EMPLOYEE2 (
ID nchar(5) UNIQUE
, NAME nvarchar(20) NOT NULL
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
)

ID列の後ろに UNIQUE を付けるだけです。

ここで同じIDを挿入しようとするとエラーになります。
『メッセージ 2627、レベル 14、状態 1、行 2
制約 ‘UQ__EMPLOYEE__3214EC26A59C0AD4’ の UNIQUE KEY 違反。オブジェクト ‘dbo.EMPLOYEE2’ には重複するキーを挿入できません。重複するキーの値は (T0001) です。』と今回は表示されました。尚、機械的な制約名にならないので制約名を付けるとエラーもわかりやすくなるかも。

UNIQUE列の追加エラー表示
UNIQUE列の追加エラー表示

UNIQUE制約(テーブル制約)

テーブル制約でのUNIQUE制約は、複数列の組み合わせでUNIQUEに設定できるようになっています。もちろん1つの列のみ設定もできますが。

下の例、テーブルEMPLOYEE2-1の場合は、ID列とYYYY列の組み合わせでUNIQUEになるように設定しています。IDがT0001の人が2人居たとしても、YYYYが2020と2019のように異なればOKです。

テーブルにUNIQUE制約を行う構文

create table テーブル名 (
列名 ・・・,
[制約名] UNIQUE (列名, 列名・・・)
)
制約名は付けなくても可。

create table EMPLOYEE2_1 (
ID nchar(5)
, YYYY nchar(4)
, NAME nvarchar(20) NOT NULL
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
, unique (ID, yyyy)
)

ID列YYYY列を同一で挿入しようとすると下のようにエラーがでます。

UNIQUEテーブル制約時の列データの追加正常表示
UNIQUEテーブル制約時の列データの追加エラー表示

ID列YYYY列のどちらかを変えると同一ではなくなるので正常に挿入できます。

UNIQUEテーブル制約時の列データの追加正常表示
UNIQUEテーブル制約時の列データの追加正常表示

PRIMARY KEY制約(列)

PRIMARY KEY制約は列に設定し、その列に格納されるデータの中には同じコードや名称などが無い唯一のデータになるように制約を掛けることです。

上の従業員テーブルEMPLOYEEでは、IDがそれに当たることが多いです。
IDをPRIMARY KEY設定すると同じIDは挿入できなくなります。
UNIQUEとほぼ同じですが、NULL を格納することができません。INDEXが自動で作成されます。またテーブルに複数列の組み合わせで作成したい時は列制約ではなく、テーブル制約でPRIMARY KEYを設定することになります。

列にPRIMARY KEY制約を行う構文

create table テーブル名 (
列名 データ型 [制約名] PRIMARY KEY
)
制約名は付けなくても可。

create table EMPLOYEE3 (
ID1 nchar(5) primary key
, NAME nvarchar(20) NOT NULL
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
)

ID列の後ろに PRIMARY KEY を付けるだけです。

ここで同じIDを挿入しようとするとエラーになります。

メッセージ 2627、レベル 14、状態 1、行 2
制約 ‘PK__EMPLOYEE__C49703DD6C96672F’ の PRIMARY KEY 違反。オブジェクト ‘dbo.EMPLOYEE3’ には重複するキーを挿入できません。重複するキーの値は (T0001) です。』と今回は表示されました。尚、機械的な制約名にならないので制約名を付けるとエラーもわかりやすくなるかも。

PRIMARY KEY列の追加エラー表示
PRIMARY KEY列の追加エラー表示

PRIMARY KEY制約(テーブル)

テーブル制約でのPRIMARY KEY制約は、複数列の組み合わせでPRIMARY KEYに設定できるようになっています。もちろん1つの列のみ設定もできますが。

下の例、テーブルEMPLOYEE2-1の場合は、ID列とYYYY列の組み合わせでPRIMARY KEYになるように設定しています。IDがT0001の人が2人居たとしても、YYYYが2020と2019のように異なればOKです。尚、NULL を格納することができませんし、INDEXも自動で作成されます。

テーブルにPRIMARY KEY制約を行う構文

create table テーブル名 (
列名 ・・・,
[制約名] PRIMARY KEY (列名, 列名・・・)
)
制約名は付けなくても可。

create table EMPLOYEE3_1 (
ID nchar(5)
, YYYY nchar(4)
, NAME nvarchar(20) NOT NULL
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
, primary key (ID, yyyy)
)

FOREIGN KEY制約(テーブル)

FOREIGN KEY制約は指定した列の値を登録する時に、指定したテーブルの列の中に無くてはならないと言う制約です。
今まで上で使用してきた例題のテーブルで言うと、YYYY列がNENDOテーブルのYYYYになければならない場合に使います。

テーブルにFOREIGN KEY制約を行う構文

create table テーブル名 (
列名 ・・・,
[制約名] FOREIGN KEY (列名, 列名・・・) REFERENCES 参照テーブル名(参照列名)
)
制約名は付けなくても可。

下の例では参照先のテーブルが無いとエラーになるので、参照先のテーブル作成例も合わせて挙げています。
尚、参照先のテーブルの参照列にはPRIMARY KEYが設定されていないとエラーになります。テーブル設計時に注意しましょう。

挿入時、削除時の注意事項

EMPLOYEE4_1テーブルに挿入する時は、事前に参照先のテーブルYYYY4_1に挿入うデータ値が格納されている必要があります。
で削除時ですが、EMPLOYEE4_1からは普通に削除できるのですが、YYYY4_1から削除する際にはEMPLOYEE4_1で削除しようとするデータ値が使われているとYYYY4_1で削除時にエラーが発生します。データ整合性が保たれるためです。

create table YYYY4_1(
YYYY nchar(4)
primary key (YYYY)
)
create table EMPLOYEE4_1 (
ID nchar(5)
, YYYY nchar(4)
, NAME nvarchar(20) NOT NULL
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
, foreign key (YYYY) references YYYY4_1(YYYY)
)

CHECK制約(テーブル)

CHECK制約はFOREIGN KEY制約に似ていて、その列に登録可能なデータ値をチェックするのですが、あくまでも登録時だけのチェックです。
FOREIGN KEY制約ではデータの整合性も保たれますが、CHECK制約では

テーブルにCHECK制約を行う構文

create table テーブル名 (
列名 ・・・,
[制約名] CHECK (条件式)
)
制約名は付けなくても可。

CHECK制約の条件式例

・列名1 > 10
・列名1 in (, , ,)
・列名1 between x and z
・列名1 > 10 AND 列名2 in (‘A’,’B’,’C’)

create table EMPLOYEE5_1 (
ID nchar(5)
, YYYY nchar(4)
, NAME nvarchar(20) NOT NULL
, KANANAME nvarchar(20)
, BATHDAY DATE
, SEX nchar(1)
, JOIN_DATE DATE
, POST_CODE nchar(7)
, ADDRESS nvarchar(100)
, TEL nvarchar(11)
, check (YYYY between '2010' and '2022')
)

check (YYYY in (select YYYY from [dbo].[YYYY4_1]))
のような使い方はできません。『このコンテキストではサブクエリは許可されません。スカラー式だけが許可されます。』とエラーになります。

以上、「SQL Server Create Table」でした。

尚、SQL Server にはデータベースを作成するとき、複数のストレージに渡って作ることができます。HDD(ハードディスク)の個数でパフォーマンスが変わってくるので、大規模システムでは複数のストレージを使うことが多いです。
そして、ストレージ毎にファイルグループを持つように設定し、ストレージを分けることで負荷分散を行います。
このファイルグループにそれぞれ名前を付けることができます。 デフォルトはPRIMARYで、後は任意に名前を付けられます。

テーブルやインデックを作る時に「on」指定でこのファイルグループを指定できます。
この辺りは基礎ではないので私も詳しく説明できませんが、知っておくことは大切ですので、追記しておきます。詳しく知りたい人は「SQL Server Create table on」で検索するといろいろ出てくると思います。検索してないので……多分。

データベースのNULL

データベースのNULL

データベースを扱っていると「NULL」と言うデータを良く見かけます。
データベースの値の初期値もNULLです。

では、データベースのNULLとはどんなものなのでしょうか?

データベースのNULLは空

データベースのNULLは、登録されたデータが空であることを意味しています。
空とは未設定の意味にも取れますが、そこに登録すべき値が無いと言う方が正確だと思います。
なのでデータベースの値の初期値はNULLにしておくことが多いです。

データベースの仕組みでは、どんな値が登録されても何らかの意味を持つ可能性が有ると判断しますが、NULLだけ空の意味しか持たず、「IS NULL」判定の式があります。
NULLは特別なデータです。

データベース システム開発におけるNULLの扱い

更にNULLには空には違いがないのですが、「未知・不明 (Unknown)」 の場合と「適用不能・非存在 (Not Applicable, Inapplicable)」 の場合があります。

例えば、電話番号がNULLの場合
未知・不明のNULLは「電話はあるが番号分からない」
適用不能・非存在のNULLは「電話を持っていない」
の違いがあります。

システム的にはどのように解釈してもシステム内で統一した認識があればよいのですが、例えば、「開発運用初期は000-0000-0000 が入っていた場合、在りえない値なので電話番号を不明として扱う場合に登録しておいた」つもりが、改修の際にNULLでないため実際の値として扱い不具合が出たなど、上の例は極端ですがもっと複雑なデータの場合は往々にして不具合発生の温床となりますので注意が必要です。

NULL を含む演算

NULLを含む演算の結果は、多くがNULLとなります。

売り上げの集計をしていて、一つでもNULLデータが混入していると、結果はNULLとなります。
集計の加算(足し算)だけでなく、客単価等を求めるために割り算などを使った場合も客数がNULLなら結果はNULLになります。

思わぬNULLデータを含む演算による不具合は良く発生するので注意が必要です。

下の結果のようにNULLになります。

NULLを含む演算結果

NULLを取り扱う工夫

思わぬところで不具合が発生してしてしまうNULLですが、なるべくそうならないように工夫することが可能です。

1.テーブルの列に NOT NULL 制約をかける

テーブルの列に NOT NULL制約をかけてしまい、NULLデータを登録(追加・更新)できないようにしてしまう。
追加や更新時にエラーで落ちてしまう不具合が発生するが、エラーハンドリングも取りやすい。

2.適宜 NULL 判定を入れる

isnull関数を使いNULLを妥当な数値やデータに変えます。
妥当性はシステムの性格にもよるので何とも言えないですが、NULL判定を上手く使い予期せぬ不具合を未然に防ぐようにします。

先ほどの演算結果の一部をisnull関数で書き換えてみました。

isnull関数でNULLをチェック
isnull関数でNULLをチェック

このisnull関数の他にもCase文を使ってチェックすることも可能です。
とりあえず例を載せておきます。

Case文を使ってNULLをチェック
Case文を使ってNULLをチェック

NULLの扱いについては「SQL Server NULL」でも少し説明しています。

以上、データベースにおけるNULLについての簡単な説明でした。

SQL Server Create Database

Create Database

新しいデータベースを作成します。

構文

create database データベース名

例文

create database DB_TEST

実行

データベースの作成を実行し、正常に終了すると下のように「コマンドは正常に完了しました」と表示されます。
データベースを作成実行

作成に失敗すると、エラーメッセージが表示されます。
例えばデータベースを作成する権限がない場合は、「メッセージ 262、レベル 14、状態 1、行 1
CREATE DATABASE 権限がデータベース ‘master’ で拒否されました。」
と表示されます。

もう少し詳しく知りたい場合は、「SQL Server 新規にデータベースを作成する」を参照してください。

SQL Server 新規にログインアカウントを作成する

SQL Server 新規にログインアカウントを作成する

SQL Server の勉強をする場合、もしもに備えて仕事で使うデータベースやログインアカウントは使いたくないですね。
SQL Serverは簡単にデータベースやログインアカウントを作成できます。

基本的にSQL Serverの学習を行う際は、自前のPCにデータベースを作成し新規に学習ユーザーを作ることが、セキュリティ上からも間違えてデータベースを壊さないためにも良いです。

今回は新規にログインアカウントを作成する方法を紹介します。
新規ログインアカウント名を「Learning-user」と言う名前にします。

新規にログインアカウントを作成する

操作はとっても簡単なのですが、作成する権限が必要なので仕事先で作成する場合はデータベース管理者に相談してください。
自前で自分のPCにSQL Serverをインストールした場合は、saでログインするか、sysadminもしくはsecurityadmin権限があるか確認してください。
もし権限が無い状態で新規にローカルアカウントを作成すると以下のようなエラーになります。

新しいログインを作る

SSMS(SQL Server Management Studio)を立ち上げます。
作成したいSQL Serverを展開し、更に[セキュリティ]を展開します。
[ログイン]が展開表示されるので、右クリックでコンテキストメニューを開きます。
コンテキストメニューの中から[新しいログイン]をクリックします。

ログイン-新規作成

ログイン-新規作成画面が表示されます。

他のデータベースに影響を与えないようにするために、作成するログインアカウントはSQL Server認証が好ましいです。
会社や仕事先の場合は、データベース管理者に相談してください。

ログイン名を任意に入力します。(例ではLearning-user)
SQL Server 認証を選択します。
パスワードを任意の文字列で入力します。
パスワードポリシーを適用するは基本的に必要ないと思います。
※重要なデータベースに元々アクセスしないし学習用なので。
既定のデータベースが決まって居れば指定してください。
最後にOKボタンをクリックして登録します。

作ったログインアカウントにデータベースのアクセス権を与える

作ったばかりのログインユーザーは権限を持っていないため、アクセス拒否をされてしまします。
試しにアクセス拒否されると以下のようになります。

そこで、作成したlearning-userがlearning-dbにアクセスできるようにします。
SSMSを立ち上げます。
SQL Serverを展開し、更に[データベース]を展開します。
既存のデータベースが展開表示されるので、Learning-dbデータベースを確認します。
[Learning-dbデータベース]をクリックして展開します。
[セキュリティ]を展開します。
[ユーザー]を展開します。
Learning-userが居ないことを確認します。
[ユーザー]を右クリックしてコンテキストメニューを開きます。
[新しいユーザー(N)]をクリックします。

「データベース ユーザー – 新規」画面が表示されます。
ログイン名の右端にあるボタンをクリックします。

「ログインの選択」画面が表示されます。
[参照(B)]ボタンをクリックします。

「オブジェクトの参照」画面が表示されます。
今回は先ほど作成した新規ログインアカウントを探して、チェックボックスにチェックを入れます。
[OK]ボタンをクリックします。

「ログインの選択」画面が表示されます。
選択するオブジェクト名欄にlearning-userが表示されているのを確認します。
[OK]ボタンをクリックします。

「データベース ユーザー – 新規」画面が表示されます。
ログイン名が表示されているので確認します。
ユーザー名を適当な名前で入れます。今回はそのままlearning-userとしました。
右側のページ選択メニューのメンバーシップをクリックします。

「データベース ロールのメンバーシップ」設定画面が表示されます。
db_datareader、db_datawriter、db_ddladminにチェックを入れます。
簡単なロールの役割を下に記述しておきます。
[OK]ボタンをクリックします。

Learning-userが追加されていることを確認します。

SSMSでログインできることを確認します。
正常にログインできたらデータアクセスが出来るようになっています。
データベース操作で足らない権限が出てきたらロールを追加して対応します。

固定データベース ロール

固定データベース ロールとその機能を示します。
これらのロールは、すべてのデータベースに存在します。

固定データベース ロールの名前 [説明]
db_owner db_owner 固定データベース ロールのメンバーは、データベースでのすべての構成作業とメンテナンス作業を実行でき、 SQL Serverでデータベースを削除することもできます。 ( SQL データベース と SQL データ ウェアハウスでは、一部のメンテナンス作業にサーバー レベルの権限が必要であり、 db_ownersでは実行できません。)
db_securityadmin db_securityadmin 固定データベース ロールのメンバーは、ロールのメンバーシップを変更し、権限を管理できます。このロールにプリンシパルを追加すると、特権が意図せず昇格されることがあります。
db_accessadmin db_accessadmin 固定データベース ロールのメンバーは、Windows ログイン、Windows グループ、および SQL Serverログインのデータベースに対するアクセスを追加または削除できます。
db_backupoperator db_backupoperator 固定データベース ロールのメンバーは、データベースをバックアップできます。
db_ddladmin db_ddladmin 固定データベース ロールのメンバーは、すべての DDL (データ定義言語) コマンドをデータベースで実行できます。
db_datawriter db_datawriter 固定データベース ロールのメンバーは、すべてのユーザー テーブルのデータを追加、削除、または変更できます。
db_datareader db_datareader 固定データベース ロールのメンバーは、すべてのユーザー テーブルからすべてのデータを読み取ることができます。
db_denydatawriter db_denydatawriter 固定データベース ロールのメンバーは、データベース内のユーザー テーブルのデータを追加、変更、または削除することはできません。
db_denydatareader db_denydatareader 固定データベース ロールのメンバーは、データベース内のユーザー テーブルのデータを読み取ることはできません。

以上、簡単な説明でしたが「SQL Server 新規にログインアカウントを作成する」でした。

SQL Server 新規にデータベースを作成する

SQL Server 新規にデータベースを作成する

SQL Server の勉強をする場合、もしもに備えて仕事で使うデータベースやログインアカウントは使いたくないですね。
SQL Serverは簡単にデータベースやログインアカウントを作成できます。

基本的にSQL Serverの学習を行う際は、自前のPCにデータベースを作成し新規に学習ユーザーを作ることが、セキュリティ上からも間違えてデータベースを壊さないためにも良いです。

今回は新規にデータベース・Databaseを作成する方法を紹介します。
今回は新規データベース名を「Learning-db」と言う名前にします。

新規にデータベースを作成する

操作はとっても簡単なのですが、作成する権限が必要なので仕事先で作成する場合はデータベース管理者に相談してください。
自前で自分のPCにSQL Serverをインストールした場合は、saでログインするか、sysadminもしくはdbcreator権限があるか確認してください。
もし権限が無い状態で新規にローカルアカウントを作成すると以下のようなエラーになります。



新規データベース「Learning-db」を作成する

SSMS(SQL Server Management Studio)を立ち上げます。
作成したいSQL Serverを展開し、更に[データベース]を展開します。
既存のデータベースが展開表示されるので、新規データベースと同じ名前のデータベースが無いことを確認します。
[データベース]を右クリックしてコンテキストメニューを開きます。
コンテキストメニューの中から[新しいデータベース(N)]をクリックします。

新しいデータベース画面が立ち上がります。
データベース名を入力します。
※後から変更も可能です。
基本的に何も変更せずに追加を行えば良いのです。
しかし、もし使用領域の制限を設けたいなら「自動拡張/最大サイズ」で下のように設定してください。
また、右スクロールすると「パス」でデータベースファイルを作るドライブやディレクトリを指定できます。
テスト用のドライブやディレクトリに入れたい場合は変更します。
「追加(A)」ボタンはデータベースファイルを増やしたい時に使用します。
この辺りの設定はテストでは必要ないですが、実運用の際はパフォーマンスに影響を及ぼすため、後日説明したいと思います。
最後に「OK」ボタンをクリックしてLearning-dbを作成します。

使用領域の制限を設ける

「ファイルの最大サイズ」を次のサイズに制限を選択してMB単位で指定します。
下の場合は、1Gまで拡張出来るように設定しました。
「OK」をクリックして設定します。

新規に作成したLearning-dbが作成されているか確認します。
正常に表示されていればOKです。

データベースを削除する

必要が無くなったデータベースを簡単に削除できます。
※簡単に削除出来てしまうため、注意が必要です。

SSMSを立ち上げます。
SQL Serverを展開し、更に[データベース]を展開します。
既存のデータベースが展開表示されるので、削除を行いたいデータベースを確認します。
[削除したいデータベース]を右クリックしてコンテキストメニューを開きます。
コンテキストメニューの中から[削除(D)]をクリックします。

確認画面が表示されますので間違いがなければ「OK」ボタンをクリックします。
以上で(削除権限が有れば)データベースが削除されます。

データベース名を変更する

データベース名の変更も簡単にできます。
※簡単に変更が出来てしまうため、注意が必要です。

SSMSを立ち上げます。
SQL Serverを展開し、更に[データベース]を展開します。
既存のデータベースが展開表示されるので、名前の変更を行いたいデータベースを確認します。
[名前を変更したいデータベース]を右クリックしてコンテキストメニューを開きます。
コンテキストメニューの中から[名前の変更(M)]をクリックします。

下のようにテキスト入力可能になりますので任意の名前を入力します。

データベースのプロパティを確認する

データベースのプロパティを変更することで多くのカスタマイズが可能になります。

例えば、データベースの処理速度が重たくなって場合、別ドライブを追加し物理アクセスの負担を軽くする場合にファイルを増やして対応するなど。
ユーザー権限の設定もできます。
また、自動圧縮するしないとか照合順序を変更してカタカナ検索をカスタマイズするなど。
かなりのことができますので、勉強がてら壊しても問題のないテスト用データベースをカスタマイズするのも良いです。

以上、簡単な説明でしたが「SQL Server 新規にデータベースを作成する」でした。