T-SQL basics for app developers from other databases

If you know SQL from PostgreSQL or MySQL, you already know most of T-SQL. SELECT, joins, GROUP BY, window functions and subqueries work the way you expect. What trips developers up is a short list of differences: there is no LIMIT (use TOP or OFFSET ... FETCH), no RETURNING (use OUTPUT), no boolean (use bit), no ON CONFLICT (use MERGE carefully, or an update-then-insert), + concatenates strings and turns the whole result NULL if any part is, and readers can block writers under the default isolation level. This guide goes through those differences with working examples for SQL Server 2022, so that the first week on SQL Server is spent on your application rather than on error messages.

Limiting and paging results#

There is no LIMIT. The two replacements are TOP and OFFSET ... FETCH:

-- The ten newest ordersSELECT TOP (10) OrderId, CustomerId, CreatedAtFROM dbo.OrdersORDER BY CreatedAt DESC;-- Page 3 at 20 rows per pageSELECT OrderId, CustomerId, CreatedAtFROM dbo.OrdersORDER BY CreatedAt DESC, OrderId DESCOFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

TOP accepts a variable or parameter in its parentheses, and TOP (10) WITH TIES includes any further rows that tie with the tenth on the ORDER BY columns. TOP without ORDER BY is legal and returns whichever rows are quickest to find, which is rarely what you meant.

OFFSET ... FETCH requires an ORDER BY, and the order should be unique - add the primary key as a tie-breaker, as above, or rows with the same timestamp can appear on two pages or none. Large offsets are as expensive here as in any database, because SQL Server reads and discards every skipped row. For deep paging, use keyset pagination: remember the last row's sort values and ask for what comes after them:

SELECT TOP (20) OrderId, CustomerId, CreatedAtFROM dbo.OrdersWHERE CreatedAt < @lastCreatedAt   OR (CreatedAt = @lastCreatedAt AND OrderId < @lastOrderId)ORDER BY CreatedAt DESC, OrderId DESC;

With an index on (CreatedAt, OrderId), every page costs the same however deep it is. SQL Server indexes and execution plans explains why the index order matters.

Identity columns and getting the new id back#

The equivalent of AUTO_INCREMENT and SERIAL is an IDENTITY property:

CREATE TABLE dbo.Customers (    CustomerId  int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customers PRIMARY KEY,    Email       nvarchar(320) NOT NULL CONSTRAINT UQ_Customers_Email UNIQUE,    IsActive    bit NOT NULL CONSTRAINT DF_Customers_IsActive DEFAULT (1),    CreatedAt   datetime2(3) NOT NULL CONSTRAINT DF_Customers_CreatedAt DEFAULT (SYSUTCDATETIME()));

Instead of RETURNING, T-SQL has OUTPUT, which returns columns from the inserted, updated or deleted rows:

INSERT INTO dbo.Customers (Email)OUTPUT INSERTED.CustomerId, INSERTED.CreatedAtVALUES (N'ana@example.com');

It works on UPDATE (with both DELETED for old values and INSERTED for new ones), DELETE and MERGE too. One restriction: if the table has an enabled trigger, OUTPUT must write INTO a table variable rather than return rows directly.

The older way to get the id is SCOPE_IDENTITY(), which returns the last identity value generated in the current scope. Avoid @@IDENTITY, which returns the last value generated anywhere in the session - including inside a trigger that inserted into an audit table - and IDENT_CURRENT('dbo.Customers'), which returns the last value for the table across all sessions and is wrong under concurrency.

Identity values have gaps, and that is normal. A rolled-back insert consumes a value, and SQL Server caches identity values in blocks, so an unexpected restart can make the sequence jump - by up to 1,000 for an int. If gaps genuinely matter (they almost never should; invoice numbers are the usual case), generate those numbers yourself inside a transaction rather than relying on identity. To insert explicit values into an identity column, for a data migration, wrap the insert in SET IDENTITY_INSERT dbo.Customers ON; and OFF.

For values shared across tables, or needed before the insert, a SEQUENCE object works like PostgreSQL's: CREATE SEQUENCE dbo.OrderNumbers START WITH 1000; then NEXT VALUE FOR dbo.OrderNumbers.

Upserts: MERGE and the safer alternative#

T-SQL has no ON CONFLICT or ON DUPLICATE KEY UPDATE. It has MERGE:

MERGE dbo.PageHits WITH (HOLDLOCK) AS targetUSING (SELECT @pageId AS PageId) AS source    ON target.PageId = source.PageIdWHEN MATCHED THEN    UPDATE SET Hits = target.Hits + 1WHEN NOT MATCHED THEN    INSERT (PageId, Hits) VALUES (source.PageId, 1);

Two things about MERGE are not optional. It must end with a semicolon, or you get a syntax error. And without HOLDLOCK (serialisable locking on the target), two sessions can both see "not matched" for the same key and both try to insert, so one fails with a duplicate key error under load. MERGE has also had a long list of bugs over the years, mostly in combination with triggers, filtered indexes and indexed views. Many experienced SQL Server developers avoid it for single-row upserts and write the explicit form instead:

