3

I have a few 1000 jumps in my table. I've figured out the reason, rather late I would say, which is frequent server failure and restarts and executed set identity cache=off.

Hopefully, these large jumps will not occur. Now I want to reuse these numbers in the gaps for the new entries, what is the best way to do this? is changing the seed value is possible? Please note that I can not alter any existing data. Also, note the rate at which new entries are added is slow (less than 10 entries daily) and I can keep an eye on this database and change the seed value again manually when necessary.

Thank you very much.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Schwan Abdulkareem
  • 107
  • 1
  • 7
  • 2
    Why do you need to reuse them? The purpose of the `IDENTITY` is to provide a continually incrementing value; not to make use of each value. Would you expect to go "backwards" in the event of a failed transaction too? – Thom A Feb 23 '19 at 10:51
  • 1
    why do you want to do this? why is jumps unacceptable for you? – Derviş Kayımbaşıoğlu Feb 23 '19 at 10:51
  • These IDs are used to link each person with some files (photos) in my application, so they are exposed to the end user, small jumps are acceptable but large "1000" jumps are quite bothersome to my user for some reason. – Schwan Abdulkareem Feb 23 '19 at 10:56
  • I don't think these numbers are important for users. but you can consider to make bulk operation for once to fix them. Don't try to insert in between them – Derviş Kayımbaşıoğlu Feb 23 '19 at 10:58
  • 2
    There is no good reason to go this then, in my opinion. Users can, and do make poorly informed decisions and requirements. A User ID is just a numbers, that's all. What number that is is completely arbitrary and meaningless. If they are continuous or not doesn't matter; and you should educate your user to know so. – Thom A Feb 23 '19 at 10:58
  • If you can't change existing user ID values, I'm assuming you only want to use these "gaps" moving forward? If do then I would add a column to your table (or create a wholly new table) that for existing records is equal to the identity column, but for future records will use the values in the gaps, and use that as the "user ID" exposed to the public while using the identity column internally. Of course you'll still have gaps when users are deleted, and sequential user ID values exposed to the public can be a security issue.... – David Faber Feb 24 '19 at 12:55

2 Answers2

1

You can write a script for each instance using SET IDENTITY INSERT table_name ON and SET IDENTITY INSERT table_name OFF at the start and end of your script. The full documentation is here. You can only use it on one table at a time.

Changing the seed will have no effect as the next highest value will always be used.

The following script will help with identifying gaps.

SELECT  TOP 1
    id + 1
FROM    mytable mo
WHERE   NOT EXISTS
    (
    SELECT  NULL
    FROM    mytable mi 
    WHERE   mi.id = mo.id + 1
    )
ORDER BY
    id

Which is from the this question/answer

UPDATE

A possible strategy would be to take the database offline, use SET IDENTITY INSERT to fill the gaps/jumps with the required ID but otherwise minimum/empty data and then make live again. Then use the empty records until all are used and then revert to the previous method.

Peter Smith
  • 5,528
  • 8
  • 51
  • 77
  • Thank you, but how should I know which value is available each time? – Schwan Abdulkareem Feb 23 '19 at 11:02
  • although this is doable, it will introduce transactional problems which the OP is not aware of. It needs to address potential problems so that OP can choose right direction for himself – Derviş Kayımbaşıoğlu Feb 23 '19 at 11:03
  • @Simonare Thanks for pointing this out. How would you manage potential transactional problems. – Peter Smith Feb 23 '19 at 11:09
  • If you set identity insert off definitely you cannot. suppose that another insert came after the first operation started. the other operation also set identity insert off. but then first operation finished and the identity insert is set to ON. second operation will use identity insert and your QUERY will fail because it will try to insert ID. right? it is not failsafe to use this aproach – Derviş Kayımbaşıoğlu Feb 23 '19 at 11:16
  • one thing the OP can do is disabling identity insert for long time. But then again he can get same id number for both if there are two operations which start at the same time. for this OP can use TRANSACTIONS to prevent this issue, but then this means that second transaction needs to wait first transaction to finish (Blocking sessions).... – Derviş Kayımbaşıoğlu Feb 23 '19 at 11:20
  • @Simonare I've add a possible strategy to overcome the potential transactional problems. – Peter Smith Feb 24 '19 at 12:58
0

I don't think these numbers are important for users. but you can consider to make bulk operation for once to fix them. Don't try to insert in between them

setting identity insert on and of for table level changes structure of your table and it is not transaction safe.

you need to write TSQL script to alter your base table and dependent table at the same time

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
  • Thank you, they are important for my user, and changing the IDs of existing data is, unfortunately, not an option. – Schwan Abdulkareem Feb 23 '19 at 11:05
  • I believe that it is totally doable. But you are not aware of it. I am not talking about procedural difficulties but technically there are many ways of doing this. how many records do you have in this table? – Derviş Kayımbaşıoğlu Feb 23 '19 at 11:07
  • less than 1000 and I have three 1000 gaps. but then I have to change the file names associated with the persons that will get different IDs, the ID is part of the file names that is how the application link each person with its photos. – Schwan Abdulkareem Feb 23 '19 at 11:12
  • 2
    But why is your user in charge of deciding what a user's unique ID is @SchwanAbdulkareem? Why is it important to them? If you want continuous numbers, consider giving them a view using `ROW_NUMBER` or something. Don't let your user drive a poor (foolish) requirement over something so arbitrary as to reuse "missed" numbers; numbers that are missed by design by the product you are using. Like I said before, as well,that means you need to "reuse" numbers when a transaction fails too. this isn't a rabbit hole you want to go down. – Thom A Feb 23 '19 at 11:19
  • 1
    The decision of disabling identity cache is also another moot point. You need to concentrate on **WHY these server failures even occur?** – Derviş Kayımbaşıoğlu Feb 23 '19 at 11:23
  • 2
    In truth, the "UserID" (the number) isn't normally exposed to the user anyway, doing so can actually be a bad idea (There are plenty of examples out there where systems have been breached because someone changed a URL from something like `userID=1271` to `userID=1272` and got someone else's details). The thing that is exposed is the username the user chose, and that should be what your user is seeing too. – Thom A Feb 23 '19 at 11:24
  • @Larnu I agree with you, but if I can make him/her happy without major issues then I would, and to be honest , personally I don't think this is a good drsign strategy by Microsoft to introduce these 1000 gaps on each server failure or restart. – Schwan Abdulkareem Feb 23 '19 at 11:27
  • 2
    It's by design @SchwanAbdulkareem and is there for good reason (one of the biggest reasons is for performance). Using uncached identity values can have big performance degradation on your server; as the identity value has to be checked for EVERY insert. If you don't like that functionality you shouldn't be using `IDENTITY` and should be looking at using a `SEQUENCE`. – Thom A Feb 23 '19 at 11:30
  • @Simonare, after thinking about it I don't think it is very difficult to write a VB script and change file names after reallocating the IDs. Can you please guide me to the way to do it on SQL-server side. – Schwan Abdulkareem Feb 23 '19 at 11:32
  • sure, can you please share table definitions of your table? you can use https://dbfiddle.uk – Derviş Kayımbaşıoğlu Feb 23 '19 at 11:48
  • https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=dfd8f847aad087a7fc183286e077491a – Schwan Abdulkareem Feb 23 '19 at 12:20
  • you didn't include your dependent table. We need it in order to update referenced id's – Derviş Kayımbaşıoğlu Feb 23 '19 at 14:32