Prev: Update a value in an XML column
Next: Index Rebuild with ONLINE = ON causes major blocking
From: tshad on 31 Mar 2010 19:21

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:q_qdnd1Bq8s4Ui7WnZ2dnUVZ_qUAAAAA(a)speakeasy.net...
> One more comment: I do not see the target Employees table in the FROM
> clause. While the query may work it is a good practice when using the SQL
> Server specific UPDATE with FROM clause to include the table in FROM.
>
True.

Thanks,

Tom


From: tshad on 31 Mar 2010 19:28

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:q_qdndxBq8v0SS7WnZ2dnUVZ_qWdnZ2d(a)speakeasy.net...
> tshad wrote:
>> Why would that be the case?
>>
>> The result set wouldn't be in the order of StatusID - which I assume is
>> why they have the ORDER BY.
>
> ORDER BY still needs to sort in order to provide the TOP values.
>
I agree with that I was commenting on your statement:

(this may depend if you have an index on the column)

Not sure why having an index on the column would affect whether you sort the
result or not.

>>
>> But if the TOP statement were removed, I assume that the ORDER BY would
>> slow down the querys unnecessarily.
>
> No, you will get an error. A table expression cannot be ordered set, and
> you cannot use ORDER BY without the TOP option.
>

Right. I assume by table expression you are referring to any type of table
expression: Derived Tables, Common Table Expressions, Subqueries etc.

Thanks,

Tom

> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 31 Mar 2010 21:23
tshad wrote:
> I agree with that I was commenting on your statement:
>
> (this may depend if you have an index on the column)
>
> Not sure why having an index on the column would affect whether you sort the
> result or not.
>

Because ORDER BY can use an existing index for the sort.

>>> But if the TOP statement were removed, I assume that the ORDER BY would
>>> slow down the querys unnecessarily.
>> No, you will get an error. A table expression cannot be ordered set, and
>> you cannot use ORDER BY without the TOP option.
>>
>
> Right. I assume by table expression you are referring to any type of table
> expression: Derived Tables, Common Table Expressions, Subqueries etc.
>

Correct.


--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 31 Mar 2010 21:41

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:q_qdnd9Bq8swaC7WnZ2dnUVZ_qUAAAAA(a)speakeasy.net...
> tshad wrote:
>> I agree with that I was commenting on your statement:
>>
>> (this may depend if you have an index on the column)
>>
>> Not sure why having an index on the column would affect whether you sort
>> the result or not.
>>
>
> Because ORDER BY can use an existing index for the sort.
>

But it is a derived table, and there would be no index on it, so how would
it use the index?

Doesn't it build the new table and the last thing it does is sort by the
columns you request?

Even if it had an index, the index would be on one of the tables used for
the join not on the resulting Derived table where you are sorting.

Tom


From: Plamen Ratchev on 31 Mar 2010 22:09
tshad wrote:
>
> But it is a derived table, and there would be no index on it, so how would
> it use the index?
>

I mean if there is an index on the column in the base table. A derived table is just a subquery and indexes on the based
tables are utilized.

> Doesn't it build the new table and the last thing it does is sort by the
> columns you request?

No, the optimizer may perform first sort if it finds that more efficient.


--
Plamen Ratchev
http://www.SQLStudio.com
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Update a value in an XML column
Next: Index Rebuild with ONLINE = ON causes major blocking