🚀 SQL Server Universal Compatibility

// Version-aware feature detection
public class SqlServerFeatureDetector
{
    private readonly ServerVersion _version;
    
    public bool SupportsJsonFunctions() 
        => _version >= ServerVersion.Sql2016;
    
    public bool SupportsStringSplit() 
        => _version >= ServerVersion.Sql2016;
    
    public bool SupportsStringAgg() 
        => _version >= ServerVersion.Sql2017;
    
    public bool SupportsWindowFunctions() 
        => _version >= ServerVersion.Sql2012;
    
    public bool SupportsGraphQueries() 
        => _version >= ServerVersion.Sql2017;
    
    public ISqlGenerator GetOptimalGenerator()
    {
        return _version switch
        {
            >= ServerVersion.Sql2022 => 
                new ModernSqlGenerator(),
            
            >= ServerVersion.Sql2016 => 
                new EnhancedSqlGenerator(),
            
            >= ServerVersion.Sql2012 => 
                new IntermediateSqlGenerator(),
            
            _ => 
                new LegacySqlGenerator()
        };
    }
}

-- Leverages native window functions for efficiency
WITH RankedProducts AS 
(
    SELECT 
        p.ProductName,
        c.CategoryName,
        SUM(s.Quantity * s.UnitPrice) AS TotalSales,
        ROW_NUMBER() OVER (
            PARTITION BY c.CategoryID 
            ORDER BY SUM(s.Quantity * s.UnitPrice) DESC
        ) AS CategoryRank
    FROM Products p
        INNER JOIN Categories c 
            ON p.CategoryID = c.CategoryID
        INNER JOIN Sales s 
            ON p.ProductID = s.ProductID
    GROUP BY 
        p.ProductName, 
        c.CategoryName, 
        c.CategoryID
)
SELECT 
    CategoryName,
    ProductName,
    FORMAT(TotalSales, 'C') AS FormattedSales,
    CategoryRank
FROM RankedProducts
WHERE CategoryRank <= 5
ORDER BY 
    CategoryName, 
    CategoryRank;

-- Achieves same result without window functions
SELECT 
    t1.CategoryName,
    t1.ProductName,
    '$' + CAST(t1.TotalSales AS VARCHAR) AS FormattedSales,
    (
        SELECT COUNT(*) + 1
        FROM (
            SELECT 
                c.CategoryID,
                SUM(s.Quantity * s.UnitPrice) AS Sales
            FROM Products p2
                INNER JOIN Sales s 
                    ON p2.ProductID = s.ProductID
                INNER JOIN Categories c 
                    ON p2.CategoryID = c.CategoryID
            WHERE c.CategoryID = t1.CategoryID
            GROUP BY 
                c.CategoryID, 
                p2.ProductID
            HAVING SUM(s.Quantity * s.UnitPrice) > t1.TotalSales
        ) t2
    ) AS CategoryRank
FROM (
    SELECT 
        c.CategoryID,
        c.CategoryName,
        p.ProductName,
        SUM(s.Quantity * s.UnitPrice) AS TotalSales
    FROM Products p
        INNER JOIN Categories c 
            ON p.CategoryID = c.CategoryID
        INNER JOIN Sales s 
            ON p.ProductID = s.ProductID
    GROUP BY 
        c.CategoryID, 
        c.CategoryName, 
        p.ProductName
) t1
WHERE (
    SELECT COUNT(*) + 1
    FROM (
        SELECT SUM(s2.Quantity * s2.UnitPrice) AS Sales
        FROM Products p3
            INNER JOIN Sales s2 
                ON p3.ProductID = s2.ProductID
        WHERE p3.CategoryID = t1.CategoryID
        GROUP BY p3.ProductID
        HAVING SUM(s2.Quantity * s2.UnitPrice) > t1.TotalSales
    ) t3
) <= 5
ORDER BY 
    t1.CategoryName, 
    CategoryRank;

-- Native JSON functions make this elegant
SELECT
    SettingID,
    JSON_VALUE(ConfigJSON, '$.theme') AS Theme,
    JSON_VALUE(ConfigJSON, '$.language') AS Language,
    JSON_VALUE(ConfigJSON, '$.notifications.email') AS EmailNotify,
    JSON_VALUE(ConfigJSON, '$.notifications.sms') AS SMSNotify
FROM UserSettings
WHERE JSON_VALUE(ConfigJSON, '$.active') = 'true';

-- Convert JSON to XML, then extract using XQuery
DECLARE @ConfigXML XML;

WITH ConvertedSettings AS 
(
    SELECT
        SettingID,
        -- Simplified JSON to XML conversion
        CAST(
            '<root>' + 
            REPLACE(REPLACE(REPLACE(
                ConfigJSON,
                '{', '<object>'),
                '}', '</object>'),
                ':', '><value>') +
            '</root>'
        AS XML) AS ConfigXML
    FROM UserSettings
)
SELECT
    SettingID,
    ConfigXML.value('(/root/object/theme/value)[1]', 'VARCHAR(50)') 
        AS Theme,
    ConfigXML.value('(/root/object/language/value)[1]', 'VARCHAR(50)') 
        AS Language,
    ConfigXML.value('(/root/object/notifications/object/email/value)[1]', 'BIT') 
        AS EmailNotify,
    ConfigXML.value('(/root/object/notifications/object/sms/value)[1]', 'BIT') 
        AS SMSNotify
FROM ConvertedSettings
WHERE ConfigXML.value('(/root/object/active/value)[1]', 'VARCHAR(10)') = 'true';

// Core abstraction for version-specific implementations
public interface ISqlDialect
{
    ServerVersion Version { get; }
    
    string GenerateStringAggregation(
        string column, 
        string separator
    );
    
    string GenerateJsonExtraction(
        string jsonColumn, 
        string path
    );
    
    string GenerateWindowFunction(
        WindowFunctionType type, 
        string partitionBy, 
        string orderBy
    );
    
    string GeneratePagination(
        int offset, 
        int fetch
    );
    
    bool SupportsFeature(SqlFeature feature);
}

// Modern implementation leveraging latest features
public class SqlServer2022Dialect : ISqlDialect
{
    public ServerVersion Version 
        => ServerVersion.Sql2022;
    
    public string GenerateStringAggregation(
        string column, 
        string separator
    )
        => $"STRING_AGG({column}, '{separator}')";
    
    public string GenerateJsonExtraction(
        string jsonColumn, 
        string path
    )
        => $"JSON_VALUE({jsonColumn}, '{path}')";
    
    public string GenerateWindowFunction(
        WindowFunctionType type, 
        string partitionBy, 
        string orderBy
    )
        => $"{type}() OVER (PARTITION BY {partitionBy} ORDER BY {orderBy})";
}

// Legacy implementation with compatibility workarounds
public class SqlServer2008Dialect : ISqlDialect
{
    public ServerVersion Version 
        => ServerVersion.Sql2008R2;
    
    public string GenerateStringAggregation(
        string column, 
        string separator
    )
        => $@"STUFF((
            SELECT '{separator}' + {column}
            FROM source
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')";
    
    public string GenerateJsonExtraction(
        string jsonColumn, 
        string path
    )
        // Complex XML-based workaround
        => $"CAST({jsonColumn} AS XML).value('...')";
    
    public string GenerateWindowFunction(
        WindowFunctionType type, 
        string partitionBy, 
        string orderBy
    )
        // Fallback to subquery approach
        => GenerateSubqueryEquivalent(type, partitionBy, orderBy);
}