Generating Sequential Invoice or Unique Numbers with SQL Server Sequences and .NET Core

Views: 99
Comments: 0
Like/Unlike: 0
Posted On: 15-Jun-2026 22:50

Share:

Rahul M...

5074 Points

37 Posts

When building enterprise applications, generating unique numeric identifiers is a common requirement. Examples include invoice numbers, order numbers, ticket numbers, customer references, and utility bill identifiers.

Many developers initially use GUIDs (NEWID() in SQL Server) or random numbers, but these approaches often produce values that are difficult for users to read and manage.SQL Server Sequences provide a reliable, scalable, and database-driven solution for generating unique numeric values.

In this article, we'll explore SQL Server Sequences, their advantages over Identity columns, and how to integrate them with .NET Core and Entity Framework Core.

What is a SQL Server Sequence?

A Sequence is a database object that generates a sequence of numeric values according to rules defined during creation.

Unlike an Identity column, a Sequence is independent of any specific table and can be shared across multiple tables and applications.

Example:

CREATE SEQUENCE dbo.InvoiceSequence
    AS BIGINT
    START WITH 1000000001
    INCREMENT BY 1;

Generate the next value:

SELECT NEXT VALUE FOR dbo.InvoiceSequence;

Output:

1000000001
1000000002
1000000003

Why Use a Sequence Instead of Identity?

Identity Column

CREATE TABLE Invoice
(
    Id BIGINT IDENTITY(1,1),
    Amount DECIMAL(18,2)
);

Identity values are tied to a specific table.

Advantages:

Simple to implement
Automatically generated during insert

Limitations:

Cannot easily share between tables
Value available only after insert
Less flexible for business numbering schemes

SQL Sequence

CREATE SEQUENCE dbo.InvoiceSequence
AS BIGINT
START WITH 1000000001
INCREMENT BY 1;

Advantages:

Independent of tables
Can be shared across multiple tables
Can generate values before insert
Supports custom increments
Ideal for invoice and order numbering

Creating a Sequence

Create a sequence starting from a 10-digit invoice number:

CREATE SEQUENCE dbo.InvoiceSequence
AS BIGINT
START WITH 1000000001
INCREMENT BY 1;

Verify the sequence:

SELECT *
FROM sys.sequences
WHERE name = 'InvoiceSequence';

Generate a value manually:

SELECT NEXT VALUE FOR dbo.InvoiceSequence;

Using Sequences in Entity Framework Core

Configure Sequence in DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasSequence<long>("InvoiceSequence")
        .StartsAt(1000000001)
        .IncrementsBy(1);
}

When migrations are executed, EF Core creates the SQL Server sequence automatically.

Automatically Generate Invoice Numbers

Consider the following entity:

public class Invoice
{
    public int Id { get; set; }

    public long InvoiceNumber { get; set; }

    public decimal Amount { get; set; }
}

Configure the property:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasSequence<long>("InvoiceSequence")
        .StartsAt(1000000001)
        .IncrementsBy(1);

    modelBuilder.Entity<Invoice>()
        .Property(x => x.InvoiceNumber)
        .HasDefaultValueSql(
            "NEXT VALUE FOR dbo.InvoiceSequence");
}

Now whenever a new invoice is inserted, SQL Server automatically assigns the next sequence value.

var invoice = new Invoice
{
    Amount = 100
};

context.Invoices.Add(invoice);
await context.SaveChangesAsync();

Result:

InvoiceNumber = 1000000001

Retrieving the Next Sequence Value Manually

Sometimes the invoice number must be known before inserting the record.

For example:

Display invoice number on screen
Generate PDF before saving
Create related records using the same identifier

A common approach is:

var invoiceNo = context.Database
    .SqlQuery<long>(
        $"SELECT NEXT VALUE FOR dbo.InvoiceSequence")
    .Single();

However, this fails in EF Core.

Error:

NEXT VALUE FOR function is not allowed in derived tables.

Why EF Core Fails

EF Core wraps raw SQL inside a subquery:

SELECT TOP(2) Value
FROM
(
    SELECT NEXT VALUE FOR dbo.InvoiceSequence
) s

SQL Server does not allow NEXT VALUE FOR inside:

Derived tables
Subqueries
Views
Functions
Common table expressions

As a result, the query throws an exception.

Correct Solution Using ADO.NET

Execute the SQL directly without EF Core wrapping it.

using System.Data;

public long GetNextInvoiceNumber()
{
    using var connection =
        _dbContext.Database.GetDbConnection();

    if (connection.State != ConnectionState.Open)
        connection.Open();

    using var command = connection.CreateCommand();

    command.CommandText =
        "SELECT NEXT VALUE FOR dbo.InvoiceSequence";

    return Convert.ToInt64(
        command.ExecuteScalar());
}

Output:

1000000001
1000000002
1000000003

Formatting as a 10-Digit Invoice Number

Store the value as a BIGINT and format it only when displaying.

long invoiceNo = 1000000001;

string formatted =
    invoiceNo.ToString("D10");

Output:

1000000001

For shorter values:

long invoiceNo = 123;

string formatted =
    invoiceNo.ToString("D10");

Output:

0000000123

This keeps storage efficient while maintaining a consistent display format.

Important Considerations

Sequence Values Can Have Gaps

Sequences are not gap-free.

Example:

BEGIN TRAN

SELECT NEXT VALUE FOR dbo.InvoiceSequence

ROLLBACK

Even though the transaction rolls back, the sequence value is consumed.

Example output:

1000000001
1000000002
1000000004

Notice that 1000000003 was skipped.

This behavior is normal and improves performance.

When Are Gaps Acceptable?

Sequences are suitable for:

Invoice numbers
Order numbers
Ticket numbers
Utility bill identifiers
Customer references

If your business requires strictly gapless numbering for tax or regulatory purposes, a custom locking strategy should be implemented instead.

Best Practices

Use BIGINT for future scalability.
Store numbers as numeric values, not strings.
Format invoice numbers only when displaying them.
Use database-generated sequences rather than random numbers.
Reference sequences using schema-qualified names such as:

dbo.InvoiceSequence

Let SQL Server generate values whenever possible.

Conclusion

SQL Server Sequences provide a powerful and scalable mechanism for generating unique numeric identifiers. They are particularly useful for invoice numbers, utility bills, and order references where readability and sequential numbering are important.

When using Entity Framework Core:

Configure sequences through Fluent API.
Use HasDefaultValueSql() for automatic generation.
Use ADO.NET when retrieving sequence values manually.
Store values as BIGINT and format them at the presentation layer.

For enterprise applications, SQL Server Sequences are often the best alternative to GUIDs and random number generators, providing predictable, database-managed numbering with minimal effort.

0 Comments