|
|
Posted by drawnai on 04/23/06 02:35
I'm sorry David, I'd just drank a couple of bottles of particularly
nice Castello Banfi, Brunello di Montalcino. Any provocative statements
I make during such inebriated times is purely to have a poke. I value
your opinions I assure you.
As for your comments about updates that affect multiple rows being
undefined, I'm afraid you're wrong.
Update table set @fred = column = @fred + 1 is defined in SQL server's
own help, please look it up if you don't believe me.
Not only that, but SQL Server evaluates columns in order (otherwise you
can't do order by 1, order by 2 etc.)
Thus, it's perfectly legal to do this...
declare @rows int
Select a.Column1,
a.Column2,
b.Column3,
b.Column4.
c.Column5.
d.Column6,
sort1idx = convert (smallint, null)
sort2idx = convert (smallint, null)
sort3idx = convert (smallint, null)
into #results
>From A
Inner Join B on A.join = B.join
inner join C on C.join = b.join
Select @rows = @@rowcount,
@rc = @@error
create index #re1 on #results (column6, column3)
create index #re2 on #results (column2, column4)
create index #re1 on #results (column1, column5)
declare @fred = int
set @fred = 0
update R set @fred = @fred + 1, sort1idx = case when @fred <= 1000 then
convert (smallint, @fred) when @rows - @fred < 1000 then convert
(smallint, 2000 + @rows - @fred) else null end From #results R With
(index= #re1)
set @fred = 0
update R set @fred = @fred + 1, sort2idx = case when @fred <= 1000 then
convert (smallint, @fred) when @rows - @fred < 1000 then convert
(smallint, 2000 + @rows - @fred) else null end From #results R With
(index= #re2)
set @fred = 0
update R set @fred = @fred + 1, sort3idx = case when @fred <= 1000 then
convert (smallint, @fred) when @rows - @fred < 1000 then convert
(smallint, 2000 + @rows - @fred) else null end From #results R With
(index= #re3)
Select *
>From #results
where coalesce (sort1idx, sort2idx, sort3idx) Is not Null
-- This line returns the top and bottom 1000 by three dimensions (more
or less, I've been on Sauvignon Blanc in All bar one in Leicester
Square all night) and is the equivalent of SQL 2005's
Select t.Column1, t.column2, t.column3, t.column4, t.column5,
t.column6,
sort1idx = case when sort1idxasc + sort1idxdesc < 2002 then
sort1idxasc when sort1idxasc < 1001 then sort1idx when sort1idxasc >
1000 and sort1idxdesc > 1000 then null else 1000 + 1001 - sort1idxdesc
end, -- or something, I'm a bit pissed
sort2idx = etc...
sort3idx =
>From (
Select a.Column1,
a.Column2,
b.Column3,
b.Column4.
c.Column5.
d.Column6,
sort1idxasc = Row_number () (over column6, column3)
sort2idxasc = Row_number () (over column2, column4)
sort3idxasc = Row_number () (over column1, column5)
sort1idxasc = Row_number () (over column6 desc, column3 desc)
sort2idxasc = Row_number () (over column2 desc, column4 desc)
sort3idxasc = Row_number () (over column1 desc, column5 desc)
into #results
>From A
Inner Join B on A.join = B.join
inner join C on C.join = b.join) T Where sort1idxasc <= 1000 or
sort2idxasc <= 1000 or
sort3idxasc <= 1000 or
sort1idxdesc <= 1000 or
sort2idxdesc <= 1000 or
sort3idxdesc <= 1000)
It implements these with the same execution plan as it would a cursor.
I'm sure M. Ben Gan can confirm this.
The only difference is that SQL 2005 doesn't seem to need tempdb, and
it performs the work about 27% faster.
Navigation:
[Reply to this message]
|