Repopulating a sequenced column that has gone out of sequence.

Question

Repopulating a sequenced column that has gone out of sequence.

Viewing 2 posts - 16 through 16 (of 16 total)

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

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

Lynn Pettis (7/6/2014)
Jeff Moden (7/6/2014)
Not really... I like to know what the business reasons actually are and not that they simply exist. The reason is that people making the business decisions frequently don't know much about SQL Server and they, the data, the server itself, and future processes designed around their business reasons all need to be protected from possibly poor or inadequate business "decisions". 😉
For example, how many times have you and I seen someone wanting to home-grow a sequence column instead of using an IDENTITY property or SEQUENCE because the "business reason" for something is that gaps are "intolerable" only to find out that some "expert" has inappropriately decided to keep track of the count using that column? :w00t:
In this particular case, what will the internal sequence number actually be used for? If it's used only to guarantee the order of events, then there's no need at all for an internal sequence number. The external sequence number does that correctly and without modification.
Considering the human element, that's not usually the case. Someone will decide to use that internal sequence number as a reference to a given event (row). The problem is that if there's a missing item in a previous run or an existing external reference is deleted for whatever reason, the calculated internal sequence will change for all rows after that and, suddenly, the whole system is broken.
If people are adamant about the internal restatement of the external sequence numbers, then, at the very least, missing sequence numbers absolutely MUST be accounted for. This would be done by creating a range of external sequence numbers (like a Tally table) that start at the minimum external sequence number (which should be stored in a reference table to overcome future eventualities) and end at the current maximum external sequence number. Then, generate the internal sequence number from those numbers and do a left outer join based on the external sequence number so that the internal sequence number never changes by item on the row.
Now, with that explanation, hopefully the OP will grace us with an answer. Of course, having been working 131 days straight and with another 166 before I get home for 2 weeks over Christmas, I won't hold my breath. 😀
The fun part with sequences is when there is actually a legal reason that you can't have a break in the sequence numbers. Think Invoices in Italy (and other EU nations probably) for one.

Heh... sometimes laws are just like business requirements... stoooopid! 😀

Here's the code on how I'd probably do this to preserve the Internal Sequence numbers. Details and explanations are in the code...

--======================================================================

-- Imagine, 3 batches the first day.

--======================================================================

--===== If the temp table exists, drop it to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#Labels','U') IS NOT NULL

DROP TABLE #Labels

GO

--===== Create the test table.

-- Just a note that we're missing a PK on the ID

-- column and an AK on both the ExternalSequence

-- and IntSequence (seriously? Someone abbreviated that).

-- All

CREATE TABLE #Labels

(

ID INT IDENTITY (1,1)

,Value VARCHAR(20)

,IntSequence BIGINT

,ExternalSequence BIGINT

,BatchId INT

)

--===== Insert the currently known data.

-- Notice that I've NOT included IntSequence in the

-- initial load because IT'S GOING TO CHANGE ANYwAY.

INSERT INTO #Labels

(Value, ExternalSequence, BatchId)

VALUES --===== Batch 1

('exampleValue',1,1)

,('exampleValue',2,1)

,('exampleValue',3,1)

--===== Batch 2

,('exampleValue',6,2)

,('exampleValue',7,2)

,('exampleValue',8,2)

--===== Batch 3

,('exampleValue',10,3)

,('exampleValue',11,3)

;

--===== Find the min and max ExternalSequence numbers

DECLARE @MinExternalSequence BIGINT

,@MaxExternalSequence BIGINT

;

SELECT @MinExternalSequence = MIN(ExternalSequence)

,@MaxExternalSequence = MAX(Externalsequence)

FROM #Labels

;

--===== Populate the IntSequence column leaving room for

-- any missing ExternalSequence.

WITH

cteTally AS

(

SELECT TOP (@MaxExternalSequence-@MinExternalSequence+1)

N = ROW_NUMBEr() OVER(ORDER BY (SELECT NULL))

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

)

,cteCreateSequences AS

(

SELECT calcExternalSequence = @MinExternalSequence + t.N -1

,calcInternalSequence = t.N

FROM cteTally t

)

UPDATE l

SET IntSequence = s.calcInternalSequence

FROM #Labels l

JOIN cteCreateSequences s ON s.calcExternalSequence = l.ExternalSequence

;

--===== Show what we've got. Notice we've left room

-- for missing external sequences.

SELECT *

FROM #Labels

ORDER BY IntSequence

;

GO

--======================================================================

-- Later, or even the next week, they make some corrections.

--======================================================================

--===== Now they make the corrections, like they did before...

INSERT INTO #Labels

(Value, ExternalSequence, BatchId)

VALUES --===== Correction to Batch 1

('exampleValue',4,1)

,('exampleValue',5,1)

--===== Correction to Batch 2

,('exampleValue',9,2)

;

--===== Now, we use the very same method to update the IntSequence

--===== Find the min and max ExternalSequence numbers

DECLARE @MinExternalSequence BIGINT

,@MaxExternalSequence BIGINT

;

SELECT @MinExternalSequence = MIN(ExternalSequence)

,@MaxExternalSequence = MAX(Externalsequence)

FROM #Labels

;

--===== Populate the IntSequence column leaving room for

-- any missing ExternalSequence.

WITH

cteTally AS

(

SELECT TOP (@MaxExternalSequence-@MinExternalSequence+1)

N = ROW_NUMBEr() OVER(ORDER BY (SELECT NULL))

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

)

,cteCreateSequences AS

(

SELECT calcExternalSequence = @MinExternalSequence + t.N -1

,calcInternalSequence = t.N

FROM cteTally t

)

UPDATE l

SET IntSequence = s.calcInternalSequence

FROM #Labels l

JOIN cteCreateSequences s ON s.calcExternalSequence = l.ExternalSequence

;

--===== And now, none of the existing rows have changed their IntSequence!

SELECT *

FROM #Labels

ORDER BY IntSequence

;

--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)

Maddave SSCertifiable Points: 6828 More actions · Answer 2

Hi Jeff, everything you had said is absolutely right and some of the questions you asked are ones I have asked myself. The application and therefore database was designed by contractors who have long since moved on and released long before I joined the company. It was developed without any SQL developers or SQL architects to advise and so has many horrible inconsistencies and bad practices across the board. Therefore in depth knowledge on how the app actual works is sparse and the business dont want to rock the boat anymore than they have to to get it working. No one can give me a good reason why the external ref cannot be used for the internal ref, other than "we dont know what effect that will have on the application". I dont think it will matter since they are both numbers which increment. The business dont even want to try and use the ext reference to see what effect it will have, so have forced the solution down the reseed from 1 route. This has worked in Test and UAT so they are happy with this solution as it mimics the original design - although no one can actually find the design documentation to confirm!!! :crazy:

As a side note, this whole application design should really be a case study on how not to do things. This sequence problem is a great example. The sequence data is inserted into one table in one databases, but then is read by the application at a later time and inserted again into another database for tracking purposes. That's not just another table, but the same data is copied to another database completely - by the same application!! No referential integrity exists between the two and so it means, once I reseed the master data, I then have to go through and set the second database to the same sequence value as well. Luckily this is relatively straightforward as other relationships exist between the data I can use. It's a horrible design though and I curse the way it's been done.