Reseed one table column

Question

Reseed one table column

Viewing 8 posts - 16 through 22 (of 22 total)

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

Instead of dropping the base table, can I do something to the existing table? I kind of find that the table is continuously being inserted with records, now if I drop they may loose some records until the rename of the table is completed. No, the table does not have any foreign key.

Thanks.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

SQL-DBA-01 - Thursday, July 20, 2017 2:43 PM
Instead of dropping the base table, can I do something to the existing table? I kind of find that the table is continuously being inserted with records, now if I drop they may loose some records until the rename of the table is completed. No, the table does not have any foreign key.

As others have suggested, you could ignore it? You have a long ways to go before you will run out of values.
If you MUST remove the gaps for whatever reason, I'm thinking a manual process is likely going to be your best bet... You'd insert a high value into the gap, and then delete the one you moved and repeat until all of the gaps are filled.
It would be tricky (although not impossible) to script it, but you'd end up blocking the table if you scripted it.

But this also leads to the next question (that has been brought up already) - do you need that column? That is, does this identity column help you in any usefull manner?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

just a thought.........probably way off base !

SELECT TOP 30 PG_ID = IDENTITY( INT, 1, 1) INTO #PG FROM sys.all_columns;

SELECT * FROM #PG;

DELETE FROM #PG WHERE pg_id % 5 = 0;

SELECT *FROM #PG;

SELECT PG_ID, ROW_NUMBER() OVER(ORDER BY pg_id) AS newid FROM #pg;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Please explain the business case for trying to reuse the gaps created by deleting data. You are far from running out of identity values so, as someone else said, it is as if you have a solution looking for a problem.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Lynn Pettis

SSC Guru

Points: 442467

July 21, 2017 at 1:16 pm

#1952194

Just going to ignore my question?

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

Lynn Pettis - Friday, July 21, 2017 1:16 PM
Just going to ignore my question?

He did explain that his IDENTITY column was approaching the limits of BIGINT, which is a pretty good reason to re-sequence. A better thing to do, though, might be to just get rid of the IDENTITY column on this audit table altogether.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Jeff Moden - Friday, July 21, 2017 2:06 PM
Lynn Pettis - Friday, July 21, 2017 1:16 PM
Just going to ignore my question?
He did explain that his IDENTITY column was approaching the limits of BIGINT, which is a pretty good reason to re-sequence. A better thing to do, though, might be to just get rid of the IDENTITY column on this audit table altogether.

If you look up I asked him what the max value of the identity column was and he replied: 2377688866 (2,377,688,866). Don't think he is any where near the limit.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 8

Lynn Pettis - Friday, July 21, 2017 2:09 PM
Jeff Moden - Friday, July 21, 2017 2:06 PM
Lynn Pettis - Friday, July 21, 2017 1:16 PM
Just going to ignore my question?
He did explain that his IDENTITY column was approaching the limits of BIGINT, which is a pretty good reason to re-sequence. A better thing to do, though, might be to just get rid of the IDENTITY column on this audit table altogether.
If you look up I asked him what the max value of the identity column was and he replied: 2377688866 (2,377,688,866). Don't think he is any where near the limit.

Heh... you're right. He claimed he was approaching the limit for BIGINT but 2,377,688,866, as you and Phil have both pointed out, that's no where close to the limit of BIGINT. Maybe he's confused about what the datatype for the column in question actually is or what the limits of INT and BIGINT actually are.

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