January 8, 2002 at 3:19 am
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
January 8, 2002 at 10:44 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply