4. T-SQL that translates

A batch that arrives over TDS is parsed with a SQL Server grammar and rewritten into OriginChainDB's SQL before it executes. This page shows the rewrite for each construct that has one, so you can tell at a glance whether a query you already have will run.

This is the query language only. The procedural language is not implemented — see what is refused and the T-SQL boundary.

-- you send
SELECT [order].[id], [customer name]
  FROM [order]
 WHERE [status] = 'open';

-- the engine executes
SELECT "order".id, "customer name"
  FROM "order"
 WHERE status = 'open';

A plain, non-reserved name loses its brackets. A name with spaces or punctuation, or one that is a reserved word, becomes a double-quoted identifier instead — so [order] keeps working. If a bracket identifier ever survives the rewrite, the statement is refused rather than passed on under a name the engine would read differently.

-- you send
SELECT TOP 20 id FROM invoices ORDER BY amount_cents DESC;
SELECT id FROM invoices ORDER BY id OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- the engine executes
SELECT id FROM invoices ORDER BY amount_cents DESC LIMIT 20;
SELECT id FROM invoices ORDER BY id LIMIT 20 OFFSET 40;

FETCH is always lowered, never passed through. Left alone the engine would ignore it and return every row — a silently wrong answer, which is exactly what the translator exists to prevent. PERCENT and WITH TIES have no equivalent and are refused.

-- you send
SELECT ISNULL(status, 'open')                  AS status,
       LEN(customer_ref)                       AS ref_len,
       CHARINDEX('-', customer_ref)            AS dash_at,
       IIF(amount_cents > 100000, 'L', 'S')    AS bucket,
       CONVERT(NVARCHAR(32), amount_cents)     AS amount_text,
       GETDATE()                               AS seen_at
  FROM invoices;

-- the engine executes
SELECT COALESCE(status, 'open')                AS status,
       LENGTH(customer_ref)                    AS ref_len,
       POSITION('-' IN customer_ref)           AS dash_at,
       CASE WHEN amount_cents > 100000 THEN 'L' ELSE 'S' END AS bucket,
       CAST(amount_cents AS VARCHAR(32))       AS amount_text,
       now()                                   AS seen_at
  FROM invoices;

SUBSTRING, REPLACE, LTRIM, RTRIM, UPPER and LOWER need no rewrite. Two differences worth knowing before you rely on them:

• LEN counts trailing spaces; T-SQL trims them.
• GETDATE() returns an ISO-8601 UTC string, and every call in one statement returns the same value.

-- translated: both operands are provably string
SELECT 'INV-' + customer_ref            -> CONCAT('INV-', customer_ref)

-- untouched: both operands are numeric, so this stays arithmetic
SELECT amount_cents + tax_cents         -> amount_cents + tax_cents

-- REFUSED: one string, one unknown. In T-SQL the column's declared type
-- decides whether this concatenates or adds, and guessing would be wrong
-- half the time. Write CONCAT(...) or CAST(...) and say which you meant.
SELECT 'INV-' + some_column             -> error

Where it does translate, note that CONCAT treats NULL as empty while T-SQL's + propagates it. If NULL handling matters in that expression, write the CASE you mean.

-- you send
CREATE TABLE [audit log] (
    [id]      UNIQUEIDENTIFIER NOT NULL,
    [note]    NVARCHAR(200),
    [detail]  NTEXT
);

-- the engine executes
CREATE TABLE "audit log" (
    id      UUID NOT NULL,
    note    VARCHAR(200),
    detail  TEXT
);

T-SQL type spellings are normalized in DDL column definitions as well as in casts — but only the ones that change no value representation. The rest are left exactly as you wrote them, on purpose:

• Normalized: NVARCHAR(n), NCHAR, NTEXT and UNIQUEIDENTIFIER.
• Left alone, and therefore refused by the engine: BIT (whose T-SQL literals are 1 and 0, not true and false), MONEY and DATETIME2. Mapping them would silently change what the value means, so you get an error and choose a type yourself.