SELECT INTO bug

  • Has anyone encountered a bug that if you do a select into using a left join and an order by, all the columns have their nullability set to nulllable. In sql7 you can get round it by creating a view with a top 100 percent and the order by, but even this doesn't work in 2000

    The script below shows the problem

    drop table fred

    drop table fred_notOrdered

    drop table fred_Ordered

    drop table fred_Ordered2

    drop view s

    go

    create table fred (col1 int not null)

    insert into fred values (1)

    insert into fred values (3)

    insert into fred values (2)

    go

    create view s as select TOP 100 PERCENT f1.col1 from fred f1

    LEFT JOIN fred f2 on f1.col1= f2.col1

    order by f1.col1

    go

    select f1.col1

    into fred_NotOrdered

    from fred f1

    LEFT JOIN fred f2 on f1.col1= f2.col1

    option (maxdop 1)

    go

    select f1.col1

    into fred_Ordered

    from fred f1

    LEFT JOIN fred f2 on f1.col1= f2.col1

    order by f1.col1

    option (maxdop 1)

    go

    select *

    into fred_ordered2

    from s

    go

    select * from fred

    select * from fred_notOrdered

    select * from fred_Ordered

    select * from fred_Ordered2

    go

    sp_help fred_notOrdered

    go

    sp_help fred_Ordered

    go

    sp_help fred_Ordered2

    go


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • Haven't seen it. Verified this occurs in SQL 7, does not occur in 2000.

    Personally, I always avoid select into. Create the table and use insert select. It allows you the control.

    Steve Jones

    steve@dkranch.net

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

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