Why SQL server primary key skip number

Question

0.00/5 (No votes)

See more:

I am using Sql Server 2012 and i create a table and kept id as primary key also identity Specification "Yes" for auto increment 1.
but problem is this that table skip number
for example 1,2,3,4,5,6,7,8,9,10,11,12
and some time this skip number and give is 101,102,103 and so on
why

What I have tried:

I am using Visual Studio 2013 for developed website

Posted 16-Jun-17 8:15am

Member 10028394

Updated 16-Jun-17 8:41am

Add a Solution

Comments

ZurdoDev 16-Jun-17 14:16pm

You have probably deleted records.

PIEBALDconsult 16-Jun-17 19:42pm

Why do you care? IDs are meaningless by themselves.

Member 10028394 17-Jun-17 14:41pm

i am working on logistic website, where shipment ID generate on primary key with one increment, if it missing number than shipment ID series disturb. So i want that do not missing (skip) any shipment number.

CHill60 18-Jun-17 16:28pm

You might find CREATE SEQUENCE (Transact-SQL) | Microsoft Docs[^] useful. Do NOT count the table rows and add 1 - what if two people are trying to add a record at the same time? You will end up with duplicate Shipment IDs and that WILL cause a problem. There is no real world requirement for shipment IDs to be sequential. If you want the records to really have sequential numbers then you will need to use a sequence and also prevent deletions from the table (flag the records as "not active" or something instead)

2 solutions

Solution 1

See this discussion on StackOverflow: Identity increment is jumping in SQL Server database - Stack Overflow[^]
and: Why does my auto-incremented Id skip numbers in SQL Server? - Stack Overflow[^]

Posted 16-Jun-17 8:28am

RickZeeland

Comments

Member 10028394 17-Jun-17 14:47pm

I want that any one number is not missing (skip) in any case.
Kindly told me which method is better
1) Primary Key with one auto increment
2) i count the table rows and add (one 1) and kept this value in primary key instead of auto increment

RickZeeland 17-Jun-17 14:52pm

None of these, you can't trust primary key, instead use another field.

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 · Accepted Answer · 2017-06-16T08:41:00

Solution 2

There can be several reasons behind this, most typical ones are deletion of records nad rolling back a transaction.

But the bigger question is, why this is a problem for you? The sole idea of the auto incremented field is that it provides you a unique value for a single record. That's all. It does not quarantee that all values are present and so on, just the uniqueness.

This type of key is typically never shown to the user because it has no other meaning than providing a key value. In other words it's a surrogate[^]

Posted 16-Jun-17 8:41am

Wendelius

Comments

Member 10028394 17-Jun-17 14:46pm

I want that any one number is not missing (skip) in any case.
Kindly told me which method is better
1) Primary Key with one auto increment
2) i count the table rows and add (one 1) and kept this value in primary key instead of auto increment

Wendelius 17-Jun-17 15:47pm

With auto numbered field you cannot achieve a situation where there wouldn't be a hole in the numbering.

What about deletes, what are you going to do if a record is deleted. Suppose you have rows 1, 2, 3, and 4. Then number 2 is deleted, what happens?

As I explained, by definition a surrogate key is just a unique number, nothing else. If the numbering needs to be continuous, it sounds like you're trying to build something else than a key.