Record order in table
-
I have several lookup tables in a database I'm developing. Recently I noticed that when I go to Open Table -> Return all rows on some of these tables the order of the rows is no longer sorted by the primary key column, but rather a varchar column. I also noticed that stored procedures that return rows from these tables also are sorted in this manner. I have a .NET application that uses these tables and the application requires that these rows are in order by their primary key, so I had to add ORDER BY clauses to the stored procedures. Does anyone have any idea why SQL Server arbitrarily decides to sort tables differently? Is there something I can do to prevent it? It's not a big deal to add ORDER BY clauses, but it seems like this can be avoided. Thanks,
Chad
-
Well, I figured that it was something simple that I missed and it was. It was a clustered index issue and I simply made the primary key columns non clustered since the tables have very few records and I don't need to make them CLUSTERED. That solved my sorting problem.
-
When using SQL the only guarentee that records are returned in a particular order is to use Order By. Otherwise your are dependent on the "habits" of the underlying database which as you have discovered can change from time to time.
Should someone come along some day and remove that clustered index then you'll have a problem again. Or if Sql Server should change the internal structure of clustered indexes the problem could show up again.
My adivce, use Order By and you'll never have that problem again.
-
What you are asking is like going to a geography newsgroup and posting "how far can I travel before I fall off the edge of the Earth?"
Fortunately we live on Earth and not on Terry Pratchett's Discworld. Would be a valid question there.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I never asked for an assessment of my skill level. I'll readily admit that I am by no means a certified DBA, but I am certainly past having to read documentation before attempting to develop a database. I asked how did rows in my tables get returned differenty and I found a clear cut answer. After digging I came up with "the data rows are not sorted and stored in order based on their nonclustered keys" from the online help. I feel sorry for you if you have nothing better to do than peruse USER FORUMS and attack other users.
-
Chad -
While I realize that the posting about basics may feel like a personal attack (and maybe could have been phrased a bit softer), there is a real grain of truth to it.
Many people waste a great deal of time trying to force relational databases to work like flat files. They often back themselves into a corner when they keep pursing non-set based database use. Heck, even some very intelligent and experienced people miss the mark from time to time (including me
), thinking about the data in the way they last saw it.I hope that you can take the feedback to heart. If you approach each challenge by first thinking about the new things RDMSs bring to the table (such as presentation is separate from storage, what you see now is not guaranteed to be what you see tomorrow, etc), then you will have mastered one of the basics.
We're trying to save you some time and pain later. Sort of 'free advice that's been paid for'.
Karen
-
Karen,
Thanks for the kind response.
I think I must not have phrased my original question properly. The database I'm developing is definitely an RDBMS system. What I have are these lookup tables filled with 10-15 questions each. These questions are seperated into different tables by different topics and are to be asked in an application I'm writing. I filled the tables with these questions in the order they needed to appear in a .NET DataGrid. I did it this way so that when a question changed or was removed all the users of the app would see it without having to rewrite the app's code. It's just that I recently noticed that when the questions were being returned from the sprocs they were no longer in the order of the primary key column. Hence this
QuestionID Question 1 Why is the sky blue? 2 What time did you get to work today?
became this
QuestionID Question 2 What time did you get to work today? 1 Why is the sky blue?
And I just simply wanted to know what caused this to happen. So, I did some digging in the online help and found that making the primary key column a clustered index can cause this to happen. So, I changed the index of the primary key column to be Non Clustered and this seemed to fix it. However, as Ron suggested I'm going to leave an ORDER BY clause in the sprocs that return the questions from these tables to ensure that I get the questions in the order that I want them in. I can now see if I interpreted the question as some did, it would seem very basic to me as well. I appreciate the positive responses. Thanks,
Chad
-
Chad, take it easy. I have stopped counting how many times Joe posted this to online communities.
Now for your question: As has been mentioned, there is no guarantee for a certain ordering in your resultset unless you specify it by ORDER BY. SQL Server will choose the fastest way to return the rows to you. And while this is in most cases equal to the ordering of the clustered index, there are exceptions to this, you simply can't rely on it. So, always specify the ORDER BY and there should be no suprise anymore.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply