Alternatives to using Max+1 to create a user friendly unique sequence numbers

Question

I have the repository method inside my asp.net mvc web application, to automatically generate a unique sequence number called Tag:-

public void InsertOrUpdateServer(TMSServer server, string username)
        {

    if (server.TMSServerID == default(int))
            {
                // New entity
    int technologyypeID = GetTechnologyTypeID("Server");
    Technology technology = new Technology
       {
     IsDeleted = true,
     Tag = "S" + GetTagMaximumeNumber(technologyypeID).ToString(),
     StartDate = DateTime.Now,
     };
   Save();
//code goes here

Where the GetTagMaximumeNumber() function is :-

public int GetTagMaximumeNumber(int typeID) {
string s = tms.Technologies.Where(a => a.TypeID == typeID).Max(a2 => a2.Tag.Substring(1));

           if (s != null)
           {
             return Int32.Parse(s) + 1;

           }
           else {
               return 100;//if this is the first record created
           }}

The above will get the maximum number of the tag and add 1 then try to save it into the database. The above is working well, but I am sure that on production server where multiple users will be adding records, same tag numbers will be generated and exception will be raised when trying to add an automatically generated tag number that already exists on the database. So I have the following questions:-1

1. What are the other approaches which I can follow to have a sequence number that is always unique.

2. In my current approach the gaps in the Tag number are minimal. The gap only occurs when user delete a record and its tag number was not the maximum. So is there a way to still have this in any new approach, so gaps will be minimal?

Thanks for the help.

Answer 1

If the new sequences in SQL 2012 are not an option (because you can't upgrade) you could create a table that stores the last sequence numbers.

In the EF context the representing class would look like

class TaggedSequence
{
    [Key]
    public int Id { get; set; }

    [Required]
    public int Last { get; set; }

    [Required]
    [Column(TypeName = "CHAR"), StringLength(1)]
    public string Tag { get; set; }

    [Timestamp]
    public byte[] Rowversion { get; set; }
}

And the barebone procedure to use it:

using (var context = new MyContext())
{
    try
    {
        var tag = "S";
        var sequence = context.TaggedSequence.Single(s => s.Tag == tag);
        sequence.Last += 1;
        Technology technology = new Technology { ... };
        technology.Id = tag + sequence.Last;
        context.Technologies.Add(technology);
        context.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        // Retry
    }
}

(You'll have to fit it in into your repository object)

The concurrency check is there to prevent concurrent users form drawing the same number. The Rowversion ensures that updates are only allowed if its value did not change between fetching the record and updating it.

Answer 2

A complete solution to this problem would be to combine the use of an auto incrementing column and computed column.

The table would look something like this:

CREATE TABLE [dbo].[somedata](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [reference] [varchar](50) NULL,
    [STag]  AS ('S'+CONVERT([varchar](19),[id],(0))),
    CONSTRAINT [PK_somedata] PRIMARY KEY CLUSTERED ([id] ASC)
)   

With a mapping:

public class SomeDataMap : EntityTypeConfiguration<SomeData>
{
    public SomeDataMap()
    {
        // Primary Key
        this.HasKey(t => t.id);

        // Properties
        this.Property(t => t.id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        this.Property(t => t.reference)
            .HasMaxLength(50);

        this.Property(t => t.STag)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
    }
}