【初級編⑤】SQL Server 2008 のデータ型とサイズについて

SQL Server で作成するテーブルの各列には、「文字列」「数値」などの型（「データ型」と言います）を指定しなければいけません。今回のエントリではどんなデータ型があるのか、どの型を選ぶべきなのかを考えていきたいと思います。

データ型とは

テーブルを作成する時に、まずは列を定義していきました。社員番号を格納する列であるとか、社員名を格納する列、また給料を格納する列など。それに伴って、それぞれの列はどんなデータを格納するのかを決める必要があります。文字列の場合は「varchar」、数値の場合は「int」などを指定するのですが、この「varchar」や「int」のことを型（データ型）と呼びます。

エクセルであれば、とりあえずデータを入力しておいて書式はあとから変更するのも簡単ですが、SQL Server の場合は後からデータ型を変更するのは簡単ではないので、基本的に予め格納するデータ内容に合わせて列の型を決めておく必要があり、原則変更はしない（しなくて済むように考慮しておく）ことが重要です。

SQL Server 2008 のデータ型一覧

SQL Server 2008 のデータ型を以下に列挙します。これは初級編なので、バイナリやその他に書いている難しいものの説明は省きます。

種別 データ型 説明 サイズ(バイト)
整数型 tinyint 0 ～ 255　の範囲の数値 1
smallint おおよそ　-3万 ～ 3万の範囲の数値 2
int おおよそ　-20億 ～ 20億の範囲の数値 4
bigint おおよそ　-900京 ～ 900京の範囲の数値 8
小数(真数型) decimal(p, s) p：全体桁数　s：小数点以下桁数
固定長の有効桁数と小数点以下桁数を持つため誤差は発生しない（10進数） 		5 ～ 17
numeric(p, s)
小数(概数型) float(n) n：仮数のビット数　浮動小数点数 4 ～ 8
real 浮動小数点数 4
文字列 char(n) 固定長文字列(8000バイトまで) n
varchar(n) 可変長文字列(8000バイトまで) 実際のバイト数 + 2
varchar(max)
text 		可変長文字列(2GBまで)
Unicode文字列 nchar(n) Unicode固定長文字列(8000バイトまで) 2n
nvarchar(n) Unicode可変長文字列(8000バイトまで) (実際のバイト数) * 2 + 2
nvarchar(max)
ntext 		Unicode可変長文字列(2GBまで)
日付 smalldatetime 精度：1分 4
datetime 精度：3.33ミリ秒 8
date 精度：1日 3
time 1日の時刻のみを格納し、精度は100ナノ秒 3～ 5
datetime2 精度：100ナノ秒 6 ～8
datetimeoffset 精度：100ナノ秒　タイムゾーン付き 8 ～10
金額・通貨 smallmoney 金額や通貨を表す(範囲狭) 4
money 金額や通貨を表す(範囲広) 8
バイナリ binary(n) 固定長バイナリデータ(8000バイトまで) n
varbinary(n) 可変長バイナリデータ(8000バイトまで) 実際のバイト数 + 2
varvinary(max)
image 		可変長バイナリデータ(2GBまで)
その他 bit 1, 0, NULL のどれか 1
table SELECT文実行結果のテーブルを格納可能 実際のデータ量
XML XMLデータを格納可能  
uniqueidentifier 16バイトのGUID(グローバルユニークID) 16
timestamp
(rowversion) 		テーブルのデータが更新される度に加算されるカウンタ(行バージョンを格納) 8
sql_variant 複数のデータ型を格納可能 実際のバイト数

整数型　～ tinyint, smallint, int, bigint ～

整数型（tinyint, smallint, int, bigint）は、その名のとおり整数だけを格納することが可能な型です。4種類の違いは、扱える数値の範囲が違うことです。一番良く使うのは「int」で、その次に「tinyint」くらいでしょうか。「bigint」もたまに使うことはありますが、「smallint」は使ったことがないですし、いまいち使い道も微妙な気がします（これにするくらいならintにする・・・。）

データ型 使用するサイズ(バイト数) 扱える数値の範囲
tinyint 1 0 ～ 255
smallint 2 -32,768 ～ 32,767
(おおよそ　-3万から3万)
int 4 -2,147,483,648 ～ 2,147,483,647
(おおよそ　-20億から20億)
bigint 8 -9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807
(おおよそ　-900京から900京)

tinyintは0を含む正の数のみ

