[Order 08][P3] Remove SQL Server SET ROWCOUNT paging

[AGiorgetti]

Priority order

Order: 08 of 09
Priority: P3 correctness/performance

Problem

SQL Server dialect uses session-scoped SET ROWCOUNT for snapshot reads and natural paging.

Evidence:

• src/NEventStore.Persistence.Sql/SqlDialects/MsSqlDialect.cs:21
• GetSnapshot prepends SET ROWCOUNT 1.
• NaturalPaging uses SET ROWCOUNT @Limit.

Impact

• SET ROWCOUNT is session-scoped and easy to misuse.
• Snapshot reads can use explicit TOP (1) instead.
• Natural paging can use explicit TOP (@Limit) or keyset query shapes.

Mandatory test-first workflow

• Add tests first that verify SQL Server snapshot and natural-paging behavior.
• Include GetSnapshot coverage.
• Include GetStreamsToSnapshot coverage with more rows than page size.
• Do not change SQL generation until behavior is covered.

Proposed action

• Replace snapshot query with SELECT TOP (1) ... ORDER BY StreamRevision DESC.
• Replace natural paging with explicit TOP (@Limit) query forms or keyset-compatible SQL.

Acceptance criteria

• SQL Server integration tests pass.
• No rowcount setting can affect later commands on the same connection.
• Existing paging behavior remains ordered and complete.

Source: docs/Project-Analysis-Issue-Drafts.md.