SET XACT_ABORT ON;BEGIN TRANSACTION;UPDATE dbo.PageHits WITH (UPDLOCK, SERIALIZABLE)SET Hits = Hits + 1WHERE PageId = @pageId;IF @@ROWCOUNT = 0    INSERT dbo.PageHits (PageId, Hits) VALUES (@pageId, 1);COMMIT TRANSACTION;

The UPDLOCK, SERIALIZABLE hints lock the key range even when the row does not exist yet, so a second session waits instead of racing. For bulk upserts of many rows from a staging table, MERGE is reasonable and much shorter; test it, and keep the HOLDLOCK.

Transactions and error handling#

Every statement runs in its own transaction unless you open one, as in other engines. The part that differs is error handling. By default, many runtime errors in T-SQL abort only the current statement, not the transaction: the batch carries on, and a later COMMIT commits half the work. SET XACT_ABORT ON makes any runtime error roll back the whole transaction, and it should be the first line of every procedure or script that opens one:

CREATE OR ALTER PROCEDURE dbo.TransferCredit    @fromId int, @toId int, @amount decimal(12,2)ASBEGIN    SET NOCOUNT ON;    SET XACT_ABORT ON;    BEGIN TRY        BEGIN TRANSACTION;        UPDATE dbo.Accounts SET Balance = Balance - @amount WHERE AccountId = @fromId;        UPDATE dbo.Accounts SET Balance = Balance + @amount WHERE AccountId = @toId;        COMMIT TRANSACTION;    END TRY    BEGIN CATCH        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;        THROW;    END CATCH;END;

THROW with no arguments re-raises the original error with its number and message, so the application sees what actually went wrong. SET NOCOUNT ON suppresses the "rows affected" messages, which some drivers otherwise mistake for result sets.

Isolation is the other surprise for PostgreSQL developers. SQL Server's default READ COMMITTED level uses locks, so a long-running update blocks readers of the same rows until it commits. Turning on read committed snapshot makes readers see the last committed version instead, which is how PostgreSQL behaves:

ALTER DATABASE [appdb] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

It keeps row versions in tempdb, which costs a little I/O and space, and for most web applications it removes a whole class of blocking. Do not reach for WITH (NOLOCK) instead: it reads uncommitted data and can return rows twice or skip them during page splits.

Types that work differently#

Prefer datetime2 over the older datetime, which rounds to increments of about 3 milliseconds and has a smaller range. Avoid money, which has surprising rounding in division; use decimal. And note that rowversion, also spelt timestamp, is an automatically changing binary value for optimistic concurrency - useful, but nothing to do with time.

For text, nvarchar stores Unicode and varchar does not unless the column has a UTF-8 collation, and string literals need an N prefix to stay Unicode. SQL Server collations and Unicode covers this properly, because it is the most common source of corrupted text.

Strings, NULLs and dates#

String concatenation uses +, and NULL + 'anything' is NULL. CONCAT treats NULL as an empty string, and CONCAT_WS adds a separator and skips NULLs:

SELECT FirstName + ' ' + LastName              AS may_be_null,       CONCAT(FirstName, ' ', LastName)         AS never_null,       CONCAT_WS(', ', Street, City, Postcode)  AS addressFROM dbo.Customers;

The equivalents of common functions from other engines:

Several of those arrived only in SQL Server 2022, and some, such as GENERATE_SERIES, require the database to be at compatibility level 160. A database restored from an older server keeps its old level until you raise it; migrating a database to SQL Server hosting covers when to do that.

ISNULL and COALESCE differ in a way that bites: ISNULL returns the type of its first argument, so ISNULL(@shortVarchar, 'a much longer default') truncates the default. COALESCE follows normal type precedence. And DATEDIFF counts boundaries crossed, not elapsed time: DATEDIFF(year, '2025-12-31', '2026-01-01') is 1.

GETDATE() returns the server's local time as datetime. On a server you do not control, that may not be your time zone. Store UTC with SYSUTCDATETIME() and convert for display, with AT TIME ZONE if you must do it in SQL.

JSON, CTEs and set-based thinking#

SQL Server 2022 has no native JSON column type; JSON lives in nvarchar(max) and is handled with functions. That is less limiting than it sounds. A check constraint keeps invalid documents out, JSON_VALUE reads a scalar, JSON_QUERY reads an object or array, and OPENJSON turns a document into rows you can join:

CREATE TABLE dbo.Events (    EventId  bigint IDENTITY PRIMARY KEY,    Payload  nvarchar(max) NOT NULL CONSTRAINT CK_Events_Json CHECK (ISJSON(Payload) = 1),    UserId   AS CAST(JSON_VALUE(Payload, '$.userId') AS int)   -- computed column);CREATE INDEX IX_Events_UserId ON dbo.Events (UserId);SELECT e.EventId, t.[value] AS TagFROM dbo.Events AS eCROSS APPLY OPENJSON(e.Payload, '$.tags') AS tWHERE e.UserId = 42;

