Where T-SQL parts ways with standard SQL

If you already know SQL, most of T-SQL reads the same. The differences are small, and they are exactly where people coming from PostgreSQL or MySQL write a query that runs and returns the wrong thing.

TOP, WITH TIES and OFFSET/FETCH

SQL Server has no LIMIT. It has TOP (n), which takes the first n rows in the order of the query's ORDER BY. Without an ORDER BY, "the first three" means whichever three the engine happened to produce, and that can change between runs.

When rows tie on the sort key, TOP (3) still stops at three and picks among the tied rows arbitrarily. TOP (3) WITH TIES keeps going while the next row ties with the third on every ORDER BY column.

For paging, OFFSET ... FETCH is the standard form, and it is only allowed after an ORDER BY:

SELECT id, name, price
FROM products
ORDER BY price DESC, id          -- unique, so page 2 is well defined
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

IDENTITY never hands numbers back

INT IDENTITY(1,1) generates the key on insert. Read the value you just generated with SCOPE_IDENTITY() (this scope only) or, better, the OUTPUT clause, which also works for multi-row inserts. @@IDENTITY returns the last identity generated in the session in any scope, so a trigger that inserts into an audit table changes what it returns.

An identity value is consumed even when the insert is rolled back, so gaps are normal. Never use IDENTITY where the business needs an unbroken sequence, such as invoice numbers in some jurisdictions.

NVARCHAR, the N prefix and collation

VARCHAR stores characters in the code page of the column's collation (or as UTF-8, if the column uses one of the _UTF8 collations added in SQL Server 2019); NVARCHAR stores UTF-16, two bytes per character for most scripts. A literal written as 'text' is VARCHAR in the database's code page, so it is converted before it reaches your column; N'text' is NVARCHAR. Characters the code page cannot represent become question marks, silently.

Comparisons follow the collation. The one used here, SQL_Latin1_General_CP1_CI_AS, is case-insensitive (CI), so 'abc' = 'ABC' is true. LEN ignores trailing spaces; DATALENGTH counts bytes.

Common mistakes

• TOP without ORDER BY. It returns some rows, not the first ones, and a different plan can return different ones tomorrow.
• Paging on a non-unique sort key. With ORDER BY price alone, rows with the same price can appear on two pages or on none; add a unique tie-breaker such as id.
• Expecting identity values to be contiguous. The rolled-back insert above burnt id 2 for good.
• Writing Unicode text without N. The first column of the last example shows what the database stored: four question marks.

What interviewers ask

• SCOPE_IDENTITY(), @@IDENTITY or OUTPUT? @@IDENTITY crosses scopes, so a trigger can change it; SCOPE_IDENTITY() is safe for one row; OUTPUT inserted.id is safe for many.
• What does WITH TIES do, and what does it need? It returns extra rows that tie with the last one on the whole ORDER BY list, so it needs an ORDER BY.