tinyintはサイズが一番小さく、1バイト = 8ビット符号無し整数で表現できる 0 ～ 255 までの256通りとなります。性別コードとかフラグとか、コード系（状態が10種類あるのを、1～10のコードで表す場合など）で明らかに255までに収まるであろうものはこの型を使うといいです。

tinyintの範囲

smallint, int, bigint は0も正の数も負の数も取り扱える

tinyint以外の型は、最上位ビットに符号（プラスかマイナスか）を持ちますので、マイナスの値も取り扱えます。

smallintの範囲

値の有効範囲

取り扱える値の範囲を超えたらどうなるのか、実際に試してみましょう。「tinyint」と「smallint」の列を持つテーブルを作成して、INSERTしてみることで確認できます。

intの値の範囲確認 

 INSERT INTO intTable VALUES( -1, 0) /* tinyint に -1 はNG */ INSERT INTO intTable VALUES( 0, 0) /* tinyint に 0 はOK */ INSERT INTO intTable VALUES(255, 0) /* tinyint に 255 はOK */ INSERT INTO intTable VALUES(256, 0) /* tinyint に 256 はNG */ =================================================================
メッセージ 220、レベル 16、状態 2、行 1 値 = -1 はデータ型 tinyint では算術オーバーフロー エラーになります。 ステートメントは終了されました。 (1 行処理されました) (1 行処理されました) メッセージ 220、レベル 16、状態 2、行 4 値 = 256 はデータ型 tinyint では算術オーバーフロー エラーになります。 ステートメントは終了されました。

下の実行結果の画面に、エラーメッセージが表示されます。まず１行目は「-1」を入れようとしたので「値 = -1 はデータ型 tinyint では算術オーバーフロー エラーになります。」と表示されています。２，３行目は問題無いので「(1 行処理されました)」と表示されており、４行目は「256」を入れようとして１行目と同じエラーが出ます。

smallintも試してみましょう。tinyintと同じようにエラーが表示されます。

 INSERT INTO intTable VALUES(0, -32769) /* smallint に -32769 はNG */ INSERT INTO intTable VALUES(0, -32768) /* smallint に -32768 はOK */ INSERT INTO intTable VALUES(0, 0) /* smallint に 　0 はOK */ INSERT INTO intTable VALUES(0, 32767) /* smallint に 32767
はOK */ INSERT INTO intTable VALUES(0, 32768) /* smallint に 32768 はNG */ ================================================================= メッセージ 220、レベル 16、状態 1、行 1 値 = -32769 はデータ型 smallint では算術オーバーフロー エラーになります。 ステートメントは終了されました。 (1 行処理されました) (1 行処理されました)
(1 行処理されました) メッセージ 220、レベル 16、状態 1、行 5 値 = 32768 はデータ型 smallint では算術オーバーフロー エラーになります。 ステートメントは終了されました。

小数点以下は切り捨て

そもそも「int」とは「integer（整数）」のことですので、小数点付きの値は取り扱えませんので、切り捨てされます。これも実際に試してみましょう。

 INSERT INTO intTable VALUES(1.5, -1.5) =================================================== (1 行処理されました)

INSERT自体は成功しているようです。しかし格納された値を確認すると、小数点以下が切り捨てられているのが確認できます。

小数点以下は切り捨てられている

小数（真数型）　～ decimal, numeric ～

decimalとnumericは、表記が違うだけで中身は同一です。そもそもdecimalとは十進数という意味で、実際そのように取り扱われますが、numericだとちょっと連想しづらいので私はずっと「decimal」を使っています。decimalは、小数点付きの値を取り扱える型で、 decimal(p, s) という形で桁数を指定します。

最大桁数は、整数部と小数部を合わせて38桁（ビットじゃありません）なので、天文学的な値になります。p に指定するのは整数部の桁数ではなく、小数部も合わせた全体の桁数ですので注意が必要です。

decimalの桁数指定

有効桁数内は精度が保証される

decimalは十進数として扱われる為、指定した桁数文は精度が保証されるので、金額を扱うような場合は迷わずdecimalを使いましょう。ちなみに、有効桁数を超えた場合ですが、小数点以下は指定した桁数で丸められますが、整数部が指定した桁数を超えた場合は算術エラーになります。

decimal列を作成 

 INSERT INTO decimalTable VALUES(1.123) INSERT INTO decimalTable VALUES(1.1234) INSERT INTO decimalTable VALUES(1.1235) INSERT INTO decimalTable VALUES(1.1236)

