How a primary key value may be recalculated automatically?

Question

1.00/5 (1 vote)

See more:

Hello to everyone
There is a part of code using T-sql, quite simple and it does not need some extra explanations.

SQL

select * from MyTable
delete   from MyTable where id =  2

Just one simple question. I am a newer is the DB sphere... How can a primary key be recounted automatically if it is of integer type?
Sorry for such a simple question.

Posted 20-Aug-15 20:43pm

Dzianis Igaravich Leanenka

Add a Solution

Comments

Jörgen Andersson 21-Aug-15 7:56am

Why do you want to recalculate the IDs?
A surrogate key does not need to be recalculated and shouldn't be recalculated.
Gaps in a sequence does not affect the database the slightest.
The only reason I can see is if you want to feed your OCD.

PIEBALDconsult 21-Aug-15 16:47pm

Yeah, don't do that. You must be doing something wrong.
In fact, try not to delete, use something like an IsDeleted flag.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-08-20T20:50:00

Solution 1

You have at least three options:
- you can use IDENTITY[^]
- use uniqueidentifier along with NEWID[^]
- or use a SEQUENCE[^]

Personally I'd prefer NEWID over IDENTITY especially if the keys need to be unique across different databases.

Posted 20-Aug-15 20:50pm

Wendelius

Updated 20-Aug-15 20:52pm

v2

Comments

PIEBALDconsult 21-Aug-15 16:50pm

I can't stand IDENTITY, but I also know that I'm in the minority. For integer IDs I'd definitely use a SEQUENCE, but I prefer GUIDs.

Wendelius 22-Aug-15 2:35am

IMHO identity works with small, isolated databases but I share your opinion, identities are not so good as other options.

Advay Pandya · Answer 2 · 2015-08-21T03:20:00

Hi Dzianis,

As per my understanding of your question, you want to recalculate/ reset identity value of the column, not the primary key itself.

If I am correct then please use below DBCC command. That will help you to reset the identity value of the column.

SQL

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])

So, in your case you can put something like below"

SQL

DBCC CHECKIDENT('MyTable', RESEED, 1)

Above query will reset your identity column to value 1. So from the next value 2 will be inserted for the column id.

Please let me know if you have any concern or query on this.

Thanks

Patrice T · Answer 3 · 2015-08-21T10:37:00

Solution 3

My answer is Don't do it.
Why ?
Simply because there is no downside to have a hole in primary key values, and renumbering the key offer no gain (of space or speed).

But renumbering the key have a downside, you have to shutdown all access to the base while you are renumbering it.

Posted 21-Aug-15 10:37am

Patrice T

Updated 21-Aug-15 11:29am

v2