SELECT INTO bug

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  January 8, 2002 at 3:19 am

  #77575

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737620

  More actions

  January 8, 2002 at 10:44 am

  #421908

  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