SQL update values that participate in a unique key index

I recently found to my surprise that a query such as

update mytab set displayorder = displayorder+1 where displayorder > 50;

Open in new window

worked, even though displayorder had a unique key index and all of the values over 50 were contiguous.

A subsequent

update mytab set displayorder = displayorder-1 where displayorder > 50;

Open in new window

worked, although (of course, since there was also a 50) another such decrementing update violated the constraint and failed.

I was surprised because there isn't anything to indicate that the rows would be processed in an order that wouldn't violate the constraint.  
When the update processes the record with a value of 55 and there's already a record with a value of 56, updating record 55's value to 56 creates a temporary duplication (at least until record 56 is processed).  

But it works.

My question is, can you rely on this? I'm using SQL Server 2008 R2, but I've no idea if other SQL implementations would have this behavior.

Does this work because constraints and indexes only enforced on a commit?
If, so, I suppose that when a statement is run outside of a specific transaction, the SQL engine wraps it in a mini-transaction of its own?
Spencer SimpsonInformation Technology CoordinatorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
It will work when you don't have a final value that could not be overlaid and violate the primary key. If you do, it will not work.

For instance, if you run both queries together, they will work. If you only run the second query, it will violate the primary key.

So the rule is, there must be room for the FINAL primary value to change to, in order for all of them to work. That intelligence in SQL Server is impressive.

I used the following as an exercise:

;WITH myCTE AS    -- A little recursive magic to fill the table
SELECT n = 1
SELECT n = n + 4
WHERE  n < 49
SELECT n = n + 1

UPDATE @MyTab   --  When run together or separately, this works, because there is no DisplayOrder = 81
SET    DisplayOrder = DisplayOrder + 1
WHERE  DisplayOrder > 50

UPDATE @MyTab   --  When run together, this works, because there is no DisplayOrder = 50
SET    DisplayOrder = DisplayOrder - 1
WHERE  DisplayOrder > 50


Open in new window

Spencer SimpsonInformation Technology CoordinatorAuthor Commented:
Since the update statement's WHERE contained only DisplayOrder >50 without an upper bound, then you don't have to worry about a "final" value, since it would be greater than all of the existing values in the table.  

The only real risk is an overflow (say for a 16-bit int column that had a value of 32767 or a decimal (4,0) with a value of 9999).
dsackerContract ERP Admin/ConsultantCommented:
Exactly. And since the subtraction already had 50, 51, etc., the remaining key had nowhere to go but to the duplication reject.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Spencer SimpsonInformation Technology CoordinatorAuthor Commented:
Which was expected (and desirable). And if my incrementing update had specified an upper bound (call it UB) then it would also have failed if there had been a record with UB+1.  

But it didn't have an upper bound, so there would never have been any concern about a "final" value to overwrite.

So what is it that prevents failure while the update is being processed?
dsackerContract ERP Admin/ConsultantCommented:
Usually this is a rare occurrence. In most cases, sequence fields continually ascend. I've been a DBA, developer, etc., for over 35 years, and I've never really faced any issue with that.

However, that is quite a find on your part. And hopefully my explanation and piloting of that discovery has helped.

And if you have a business need for something like this, the best course may be to offload the table into a temp table, resequencing as you go, then truncate the source table, then reload it.
Scott PletcherSenior DBACommented:
What allows this to happen is that SQL performs set-based operations, not row by row (conceptually at least; the actual internal write mechanics can get far more complex).  That is, all key values effectively change at the same time.  It often takes humans time to get comfortable with that idea, since we tend to think more row by row.

But, if you think through this, you'll realize it's the most practical way. Otherwise, SQL would have to lookup every key value for uniqueness as it changes every single row.  That would be extraordinary additional overhead during the UPDATE.

Also, this allows the same basic controlling logic to handle UPDATEs to any column. Otherwise, the DBMS engine might need different UPDATE code for each category of column: non-key columns, non-unique key column, unique key columns, etc..  Ugh!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Spencer SimpsonInformation Technology CoordinatorAuthor Commented:
Beyond being the most practical way, it's the most desirable way in an abstract sense.

I was hoping that it wasn't restricted to just SQL Server; I'd been made cautious because of the row-by-row behavior of ROWNUM in an Oracle select statement.
Scott PletcherSenior DBACommented:
Quite true, it's a more desirable approach overall.

Also true about Oracle.  Oracle is, by its nature, far more row oriented overall; basically everything in Oracle is done by a cursor (which if, of course, nothing like a cursor in SQL Server, just to keep it all very confusing :-) ).  Another quick example: triggers in Oracle can be specified to run by row, not so in SQL Server.

But Oracle has, I believe, true deferred resolution on indexes that can allow temporary anomalies that even SQL Server can't handle.  So, I would expect Oracle (and UDB/DB2, btw) to handle these types of UPDATEs just fine. [However, I haven't been a professional DBA on Oracle since 8.0, so I can't speak in more detail about Oracle's current approach to this.]
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.