Use supported T-SQL forms

← SQL Server wire examples

Connection requirementsCopy Anchor Link

This recipe needs an existing listener using shared credentials. A per-user TDS session is read-only. Database RBAC grants or row-level policies require that per-user model, so the writes below do not apply there.

Check credential requirements. Use the HTTP API for authorized writes when this listener is read-only. Current PostgreSQL database logins are also read-only.

The listener translates selected T-SQL query syntax into engine SQL. Check each form before adapting an existing query.

Before you run it: This is query compatibility, not a procedural T-SQL runtime. Writes require the listener’s shared-credential mode.

Each pair below shows the SQL you send and its translated equivalent. Run one version, not both as a script. The invoice queries can use the invoice sample; the [order] example illustrates quoting and needs a table with those exact names.

Follow the exampleCopy Anchor Link

1. Choose a translated form below.
2. Keep its type and NULL behavior.
3. Run it through an existing listener.
4. Compare rows with the expected result.

Quote identifiersCopy Anchor Link

-- 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.

Read the next pageCopy Anchor Link

-- 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 translated before execution. PERCENT and WITH TIES are refused because this path has no equivalent behavior.

Use supported functionsCopy Anchor Link

-- 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.

Combine stringsCopy Anchor Link

-- 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.

Choose supported column typesCopy Anchor Link

-- 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.

Microsoft, SQL Server and T-SQL are trademarks of the Microsoft group of companies. Named here only to describe dialect compatibility. OriginChainDB is not affiliated with, endorsed by, or sponsored by Microsoft, and does not distribute Microsoft software.

Expected resultCopy Anchor Link

Supported forms cover selected quoting, pagination, functions and types.

ContinueCopy Anchor Link

Continue to recognize unsupported t-sql.