Where T-SQL Differs From the ANSI Standard

A query that runs on SQL Server can fail on another database without changing a table. T-SQL differs from the ANSI standard in small choices that matter during migration.

Where T-SQL Differs From the ANSI Standard, Portability Begins With a Test

SQL is a standard, but products implement different dialects and optional features. SQL Server adds T-SQL syntax and behavior that can make a query concise on one engine. A migration exposes those choices. The goal is not to avoid every extension. It is to know which parts need translation.

I begin by listing the queries that cross a boundary: application SQL, reporting expressions, ETL transformations, and generated statements. A stored procedure that never leaves SQL Server can use T-SQL features deliberately. A shared query library needs more restraint.

What is the target database and version? “ANSI compatible” is too vague for a deployment plan. Test the exact query against the exact target.

TOP and FETCH: Where T-SQL Differs From the ANSI Standard

TOP is a familiar T-SQL way to limit rows. It appears near SELECT. Standard style pagination uses ORDER BY with OFFSET and FETCH. SQL Server supports that form too. The syntax is not identical across every product, so check the target’s rules.

A row limit without a deterministic ORDER BY does not define which rows you get. Do not use TOP as a shortcut for a stable first page unless the order is explicit. The same applies to OFFSET and FETCH. Ties need a second sort key when page consistency matters.

I show both forms with catalog rows because the script runs without a sample database. The numbers are request limits, not observed row counts.

SELECT TOP (10)
    name, object_id
FROM sys.objects
ORDER BY name, object_id;

SELECT name, object_id
FROM sys.objects
ORDER BY name, object_id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

String Concatenation Needs Attention

Here T-SQL differs from the ANSI standard: it has long used the plus operator to join strings. Other database systems use different operators. SQL Server also offers CONCAT. Do not assume a plus expression will move unchanged to a new engine. Check the target language and data type conversion rules.

NULL changes the result. On supported recent SQL Server releases, a plus concatenation with NULL yields NULL. CONCAT treats NULL as an empty string. Those are different outputs. Choose the behavior the application actually needs and write it explicitly.

The query below demonstrates the difference without claiming a performance result. It also reminds you to test character types and lengths. A string that compiles can still truncate when its declared type is too short.

SELECT
    N'Order ' + CAST(NULL AS nvarchar(10)) AS PlusResult,
    CONCAT(N'Order ', CAST(NULL AS nvarchar(10))) AS ConcatResult,
    CONCAT(N'Order ', 42) AS ConvertedResult;

Test NULL With IS NULL

NULL means an unknown or missing value in SQL’s three valued logic. An equality comparison to NULL is not a reliable way to find missing rows. Use IS NULL and IS NOT NULL. This is basic SQL, but old code can still contain a setting dependent shortcut.

COALESCE can express a fallback value, though data type rules differ from SQL Server’s ISNULL function. Do not swap them in a migration without checking type, length, and evaluation behavior. A portable looking function call can still change the result.

I search for NULL comparisons and fallback functions early in a migration. They are small enough to miss in code review and large enough to change a report total.

SELECT
    CASE WHEN CAST(NULL AS int) IS NULL
         THEN N'Missing'
         ELSE N'Present'
    END AS NullCheck,
    COALESCE(CAST(NULL AS nvarchar(20)), N'Unknown') AS FallbackText;

Watch Identifiers and Built-Ins Where T-SQL Differs From the ANSI Standard

SQL Server commonly uses square brackets to delimit identifiers. Other products use different quoting rules. If your tables have spaces or reserved words in their names, portability gets harder. Use simple identifiers in new schemas when you have the choice.

Date, string, and error handling functions differ by product. GETDATE, DATEADD, TRY_CONVERT, and @@ROWCOUNT are useful T-SQL features, but a migration must translate them. Check both output and edge cases, not only whether the new query compiles.

I keep a list of vendor specific functions found in application SQL. It gives developers a concrete work queue. The list is more useful than a statement that “we mostly use standard SQL.”

Check ORDER BY and Pagination

A page query needs a stable order. If many rows share the same sort value, add a unique tie breaker. Otherwise rows can move between pages even when the syntax is accepted by both engines. The issue is logical, not just dialect specific.

Different products can handle offsets, limits, and query plans differently. Test large offsets with your real workload if the application relies on them. Keyset pagination can be a better design for some paths. That is a workload decision, not a universal rule.

For a migration, compare expected row identities from the source and target. A matching count with different rows is not a passing test.

Write a Portable Core Where Useful

Use explicit column lists, schema qualification where supported, clear NULL tests, and deterministic ordering. Keep product specific features behind a small boundary in application code when the same logic must run on multiple engines. Parameterize values rather than building text from user input.

Do not trade correctness or maintainability for a theoretical future move. SQL Server features can be the right tool for a SQL Server only system. Just document the choice so a future migration can estimate it honestly.

I ask the team to test both sides with the same input and compare results, errors, and measured behavior. A syntax translation is only the first pass.

Close the Gap With Real Data

Build a test set with NULLs, empty strings, ties in sort keys, Unicode text, and boundary dates. Those cases expose differences quickly. Keep expected results in a reproducible test. Do not invent performance figures from a tiny sample.

Run the critical queries on the target database version before committing to a migration timeline. Record which statements need rewrites and which can stay. Review client driver behavior too, since type conversion can occur outside SQL text.

Portability is a property you verify. The standard is a guide, and the target engine is the final test.

Related reading on this blog: How to check the ANSI Compatibility of SQL Server Queries? Interview Question of the Week #221 and NULL Values and CONCAT Function.

Portable SQL is not syntax that looks familiar, it is behavior you tested on the target engine.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.