SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 guaranteeing insert order returned, without order?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 05/12/2004 :  09:53:46  Show Profile
Hi all,

Feel kinda silly asking this, but I am trying to find a definitive answer, and didn't get it in BOL.

Given a newly created table, with no indexes.
Insert a host of records (lets say 1 million) in a very specific order.
Select * from the table.

Is the select going to be in the same order as the rows were inserted?

I am sure I read that there is no guarantee that rows will be returned in the same order they were inserted, and that you must use Order by to ensure data is sorted the way you want it.

We have guys building this temp table, then selecting from it, and they would like to avoid indexing the table, since they say they will read all the rows when they read, and they want to avoid the overhead of the index on the insert.

My gut says to cluster the table on their "sort criteria" ...

Comments ?

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/12/2004 :  09:57:40  Show Profile  Visit jsmith8858's Homepage
quote:

Is the select going to be in the same order as the rows were inserted?



No ... the order is never guaranteed unless you use an ORDER BY clause. now, you might select from the table 1000 times with no ORDER BY and it might always come back ordered just as you like, but it still is not guaranteed and SQL Server is under no obligation to always have that same behavior. A patch, bug fix, new version, daylight saving time, mild weather, a Red Sox losing streak, or some other detail may completely change this at some random time.

- Jeff
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 05/12/2004 :  10:13:21  Show Profile
Hi Jeff,

That was what I thought
quote:
I am sure I read that there is no guarantee that rows will be returned in the same order they were inserted, and that you must use Order by to ensure data is sorted the way you want it.



Has anyone got a link to a formal, like an MS article or a book, that states this ? Maybe even explains it ?

Thanks

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Edited by - Wanderer on 05/12/2004 10:15:52
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/12/2004 :  11:12:34  Show Profile
Search for "Merry-Go-Round Scan".
Or have a look at pages 99 and 100 in "Inside Microsoft SQL Server 2000".

quote:

SQL Server 2000 Enterprise Edition dynamically adjusts the maximum number of read ahead pages based on the amount of memory present. For all other editions of SQL Server 2000 the value is fixed. Another advance in SQL Server 2000 Enterprise Edition is commonly called merry-go-round scan, which allows multiple tasks to share full table scans. If the execution plan of an SQL statement calls for a scan of the data pages in a table, and if the relational database engine detects that the table is already being scanned for another execution plan, the database engine joins the second scan to the first at the current location of the second scan. The database engine reads each page once and passes the rows from each page to both execution plans. This continues until the end of the table is reached. At that point, the first execution plan has the complete results of a scan, but the second execution plan must still retrieve the data pages that occur before the point at which it joined the in-progress scan. The scan for second execution plan then wraps back to the first data page of the table and scans forward to the point at which it joined the first scan. Any number of scans can be combined in this way; the database engine will keep looping through the data pages until it has completed all the scans.
-http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx


Jay White
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/12/2004 :  12:34:28  Show Profile
quote:
Originally posted by jsmith8858

a Red Sox losing streak




See, some things are guaranteed ....





Brett

8-)

Edited by - X002548 on 05/12/2004 12:35:00
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 05/13/2004 :  06:27:06  Show Profile
Thanx Guys - this looks great. I've managed to convince the developer to order by. Now to prove that he needs the index :-)

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

ravilobo
Flowing Fount of Yak Knowledge

India
1184 Posts

Posted - 05/13/2004 :  11:47:24  Show Profile
quote:
Originally posted by Wanderer


We have guys building this temp table, then selecting from it, and they would like to avoid indexing the table, since they say they will read all the rows when they read, and they want to avoid the overhead of the index on the insert.

My gut says to cluster the table on their "sort criteria" ...

Comments ?




If it is 1 million rows and a range search go for a CLUSTERED index.
You can drop and recreate the index before and after importing the data. Since it is a single table you can also think of using BCP. BCP is much faster than rest of the options.


------------------------
I think, therefore I am
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000