Will inserting an IDENTITY PK leave row order unchanged?

Question

Post reply

Will inserting an IDENTITY PK leave row order unchanged?

Allen Nugent

Right there with Babe

Points: 734
More actions
September 16, 2010 at 10:49 pm

#143460

I have a poorly designed table with no PK or Identity column. The app that reads and writes it expects the rows to always come back from a query in the order they were posted -- although there is no ORDER BY clause in the query, no sorting on the dataset, and no surrogate key that can ensure correct sort order.
I want to insert an IDENTITY PK so that I can make the app more robust, but I haven't been able to get a straight answer on whether or not this insert might cause the table rows to get reordered.
Does anyone know the answer?
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

They only documented way to get rows in a particular order is using ORDER BY.

Any other method is prone to failure.

Adding an IDENTITY CLUSTERED PK won't change this behaviour and probably won't affect your app. But, again, ORDER BY is the only way to go.

Hope this helps

Gianluca

-- Gianluca Sartori

Lowell SSC Guru Points: 323482 More actions · Answer 2

SQL chooses the fastest way to access the data, and if no ORDER BY is used, returns it in that order;

So if there are no joins to other tables, then the clustered index provides SQL with the fastest way to get the data...and thus would determine be the order the rows are returned in when no ORDER by was used;

so if you rebuilt the table so it had an identity, and the clustered index was on that column, then yes, queries on that table would be returned in the order they were created.

if you join to another table, the opimizer may decide a different index or some other operation is better, and you fall back into requiring an ORDER BY to guarantee the order.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hallidayd SSCrazy Points: 2490 More actions · Answer 3

Lowell (9/17/2010)
So if there are no joins to other tables, then the clustered index provides SQL with the fastest way to get the data...and thus would determine be the order the rows are returned in when no ORDER by was used;

What about in the event of parallelism or a merry-go-round scan?

spaghettidba SSC Guru Points: 105712 More actions · Answer 4

hallidayd (9/17/2010)
Lowell (9/17/2010)
So if there are no joins to other tables, then the clustered index provides SQL with the fastest way to get the data...and thus would determine be the order the rows are returned in when no ORDER by was used;
What about in the event of parallelism or a merry-go-round scan?

Agreeed. Relying on an implementation detail is not a good idea IMHO.

The best way to solve the problem is having the app fixed.

Since you're using SQL Server 7/2K you could:

1) create the PK

2) rename the table

3) create a view with the original table name as SELECT TOP 100 PERCENT * FROM newTableName ORDER BY PKField

I don't have a SQL 2K at hand to test, but it should work, unless TOP makes the view non-updatable. In that case, you could create a trigger INSTEAD OF INSERT, UPDATE, DELETE on the view, but it would be overcomplicating something that should be fixed on the app side.

Good luck!

-- Gianluca Sartori

WayneS SSC Guru Points: 95405 More actions · Answer 5

Allen Nugent (9/16/2010)
I want to insert an IDENTITY PK so that I can make the app more robust, but I haven't been able to get a straight answer on whether or not this insert might cause the table rows to get reordered.
Does anyone know the answer?
Thanks!

It has been my experience that adding an identity column to an existing table will in fact number the identity column in the physical order that the rows are in the table. However, I don't think that there is anything that guarantees this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Allen Nugent Right there with Babe Points: 734 More actions · Answer 6

Thanks, everybody!

I bit the bullet and inserted an INDETITY(1,1) column, as primary key. Haven't had any issues yet.

Now to recode that naughty app...

- Al

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

I was going to suggest that if the app likes the "natural" order up to now, have the app read the data from the table into a new table to ensure that the order is correct. Emphasis on "was". 😛

--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)
Intro to Tally Tables and Functions