The computed column is the trick that makes JSON queries fast: SQL Server cannot index inside a document directly, but it can index a computed column that extracts a value, and the optimiser uses that index for queries filtering on the same expression. Going the other way, FOR JSON PATH at the end of a SELECT returns the result as a JSON document, with dotted column aliases such as [customer.email] producing nested objects. SQL Server 2022 also added JSON_OBJECT and JSON_ARRAY for building documents inline, and JSON_PATH_EXISTS for testing whether a path is present.

Common table expressions work as in other engines, with two details. The statement before WITH must end in a semicolon, which is why you see T-SQL written as ;WITH. And recursive CTEs - written without a RECURSIVE keyword, simply by referring to themselves - stop with an error after 100 levels by default; add OPTION (MAXRECURSION 1000) to the outer query to go deeper, or 0 for no limit if you are sure the recursion ends.

The broader habit worth bringing to T-SQL is set-based thinking. Procedural code with cursors and WHILE loops processing one row at a time is the classic SQL Server performance problem, because each iteration is a separate statement with its own overhead and locking. An UPDATE with a join, or an INSERT ... SELECT, does the same work in one pass. Loops are the right tool in exactly one common case: deliberately splitting a huge change into batches so it does not hold locks or fill the transaction log, as SQL Server recovery models and log growth describes.

Identifiers, batches and DDL habits#

Identifiers that clash with keywords or contain spaces go in square brackets: [Order], [User Name]. Double quotes also work when QUOTED_IDENTIFIER is on, which it is for every modern driver; backticks do not work at all. Objects live in schemas, dbo by default, and it is good practice to always write the schema - dbo.Orders - which avoids a name-resolution step and plan cache surprises.

Scripts are split into batches by GO, which is a client-side separator in sqlcmd and SSMS, not T-SQL. CREATE PROCEDURE, CREATE VIEW and CREATE FUNCTION must be the first statement in a batch, and local variables do not survive a GO. sqlcmd and bcp covers running such scripts from a pipeline.

Semicolons are optional on most statements but required in a few places: MERGE must end with one, and the statement before a WITH common table expression or a THROW must be terminated. Ending every statement with a semicolon avoids the whole question.

For repeatable DDL:

CREATE OR ALTER VIEW dbo.ActiveCustomers ASSELECT CustomerId, Email FROM dbo.Customers WHERE IsActive = 1;GODROP TABLE IF EXISTS dbo.ImportStaging;IF OBJECT_ID(N'dbo.AuditLog', N'U') IS NULL    CREATE TABLE dbo.AuditLog (AuditId bigint IDENTITY PRIMARY KEY, Message nvarchar(4000));

CREATE OR ALTER works for views, procedures, functions and triggers but not tables, and there is no CREATE TABLE IF NOT EXISTS, hence the OBJECT_ID check. Adding a column is ALTER TABLE dbo.Orders ADD Notes nvarchar(500) NULL;, with no COLUMN keyword. Temporary tables start with # and vanish when the session ends; ## makes a global one visible to all sessions, which is rarely what you want.

Parameters and dynamic SQL#

Everything above assumes parameters, and the rule is the same as everywhere: never build SQL by concatenating user input. When you genuinely need dynamic SQL inside T-SQL - a sort column chosen at run time, say - use sp_executesql with parameters for values and QUOTENAME for identifiers:

DECLARE @sql nvarchar(max) =    N'SELECT TOP (@n) OrderId, CreatedAt FROM dbo.Orders ORDER BY '    + QUOTENAME(@sortColumn) + N' DESC;';EXEC sp_executesql @sql, N'@n int', @n = @pageSize;

QUOTENAME wraps the name in brackets and escapes any closing bracket inside it, so a malicious column name cannot break out. Validate it against a list of allowed columns as well. Parameterised sp_executesql calls also let SQL Server reuse one plan for every value, which concatenated SQL does not. SQL Server logins, users and roles covers limiting what the application's login can do if something does slip through.

FAQ#

How do I limit rows in SQL Server without LIMIT?

Use SELECT TOP (n) ... ORDER BY ... for the first n rows, or ORDER BY ... OFFSET x ROWS FETCH NEXT n ROWS ONLY for paging. OFFSET requires an ORDER BY, which should include a unique column so pages are stable.

What is the SQL Server equivalent of RETURNING?

The OUTPUT clause. INSERT ... OUTPUT INSERTED.Id VALUES (...) returns the new identity, and it works on UPDATE, DELETE and MERGE too, with DELETED giving the old values. On a table with triggers, output into a table variable.

Does SQL Server have a boolean type?

No. Use bit, which stores 0, 1 or NULL, and compare with = 1 or = 0. Most ORMs and drivers map their boolean type to bit automatically.

Why do my reads block while another session updates?

The default isolation level uses shared locks, which wait for writers to commit. Turn on READ_COMMITTED_SNAPSHOT for the database so readers see the last committed version instead. It is what most developers coming from PostgreSQL expect.

Is MERGE safe to use for upserts?

It works, but it needs HOLDLOCK to be safe under concurrency, a terminating semicolon, and testing if triggers or filtered indexes are involved. For single-row upserts, an UPDATE with UPDLOCK, SERIALIZABLE followed by a conditional INSERT is simpler and predictable.