Link to home
Start Free TrialLog in
Avatar of Spencer Simpson
Spencer SimpsonFlag for United States of America

asked on

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?
Avatar of dsacker
dsacker
Flag of United States of America image

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:
DECLARE @MyTab TABLE (DisplayOrder int NOT NULL PRIMARY KEY)

;WITH myCTE AS    -- A little recursive magic to fill the table
(
SELECT n = 1
UNION ALL
SELECT n = n + 4
FROM  myCTE
WHERE  n < 49
UNION ALL
SELECT n = n + 1
FROM  myCTE
WHERE  n BETWEEN 49 AND 79
)
INSERT INTO @MyTab
SELECT * FROM myCTE

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

SELECT * FROM @MyTab

Open in new window

Avatar of Spencer Simpson

ASKER

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).
Exactly. And since the subtraction already had 50, 51, etc., the remaining key had nowhere to go but to the duplication reject.
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?
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.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.]