UUID Library v1.3.0

Microsoft SQL Server Integration

webpatser@dev: ~/uuid/1.3.0 $ cat mssql-guide.md

Microsoft SQL Server Integration - UUID Library v1.3.0

Complete guide for using UUIDs with Microsoft SQL Server, including common issues, fixes, and best practices for optimal performance and compatibility.

Key SQL Server Fixes
  • UNIQUEIDENTIFIER Format: Proper conversion to SQL Server's native UUID format
  • Byte Order Fix: Corrects Microsoft's mixed-endian byte ordering
  • Index Performance: Optimized UUID generation for better clustering
  • Compatibility Layer: Seamless PHP ↔ SQL Server UUID exchange

SQL Server UUID Format Issues

Microsoft SQL Server stores UUIDs in a specific format that differs from the standard RFC 4122 representation, requiring special handling.

The Problem: Mixed-Endian Byte Order

use Webpatser\Uuid\Uuid;
 
// Standard UUID format
$uuid = Uuid::v4();
echo $uuid->string;
// Output: "550e8400-e29b-41d4-a716-446655440000"
 
// SQL Server stores this as mixed-endian:
// First 3 sections: Little-endian
// Last 2 sections: Big-endian
// Result: "00840e55-9be2-d441-a716-446655440000"
Common Issue
Without proper conversion, UUIDs generated in PHP won't match those stored in SQL Server, causing lookup failures and data inconsistencies.

Solutions & Fixes

1. SQL Server Compatible UUID Generation

use Webpatser\Uuid\Uuid;
 
// Generate UUID with SQL Server compatibility
function generateSqlServerUuid(): string
{
    $uuid = Uuid::v4();
    return convertToSqlServerFormat($uuid->string);
}
 
// Convert standard UUID to SQL Server format
function convertToSqlServerFormat(string $uuid): string
{
    // Remove dashes
    $hex = str_replace('-', '', $uuid);
 
    // Rearrange bytes for SQL Server's mixed-endian format
    $part1 = substr($hex, 6, 2) . substr($hex, 4, 2) . substr($hex, 2, 2) . substr($hex, 0, 2);
    $part2 = substr($hex, 10, 2) . substr($hex, 8, 2);
    $part3 = substr($hex, 14, 2) . substr($hex, 12, 2);
    $part4 = substr($hex, 16, 4);
    $part5 = substr($hex, 20, 12);
 
    return strtoupper($part1 . '-' . $part2 . '-' . $part3 . '-' . $part4 . '-' . $part5);
}
 
// Usage
$sqlServerUuid = generateSqlServerUuid();
echo $sqlServerUuid; // "00840E55-9BE2-D441-A716-446655440000"

2. Database Schema Setup

-- Create table with UNIQUEIDENTIFIER column
CREATE TABLE users (
    id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    name NVARCHAR(255) NOT NULL,
    email NVARCHAR(255) UNIQUE NOT NULL,
    created_at DATETIME2 DEFAULT GETDATE()
);
 
-- Index for better performance
CREATE NONCLUSTERED INDEX IX_users_id ON users(id);
 
-- Alternative with custom UUID from PHP
CREATE TABLE products (
    id UNIQUEIDENTIFIER PRIMARY KEY,
    name NVARCHAR(255) NOT NULL,
    price DECIMAL(10,2),
    created_at DATETIME2 DEFAULT GETDATE()
);

3. PHP Integration with PDO

use Webpatser\Uuid\Uuid;
 
class SqlServerUuidHelper
{
    private PDO $pdo;
 
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
 
