Sql server strange Identity increment

Tags:

azure-sql-database

I have this table setup at SQL Azure

CREATE TABLE [dbo].[Sl](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PublicId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PrimaryKey_ba033f1f-ac1b-4616-8591-fcd47fe0f63d] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
 CONSTRAINT [PublicId_UNIQUE] UNIQUE NONCLUSTERED 
(
    [PublicId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

The very strange thing is that the ID gets incremented in a unexpected way.

Any idea how to make this increment normally? So that the numbers don't jump like that? Any idea how to make this increment normally? So that the numbers don't jump like that?

220

asked Dec 07 '13 14:12

1 Answers

This is a known issue. There are two related items reported on Connect. This one is actually closed as by design, but I don't think the engineer who left the comment really understood the issue:

http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value

This (earlier!) bug is active:

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

The latter has one interesting workaround, and others that raise my eyebrows. The primary workaround on the suggested dupe would be fine for your own box, but good luck running a trace flag in Azure (or creating a startup proc as one of the connect items suggested). Using a SEQUENCE with no cache may be a viable option, but you should validate that the trade-offs are worth avoiding gaps in a meaningless, surrogate number. The other answer there suggests issuing a CHECKPOINT after every insert. Ouch.

In the end, if you want a sequence without gaps, stop using IDENTITY - that's not what it was designed to do.

166

answered Oct 21 '22 11:10

Aaron Bertrand

Related questions
                            
                                What is the alternative for number(2,0) of oracle in sqlserver?
                            
                                TRY/CATCH does not work on SQL Server Agent error?
                            
                                Paging of records using sql server stored procedure
                            
                                Is it possible to find out who called a stored procedure in SQL Server 2005
                            
                                T-SQL to find if a Database is Subscribed on the Subscriber in Transactional Replication
                            
                                find the count using group by
                            
                                Sql Grouping by on a range of year
                            
                                SQL71561 error - ambiguous reference
                            
                                uncoditioned Count(*) vs System tables
                            
                                Getting a single value from SQL with ADO.NET
                            
                                Delete duplicates from large dataset (>100Mio rows)
                            
                                Selecting all parents in the order of relation from hierarchical table SQL
                            
                                Find all integer gaps in SQL
                            
                                SQL - Restrict the number of rows returned based on a count of rows
                            
                                Common Table Expression With Insert and Delete
                            
                                SQLSRV and multiple selects in Stored Procedure
                            
                                How to find which indexes and constraints contain a specific column?
                            
                                Select Top into Different Variables
                            
                                Select literals as table data in SQL server
                            
                                Access the database name inside Up in DbMigration

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!

Donate Us With

Sql server strange Identity increment

Tags:

sql-server

azure-sql-database

user3077725

People also ask

1 Answers

Aaron Bertrand

Recent Activity

Donate For Us