0

This is somewhat related to question: SQL server identity column values start at 0 instead of 1

But I'm not sure what happening here. We have our "blank" database which we use to start customer's accounts. We do that by backing up and restoring database.

Customer gets completely blank tables after restore. Example of table, all our tables have PKs exactly like this one:

CREATE TABLE [dbo].[RNPRatePlanStop]
(
    [RatePlanStopKey] [int] IDENTITY(1,1) NOT NULL,
    [RatePlanKey] [int] NOT NULL,   
    .....
)

Once in a while (not always and not on all tables) when new record inserted - 0 not expected 1 inserted. I'm not sure how this can be fixed or what I'm doing wrong. This is causing problems in UI because we don't count on having 0 keys..

Community
  • 1
  • 1
katit
  • 17,375
  • 35
  • 128
  • 256
  • 2
    you can use `truncate table [dbo].[RNPRatePlanStop]` This resets the seed value defined for the identity columns. Use this to truncate all the tables whose identity you want to reset to the default seed. – Tudor Saru Jun 16 '16 at 17:36
  • I would note here as well that if your primary key is just RatePlanStopKey, this isn't guaranteeing uniqueness by just having IDENTITY() on it. You could add PRIMARY KEY to it to guarantee that. You can refer to: https://msdn.microsoft.com/en-us/library/ms186775.aspx for more info. If you're using a composite key though then that's a different story. – Ryan Intravia Jun 16 '16 at 23:21
  • Ryan, I omitted rest of table DDL in question but yes, it is PK. I already found the problem, will put in my answer – katit Jun 16 '16 at 23:28
  • @TudorSaru, Unfortuantely this won't work if table has FKs on it. – katit Jun 16 '16 at 23:28

1 Answers1

0

Also declaration is IDENTITY(1,1) when I run command like this:

SELECT IDENT_CURRENT('RNPRatePlanStop')

It returned 0. For this table and about 7 other tables. Don't know how that was possible, maybe because IDENTITY was applied later via script.

Solution was to "seed" proper identity on those tables:

DBCC CHECKIDENT(RNPRatePlanStop, RESEED, 1)

After that - they all reset correctly and all is well. Still a mistery how those few out of a 100+ tables ended up like so.

katit
  • 17,375
  • 35
  • 128
  • 256