<commit_before>DROP TABLE dbo.Events
DROP TABLE dbo.Streams

CREATE TABLE dbo.Streams(
	StreamId CHAR(40) NOT NULL,
	StreamIdOriginal NVARCHAR(1000) NOT NULL,
	StreamIdInternal INT IDENTITY(1,1) NOT NULL,
	IsDeleted BIT NOT NULL DEFAULT ((0)),
	CONSTRAINT PK_Streams PRIMARY KEY CLUSTERED (StreamIdInternal)
);

CREATE UNIQUE NONCLUSTERED INDEX IX_Streams_StreamId ON dbo.Streams (StreamId);

CREATE TABLE dbo.Events(
	StreamIdInternal INT NOT NULL,
	[Checkpoint] int IDENTITY(1,1) NOT NULL,
	EventId UNIQUEIDENTIFIER NOT NULL,
	SequenceNumber INT NOT NULL,
	Created DATETIME NOT NULL,
	[Type] NVARCHAR(128) NOT NULL,
	JsonData NVARCHAR(max) NOT NULL,
	JsonMetadata NVARCHAR(max),
	CONSTRAINT PK_Events PRIMARY KEY CLUSTERED ([Checkpoint]),
	CONSTRAINT FK_Events_Streams FOREIGN KEY (StreamIdInternal) REFERENCES dbo.Streams(StreamIdInternal)
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_Events_StreamIdInternal_SequenceNumber] ON [dbo].[Events] ([StreamIdInternal], [SequenceNumber]);

-- ExpectedVersion.NoStream
DECLARE @sequenceNumber INT = 0;
DECLARE @streamId CHAR(40) = 'stream-1';

DECLARE @eventId_1 UNIQUEIDENTIFIER = NEWID();
DECLARE @created_1 DATETIME = GETDATE();
DECLARE @type_1 NVARCHAR(128) = 'type1';
DECLARE @jsonData_1 NVARCHAR(max) = '\"data1\"';
DECLARE @jsonMetadata_1 NVARCHAR(max) = '\"meta1\"';

DECLARE @eventId_2 UNIQUEIDENTIFIER = NEWID();
DECLARE @created_2 DATETIME = GETDATE();
DECLARE @type_2 NVARCHAR(128) = 'type1';
DECLARE @jsonData_2 NVARCHAR(max) = '\"data2\"';
DECLARE @jsonMetadata_2 NVARCHAR(max) = '\"meta2\"';

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION CreateStream
	DECLARE @count AS INT;
	DECLARE @streamIdInternal AS INT;
	SELECT @count = COUNT(*) FROM [dbo].[Streams] WHERE [StreamId]=@streamId;
	PRINT @count;
	BEGIN
		INSERT INTO dbo.Streams (StreamId, StreamIdOriginal) VALUES (@streamId, @streamId);
		SELECT @streamIdInternal = SCOPE_IDENTITY();
		
		INSERT INTO dbo.Events (StreamIdInternal, SequenceNumber, EventId, Created, [Type], JsonData, JsonMetadata)
			VALUES (@streamIdInternal, @sequenceNumber, @eventId_1, @created_1, @type_1, @jsonData_1, @jsonMetadata_1);
		
		SET @sequenceNumber = @sequenceNumber + 1
		INSERT INTO dbo.Events (StreamIdInternal, SequenceNumber, EventId, Created, [Type], JsonData, JsonMetadata)
			VALUES (@streamIdInternal, @sequenceNumber, @eventId_2, @created_2, @type_2, @jsonData_2, @jsonMetadata_2)
		-- This can continue for N inserts of N events

    END
    SELECT @streamIdInternal
COMMIT TRANSACTION CreateStream

SELECT * FROM dbo.Streams
SELECT * FROM dbo.Events<commit_msg>Example with 3 events.<commit_after>DROP TABLE dbo.Events
DROP TABLE dbo.Streams

CREATE TABLE dbo.Streams(
	StreamId CHAR(40) NOT NULL,
	StreamIdOriginal NVARCHAR(1000) NOT NULL,
	StreamIdInternal INT IDENTITY(1,1) NOT NULL,
	IsDeleted BIT NOT NULL DEFAULT ((0)),
	CONSTRAINT PK_Streams PRIMARY KEY CLUSTERED (StreamIdInternal)
);

CREATE UNIQUE NONCLUSTERED INDEX IX_Streams_StreamId ON dbo.Streams (StreamId);

CREATE TABLE dbo.Events(
	StreamIdInternal INT NOT NULL,
	[Checkpoint] int IDENTITY(1,1) NOT NULL,
	EventId UNIQUEIDENTIFIER NOT NULL,
	SequenceNumber INT NOT NULL,
	Created DATETIME NOT NULL,
	[Type] NVARCHAR(128) NOT NULL,
	JsonData NVARCHAR(max) NOT NULL,
	JsonMetadata NVARCHAR(max),
	CONSTRAINT PK_Events PRIMARY KEY CLUSTERED ([Checkpoint]),
	CONSTRAINT FK_Events_Streams FOREIGN KEY (StreamIdInternal) REFERENCES dbo.Streams(StreamIdInternal)
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_Events_StreamIdInternal_SequenceNumber] ON [dbo].[Events] ([StreamIdInternal], [SequenceNumber]);

-- ExpectedVersion.NoStream
DECLARE @sequenceNumber INT = 0;
DECLARE @streamId CHAR(40) = 'stream-1';

DECLARE @eventId_1 UNIQUEIDENTIFIER = NEWID();
DECLARE @created_1 DATETIME = GETDATE();
DECLARE @type_1 NVARCHAR(128) = 'type1';
DECLARE @jsonData_1 NVARCHAR(max) = '\"data1\"';
DECLARE @jsonMetadata_1 NVARCHAR(max) = '\"meta1\"';

DECLARE @eventId_2 UNIQUEIDENTIFIER = NEWID();
DECLARE @created_2 DATETIME = GETDATE();
DECLARE @type_2 NVARCHAR(128) = 'type2';
DECLARE @jsonData_2 NVARCHAR(max) = '\"data2\"';
DECLARE @jsonMetadata_2 NVARCHAR(max) = '\"meta2\"';

DECLARE @eventId_3 UNIQUEIDENTIFIER = NEWID();
DECLARE @created_3 DATETIME = GETDATE();
DECLARE @type_3 NVARCHAR(128) = 'type3';
DECLARE @jsonData_3 NVARCHAR(max) = '\"data3\"';
DECLARE @jsonMetadata_3 NVARCHAR(max) = '\"meta3\"';

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION CreateStream
	DECLARE @count AS INT;
	DECLARE @streamIdInternal AS INT;
	SELECT @count = COUNT(*) FROM [dbo].[Streams] WHERE [StreamId]=@streamId;
	PRINT @count;
	BEGIN
		INSERT INTO dbo.Streams (StreamId, StreamIdOriginal) VALUES (@streamId, @streamId);
		SELECT @streamIdInternal = SCOPE_IDENTITY();
		
		INSERT INTO dbo.Events (StreamIdInternal, SequenceNumber, EventId, Created, [Type], JsonData, JsonMetadata)
			VALUES (@streamIdInternal, @sequenceNumber, @eventId_1, @created_1, @type_1, @jsonData_1, @jsonMetadata_1);
		
		SET @sequenceNumber = @sequenceNumber + 1
		INSERT INTO dbo.Events (StreamIdInternal, SequenceNumber, EventId, Created, [Type], JsonData, JsonMetadata)
			VALUES (@streamIdInternal, @sequenceNumber, @eventId_2, @created_2, @type_2, @jsonData_2, @jsonMetadata_2)

		SET @sequenceNumber = @sequenceNumber + 1
		INSERT INTO dbo.Events (StreamIdInternal, SequenceNumber, EventId, Created, [Type], JsonData, JsonMetadata)
			VALUES (@streamIdInternal, @sequenceNumber, @eventId_3, @created_3, @type_3, @jsonData_3, @jsonMetadata_3)
		-- This can continue for N inserts of N events.. Is there a better way?

    END
    SELECT @streamIdInternal
COMMIT TRANSACTION CreateStream

SELECT * FROM dbo.Streams
SELECT * FROM dbo.Events
{'fraction_non_alphanumeric': 0.10842529339639166, 'fraction_numerical': 0.021019442984760904, 'mean_word_length': 6.273885350318471, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5554569', 'n_tokens_mistral': 2085, 'n_tokens_neox': 1917, 'n_words': 539}