    // Insert record with PHP-generated UUID
    public function createUser(string $name, string $email): string
    {
        $uuid = Uuid::v4();
        $sqlServerUuid = $this->convertToSqlServerFormat($uuid->string);
 
        $stmt = $this->pdo->prepare("
            INSERT INTO users (id, name, email)
            VALUES (CAST(? AS UNIQUEIDENTIFIER), ?, ?)
        ");
 
        $stmt->execute([$sqlServerUuid, $name, $email]);
 
        return $uuid->string; // Return standard format for PHP
    }
 
    // Retrieve and convert back to standard format
    public function getUser(string $uuid): ?array
    {
        $sqlServerUuid = $this->convertToSqlServerFormat($uuid);
 
        $stmt = $this->pdo->prepare("
            SELECT id, name, email, created_at
            FROM users
            WHERE id = CAST(? AS UNIQUEIDENTIFIER)
        ");
 
        $stmt->execute([$sqlServerUuid]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
 
        if ($result) {
            // Convert SQL Server UUID back to standard format
            $result['id'] = $this->convertFromSqlServerFormat($result['id']);
        }
 
        return $result ?: null;
    }
 
    private function convertToSqlServerFormat(string $uuid): string
    {
        $hex = str_replace('-', '', $uuid);
 
        $part1 = substr($hex, 6, 2) . substr($hex, 4, 2) . substr($hex, 2, 2) . substr($hex, 0, 2);
        $part2 = substr($hex, 10, 2) . substr($hex, 8, 2);
        $part3 = substr($hex, 14, 2) . substr($hex, 12, 2);
        $part4 = substr($hex, 16, 4);
        $part5 = substr($hex, 20, 12);
 
        return strtoupper($part1 . '-' . $part2 . '-' . $part3 . '-' . $part4 . '-' . $part5);
    }
 
    private function convertFromSqlServerFormat(string $sqlServerUuid): string
    {
        $hex = str_replace('-', '', $sqlServerUuid);
 
        // Reverse the byte order conversion
        $part1 = substr($hex, 6, 2) . substr($hex, 4, 2) . substr($hex, 2, 2) . substr($hex, 0, 2);
        $part2 = substr($hex, 10, 2) . substr($hex, 8, 2);
        $part3 = substr($hex, 14, 2) . substr($hex, 12, 2);
        $part4 = substr($hex, 16, 4);
        $part5 = substr($hex, 20, 12);
 
        return strtolower($part1 . '-' . $part2 . '-' . $part3 . '-' . $part4 . '-' . $part5);
    }
}
 
// Usage example
$pdo = new PDO('sqlsrv:Server=localhost;Database=mydb', $username, $password);
$helper = new SqlServerUuidHelper($pdo);
 
$userId = $helper->createUser('John Doe', '[email protected]');
$user = $helper->getUser($userId);
 
echo "Created user: " . $user['name'] . " with ID: " . $user['id'];

Laravel Integration

For Laravel applications using SQL Server:

// Model with UUID primary key
use Illuminate\Database\Eloquent\Model;
use Webpatser\Uuid\Uuid;
 
class User extends Model
{
    protected $keyType = 'string';
    public $incrementing = false;
 
    protected $casts = [
        'id' => 'string',
    ];
 
    protected static function boot()
    {
        parent::boot();
 
        static::creating(function ($model) {
            if (!$model->id) {
                // Generate UUID v7 for better database performance
                $uuid = Uuid::v7();
 
                // Convert to SQL Server format if using SQL Server
                if (config('database.default') === 'sqlsrv') {
                    $model->id = self::convertToSqlServerFormat($uuid->string);
                } else {
                    $model->id = $uuid->string;
                }
            }
        });
 
        static::retrieved(function ($model) {
            // Convert back from SQL Server format if needed
            if (config('database.default') === 'sqlsrv') {
                $model->id = self::convertFromSqlServerFormat($model->id);
            }
        });
    }
 
    private static function convertToSqlServerFormat(string $uuid): string
    {
        // Implementation same as above
        $hex = str_replace('-', '', $uuid);
        $part1 = substr($hex, 6, 2) . substr($hex, 4, 2) . substr($hex, 2, 2) . substr($hex, 0, 2);
        $part2 = substr($hex, 10, 2) . substr($hex, 8, 2);
        $part3 = substr($hex, 14, 2) . substr($hex, 12, 2);
        $part4 = substr($hex, 16, 4);
        $part5 = substr($hex, 20, 12);
        return strtoupper($part1 . '-' . $part2 . '-' . $part3 . '-' . $part4 . '-' . $part5);
    }
 
    private static function convertFromSqlServerFormat(string $sqlServerUuid): string
    {
        // Reverse conversion implementation
        $hex = str_replace('-', '', $sqlServerUuid);
        $part1 = substr($hex, 6, 2) . substr($hex, 4, 2) . substr($hex, 2, 2) . substr($hex, 0, 2);
        $part2 = substr($hex, 10, 2) . substr($hex, 8, 2);
        $part3 = substr($hex, 14, 2) . substr($hex, 12, 2);
        $part4 = substr($hex, 16, 4);
        $part5 = substr($hex, 20, 12);
        return strtolower($part1 . '-' . $part2 . '-' . $part3 . '-' . $part4 . '-' . $part5);
    }
}

Performance Optimizations

1. Index-Friendly UUID Generation

use Webpatser\Uuid\Uuid;
 
// Use UUID v7 for better SQL Server performance
// v7 UUIDs are time-ordered, reducing index fragmentation
function generateOptimalSqlServerUuid(): string
{
    $uuid = Uuid::v7(); // Time-ordered UUID
    return convertToSqlServerFormat($uuid->string);
}
 
// For high-volume inserts, generate UUIDs in batches
function generateUuidBatch(int $count): array
{
    $uuids = [];
    for ($i = 0; $i < $count; $i++) {
        $uuids[] = generateOptimalSqlServerUuid();
    }
    return $uuids;
}

2. Bulk Insert Optimization

-- Use table-valued parameters for bulk inserts
CREATE TYPE UuidTableType AS TABLE (
    id UNIQUEIDENTIFIER,
    name NVARCHAR(255),
    email NVARCHAR(255)
);
 
-- Stored procedure for bulk insert
CREATE PROCEDURE BulkInsertUsers
    @Users UuidTableType READONLY
AS
BEGIN
    INSERT INTO users (id, name, email)
    SELECT id, name, email FROM @Users;
END

Common Issues & Troubleshooting

Common Issues
Issue: "Invalid GUID format" errors
Fix: Ensure proper byte order conversion before SQL Server queries
Issue: Slow queries with UUID WHERE clauses
Fix: Add proper indexes and use CAST(? AS UNIQUEIDENTIFIER)
Issue: UUID comparison failures
Fix: Always convert to SQL Server format before comparisons
Issue: Index fragmentation with random UUIDs
Fix: Use UUID v7 (time-ordered) instead of v4 (random)

Debugging UUID Conversion

// Debug helper to verify UUID conversion
function debugUuidConversion(string $originalUuid): void
{
    echo "Original UUID: " . $originalUuid . "\n";
 
    $sqlServerFormat = convertToSqlServerFormat($originalUuid);
    echo "SQL Server Format: " . $sqlServerFormat . "\n";
 
    $backConverted = convertFromSqlServerFormat($sqlServerFormat);
    echo "Back Converted: " . $backConverted . "\n";
 
    $isMatch = strtolower($originalUuid) === strtolower($backConverted);
    echo "Conversion Valid: " . ($isMatch ? "✓" : "✗") . "\n\n";
}
 
// Test conversion
$testUuid = Uuid::v4()->string;
debugUuidConversion($testUuid);

Migration Guide

If you're migrating existing UUID data to/from SQL Server:

// Migration script for existing data
function migrateLegacyUuids(PDO $pdo): void
{
    // Get all existing UUIDs
    $stmt = $pdo->query("SELECT id, other_uuid_column FROM legacy_table");
    $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
    foreach ($records as $record) {
        $convertedId = convertToSqlServerFormat($record['id']);
        $convertedOther = convertToSqlServerFormat($record['other_uuid_column']);
 
        // Update with converted UUIDs
        $updateStmt = $pdo->prepare("
            UPDATE legacy_table
            SET id = CAST(? AS UNIQUEIDENTIFIER),
                other_uuid_column = CAST(? AS UNIQUEIDENTIFIER)
            WHERE id = CAST(? AS UNIQUEIDENTIFIER)
        ");
 
        $updateStmt->execute([
            $convertedId,
            $convertedOther,
            $record['id']
        ]);
    }
 
    echo "Migrated " . count($records) . " UUID records\n";
}

Best Practices Summary

  • ✅ Always convert UUIDs to SQL Server format before database operations
  • ✅ Use UUID v7 for better index performance in SQL Server
  • ✅ Add proper indexes on UNIQUEIDENTIFIER columns
  • ✅ Use CAST(? AS UNIQUEIDENTIFIER) in SQL queries
  • ✅ Implement conversion helpers to avoid manual byte manipulation
  • ✅ Test conversions thoroughly before production deployment
  • ✅ Consider bulk operations for high-volume UUID generation
  • ✅ Monitor index fragmentation and rebuild as needed

Conclusion

With proper UUID conversion and the techniques outlined in this guide, you can seamlessly integrate PHP-generated UUIDs with Microsoft SQL Server while maintaining optimal performance and data integrity.

The key is understanding SQL Server's mixed-endian byte order and implementing proper conversion functions to handle the format differences between standard RFC 4122 UUIDs and SQL Server's UNIQUEIDENTIFIER type.