How Compatible Is Babelfish with SQL Server? A Practical T-SQL Test

Babelfish promises SQL Server compatibility, but compatibility claims are only useful when grounded in tested behaviour. This post documents what actually works and what does not, based on systematic testing against Babelfish 4.8.0 on PostgreSQL 16.11, running as a FoundryDB managed service.

Every result below was verified by running the query and checking the output. Nothing here is speculative.

Test Setup

• Babelfish 4.8.0 on PostgreSQL 16.11
• Connected via sqlcmd (mssql-tools Docker image) on port 1433
• SQL Server compatibility level: 12.0.2000.8 (SQL Server 2014 wire protocol)

All queries were run against a fresh Babelfish instance with no prior schema.

DDL: Tables, Schemas, and Constraints

CREATE TABLE with IDENTITY columns, PRIMARY KEY, UNIQUE constraints, FOREIGN KEY references, computed columns, and DEFAULT values all work.

CREATE SCHEMA sales;

CREATE TABLE sales.products (
    product_id   INT IDENTITY(1,1) PRIMARY KEY,
    sku          NVARCHAR(50)  NOT NULL UNIQUE,
    name         NVARCHAR(200) NOT NULL,
    unit_price   MONEY         NOT NULL,
    stock_qty    INT           NOT NULL DEFAULT 0,
    is_active    BIT           NOT NULL DEFAULT 1,
    created_at   DATETIME2     DEFAULT GETDATE(),
    updated_at   DATETIME2     DEFAULT GETDATE()
);

CREATE TABLE sales.orders (
    order_id       INT IDENTITY(1,1) PRIMARY KEY,
    order_date     DATETIME2 DEFAULT GETDATE(),
    customer_name  NVARCHAR(100) NOT NULL,
    status         NVARCHAR(20)  NOT NULL DEFAULT 'pending',
    total_amount   MONEY
);

Computed columns also work:

CREATE TABLE sales.order_items (
    item_id      INT IDENTITY(1,1) PRIMARY KEY,
    order_id     INT     NOT NULL REFERENCES sales.orders(order_id),
    product_id   INT     NOT NULL REFERENCES sales.products(product_id),
    quantity     INT     NOT NULL,
    unit_price   MONEY   NOT NULL,
    discount_pct DECIMAL(5,2) DEFAULT 0,
    line_total   AS (quantity * unit_price * (1 - discount_pct/100))
);

Supported data types confirmed working: INT, NVARCHAR(n), NVARCHAR(MAX), DECIMAL, MONEY, BIT, DATE, DATETIME2.

DML: SELECT, INSERT, UPDATE, DELETE

Standard DML works as expected. INSERT with IDENTITY columns, UPDATE with WHERE clauses, and DELETE all behave identically to SQL Server.

CTEs

Both simple and recursive CTEs work:

-- Simple CTE
WITH recent_orders AS (
    SELECT order_id, customer_name, total_amount
    FROM sales.orders
    WHERE status = 'completed'
)
SELECT * FROM recent_orders;

-- Recursive CTE
WITH numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;

Window Functions

RANK(), ROW_NUMBER(), SUM() OVER (PARTITION BY...), LAG(), and LEAD() all work:

SELECT
    order_id,
    customer_name,
    total_amount,
    RANK()       OVER (ORDER BY total_amount DESC) AS amount_rank,
    ROW_NUMBER() OVER (ORDER BY order_date)        AS row_num,
    SUM(total_amount) OVER (PARTITION BY status)   AS status_total,
    LAG(total_amount)  OVER (ORDER BY order_date)  AS prev_amount,
    LEAD(total_amount) OVER (ORDER BY order_date)  AS next_amount
FROM sales.orders;

Stored Procedures

Stored procedures with input parameters, default values, output parameters, conditional logic, and error raising all work:

CREATE PROCEDURE sales.upsert_product
    @sku        NVARCHAR(50),
    @name       NVARCHAR(200),
    @unit_price MONEY,
    @stock_qty  INT = 0,
    @product_id INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM sales.products WHERE sku = @sku)
    BEGIN
        UPDATE sales.products
        SET name = @name, unit_price = @unit_price, updated_at = GETDATE()
        WHERE sku = @sku;

        SELECT @product_id = product_id FROM sales.products WHERE sku = @sku;
    END
    ELSE
    BEGIN
        INSERT INTO sales.products (sku, name, unit_price, stock_qty)
        VALUES (@sku, @name, @unit_price, @stock_qty);

        SET @product_id = SCOPE_IDENTITY();
    END
END;

DATEDIFF(), GETDATE(), SCOPE_IDENTITY(), and SET NOCOUNT ON all work as expected.

TRY/CATCH Error Handling

TRY/CATCH blocks work, including ERROR_NUMBER() and ERROR_MESSAGE():

BEGIN TRY
    DECLARE @result INT = 1 / 0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER()  AS error_number,
        ERROR_MESSAGE() AS error_message;
END CATCH;

Output:

error_number  error_message
------------  ---------------
8134          division by zero

Transactions

BEGIN TRANSACTION, ROLLBACK, and COMMIT work correctly:

BEGIN TRANSACTION;

    INSERT INTO sales.orders (customer_name, status, total_amount)
    VALUES ('Test Customer', 'pending', 99.99);

    -- Verify the insert is visible within the transaction
    SELECT order_id, customer_name FROM sales.orders WHERE customer_name = 'Test Customer';

