Member Avatar for emaduddeen

Hi Everyone,

I have a table that has a 2 column primary key. I set up the key where the 2nd column is an identity column.

When I ran my VB application I discovered the ID (2nd column) did not increment the correct way. I was hoping it would increment like this:

Category            ID

50001                1

50001                2

50001                3

50002                1

50002                2

Instead it did this:

Category            ID

50001                1

50001                2

50001                3

50002                4

50002                5

I'm using Visual Studio 2010 to do this.

Can you tell me what I did wrong? I used to use Clarion and it worked when telling it that the 2nd column was an auto incrementing column but I'm still new to SQL Server.

Thanks.

Truly,
Emad

  • 2 Contributors
  • 2 Replies
  • 195 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by emaduddeen

Dani AI

Generated

Short answer: SQL Server’s IDENTITY is a table-level auto-number generator — it does not “restart per Category.” That explains why your second column kept counting up across categories. was right that IDENTITY produces unique values for the table; it’s just not designed for per-group (per-category) sequencing. See the IDENTITY documentation and note that identity values can also show gaps due to caching or restarts. (IDENTITY (property), IDENTITY_CACHE / scoped config)

Practical options (pick one):

  • Keep a single IDENTITY surrogate PK and add a separate business column for the per-category number that you maintain yourself (recommended). Microsoft documents using IDENTITY as a surrogate key. (Use IDENTITY for surrogate keys)

  • Use a small counter table and atomically increment the per-category value inside a transaction. Use an UPDATE ... OUTPUT into a table variable to return the new number, and protect the row with locking hints (UPDLOCK/HOLDLOCK) or a transaction to avoid races. The OUTPUT clause is documented and works well for this pattern. Example pattern:

-- counter table
CREATE TABLE CategoryCounters(Category INT PRIMARY KEY, LastNumber INT NOT NULL);

-- get next number (run in your insert flow)
DECLARE @Next TABLE (n INT);

BEGIN TRAN;
UPDATE CategoryCounters WITH (UPDLOCK, HOLDLOCK)
  SET LastNumber = LastNumber + 1
  OUTPUT inserted.LastNumber INTO @Next
  WHERE Category = @cat;

IF @@ROWCOUNT = 0
  INSERT INTO CategoryCounters (Category, LastNumber) VALUES (@cat, 1);

COMMIT;

SELECT ISNULL((SELECT n FROM @Next), 1) AS NewCategoryNumber;

(See OUTPUT clause and table-hint docs.) (OUTPUT clause, Table hints)

  • Use SEQUENCE objects (CREATE SEQUENCE / NEXT VALUE FOR) if you prefer a sequence-based approach — you’d usually need one sequence per category or a mapping layer. (CREATE SEQUENCE / NEXT VALUE FOR)

Notes: if you need contiguous, gap‑free numbers for business rules, don’t rely on IDENTITY — implement your own controlled allocator (counter table or sequence with NO CACHE). Also make sure your VB/.NET code retrieves the generated per-category number from the stored procedure or OUTPUT so the application and DB remain atomic.

Recommended Answers

All 2 Replies

Member Avatar for buddylee17

Defining the column as Identity, means that each value is unique and will never repeat. It is an identifier for the row.

Member Avatar for emaduddeen

Hi Everyone,

Since only 1 column can be an identity column I created a column in a table to hold the cateogy and another one to hold the id which I increment manually based on the category.

Truly,
Emad

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.