Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IDENTITY(1,1) doesn't work, from 17 jumped to 1001 Expand / Collapse
Author
Message
Posted Monday, March 31, 2014 7:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 165, Visits: 400
Hi,
I have a table with PK column defined like IDENTITY(1,1), it has 16 entries, now after series of migration and restores I'm adding new row into it like below and get new TypeID = 1001 ? Totaly lost is this somewhere go reset to 1000? Before was only 16 entries with correct sequence 1 by 1 thru 16.

What could be wrong , can anybody help.

Best M

CREATE TABLE [dbo.].[Types](
[TypeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Code] [nvarchar](350) NULL,
[Description] [nvarchar](350) NULL,
[IsActive] [bit] NOT NULL,

CONSTRAINT [pkTypes] PRIMARY KEY CLUSTERED
( [TypeID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )

INSERT dbo.Types
( Code ,
Description ,
IsActive
)
VALUES ( N'Alpha' , -- Code - nvarchar(50)
N'Alpha Type 17' , -- Description - nvarchar(250)
1 -- IsActive - bit )

select * from dbo.Types


Post #1556781
Posted Monday, March 31, 2014 8:27 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 4,343, Visits: 6,151
If you shut down SQL Server there is a known bug that advances the identity values. I don't know if there is a fix for that or not.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1556784
Posted Monday, March 31, 2014 8:29 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 4,343, Visits: 6,151
Here is a link with more information:

http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1556785
Posted Monday, March 31, 2014 9:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:45 AM
Points: 216, Visits: 840
Also remember that if you begin a transaction and rollback, the identity values will also have increased afterwards. That makes sense if you think about it.

Identity is a count of attempted inserts, not consecutive sequential numbers. AFAIK you can't even really trust a sequence data type for that purpose either.

You can do a DBCC CHECKIDENT(blah, RESEED) to reset it to the next available number though.
Post #1556790
Posted Monday, March 31, 2014 10:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 165, Visits: 400
Thanks, all
Yes, I did reseed and it worked.

DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
Post #1556800
Posted Tuesday, April 1, 2014 2:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
Bear in mind that identity has never been guaranteed to not have gaps. It's an ever-increasing number, that's all. It's not gap-free and it's not even guaranteed unique (you can reseed under the current max value)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1556833
Posted Tuesday, April 1, 2014 3:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,291, Visits: 13,552
TheSQLGuru (3/31/2014)
If you shut down SQL Server there is a known bug that advances the identity values. I don't know if there is a fix for that or not.

according to MS it's not a bug, it's by design.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1556865
Posted Tuesday, April 1, 2014 3:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,291, Visits: 13,552
mario17 (3/31/2014)
Thanks, all
Yes, I did reseed and it worked.

DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);

The problem here is, when the value jumped if identities were created in the upper range and you reseed back to 10, you'll have issues later


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1556868
Posted Wednesday, April 2, 2014 1:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:03 AM
Points: 488, Visits: 403
Also note that with Replicated Tables you can/will also have gaps, as each subscriber of the published Table is pre-allocated a range of IDs it can use.
Post #1557344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse