Table data shown not in order in E.M. Why?

Question

Table data shown not in order in E.M. Why?

Dinuj Nath-203580

SSC Veteran

Points: 207
More actions
April 23, 2005 at 4:50 am

#164057

Hi,
Normally when opening tables in Enterprise Manager. Rows are displayed in order they are inserted. But I have one table where this is not happening. I wanted to know why?
Thanks and regards
Dinuj
Thanks and Regards,
Dinuj Nath

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

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

If you have a clustered index or a primary key on the table (and you absolutely should have a Primary Key on every table), the data will be displayed in order of the clustered index or primary key (which is usually associated with a clustered index).

Now, why is it important to you that Enterprise Manager doesn't show things in the order you think it should? First, you should never rely on the mere order that something was entered. And, even if you have an autopopulating date field, you could insert more than 1 row in the same millisecond. Even if you add an autonumbering ID column, there is no particular guarantee the the ID will be numbered in precisely the same order that you think the records are being inserted in (it'll be close but not always 100%). Second, most of the work you do will be in stored procedures or, perhaps, views. Add the appropriate ORDER BY and stop worrying about what order the data is stored in. It'll just drive you nuts and it really doesn't matter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ron k SSCertifiable Points: 6103 More actions · Answer 2

Many people develop the false idea that Sql Server or any relational database will return rows in the order they were inserted. Although this may appear to be the case at times there is just no guarantee of the order rows are returned without the use of the ORDER BY clause.

If you need to depend on the order of the rows then you must use an Order By clause.

To specifically answer your question, the order that the rows are returned (without an Order By clause) in EM is sometimes influenced by rows having been deleted and/or inserted.

Dinuj Nath-203580 SSC Veteran Points: 207 More actions · Answer 3

Thanks guys,

I know tables are basically sets so the ordering is irrelevant. But I am doing some dev so I check if the insertions were correct. I know I can always use the ORDER clause. I posted this just to check if there was any other way.

Thanks and regards

Dinuj Nath

Thanks and Regards,

Dinuj Nath