Re: Row Order

From: Guadala Harry (GMan_at_NoSpam.com)
Date: 11/02/04 

Date: Tue, 2 Nov 2004 11:15:35 -0800

<<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>>

IF your statement is true (which I assume it is), AND the temp table has an
IDENTITY property, then can I assume that the IDENTITY property will
accurately reflect the order in which the rows were inserted?

Please note that I am clear on the fact that order doesn't matter on storage
and neither me nor my application cares about that order (never have, never
will) ... but this particular scenario I'm dealing with is all about
retrieval and presentation of the data. Remember this is a TEMP table I'm
inquiring about...and... rather than having a denormalized "normal table"
(by that I mean 'not a temp table') that is maintained by triggers, I'm
creating a sp that builds the denormalized TEMP table on the fly, populates
it, and then SELECTS from it for reporting purposes (returns data to a
reporting application). The final SELECT statement is of course where I'm
ultimately concerned about returning data in a specific order. But given the
nature of this denormalized data, I'm chosing to place in IDENTITY column in
the table to reflect the order in which the data was inserted into the temp
table... because... rather than bringing in all the columns from the
underlying tables required to sort the final SELECT statement correctly, I
was hoping I could use the IDENTITY property which would reflect the order
in which the rows were inserted into the temp table.

-GH

-GH

"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:ONe4neQwEHA.3416@TK2MSFTNGP09.phx.gbl...
> 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

  • Re: faster way to write tables?
    ... What I've found to be the fastest way of inserting hundreds/thousands of records into a table which might already contain millions of rows, using ADO in Access/SQLserver is to use RecordSets and insert via a temporary table ... Create a new temp table which exactly matches the structure of the target table... ... For inserting millions of rows (into an Access database from SQLserver) I still haven't found anything which performs as well as... ... According to a random database creation log that I'm looking at, Access can insert 3 million rows in just over 70 seconds ...
    (borland.public.delphi.database.ado)
  • Help with a list variable
    ... then looping through the list and inserting the values into ... the temp table, then selecting all the records from teh temp table. ... DECLARE @startingPosition int ...
    (comp.databases.ms-sqlserver)
  • Re: Row Order
    ... Server will insert the rows wherever it can. ... the rows back out of the table, SQL Server can access them in whatever order ... > 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 ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with a list variable
    ... >I have made the following test stored proc that all it does right now ... then looping through the list and inserting the values into ... >the temp table, then selecting all the records from teh temp table. ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: Forcing a sequence in a table
    ... One of the consequences of a table having no inherent order is that ... there is no guarantee that the records will appear in the same order ... Then when inserting a record "between" two others, get the SortOrder ...
    (microsoft.public.access.tablesdbdesign)