小数点以下は丸められる 

 /* 整数部は2桁しか格納できない */ INSERT INTO decimalTable VALUES(123) INSERT INTO decimalTable VALUES(123.1234) INSERT INTO decimalTable VALUES(1234) INSERT INTO decimalTable VALUES(1234.1234) ========================================================= メッセージ
8115、レベル 16、状態 8、行 1 int をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。 ステートメントは終了されました。 メッセージ 8115、レベル 16、状態 8、行 2 numeric をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。 ステートメントは終了されました。 メッセージ 8115、レベル 16、状態 8、行 3 int をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。
ステートメントは終了されました。 メッセージ 8115、レベル 16、状態 8、行 4 numeric をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。 ステートメントは終了されました。

使用されるバイト数

decimalとnumericでは、有効桁数を自分で決められるので、指定した桁数（p：全体の桁数）により使用されるバイト数が変わってきます。有効桁数と使用されるバイト数は表のとおりです。

p（全体の桁数） 使用されるバイト数
1 ～ 9 5
10 ～ 19 9
20 ～ 28 13
29 ～ 38 17

小数（概数型）　～ float, real ～

floatとrealは小数点付きの値を扱える型ですが、decimalやnumericと違い桁数を指定することができません（これを浮動小数点数といいます）。floatとrealの取り扱える値の範囲は以下のとおりです。

データ型 使用するサイズ(バイト数) 有効桁数
float 8 整数部、小数部合わせて15桁
real 4 整数部、小数部合わせて7桁

floatとrealは浮動小数点数ですので、値によって小数点以下の桁数が変動してしまいます。なので、使いどころはよく考えた方がいいです。

floatとrealの違い

文字列　～ char, varchar ～

文字列を格納する場合は、「char」や「varchar」を使います。2つの違いは、charが固定長でvarcharが可変長であるという点です。固定長の場合足りない部分はスペースで自動的に埋められます。

charもvarcharも格納可能な最大サイズを「char(10)」「varchar(10)」のように指定します。

charとvarcharの違い

上の図のとおり、charは常に指定した最大バイト数分消費されてしまうので、使いどころはよく考えた方がいいです。格納するデータの桁数が常に固定、もしくは取り出した値の足りない部分はスペース埋めしたまま取り扱うような場合であればcharも有効だと思いますが。そうでもなければvarcharを使用する方が無難です。

桁数にバラ付きがある上に、実際に格納されたデータの部分だけしか使用しない場合、取り出した値から後ろのスペースをわざわざ取り除かないといけないですし、もしそれを忘れるとバグが発生してしまいます。

半角は1バイト、全角は2バイト

charとvarcharで指定するのは文字数ではなく、バイト数です。半角文字は1文字1バイト、全角文字は1文字2バイトになります。DATALENGTH という関数（詳しくは次回のエントリで）を使用すると、データのバイト数を計ることができます。使用方法は、調べたいデータを DATALENGTH() のカッコの中に書き、SELECT文を実行します。

半角は1文字1バイト全角は1文字2バイト

指定されたバイト数を超える値を格納しようとするとエラーが発生します。

 /* どちらも10バイトに収まっている */ INSERT INTO charTable VALUES('1234567890', '１２３４５') /* 11バイトを指定しようとしてエラーが起きる */ INSERT INTO charTable VALUES('12345678901', '１２３４５') ======================================================= (1 行処理されました) メッセージ 8152、レベル 16、状態
14、行 5 文字列データまたはバイナリ データが切り捨てられます。 ステートメントは終了されました。

8000バイトを超える文字を格納するには

charもvarcharも指定できる最大バイト数は8000ですが、これを超えて文字列を格納したい場合は、バイト数の指定のところを「max」を指定します。こうすることで、最大2GBまで格納できるようになります。

Unicode文字列　～ nchar, nvarchar ～

charとvaecharは、標準の日本語環境のまま変更していなければ「Shift_JIS」で取り扱われます。Unicodeの文字列を格納したい場合は、「nchar」「nvarchar」を使用します。頭に「n」が付いているだけで、charは固定長、varcharは可変長であることは変わりません。

Unicodeは1文字2バイト

Unicodeでは、半角だろうが全角だろうが1文字を2バイトで表現します。格納できるサイズは8000で変わらないのですが、型を指定する際は「nchar(4000)」という風に4000までしか指定できません。ここの数字って文字数なんだっけ？と思ってしまいましたがそういう仕様だそうです。

SQLの中で文字列を指定するときは”で囲みますが、頭に「N」を付けることで、Unicode文字列だと認識してくれます。Nは”の中に入れてはだめです。

Unicodeは1文字2バイト






日付・時刻　～ date, datetime2, time ～

日付や時刻を格納する型は、「date」「datetime2」「time」などがあります。それぞれの違いは、日付（年月日）だけを格納するか、時分秒まで含めるか等の違いです。SQL Server 2008 からは、「smalldatetime」や「datetime」は使用せず、「date」「datetime2」「time」を使用するよう推奨されています。

データ型 使用するサイズ(バイト数) 精度 データの範囲
smalldatetime 4 1分 1900年1月1日 ～ 2079年6月6日
datetime 8 3.33ミリ秒 1753年1月1日 ～ 9999年12月31日
date 3 1日 1年1月1日 ～ 9999年12月31日
time 3 ～ 5 100ナノ秒 00:00:00.0000000 ～ 23:59:59.9999999
※時刻のみ
datetime2 6 ～ 8 100ナノ秒 1年1月1日 ～ 9999年12月31日
datetimeoffset 8 ～ 10 100ナノ秒 1年1月1日 ～ 9999年12月31日
※タイムゾーン付　-14:00 ～ +14:00

date型は日付のみを格納

dateは年月日だけを格納する型で、西暦1年1月1日 ～ 9999年12月31日までを取り扱えます。時分秒が不要な日付データを格納する時はdate型を使いましょう。INSERTする時に時分秒のデータを指定しても、切り捨てられます。

date型は年月日まで

日付と言っても、結局は文字列なんですが、あくまで日付として解釈できる形式の文字列を指定するということになります。日付として解釈されるには以下のような形式です。

  • 1993-01-01（1993-1-1 でも可、日だけ省略でき、その場合は1日が自動で入る）
  • 1993/01/01（1993/1/1 でも可、日だけ省略でき、その場合は1日が自動で入る）
  • 1993年1月1日（月と日は省略でき、その場合は1月もしくは1日が自動で入る）
  • 19930101（月と日は省略でき、その場合は1月もしくは1日が自動で入る）

datetime2型は時刻も含む標準的な日付型

datetime2は一番標準的な日付型で、年月日時分秒に加え、ミリ秒以下まで格納できます。年月日は西暦1年1月1日 ～ 9999年12月31日まで格納でき、秒数は小数点以下最大7桁まで格納でき、これはユーザが指定することができます。

datetime2型の指定の仕方

時刻の指定は任意

datetime2型は時分秒以下まで格納できますが、省略することもできます。省略した場合は、全て0で規定値が入ります。時刻を指定する場合は、年月日に続けて半角スペースを一つ入れ、「HH:MM:DD.xxxxx」の形式にします。

時刻の指定の仕方

time型は時刻のみを格納

time型は、その名のとおり時刻だけを格納します。0時0分0秒 ～ 23時59分59秒までは基本ですが、datetime2型と同じく秒数の小数点以下の桁数を指定します。イメージは、date型とtime型を足したものがdatetime2型というかんじです。

各日付型が取り扱う範囲

金額・通貨　～ smallmoney, money ～

smallmoneyとmoneyは、その名のとおり金額（通貨）を格納する為の型です。smallmoney型は4バイト、money型は8バイト分のデータを格納できます。

データ型 使用するサイズ(バイト数) 有効桁数
smallmoney 4 – 214,748.3648 ～ 214,748.3647
おおよそ　-21万 ～ 21万
money 8 -922,337,203,685,477.5808 ～ 922,337,203,685,477.5807
おおよそ　-900兆 ～ 900兆

外国では小数点付きで金額を表現したりする（1.2＄とか）のですが、日本では最小単位が1円ですから、これを使用するくらいならintやdecimalを使いましょう。

で、結局どれを使ったらいいの？

データ型は沢山ありますが、実際どれを使うべきなのか、あくまで私の持論ですが以下にまとめてみました。

種類 データの内容 型 初期選択
文字列 桁数が常に固定かつブランクがない char
それ以外の文字列
（フラグやステータスコードなどの数字も含む）		 varchar
○
数値 明らかに0 ～ 255に収まる tinyint
-20億 ～ 20億に収まる int
○
20億を超えそう bigint
日付 明らかに時刻は不要 date
年月日に加えて時刻も必要（あった方がいい場合） datetime2
○
明らかに時刻のみでいい time

文字列、数値、日付それぞれにおいて、特に理由がなければこれを選ぶべしという型を「初期選択」の列に○を付けています。ご参考までに。

この記事と関連性の高い記事