Update all the sequence number of "Restart sequence" with the Current Value
-
Hi all,
how can I update all the numbers of the sequences under Programmability/Sequences to be aligned in this order: all the value Current Number for example 4001 will substitute the 4000 number of Restart sequence, this for all the sequences.
Thanks in advance.
- This topic was modified 4 years, 4 months ago by gdf2020.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
I have to ask, since "all sequences" are likely to grow at a different rate, why are you even thinking about doing such a thing?
--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) -
Hi Jeff Moden thanks for your reply, I don't know perfectly why bu I need to align that before a backup and a restore process, could you imagine some solution to do that to save some times?
-
Are you saying that you want ALL sequences to be set to the same number? If so, it makes no sense and the restored databases will all suffer multiple PK violations the first time someone does an insert.
If you mean something else, I for one, could use a little more clarity on what the requirement I'm obviously missing actually is.
--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) -
Hi Jeff Moden I have not explained well the issue, my problem is that I don't need to change all the same but for example, I have 3 sequences with this settings:
Restart sequence: 3500, Current Value:4000 - I need to automatically set the Restart sequence to 4000 value
Restart sequence: 3600, Current Value: 4500 I need to automatically set the Restart sequence to 4500 value
Restart sequence:4000, Current Value: 5000 I need to automatically set the Restart sequence to 5000 value
this process for all the sequence every sequence need to match the restart sequence with current value. Current Value is the reference.
-
@jeff Moden any news on this ?
-
Check out the following for what you can do with ALTER SEQUENCE.
Admittedly, their examples on that page suck. What you're looking for is the example that uses the RESTART option. The given example is ...
The value returned of -9,223,372,036,854,775,808 is the lowest possible value for the bigint data type. The owner realizes he wanted the sequence to start with 1, but did not indicate the START WITH clause when he created the sequence. To correct this error, the owner executes the following statement.
ALTER SEQUENCE Test.CountBy1 RESTART WITH 1 ;
From that, we can extrapolate the you can use this to change your sequences. You just need to replace the name of the sequence with the name of the sequence you want to change and replace the "1" with the correct starting value.
I still am in a quandary as to why you want/need to do this for the backup/restore process. If such a prerequisite thing is holding up a backup/restore process, there is something seriously wrong with the design and usage of the database. Seriously.
--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) -
Sounds like a good case for a bit of dynamic sql... The following will look at sys.sequences and, where the start_value is less than the ;ast_used_value, generate and ALTER SEQUENCE script.
DECLARE @d_sql nvarchar(4000) = N'';
SELECT
@d_sql = CONCAT(@d_sql, N'
ALTER SEQUENCE ', QUOTENAME(SCHEMA_NAME(s.schema_id)), N'.', QUOTENAME(s.name), N' RESTART WITH ', CONVERT(nvarchar(20), s.last_used_value), N';')
FROM
sys.sequences s
WHERE
s.start_value < s.last_used_value;
PRINT(@d_sql);
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply