ITコーディネータのシュウです。
先日、会社の同僚の誘いで海釣りに行く機会がありました。写真は、そのときにボートから撮った写真です。小学生の息子も連れて行って来ました。ボートに乗る30分から1時間くらい前には酔い止めの薬を飲んで（妻から、忘れちゃダメと何度も言われたので、そこはしっかりとやって）、いよいよ乗船！
最初のポイントでは、キスとかが釣れたらと思っていたところ、私も息子も同じく得体のしれない20cm超の魚をゲット！ 釣れたのはたぶん「オキエソ」と言われるあまりかっこうのよくない魚。知人が「外道」が釣れたねと言っていたので、すごい名前の魚もいるものだと思いましたが、実は、狙っていた魚ではないとき、外道が釣れたと言うんですね！
次のポイントの岩場で、息子はカサゴをゲット。私も結構な引きがあったので、もう少しでカサゴが釣れそうだったけれど、リールを巻いているときに逃げられてしまい、とても残念。
息子は初めての海釣りの経験だったのですが、なかなか釣れない中、頑張っていました。その日は潮時としては「小潮」と言われるときで、魚のエサの捕食行動が鈍り、釣りにくい日だったようで、確かに全体的に釣れなかったですね。
それでも、いい経験ができてよかったです。家に帰ったら、釣った魚を妻に料理してもらって、その日はおいしい魚料理でした。
＜本日の題材＞
テーブルの項目数、レコード長の制限
仕事でEXCELを使って様々な業務を行う場合に、列数がものすごく多いシートを扱わざるを得ないケースが出てきますよね。そのようなEXCELのシートをそのまま使ってWebシステム化するためのツールとして、dbSheetClientというものがあることは、以前ご紹介したと思います。
http://www.newcom07.jp/dbsheetclient/dbsclan/index.html
そのソフトを使って開発する際に、EXCELの内容をDBと連結するための設計を行うのですが、たくさんの列数をそのままのかたちでデータベースに持たせたいと思う場合もときにあります。
そんなときに、1つのテーブルに持てる列数っていくつまでなんだろう？ 1レコードに持てるレコード長に制限はあるのだろうか？ などということを考える場合があるので、今回その内容を取り上げてみます。
SQL Serverの場合、調べてみると、通常のテーブルでは、列数は1024まで、行ごとの最大のバイト数は8060バイトとなっています。
https://msdn.microsoft.com/ja-jp/library/ms143432.aspx
それでは、実際に1024個の列を持つテーブルを作成してみます。
CREATE TABLE test_col_max(
col_001key VARCHAR(10) NOT NULL
, col_002 VARCHAR(10)
, col_003 VARCHAR(10)
, col_004 VARCHAR(10)
, col_005 VARCHAR(10)
, col_006 VARCHAR(10)
, col_007 VARCHAR(10)
..... 途中省略
, col_1020 VARCHAR(10)
, col_1021 VARCHAR(10)
, col_1022 VARCHAR(10)
, col_1023 VARCHAR(10)
, col_1024 VARCHAR(10)
, CONSTRAINT PK_test_col_max PRIMARY KEY (col_001key)
) ;
テーブルは作成できました。
ただし、以下のような警告が表示されます。
「警告: 行の最大長が許容制限の 8060 バイトを超えています。一部の大きな値の組み合わせでは、挿入操作または更新操作が失敗します。」。これは、各列のタイプを VARCHAR(10)としたので、各列にMAXで値を詰め込んだ場合、10 * 1000 = 10000 バイトになってしまうので、このような警告が出るということです。
それでは、列数が 1025個ではテーブル作成がどうなるか試してみます。CREATE TABLE文で項目を1つ追加して、再度作成を実行してみると、下記のようなエラーが出て作成できません。
エラーメッセージは、「CREATE TABLE が失敗しました。テーブル 'test_col_max' の列 'col_1025' が、列の最大数 1024 列を超えています。」。やはり、1024列までしか作成できないことが確認できました。（これは、一時テーブルでも同じ）
では、1024列までの項目でテーブルの作成まではできたとしても、行サイズのトータルが8060バイトを超える場合はどうなるでしょうか？ それも試してみます。
まずは、8060バイトを超えない場合として、EXCELで作成したCSVファイルを、1024個の列を持つ「test_col_max」テーブルに登録してみます。前々回、BCPコマンドを紹介しましたが、今回は、BULK INSERTコマンドで登録してみます。各列に「1」、「2」など、1文字のみを設定したCSVファイル（サンプルとして3行）を読込んでみます。
BULK INSERT dbo.test_col_max
FROM 'c:\WORK\blog\blog_1024.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
登録できました。データを確認してみます。
SELECT * FROM dbo.test_col_max
ORDER BY col_001key;
データが3行作成されているのが確認できます。
次に、各列に「1000000000」など10文字ずつの値が設定されたcsvファイルを読込んでみます。
先ほどと同様に、BULK INSERTを実行してみると、下記のようなエラーが発生してデータを取込めません。
これは、行の合計のレコード長が10 * 1024 = 10240 バイトとなり、8060バイトを超えてしまっているためです。
なお、テーブルの列数の制限は、1024ですが、SELECT文での列数の最大値は 4096ということですので、以前取り上げた縦横変換などで、縦のデータを横に項目を並べて抽出する場合など、SELECT文で多くの項目を抽出する場合、1024を超えて4096列まで行うことができるということです。
<ORACLEの場合>
ちなみに、ORACLEではどうかというと、1つのテーブルに持てる最大列数は、1000列です。
上記と同様に、1001列を持つテーブルを作成しようとすると、下記のようにエラーになります。
「ORA-01792：表またはビューに指定できる最大列数は1000です。」というエラーが出て、テーブルを作成できません。
上記から1列減らして1000列を持つテーブルを作成しようとすると、下記のように無事作成することができました。
ここで、SQL Serverでは行の合計サイズの制限のためエラーとなった、各項目が全て10バイトまで詰まったCSVファイルを取込んでみます。ORACLEのSQL*Loaderというツールで行ってみます。
制御ファイルは以下のような感じ：
（項目が多いので、上記の制御ファイルの画面コピーは、途中で項目が切れてます。）
それでは実行してみます。
sqlldr userid=blog control=blog.ctl
問題なく取り込めました。結果を確認してみます。
SELECT * FROM TEST_COL_MAX
ORDER BY COL_001KEY;
1000個のすべての列に、10バイトずつ登録されたデータが確認できます。
ちなみに、ORACLEの場合は、行の合計レコード長が 8060バイトのような制限は特にはなく、例えば全ての項目がVARCHAR2型であれば、1項目で4000バイトまで持てるので、1000列 * 4000 バイト = 4000000 バイトまでは持てることになりますね。
※SQL Serverでも SQL Server 2005から、大きな値のデータ型とよばれる、varchar(max)、nvarchar(max)型というものが機能として増えています。これについては、別の機会に取り上げてみたいと思います。
今日は以上まで
にほんブログ村