January 31, 2011 at 10:47 am
I have a table that has a clustered index on a column called time_stamp. When running a query, the data displayed in ascending order by time_stamp (which I would expect). I had to delete some rows from the table and then export some rows from another table into it.
When running a query now through Management Studio, the result set is not in ascending order by time_stamp. However, if I press 'Execute' a second time, the same result set does display in time_stamp order. Please explain.
January 31, 2011 at 11:15 am
If you want the data in a particular order, use an Order By clause. Otherwise, SQL will give you whatever order it comes up with, which may or may not match what you want.
By definition, rows in a relational database don't have an "order" to them. Rows in a Select query have an order if you tell them to, or don't if you don't.
That you can get them in sequence by the clustered index sometimes is just luck, essentially. It's not something you can count on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2011 at 7:26 am
I understand the need for the ORDER BY clause. As the matter of fact, I did the export using a query with an 'ORDER BY time_stamp' clause in the SELECT.
Any other thoughts?
February 2, 2011 at 6:13 am
Any other thoughts on what? You seemed to be asking about the order of the data returned from a Select statement. Do you have a different/other question?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2011 at 6:24 am
OK, I want to use a query to perform an export into a table named MergedData. I want to store the data in the MergedData table in ascending order on the time_stamp column.
if I export data into the MergedData table using the query:
SELECT time_stamp, userid, jsessionid
FROM AppServerData
ORDER BY time_stamp
Shouldn't it store the rows in the MergedData table in time_stamp order (ascending)?
February 2, 2011 at 3:31 pm
When you insert data into a table, SQL Server stores the data based on the table definition and index structure, not on how you feed the data to it. Read up on clustered and non-clustered indexes to see how the existence of indexes affects how SQL Server stores and retrieves data.
Jason Wolfkill
February 3, 2011 at 6:27 am
Rows in a table don't really have an "order" to them. They may be stored in a particular sequence (if they have a clustered index), but that's just a mechanical convenience for looking them up easily or for increasing speed for inserts.
The closest you'll get to having the rows in a table in a specific order is to create the clustered index on the column(s) you want to order the data by. But really, order matters when you're doing stuff with the rows, not when you're storing them. The real answer is to order the data when you need to use it that way.
What problem are you trying to solve by storing the data in a particular order? What's the "business need"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply