Sorting Table

Question

Sorting Table

Snigdha Sinha

SSC-Addicted

Points: 420
More actions
December 6, 2005 at 8:22 am

#188658

Hi,
I am inserting the values in a table in SQL Server 2000. Now my primary key is an int. But the values in my table have been sorted by some field which I am not able to identify. Because of this the last entry which I am adding is not getting appended in the end...but shows somewhere randomly in the middle of the table.
I want that my fields should be displayed acc to the Primary key. But I dont want to sort the rows programatically. Isnt there any feature in Enterprise Manager which enables us to set the field on thich items shoud get stored.
TestRequestId TestRequestNo
15      T13431
13      TR 12836
10      TR0906
9        TR1100
16       TR12050
20       TR12051
6         TR123
19        TR12358
17         TR12513
21        TR12662
18            TR12741
11          TR55
12          TR66
8           TR89

TestRequestStateId is my primary key and see how the data is. These are just 2 colums of my table.
Kindly help.
Snigdha

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ron k SSCertifiable Points: 6053 More actions · Answer 1

The only way to guarantee the order in which rows from a Select statment are displayed is to use an ORDER BY clause in your statement.

ron

Vinoo Say Hey Kid Points: 670 More actions · Answer 2

I always thought that a primary key constraint would sort the underlying column data.

But here I am surprised thats not getting sorted??? Why is it so??

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

You may be thinking that is the case because that is how Access databases work. SQL Server does not work that way.

Records may be stored in any order that SQL Server can store them most efficiently. If you insert records that cause a page split, then that new page will not necessarily be anywhere close the original page, so the physical order will not be retained. If you update a record with variable-length fields (varchar or varbinary, for instance), and that increases the size of the record greater than the available free space in the page, that will cause a page split and the records will not retain the same order.

Also, SQL Server query engine may break your query up into parallel queries for efficiency, and unless you specify an ORDER BY you will not have any consistency how the records are returned.

These are some of the reasons why Microsoft says that Order By is the only way to guarantee a sorted order in a resultset.

Hope this helps

Mark

Snigdha Sinha SSC-Addicted Points: 420 More actions · Answer 4

Hello,

Now I understand it completey and all makes sense to me. Looks like I have to use the OrderBy Clause.

Thanks a lot for the detailed explanation. Cleared my basics.

Snigdha