Sequences

The Sequence class in Weasel.SqlServer manages SQL Server sequences with support for delta detection and ownership.

Defining a Sequence

// Simple sequence starting at 1
var seq = new Sequence("dbo.order_seq");

// Sequence with a custom start value
var seq2 = new Sequence(
    DbObjectName.Parse(SqlServerProvider.Instance, "dbo.invoice_seq"),
    startWith: 1000
);

^{snippet source | anchor}

Sequence Ownership

A sequence can be associated with a table column:

seq.Owner = DbObjectName.Parse(SqlServerProvider.Instance, "dbo.orders");
seq.OwnerColumn = "id";

^{snippet source | anchor}

Using Sequences with Table Columns

Set a column's default value from a sequence:

var table = new Table("dbo.orders");
var seq = new Sequence("dbo.order_seq");

table.AddColumn<long>("id").AsPrimaryKey()
    .DefaultValueFromSequence(seq);

^{snippet source | anchor}

This generates: DEFAULT next value for dbo.order_seq.

Generating DDL

var migrator = new SqlServerMigrator();
var writer = new StringWriter();

seq.WriteCreateStatement(migrator, writer);
// Output: IF OBJECT_ID(N'dbo.order_seq', N'SO') IS NULL, then CREATE SEQUENCE dbo.order_seq START WITH 1;

seq.WriteDropStatement(migrator, writer);
// Output: DROP SEQUENCE IF EXISTS dbo.order_seq;

^{snippet source | anchor}

CREATE SEQUENCE has no IF NOT EXISTS of its own, so the create statement leads with an OBJECT_ID check for a sequence object (N'SO') of that name. Without it a rendered migration script fails on its second run with "There is already an object named ... in the database", taking every statement after it down with it. See batch separators and re-runnable scripts.

Delta Detection

Sequences are checked for existence in sys.sequences:

await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync();

var delta = await seq.FindDeltaAsync(conn);
// delta.Difference: None or Create

^{snippet source | anchor}