NEventStore's MsSql Commits table setup

Commits.sql

IF EXISTS(SELECT * FROM sysobjects WHERE name='Commits' AND xtype = 'U') RETURN;
CREATE TABLE [dbo].[Commits]
(
       [BucketId] [varchar](40) NOT NULL,
       [StreamId] [char](40) NOT NULL,
       [StreamIdOriginal] [nvarchar](1000) NOT NULL,
       [StreamRevision] [int] NOT NULL CHECK ([StreamRevision] > 0),
       [Items] [tinyint] NOT NULL CHECK ([Items] > 0),
       [CommitId] [uniqueidentifier] NOT NULL CHECK ([CommitId] != 0x0),
       [CommitSequence] [int] NOT NULL CHECK ([CommitSequence] > 0),
       [CheckpointNumber] [bigint] IDENTITY NOT NULL,
       [Dispatched] [bit] NOT NULL DEFAULT (0),
       [Headers] [varbinary](MAX) NULL CHECK ([Headers] IS NULL OR DATALENGTH([Headers]) > 0),
       [Payload] [varbinary](MAX) NOT NULL CHECK (DATALENGTH([Payload]) > 0),
       CONSTRAINT [PK_Commits] PRIMARY KEY CLUSTERED ([CheckpointNumber])
);

--datetime2 is supported starting from MSSQL 2008. For MSSQL 2005 datetime has to be used
IF EXISTS(SELECT * FROM SYS.types WHERE NAME = 'datetime2')
	ALTER TABLE [dbo].[Commits] ADD [CommitStamp] [datetime2] NOT NULL
ELSE
	ALTER TABLE [dbo].[Commits] ADD [CommitStamp] [datetime] NOT NULL

CREATE UNIQUE NONCLUSTERED INDEX [IX_Commits_CommitSequence] ON Commits (BucketId, StreamId, CommitSequence);
CREATE UNIQUE NONCLUSTERED INDEX [IX_Commits_CommitId] ON [dbo].[Commits] ([BucketId], [StreamId], [CommitId]);
CREATE UNIQUE NONCLUSTERED INDEX [IX_Commits_Revisions] ON [dbo].[Commits] ([BucketId], [StreamId], [StreamRevision], [Items]);
CREATE INDEX [IX_Commits_Dispatched] ON [dbo].[Commits] ([Dispatched]);
CREATE INDEX [IX_Commits_Stamp] ON Commits ([CommitStamp]);