README

The pimbay-php/sequence-number-pdo-mssql package provides the Microsoft SQL Server (MSSQL) specific PDO adapter for the PimBay Number Sequence Stack. This adapter leverages MSSQL's MERGE statement for atomic UPSERT operations, ensuring reliable and collision-free sequence generation in MSSQL environments.

This package extends the core pimbay-php/sequence-number-pdo functionality by offering a tailored implementation for MSSQL, ensuring optimal performance and transactional integrity unique to this database system.

Key Features

MSSQL MERGE Statement: Utilizes the MERGE statement for robust atomic "INSERT OR UPDATE" logic, preventing race conditions and ensuring data consistency.
PdoAdapterInterface Implementation: Fully adheres to the PdoAdapterInterface contract from pimbay-php/sequence-number-pdo, allowing seamless integration with the broader Sequence Stack.
Exception Handling: PDO exceptions bubble up to NumberSequence which wraps them consistently across all adapters. MssqlAdapter throws PdoSequenceException directly only when MERGE OUTPUT returns no value.
Zero Dependencies (External): Lightweight with no external third-party dependencies beyond PHP's pdo_sqlsrv extension, sequence-contracts, and sequence-number-pdo.

Installation

You can install the package via Composer:

composer require pimbay-php/sequence-number-pdo-mssql

This package requires the pdo_sqlsrv PHP extension.

Usage

To use the MssqlAdapter, you would typically inject it into NumberSequence or configure DriverDetectingPdoAdapter in sequence-number-pdo to use it when connecting to an MSSQL database.

<?php

declare(strict_types=1);

use PimBay\Sequence\Number\Pdo\NumberSequence;
use PimBay\Sequence\Number\Pdo\Adapter\MssqlAdapter;

// Assuming you have an initialized PDO connection to MSSQL
$pdo = new PDO('sqlsrv:Server=your_server,1433;Database=your_db', 'username', 'password');
$tableName = 'my_sequences_table';

$mssqlAdapter = new MssqlAdapter($pdo, $tableName);
$numberSequence = new NumberSequence($mssqlAdapter);

$nextNumber = $numberSequence->nextNumber('invoice', '2024', 1); // Generates next number
echo "Next Invoice Number: " . $nextNumber;

$current = $numberSequence->getCurrent('invoice', '2024'); // Retrieves current sequence
echo "Current Invoice Value: " . $current->currentValue;

If you are using DriverDetectingPdoAdapter from pimbay-php/sequence-number-pdo, it will automatically detect the sqlsrv PDO driver and use MssqlAdapter if this package is installed and MssqlAdapter::class is added to its adapter map (e.g., via custom map injection).

Performance Benchmarks

Benchmarks were run on PHP 8.3 with MSSQL 2019 and MSSQL 2022 — OPcache enabled, Xdebug enabled. Each adapter combination runs in an isolated container with a restart between runs.

Native adapter (MssqlAdapter) is ~37–38% faster than SelectWithLock — 1.140ms vs 1.834ms on MSSQL 2022, 1.215ms vs 1.756ms on MSSQL 2019 at Revs 1000. The single atomic MERGE OUTPUT eliminates three round-trips required by the explicit transaction strategy.
MSSQL 2019 and MSSQL 2022 perform identically — results within statistical error margin across all adapters and rev counts.

Use Case	Recommended
General production use	`MssqlAdapter` (Native)
Explicit transaction control required	`MssqlSelectWithLockAdapter` (SelectWithLock)

Full benchmark results and interactive charts

Test Matrix

This package is rigorously tested across multiple PHP versions and MSSQL database systems to ensure compatibility and reliability.

PHP Version	MSSQL 2019	MSSQL 2022
8.3	✅	✅

License

Public domain — Unlicense

pimbay-php / sequence-number-pdo-mssql

Maintainers

Package info

Statistics

Security