Generate sequential Ids without losing the sequence

Question

For Identity do not want, because it breaks the sequence if something happens. Max() + 1, I find it very vulnerable and slow when you have a lot of records. So I ask you, is there a way that someone knows, that I can generate sequential numbers and if there’s a problem in generation, it doesn’t fire the number, like Identity does? They say that there are algorithms that do this in Sql Server, but would not like to use triggers. Usage Sql Server 2008, Entity 6 and C#. Will be generated by C# with Entity.

Answer 1

The best way to solve this is:

Not using Identities.

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

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

To use, declare your key Model as follows:

public Guid MeuModeloId { get; set; }

Before applying SaveChanges(), it is necessary to generate a Guid to your property Id:

meuObjeto.MeuModeloId = Guid.NewGuid();

SQL Server works perfectly with Guids, but there the type of data is called UNIQUEIDENTIFIER (read more on http://msdn.microsoft.com/pt-br/library/ms190348.aspx).

Answer 2

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

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

It is possible to create a SEQUENCE with the following T-SQL

CREATE SEQUENCE dbo.NomeSeq AS INT MINVALUE 1 CYCLE;

A sequence of the type will be created INT starting from number 1, and reaching the type limit (2147483648 in the case of INT) field, the countdown will be restarted from the CYCLE.

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

SELECT NEXT VALUE FOR dbo.NomeSeq;

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

Answer 3

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.

The price is the extra query to get the ID, but it might be more interesting than the MAX()+1, with the advantage of not returning the counter if you delete the higher number record. (would be the only case of "Banguela record", but it is easy to control whether the auxiliary table will increase or not).

This solution lies between the threshold of a real solution and a technical repair ¹, but it works well if applied correctly.

_{1. gambiarra}

Addendum: if you have index in column, do not change anything a MAX() with 1 or with 10000000 records. I can’t imagine that any reasonable DB would check line by line an indexed field to get a simple MAX().

Answer 4

Creating a sequence using a precedent

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 **/