ROLLBACK;

-- Confirm the row is gone after rollback
SELECT COUNT(*) AS row_count FROM sales.orders WHERE customer_name = 'Test Customer';

The row count after rollback is 0, as expected.

Temporary Tables

Session-scoped temp tables work:

CREATE TABLE #temp_results (
    product_id  INT,
    product_name NVARCHAR(200),
    total_sold  MONEY
);

INSERT INTO #temp_results
SELECT p.product_id, p.name, SUM(oi.line_total)
FROM sales.products p
JOIN sales.order_items oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name;

SELECT * FROM #temp_results ORDER BY total_sold DESC;

DROP TABLE #temp_results;

CASE Expressions

Both simple and searched CASE syntax work:

-- Searched CASE
SELECT
    order_id,
    CASE
        WHEN total_amount > 1000 THEN 'high'
        WHEN total_amount > 100  THEN 'medium'
        ELSE 'low'
    END AS order_tier
FROM sales.orders;

-- Simple CASE
SELECT
    order_id,
    CASE status
        WHEN 'completed' THEN 'done'
        WHEN 'pending'   THEN 'waiting'
        ELSE 'other'
    END AS status_label
FROM sales.orders;

String Functions

UPPER(), LOWER(), LEN(), SUBSTRING(), CHARINDEX(), REPLACE(), and CONCAT() all work:

SELECT
    UPPER(name)                          AS name_upper,
    LEN(name)                            AS name_length,
    SUBSTRING(name, 1, 10)               AS name_short,
    CHARINDEX('a', name)                 AS first_a,
    REPLACE(name, ' ', '_')              AS name_underscored,
    CONCAT(name, ' (SKU: ', sku, ')')    AS display_label
FROM sales.products;

Date Functions

YEAR(), MONTH(), DATEDIFF(), and DATEADD() work:

SELECT
    order_id,
    YEAR(order_date)                              AS order_year,
    MONTH(order_date)                             AS order_month,
    DATEDIFF(day, order_date, GETDATE())          AS days_ago,
    DATEADD(day, 30, order_date)                  AS due_date
FROM sales.orders;

Indexes

Single-column indexes, composite indexes, and descending indexes all work. The sys.indexes view is populated correctly:

CREATE INDEX ix_products_category ON sales.products (category);
CREATE INDEX ix_products_active_price ON sales.products (is_active, unit_price DESC);

SELECT name, type_desc FROM sys.indexes
WHERE object_id = OBJECT_ID('sales.products');

Views

CREATE VIEW sales.order_summary AS
SELECT
    o.order_id,
    o.customer_name,
    o.order_date,
    COUNT(oi.item_id)    AS item_count,
    SUM(oi.line_total)   AS calculated_total
FROM sales.orders o
JOIN sales.order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.customer_name, o.order_date;

Triggers

AFTER UPDATE triggers work and fire correctly:

CREATE TRIGGER sales.trg_products_updated
ON sales.products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE sales.products
    SET updated_at = GETDATE()
    WHERE product_id IN (SELECT product_id FROM inserted);
END;

Verified by running an UPDATE and checking that updated_at was changed by the trigger.

INFORMATION_SCHEMA

INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES both return correct results:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'sales'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

What Does Not Work

CREATE OR ALTER PROCEDURE

Babelfish does not support the CREATE OR ALTER PROCEDURE syntax:

-- This fails with a syntax error
CREATE OR ALTER PROCEDURE sales.my_proc AS BEGIN ... END;

The workaround is straightforward:

DROP PROCEDURE IF EXISTS sales.my_proc;
CREATE PROCEDURE sales.my_proc AS BEGIN ... END;

TLS on Port 1433

Babelfish 4.8.0 is compiled without OpenSSL, so encrypted connections via TDS are not available. The PostgreSQL port (5432) does support TLS. If your application enforces Encrypt=true in its connection string, you will need to either disable that setting or wait for a future Babelfish release with TLS support.

Summary

Feature	Status
DDL (CREATE TABLE, SCHEMA, INDEX, VIEW)	Supported
All common SQL Server data types	Supported
IDENTITY columns	Supported
Computed columns	Supported
SELECT, INSERT, UPDATE, DELETE	Supported
CTEs (simple and recursive)	Supported
Window functions (RANK, ROW_NUMBER, LAG, LEAD)	Supported
Stored procedures with OUTPUT params	Supported
TRY/CATCH with ERROR_NUMBER/ERROR_MESSAGE	Supported
BEGIN/COMMIT/ROLLBACK transactions	Supported
Temporary tables (#temp)	Supported
CASE expressions	Supported
String functions	Supported
Date functions (DATEDIFF, DATEADD, GETDATE)	Supported
Triggers (AFTER UPDATE)	Supported
INFORMATION_SCHEMA views	Supported
sys.indexes, sys.databases	Supported
CREATE OR ALTER PROCEDURE	Not supported
TLS on TDS port (1433)	Not supported

The coverage is substantial. For greenfield projects or migrations where your schema stays within these boundaries, Babelfish is a credible option for running SQL Server workloads on PostgreSQL.

What's Next

• Provision Babelfish on FoundryDB and connect in under 10 minutes
• Check the Babelfish interoperability documentation for the full feature matrix

---

Questions or edge cases you want tested? Join the FoundryDB community or open a support ticket.