Re: Row Order
From: Guadala Harry (GMan_at_NoSpam.com)
Date: 11/03/04
- Next message: Sonya: "Re: Insert into script"
- Previous message: Mark Allison: "Re: Moving from Oracle to SQL Server"
- In reply to: Hugo Kornelis: "Re: Row Order"
- Next in thread: Tom Moreau: "Re: Row Order"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 3 Nov 2004 09:17:00 -0800
<<But the bottom line is that you just can't rely on this to work.>>
Great - this is good to know.
I'll be taking a different approach and pulling the columns from the
underlying tables into the temp table used to do the sorting in the final
SELECT.
Cheers!
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:6jcho0h4431fnnn0lm81lgpj5mogo6ak47@4ax.com...
> On Tue, 2 Nov 2004 16:16:56 -0800, Guadala Harry wrote:
>
> >Thank you Hugo for addressing my actual question...
> >
> >I like your alternative and would normally be doing that. The reason I
don't
> >want to do it is because the columns to sort on are spread amongst 4
> >underlying tables necessitating 3 joins on many columns. While that's not
a
> >big deal by itself I just didn't want all those columns in the temp table
> >because they would be used only for sorting and would not be returned to
the
> >caller. So I thought that IF - and this is the whole reason for the
original
> >post - I could trust the sequence of the inserts into the temp table
(with
> >or without IDENTITY), then I could omit all those columns and have a
cleaner
> >temp table and simpler final SELECT statement.
>
> Hi Guadala,
>
> Understood. But the bottom line is that you just can't rely on this to
> work. There is no way to force SQL Server to insert the rows (annd hand
> out the identity values) in the order you want it, even if you include an
> ORDER BY.
>
> The only workarounds I know are: using a subquery to calculate a rank and
> inserting that (instead of the identity) or using a cursor to insert the
> rows one by one (this WILL of course guarantee that the rows arre inserted
> and the identity values assigned in the desired order). Unfortunately, the
> first option is exponentially slow with large result sets; the second uses
> a trigger, which is generally frowned upon (and for good reason). In your
> case, I'd use the cursor approach if it's a one-time cleanup operation; if
> it's part of a regular, repeating process, I'd try other options first.
>
>
> > the columns to sort on are spread amongst 4
> >underlying tables necessitating 3 joins on many columns. While that's not
a
> >big deal by itself I just didn't want all those columns in the temp table
> >because they would be used only for sorting and would not be returned to
the
> >caller.
>
> I don't understand why you have a problem including more columns in the
> temp table. Okay, they take up some extra space in tempdb, they decrease
> the number oof rows per data page, reducing performance somewhat, but
> unless we're talking big columns (thousands of bytes), I don't think
> you'll notice the performance drop. If I were you' I'd give it a shot and
> test it.
>
> Other that that, you might also consider trying to post your actual table
> structure, sample data and problem description instead of the simplified
> example you posted before. That would allow me (and the other regulars) to
> look at your problem and consider using a different approach to address
> the problem - the simplified post has the down side that we can only help
> you with the solution you already decided upon, even if it's maybe not the
> best possible solution.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Sonya: "Re: Insert into script"
- Previous message: Mark Allison: "Re: Moving from Oracle to SQL Server"
- In reply to: Hugo Kornelis: "Re: Row Order"
- Next in thread: Tom Moreau: "Re: Row Order"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
