Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2004-05-12 : 09:53:46
|
| 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
7423 Posts |
Posted - 2004-05-12 : 09:57:40
|
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 |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-05-12 : 10:13:21
|
Hi Jeff,That was what I thoughtquote:
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! |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-05-12 : 11:12:34
|
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} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-05-12 : 12:34:28
|
quote:
Originally posted by jsmith8858a Red Sox losing streak
See, some things are guaranteed .... Brett8-) |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-05-13 : 06:27:06
|
| 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! |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2004-05-13 : 11:47:24
|
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 |
|
|
|
|
|
|
|
|
guaranteeing insert order returned, without order?