Generate sequential IDs without losing the sequence

The best way to resolve this is:

Not using Identities .

For this you have to abandon the sequential keys and go to random keys. In the Entity Framework , the field type is Guid .

The chance of collision of Guids (that is, you generate two Guids equal) is one in 2,600,000,000,000,000,000.

To use, declare your Model key as follows:

public Guid MeuModeloId { get; set; }

Before you apply SaveChanges() , you must generate Guid for your Id property:

meuObjeto.MeuModeloId = Guid.NewGuid();

SQL Server works perfectly with Guids , but there the data type is called UNIQUEIDENTIFIER (read more at link ).

The resource you are looking for exists from SQL Server 2012 and is called SEQUENCES .

The SEQUENCES are objects used to generate auto numbering values, where we can define the initial values and their increments. They are independent objects of the tables and triggers and have been created exactly to circumvent Identity limitations, such as the Max() + 1 for example.

You can create a SEQUENCE with the following T-SQL

CREATE SEQUENCE dbo.NomeSeq AS INT MINVALUE 1 CYCLE;

A sequence of type INT will be created starting from number 1, and when it reaches the limit of type (2147483648 in the case of INT ) of the field, the count will be restarted from CYCLE . >

Determining the next value of the sequence can be done with the command NEXT VALUE FOR

SELECT NEXT VALUE FOR dbo.NomeSeq;

More information regarding syntax and usage of SEQUENCE can be found here .     

Simply create a parallel table of a row and a field just to save your increment.

So you update this table with contador = contador + 1 every time you need a new ID, and use it in the next transaction.

Price is the extra query to get the ID, but it can be more interesting than MAX()+1 , with the advantage of not returning the counter if you delete the higher number record. (it would be the only case of "brute registry", but it is easy to control whether or not the helper table will increment).

  

This solution is between the threshold of a real solution and a technical repair 1 , but works fine if applied correctly.

1. gambiarra

  

Addendum: If you have index in the column, nothing changes a MAX () with 1 or with 10000000 records. I do not suppose that any reasonable DB would check row by line an indexed field to get a simple MAX() .

Creating a sequence using a procedure

USE [DBBANCO]
GO

/****** Object:  StoredProcedure [dbo].[Gerador_Seq]  15:35:53 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[Gerador_Seq]  @Total int as

DECLARE @intFlag INT

SET @intFlag = 1

WHILE (@intFlag <= @Total )

Begin

    SET @intFlag = @intFlag + 1
    INSERT INTO dbo.TEMP_SEQ (my_name) values ('na') ;

    IF @intFlag = @Total
       begin 
         BREAK;
       end
end
  return ''



GO

/** O campo my_id int IDENTITY =>> Completa o Serviço **/