October 15, 2003 at 1:17 am
Hello,
I am new to SQL Server 2000 and I am trying out the 'Import' feature. I successfully imported an Excel spreadsheet of 8144 rows into a pre-defined (empty) SQL Server 2000 table but for some reason the order is not entirely right. In the original spreadsheet the order is by Stock Ticker in column A and then by Date in column B, however, when I check the table it is not sequenced correctly.
Can someone explain what happened?
Thanks in advance.
October 15, 2003 at 1:57 am
Hi,
some points in here http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=17226 might be of interest to you.
In short, there is no ordering in a table.
However, you can use a clustered index to store in a sorted manner and, of course, force the db to order the rows when retrieveing data in a query by using ORDER BY
Frank
Hm... somehow the above link seem to me to be broken. I meant this link http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17226
Edited by - Frank Kalis on 10/15/2003 02:01:00 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 15, 2003 at 11:44 am
canguru,
In SQL Server the ONLY way you can guarantee that the ordering of your resultset is correct is to put an ORDER BY clause on your select statement. There are many reasons for this but just treat it as the LAW 🙂 and you should be fine.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 17, 2003 at 9:40 am
quote:
However, you can use a clustered index to store in a sorted manner
To an extent a clustered index will cause the rows to naturally be returned in the sort order implied by the clustered index. I think, however, that you can consider it reliable only if the table in quesetion is wholly contained within a single data page. If the table, therefore the clustered index, spans more than one page then you get the records in the order that SQL Server can most quickly retrieve them. The implication here is that if the pages that contain the table's data are not physically in order on the disk, SQL might return the rows sorted, but only sorted relative to the page they were stored in. Even with a clustered index, the only circumstance in which SQL Server (and relational databases in general) will gaurantee the order of the rows returned is with an explicit ORDER BY. Strictly speaking, the order in which the rows are stored in a table is not allowed to be important to the user.
Note that I think in SQL 6.5 and earlier, when it was fully reading a table, it started at page one and followed the links in order, regardless of the physical order or location of the pages on the disk, so a clustered index really did mean you could ignore the ORDER BY requirement. That technique, however, violated best practices and became broken when SQL 7.0 was released.
Matthew Galbraith
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply