Applies when writing or reviewing MS SQL Server T-SQL. Covers NVARCHAR, columnstore indexes, TRY/CATCH, CROSS APPLY, temporal tables, and parameter sniffing.
NVARCHAR for user-facing text. VARCHAR is single-byte — cannot store emoji, CJK, or many international characters. Use VARCHAR only for known-ASCII data.UNIQUEIDENTIFIER for UUIDs. NEWSEQUENTIALID() for clustered PKs (ordered, less fragmentation). NEWID() for random.DATETIME2 over DATETIME. Higher precision, wider range. DATETIMEOFFSET for timezone-aware timestamps.DECIMAL(p,s) for money, never FLOAT or REAL.IDENTITY for auto-increment PKs. SEQUENCE for cross-table sharing or pre-allocation.OUTPUT clause instead of separate SELECT after mutations:
INSERT INTO orders (customer_id, total)
OUTPUT inserted.id, inserted.created_at
VALUES (42, 99.99);
MERGE with HOLDLOCK for upserts (MERGE has had race-condition bugs without it):
MERGE INTO inventory WITH (HOLDLOCK) AS tgt
USING (VALUES (101, 5)) AS src (product_id, qty)
ON tgt.product_id = src.product_id
WHEN MATCHED THEN UPDATE SET tgt.qty = tgt.qty + src.qty
WHEN NOT MATCHED THEN INSERT (product_id, qty) VALUES (src.product_id, src.qty);
CREATE INDEX ix_pending ON orders (created_at) WHERE status = 'pending'.INCLUDE columns for covering non-clustered indexes without bloating the B-tree key:
CREATE INDEX ix_orders_customer ON orders (customer_id) INCLUDE (order_date, total);
TRY...CATCH with SET XACT_ABORT ON:
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- DML
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH;
THROW over RAISERROR (2012+). Re-raises the original error correctly.@@TRANCOUNT before ROLLBACK in CATCH blocks.CROSS APPLY / OUTER APPLY instead of correlated subqueries:
SELECT c.name, latest.order_date
FROM customer c
OUTER APPLY (
SELECT TOP 1 order_date FROM orders o
WHERE o.customer_id = c.id ORDER BY order_date DESC
) latest;
FOR SYSTEM_TIME AS OF.OPTION (RECOMPILE) for infrequent queries, OPTION (OPTIMIZE FOR UNKNOWN), or Query Store plan forcing.WHERE nvarchar_col = @varchar_param forces per-row conversion. Match parameter types to column types.NOLOCK is almost never correct. Reads dirty data, can skip or double-read rows. Use Read Committed Snapshot Isolation (RCSI) instead.SELECT TOP N without ORDER BY returns arbitrary rows.@@IDENTITY crosses scopes (including triggers). Use SCOPE_IDENTITY() or OUTPUT inserted.id.CROSS APPLY.