sqlserver.md

SQL Server usage

Docs for when using when using SQL Server SqlClient.

Implementation

For SQL Server the transaction log is used (via dm_db_log_stats) if the current user has the VIEW SERVER STATE permission.

If VIEW SERVER STATE is not allowed then a combination of Change Tracking and/or Row Versioning is used.

Give the above certain kinds of operations will be detected:

	Transaction Log	Change Tracking	Row Versioning	Change Tracking and Row Versioning
Insert	✅	✅	✅	✅
Update	✅	✅	✅	✅
Hard Delete	✅	✅	❌	✅
Soft Delete	✅	✅	✅	✅
Truncate	✅	❌	❌	❌

Timestamp calculation

VIEW SERVER STATE permission

Transaction log is used via dm_db_log_stats.

select log_end_lsn
from sys.dm_db_log_stats(db_id())

^{snippet source | anchor}

No VIEW SERVER STATE permission

A combination of change_tracking_current_version (if tracking is enabled) and @@DBTS (row version timestamp)

declare @changeTracking bigint = change_tracking_current_version();
declare @timeStamp bigint = convert(bigint, @@dbts);

if (@changeTracking is null)
  select cast(@timeStamp as varchar)
else
  select cast(@timeStamp as varchar) + '-' + cast(@changeTracking as varchar)

^{snippet source | anchor}

Usage

Example SQL schema

-- Tables

CREATE TABLE [dbo].[Companies](
	[Id] [uniqueidentifier] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
	[Content] [nvarchar](max) NULL,
 CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[Companies] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = OFF)

CREATE TABLE [dbo].[Employees](
	[Id] [uniqueidentifier] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
	[CompanyId] [uniqueidentifier] NOT NULL,
	[Content] [nvarchar](max) NULL,
	[Age] [int] NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Employees_CompanyId] ON [dbo].[Employees]
(
	[CompanyId] ASC
) ON [PRIMARY]

^{snippet source | anchor}

Add to WebApplicationBuilder

var builder = WebApplication.CreateBuilder();
builder.Services.AddScoped(_ => new SqlConnection(connectionString));
var app = builder.Build();
app.UseDelta();

^{snippet source | anchor}

Add to a Route Group

To add to a specific Route Group:

app.MapGroup("/group")
    .UseDelta()
    .MapGet("/", () => "Hello Group!");

^{snippet source | anchor}

ShouldExecute

Optionally control what requests Delta is executed on.

var app = builder.Build();
app.UseDelta(
    shouldExecute: httpContext =>
    {
        var path = httpContext.Request.Path.ToString();
        return path.Contains("match");
    });

^{snippet source | anchor}

Custom Connection discovery

By default, Delta uses HttpContext.RequestServices to discover the SqlConnection and SqlTransaction:

var sqlConnectionType = Type.GetType("Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient");
if (sqlConnectionType != null)
{
    connectionType = sqlConnectionType;
    transactionType = sqlConnectionType.Assembly.GetType("Microsoft.Data.SqlClient.SqlTransaction")!;
    return;
}

^{snippet source | anchor}

static Connection DiscoverConnection(HttpContext httpContext)
{
    var provider = httpContext.RequestServices;
    var connection = (DbConnection) provider.GetRequiredService(connectionType);
    var transaction = (DbTransaction?) provider.GetService(transactionType);
    return new(connection, transaction);
}

^{snippet source | anchor}

To use custom connection discovery:

var application = webApplicationBuilder.Build();
application.UseDelta(
    getConnection: httpContext =>
        httpContext.RequestServices.GetRequiredService<SqlConnection>());

^{snippet source | anchor}

To use custom connection and transaction discovery:

var application = webApplicationBuilder.Build();
application.UseDelta(
    getConnection: httpContext =>
    {
        var provider = httpContext.RequestServices;
        var connection = provider.GetRequiredService<SqlConnection>();
        var transaction = provider.GetService<SqlTransaction>();
        return new(connection, transaction);
    });

^{snippet source | anchor}

GetLastTimeStamp:

GetLastTimeStamp is a helper method to get the DB timestamp that Delta uses to calculate the etag.

var timeStamp = await connection.GetLastTimeStamp();

^{snippet source | anchor}

Delta.SqlServer

A set of helper methods for working with SQL Server Change Tracking and SQL Server Row Versioning

Nuget: Delta.SqlServer

GetDatabasesWithTracking

Get a list of all databases with change tracking enabled.

var trackedDatabases = await sqlConnection.GetTrackedDatabases();
foreach (var db in trackedDatabases)
{
    Trace.WriteLine(db);
}

^{snippet source | anchor}

Uses the following SQL:

select d.name
from sys.databases as d inner join
  sys.change_tracking_databases as t on
  t.database_id = d.database_id

^{snippet source | anchor}

GetTrackedTables

Get a list of all tracked tables in database.

var trackedTables = await sqlConnection.GetTrackedTables();
foreach (var db in trackedTables)
{
    Trace.WriteLine(db);
}

^{snippet source | anchor}

Uses the following SQL:

select t.Name
from sys.tables as t inner join
  sys.change_tracking_tables as c on t.[object_id] = c.[object_id]

^{snippet source | anchor}

IsTrackingEnabled

Determine if change tracking is enabled for a database.

var isTrackingEnabled = await sqlConnection.IsTrackingEnabled();

^{snippet source | anchor}

Uses the following SQL:

select count(d.name)
from sys.databases as d inner join
  sys.change_tracking_databases as t on
  t.database_id = d.database_id
where d.name = '{database}'

^{snippet source | anchor}

EnableTracking

Enable change tracking for a database.

await sqlConnection.EnableTracking();

^{snippet source | anchor}

Uses the following SQL:

alter database {database}
set change_tracking = on
(
  change_retention = {retentionDays} days,
  auto_cleanup = on
)

^{snippet source | anchor}

DisableTracking

Disable change tracking for a database and all tables within that database.

await sqlConnection.DisableTracking();

^{snippet source | anchor}

Uses the following SQL:

For disabling tracking on a database:

alter database [{database}] set change_tracking = off;

^{snippet source | anchor}

For disabling tracking on tables:

alter table [{table}] disable change_tracking;

^{snippet source | anchor}

SetTrackedTables

Enables change tracking for all tables listed, and disables change tracking for all tables not listed.

await sqlConnection.SetTrackedTables(["Companies"]);

^{snippet source | anchor}

Uses the following SQL:

For enabling tracking on a database:

alter database {database}
set change_tracking = on
(
  change_retention = {retentionDays} days,
  auto_cleanup = on
)

^{snippet source | anchor}

For enabling tracking on tables:

alter table [{table}] enable change_tracking

^{snippet source | anchor}

For disabling tracking on tables:

alter table [{table}] disable change_tracking;

^{snippet source | anchor}