Re: Row Order

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 11/02/04 

Date: Tue, 2 Nov 2004 10:13:07 -0800

There is no such thing a row order in a relational database table. SQL
Server will insert the rows wherever it can. Your rows may be inserted in a
particular order based on the ORDER BY in your initial SELECT, but that
doesn't guarantee they will be located in the table in any particular order.
For example, the first row inserted could be put on the 3rd page of the
table, the 2nd row could be put on the first page, etc. You have absolutely
no control over this, and it really doesn't matter because when you retrieve
the rows back out of the table, SQL Server can access them in whatever order
it deems most efficient. The only way to get a result set to appear in a
desired order is to use ORDER BY in your query. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Guadala Harry" <GMan@NoSpam.com> wrote in message 
news:edbcAaQwEHA.1308@TK2MSFTNGP09.phx.gbl...
> I'm inserting rows into a temp table via a SELECT statement that has an
> ORDER BY clause because I need for the rows to exist in the temp table in
> the order specified by the ORDER BY clause.
>
> The temp table is just a few varchar and int columns with an IDENTITY
> property and NO indexes.
>
> Someone told me that there was no guarantee that the rows would get 
> inserted
> into the temp table in the order specified by the ORDER BY Clause.
>
> Is this true? If so, why - and what can be done to "guarantee" that the 
> row
> ordering corresonds to the order returned by the SELECT statement?
>
> Here's what the setup looks like to which the person was commenting:
>
> USE PUBS
> CREATE TABLE #TEMP_TABLE
> (
>    RowNumber         [int] IDENTITY,
>    City              varchar(40),
>    State             varchar(40)
> )
>
> INSERT INTO #TEMP_TABLE (city, state)
>    SELECT city, state
>    FROM stores
>    ORDER BY stor_id
>
>
> Thanks.
>
>

Relevant Pages

  • SQL Server 2005 - stored proc w/temp tables & cursors works from Management Studio, but fail
    ... We have a very large & complex SQL ... logic and updates all columns and rows (using cursors from temp ... When we run it within SQL Server Management Studio - basically doing ...
    (microsoft.public.vc.database)
  • problem with SQL Server 2005 sp called from VC++ 6.0
    ... We have a very large & complex SQL ... logic and updates all columns and rows (using cursors from temp ... When we run it within SQL Server Management Studio - basically doing ...
    (microsoft.public.vc.mfc)
  • problem with SQL Server 2005 sp called from VC++ 6.0
    ... We have a very large & complex SQL ... logic and updates all columns and rows (using cursors from temp ... When we run it within SQL Server Management Studio - basically doing ...
    (microsoft.public.vc.language)
  • Importing text file
    ... The a temp table is created and the textfile is imported ... I read some stuff about DTS packages but I'm not real familiar with SQL ... Server and don't know how exactly to call the DTS package from my ...
    (microsoft.public.vsnet.general)
  • Hardware configuration help for MS SQL server
    ... the bigger question is what do we need at *minimum* for hard drives ... We can use BCP for inserting the data. ... but someone told me that with only 2 drives - it would cause SQL ... Could one server with dual Xeon CPUs do this, or do we need more than one ...
    (comp.databases.ms-sqlserver)