none
SQLserver2005における、Select Into句を使用したクエリの動作について RRS feed

  • 質問

  • お世話になります。
    掲題の件について質問させて頂きます。

    現在、SQLServer2000からSQLServer2005へのバージョンアップを行っています。

    バージョンアップの方法は
    SQLServer2000でフルバックアップを取得し、
    そのバックアップファイルをSQLServer2005で復元しました。

    移行後正常に動作しないプロシージャが存在します。
    うち、どうしても解決できない事があります。

    それが以下になります。

    Select Into句を使用しテンポラリテーブル(#も##も)へ値を挿入した場合で、
    挿入するデータがint型 or decimal型の場合、
    作成されたテーブルの定義はSQLServer2000ではNull許可となりますが、
    SQLServer2005ではNull不許可となります。
    但し、全てのint型とdecimal型がNull不許可になるわけではないので
    何か条件があったりするのか?と憶測しています。

    この差について、
    データベースのプロパティなど、既存のコードを変更せずに対応する方法はありませんでしょうか?
    何か確認する項目や対応方法など御座いましたら、ご教授頂きたいです。

    環境
    OS:
     WindowsServer2003R2 EnterpriseEditon SP2
    バージョン:
     SQLServer2000 StandardEditon SP3a
     SQLServer2005 StandardEditon SP3
    ※データベースの互換性レベルは2000にしてあります。

     

    2009年9月18日 8:29

回答

  • こんにちは、nagino です。

    すみません、詳細な検証は出来る状況にないので、2005 についてポイントだけ記載しておきます。

    (2000 はちょっと忘れましたが)2005 では SELECT 文の結果セットは型情報を持ちます。
    たしか NOT NULL 制約に関する情報も持ちます。
    INDEX の情報は持ちません。
    SELECT INTO 構文で作成されるテーブルは、その型情報を引き継ぎます。



    例を挙げると以下のようになります。

    まず、以下のようなテーブルがあるとします。
    CREATE TABLE [dbo].[TBL_SelectInto](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Int_notnull] [int] NOT NULL,
        [Int_nullable] [int] NULL,
        [Decimal_notnull] [decimal](18, 0) NOT NULL,
        [Decimal_nullable] [decimal](18, 0) NULL,
        [Datetime_notnull] [datetime] NOT NULL,
        [Datetime_nullable] [datetime] NULL,
        [Varchar_notnull] [varchar](50) NOT NULL,
        [Varchar_nullable] [varchar](50) NULL,
        CONSTRAINT [PK_TBL_SelectInto] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]
    ) ON [PRIMARY]

    ここで以下のクエリを実行します。

    SELECT *
    INTO TBL_Result
    FROM TBL_SelectInto

    ここで出来上がる TBL_Result は以下のような定義を持ちます。

    CREATE TABLE [dbo].[TBL_Result](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Int_notnull] [int] NOT NULL,
        [Int_nullable] [int] NULL,
        [decimal](18, 0) NOT NULL,
        [Decimal_nullable] [decimal](18, 0) NULL,
        [Datetime_notnull] [datetime] NOT NULL,
        [Datetime_nullable] [datetime] NULL,
        [Varchar_notnull] [varchar](50) NOT NULL,
        [Varchar_nullable] [varchar](50) NULL
    ) ON [PRIMARY]

    手元の SQL Server 2005 SP2 で確認しています。

    ただし、途中で JOIN や CASE 文等の処理を行った場合、条件が複雑になりますので現象を読み解くのに注意が必要です。

    一度上記をご確認のうえ、お手元の SELECT INTO 構文の処理と関連テーブルの列定義を追ってみてください。


    MCITP(Database Developer/Database Administrator)
    • 回答としてマーク 菊地俊介 2009年10月16日 8:56
    2009年10月1日 13:38

すべての返信

  • こんにちは、nagino です。

    すみません、詳細な検証は出来る状況にないので、2005 についてポイントだけ記載しておきます。

    (2000 はちょっと忘れましたが)2005 では SELECT 文の結果セットは型情報を持ちます。
    たしか NOT NULL 制約に関する情報も持ちます。
    INDEX の情報は持ちません。
    SELECT INTO 構文で作成されるテーブルは、その型情報を引き継ぎます。



    例を挙げると以下のようになります。

    まず、以下のようなテーブルがあるとします。
    CREATE TABLE [dbo].[TBL_SelectInto](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Int_notnull] [int] NOT NULL,
        [Int_nullable] [int] NULL,
        [Decimal_notnull] [decimal](18, 0) NOT NULL,
        [Decimal_nullable] [decimal](18, 0) NULL,
        [Datetime_notnull] [datetime] NOT NULL,
        [Datetime_nullable] [datetime] NULL,
        [Varchar_notnull] [varchar](50) NOT NULL,
        [Varchar_nullable] [varchar](50) NULL,
        CONSTRAINT [PK_TBL_SelectInto] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]
    ) ON [PRIMARY]

    ここで以下のクエリを実行します。

    SELECT *
    INTO TBL_Result
    FROM TBL_SelectInto

    ここで出来上がる TBL_Result は以下のような定義を持ちます。

    CREATE TABLE [dbo].[TBL_Result](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Int_notnull] [int] NOT NULL,
        [Int_nullable] [int] NULL,
        [decimal](18, 0) NOT NULL,
        [Decimal_nullable] [decimal](18, 0) NULL,
        [Datetime_notnull] [datetime] NOT NULL,
        [Datetime_nullable] [datetime] NULL,
        [Varchar_notnull] [varchar](50) NOT NULL,
        [Varchar_nullable] [varchar](50) NULL
    ) ON [PRIMARY]

    手元の SQL Server 2005 SP2 で確認しています。

    ただし、途中で JOIN や CASE 文等の処理を行った場合、条件が複雑になりますので現象を読み解くのに注意が必要です。

    一度上記をご確認のうえ、お手元の SELECT INTO 構文の処理と関連テーブルの列定義を追ってみてください。


    MCITP(Database Developer/Database Administrator)
    • 回答としてマーク 菊地俊介 2009年10月16日 8:56
    2009年10月1日 13:38
  • 皆様、こんにちは。

    naginoさん、回答ありがとうございます。

    悦正さん、フォーラムのご利用ありがとうございます。
    その後いかがでしょうか。疑問は解決しましたか?
    勝手ながら、有用な情報と思われたため、naginoさんの回答へ回答マークをつけさせていただきました。

    今後ともフォーラムをよろしくお願いします。
    それでは!
    2009年10月16日 9:06