特殊データの使用

  • [アーティクル]

特殊なデータ型は他のカテゴリに分類されないデータ型です。たとえば、顧客アンケートの「はい」や「いいえ」に対応して 1 または 0 としてデータを格納するには、bit データ型を使用します。Microsoft SQL Server 2005 には次のカテゴリに分類されるデータ型がいくつか存在します。

  • bit
    bit データは単一引用符で囲む必要はありません。0 と 1 だけを bit 列に格納できることを除き、SQL Server の整数型や数値型と同様の数値データです。

    文字列値 True および False は、次に示すように、bit 値に変換できます。

    SELECT CONVERT (bit, 'true'), CONVERT(bit, 'false')

    True は 1 に、False は 0 に変換されます。

  • sql_variant
    SQL Server の sql_variant データ型では、各種のデータ型のデータ値を 1 つの列、パラメータ、または変数に格納できます。sql_variant 列の各インスタンスには、データ値と、基本データ型、最大サイズ、小数点以下桁数、有効桁数、および照合順序を記述するメタデータ情報が記録されます。

    次の例の 2 番目のテーブルには、sql_variant 列があります。

    CREATE TABLE ObjectTable (ObjectID int CONSTRAINT PKObjectTable PRIMARY KEY, ObjectName nvarchar(80), ObjectWeight decimal(10,3), ObjectColor nvarchar(20))
CREATE TABLE VariablePropertyTable (ObjectID int REFERENCES ObjectTable(ObjectID), PropertyName      nvarchar(100), PropertyValue sql_variant, CONSTRAINT PKVariablePropertyTable PRIMARY KEY(ObjectID, PropertyName))

    特定の sql_variant 型のインスタンスのメタデータ情報を取得するには、SQL_VARIANT_PROPERTY 関数を使用します。

  • sysname
    SQL Server のインスタンスには、sysname という名前のユーザー定義データ型が含まれます。sysname は、オブジェクト名を格納するテーブル列、変数、およびストアド プロシージャ パラメータに使用します。sysname の正確な定義は識別子の規則に関係しています。したがって、SQL Server のインスタンス間で異なることがあります。sysname は、機能面では nvarchar(128) に相当します。SQL Server Version 6.5 以前では、短い識別子だけがサポートされるため、以前のバージョンでは、sysname は varchar(30) として定義されます。

    ms191240.note(ja-jp,SQL.90).gif重要 :
    データベースで大文字と小文字が区別されるか、バイナリ照合順序が使用されている場合、sysname は小文字で表記される場合に限り SQL Server システム データ型として認識されます。

  • table
    table データ型は一時テーブルに似たデータ型で、このデータ型を使用して、後続の処理用に結果セットを格納できます。このデータ型を使用できるのは、table 型のローカル変数と、ユーザー定義関数の戻り値を定義する場合だけです。

    table 変数または戻り値の定義には、列、各列のデータ型、有効桁数、および小数点以下桁数、オプションの PRIMARY KEY、UNIQUE、NULL、CHECK の各制約の定義が含まれます。ユーザー定義データ型は、データ型として使用できません。

    table 変数に格納された行やユーザー定義関数で返された行の形式は、変数を宣言するか関数を作成するときに定義する必要があります。この構文は CREATE TABLE 構文に基づいています。次に例を示します。

    DECLARE @TableVar TABLE (Cola int PRIMARY KEY, Colb char(3))

INSERT INTO @TableVar VALUES (1, 'abc')
INSERT INTO @TableVar VALUES (2, 'def')

SELECT * FROM @TableVar
GO

    table 変数、および table を返すユーザー定義関数は、特定の SELECT ステートメントおよび INSERT ステートメントでのみ使用できます。この場合、テーブルは、UPDATE、DELETE、および DECLARE CURSOR の各ステートメントでサポートされています。table 変数、table を返すユーザー定義関数は、その他の Transact-SQL ステートメントでは使用できません。

    テーブルに適用されたインデックスや他の制約は、DECLARE 変数または CREATE FUNCTION ステートメントの一部として定義する必要があります。これらは後から適用することができません。CREATE INDEX ステートメントや ALTER TABLE ステートメントでは table 変数およびユーザー定義関数を参照できないためです。

    table 変数とユーザー定義関数の定義に使用される構文に関する詳細については、「DECLARE @local\_variable (Transact-SQL)」と「CREATE FUNCTION (Transact-SQL)」を参照してください。

  • timestamp
    SQL Server timestamp データ型は、時間または日付とは無関係です。SQL Server timestamp 値は、データベース内で行われたデータ変更の相対順序を示す 2 進数です。timestamp データ型は、最初は SQL Server の復旧アルゴリズムをサポートするために実装されました。ページが変更されるたびに、現在の @@DBTS 値でタイムスタンプが設定され、その後 @@DBTS が 1 ずつ増加されます。これは、変更されたページの相対順序を決定する復旧には十分でしたが、timestamp 値と時刻との間に関連性はありませんでした。

    SQL Server Version 7.0 および SQL Server 2000 では、@@DBTS のみが timestamp 型の列で使用するために増分されます。テーブルに timestamp 型の列がある場合、行が INSERT、UPDATE、または DELETE ステートメントで変更されるたびに、その行の timestamp 値には現在の @@DBTS 値が設定され、その後、@@DBTS は 1 ずつ増加します。

    キー (特に主キー) には timestamp 型の列を使用しないでください。行が変更されるたびに timestamp 値が変更されるためです。

    テーブルでデータ変更が行われた時刻を記録するには、datetime データ型または smalldatetime データ型を使用して、変更が行われるときに値を自動的に更新するイベントおよびトリガを記録します。

  • 別名データ型
    別名データ型を使用すると、SQL Server の基本データ型 (varchar など) を、特定の用途に合わせた説明的な名前と形式に拡張できます。たとえば、次のステートメントでは、NULL 値を許容する birthday ユーザー定義データ型を datetime 基本データ型を使用して実装しています。

    EXEC sp_addtype birthday, datetime, 'NULL'

    ユーザー定義データ型を実装する場合に基本型を選択する場合は注意してください。たとえば、米国では、社会保障番号の形式は nnn-nn-nnnn です。社会保障番号に数字が含まれている場合、その数字は ID を示し、数学的演算の対象ではありません。そのため、ユーザー定義の社会保障番号のデータ型を varchar として作成し、テーブルに格納された社会保障番号の形式を適用するように CHECK 制約を作成するのが一般的です。

    EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'
GO
CREATE TABLE ShowSSNUsage (EmployeeID int PRIMARY KEY, EmployeeSSN SSN, CONSTRAINT CheckSSN CHECK ( EmployeeSSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )
)
GO

    SSN 列が通常はインデックス (特にクラスタ化インデックス) のキー列として使用されている場合、int 基本データ型を使用して SSN ユーザー定義データ型を代わりに実装すると、キーのサイズは 11 バイトから 4 バイトに縮小できます。このようなキー サイズの縮小により、データを取得する際のパフォーマンスが向上します。効率良くデータを取得できるようにして CHECK 制約の必要性をなくすことは、SSN 値を表示または変更する際に int から文字形式へ変換する余分な処理を行うよりも通常は優れています。

参照

その他の技術情報

CREATE FUNCTION (Transact-SQL)
データ型 (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
SQL_VARIANT_PROPERTY (Transact-SQL)
table (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手