Monday, April 09, 2012 5:33 PM
I was reading SQL Server 2012 documentation on Sequences. It says that cached sequence values are lost in scenarios like sudden power failure etc.
Is there any way to recover lost cached sequence values from logs? DB2 10 has a feature where it recovers the values from the most recent log.
Monday, April 09, 2012 8:10 PMModerator
Automatically, I don't think so... The values will be lost by default, but you might be able to use a startup stored procedure and have it run through your sequence objects, get the max value nad restart the sequence with the lowest value...
ALTER SEQUENCE Demo.Generic_SEQUENCE
RESTART WITH 10
You will have to use dynamic SQL to do it programatically:
DECLARE @QueryText nvarchar(100) =
CONCAT('ALTER SEQUENCE Demo.TransactionExample_SEQUENCE
RESTART WITH ',(select MAX(TransactionSequence) + 1 FROM Demo.TransactionExample))
That query procedes the following query to resets all of the values in the table to contiguous values:
;WITH FixNumbers AS(
SELECT TransactionSequence, ROW_NUMBER() OVER (ORDER BY TransactionSequence) as RowNumber
SET TransactionSequence = RowNumber
WHERE TransactionSequence <> RowNumber; --Start where values are different (performance
The best way to not lose values on a crash (well, other than not crashing :) is to not use caching, which if you need contiguous values, is probably the best idea. Usually when this question is asked it comes down to the person not really understanding sequence/identity values and that you will lose values if an error occurs, a row is rolled back, etc. Using either identity or sequences for a surrogate key should be treated like the number doesn't have a lot of value other than for joins and singleton row pickups...
I will note that a lot of times that when people ask this question they are missing the point of using an object like this. If you need a contiguous set in all cases, sequence objects have the same
- Marked As Answer by OldEnthusiast Thursday, April 19, 2012 6:21 PM