Post reply

Select ... into statement generates different schemas between SQL2000 vs SQL2008R2

  • May 18, 2012 at 8:33 am

    #261466

    Hello,

    I'm in the middle of doing an upgrade from MS SQL 2000 to MS SQL 2008R2

    During the conversion of one of our DTS packages to SSIS, I found a curious thing with the results of one of the TSQL Statements.

    When I run the following kind of Statement in MS SQL 2000 vs MS SQL2008R2 (I've simplified the original statement as there are a number of inner Joins, as well as a Union as well)

    SelectDistinct

    Field1,

    Field2,

    Field3,

    Field4,

    CASEWhen WA.Field1 = ''1'' Then ''Primary''

    Else ''Secondary''

    End as Field5,

    IntoOutputTable

    FromInputTable emp

    Inner Join InputTable2 Wa on emp.field1=wa.field1

    I get differences in the schema that gets generated between the 2 environments.

    One of the difference's is in MS SQL 2000 fields 1,2,3,5 get generated as

    [char](30) NULL

    But in MS SQL2008R2 fields 1,2,3,5 get generated as

    [char](30) NOT NULL,

    Field4 gets generated in both environments as

    [char](30) NULL,

    I've done schema compares on all the source tables in both environments, and the schema's compare identically.

    I've run the statements in Query Analyzer in both enviroments with the same results, so I don't think it's a DTS vs SSIS thing.

    I've checked the Database options, and all the Ansi and null settings appear to be identical.

    I'm at a loss as to why the schema's would be different. Has anyone run into this before, or can shed some light as to why I'm encountering this situation?

  • May 18, 2012 at 8:54 am

    #1489746

    Can you give us the actual schema of the tables in question plus some sample data?

    See the link in my signature for help.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • May 18, 2012 at 1:43 pm

    #1489939

    Ensure you are running a recent build of 2008 R2. There were bugs that could result in a column being marked as NOT NULL when in fact it was nullable. On the other hand, it could also be correct, and 2008 R2 is reasoning about potential nullability better than 2000 did. Without seeing the full execution plan (and spending quite some time on the analysis!) it's impossible to say.

    There are a few KB/Connect items around this, the first one I found on a quick search was:

    http://connect.microsoft.com/SQLServer/feedback/details/532561/attempting-to-set-a-non-null-able-columns-value-to-null-on-sql-2008-sp1-and-2008-r2-ctp

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply