Why Sql Server SELECT does not return all entries?

Question

Ok. I completely rewrote my question.

Database table

CREATE TABLE [dbo].[IntegrationCommandLog](
    [Id] [uniqueidentifier] NOT NULL,
    [Content] [nvarchar](max) NULL,
    [OrderingKey] [bigint] IDENTITY(1,1) NOT NULL,
    [RowVersion] [timestamp] NULL,
    [Topic] [nvarchar](13) NULL,
 CONSTRAINT [PK_IntegrationCommandLog] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_IntegrationCommandLog_OrderingKey] ON [dbo].[IntegrationCommandLog]
(
    [OrderingKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Reproducer. Run release without attached debugger is required

Required packages:

Install-Package Dapper
Install-Package System.Data.SqlClient

Code:

using Dapper;
using System;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace TestApp
{
    class Program
    {
        private const string Sql = "SELECT * FROM dbo.IntegrationCommandLog WHERE OrderingKey > @OrderingKey ORDER BY OrderingKey";
        private const string cs = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

        static void Main(string[] args)
        {
            Task.Run(() => Query());

            var tasks = new Task[200];
            for (int i = 0; i < tasks.Length; ++i)
                tasks[i] = Task.Run(() => Insert());

            while (true)
            {
                int j = Task.WaitAny(tasks);
                tasks[j] = Task.Run(() => Insert());
            }
        }

        private async static Task Query()
        {
            long last = -1;
            var connection = new SqlConnection(cs);
            await connection.OpenAsync();
            while (true)
            {
                var entries = await connection.QueryAsync<IntegrationLogEntry>(Sql, new { OrderingKey = last });

                Console.WriteLine(entries.Count());
                if (entries.Any())
                {
                    last = entries.Aggregate((e1, e2) =>
                    {
                        if (e1.OrderingKey + 1 != e2.OrderingKey)
                            Console.WriteLine($"Sequence violation {e1.OrderingKey} {e2.OrderingKey}");

                        return e2;
                    }).OrderingKey;
                }
                await Task.Delay(1000);
            }
        }

        private static async Task Insert()
        {
            string sql = @"SET NOCOUNT ON;
INSERT INTO [dbo].[IntegrationCommandLog] ([Id], [Content], [Topic])
VALUES ( @Id, @Content, @Topic);
SELECT [OrderingKey], [RowVersion]
FROM [dbo].[IntegrationCommandLog]
WHERE @@ROWCOUNT = 1 AND [Id] = @Id";

            var content = new string('a', 1000);
            using (var connection = new SqlConnection(cs))
            {
                await connection.OpenAsync();
                await connection.ExecuteAsync(sql, new { Id = Guid.NewGuid(), Content = content, Topic = "SomeTopic" });
            }
        }
    }

    public class IntegrationLogEntry
    {
        public Guid Id { get; private set; }
        public string Content { get; private set; }
        public string Topic { get; private set; }
        public long OrderingKey { get; private set; }
        public byte[] RowVersion { get; set; }
    }
}

Make sure there are no gaps

SELECT top 100 * FROM (SELECT *, rowid = ROW_NUMBER() OVER (ORDER BY OrderingKey) FROM [dbo].[IntegrationCommandLog]) l1
JOIN (SELECT *, rowid = ROW_NUMBER() OVER (ORDER BY OrderingKey) FROM [dbo].[IntegrationCommandLog]) l2 on l1.rowid + 1 = l2.rowid
WHERE l1.OrderingKey + 1 != l2.OrderingKey

Output

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 6.3 (Build 14393: ) (Hypervisor)

Questions:

1. Why the query does not return all entries?
2. How to get correct list of entries?

Answer 1

e1.OrderingKey + 1 != e2.OrderingKey tests that e2 is only 1 higher than the previous key. That isn't guaranteed by the database.

There are many things that will cause non-consecutive IDENTITY: (not forgetting deletes)

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.