|
|
Posted by drawnai on 04/23/06 02:44
drawnai@hotmail.com wrote:
> 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)
-- God I am a bit smashed. Went straight out onto the piss after
leaving the ofiice vandag.
-- into #results -- -dednae mean that.
> >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
(or at least its transaction log, half so much)
, and
> it performs the work about 27% faster.
Navigation:
[Reply to this message]
|