SQL Server Performance Forum – Threads Archive

ORDER BY in SELECT INTO

I have an Order by clause in a select into statement. however i create a clustered index on that table immediately with the columns that i give in the order by clause.
My question is, Does removing the order by clause affect anywhere wherever i use that table?
The statement i wrote is given below… SELECT IDENTITY(INT, 1, 1) AS ABC,
ccp.SupplierSeriesNumber,
‘C’ AS InheritedPriceCode
INTO dbo.Table2
FROM dwFOODS.dbo.Table1 ccp
WHERE ccp.StartDateID <> -1
ORDER BY
ccp.SupplierSeriesNumber,
ccp.ProductID CREATE CLUSTERED INDEX IX_Index1 ON dbo.Table2 (SupplierSeriesNumber, ProductID) Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Check this out:http://support.microsoft.com/default.aspx?scid=kb;en-us;273586 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Interesting topic link, Frank! In any case, you should never rely on the order of values on an identity column to carry any meaning. Also, you must always use an ORDER BY clause to get an ordered resultset. Without it, you may get an ordered resultset, but only by accident.

Frank is always helpful in giving useful links. I learned more from the links he posted [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Thanks for that kind words. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Thanks Frank. That was very interesting. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

]]>