SQL Server (T-SQL) cheat sheet

-- Auto-increment.
id INT IDENTITY(1, 1) PRIMARY KEY               -- (seed, increment)
id BIGINT IDENTITY(1, 1) PRIMARY KEY            -- safer for growth

-- Get last insert ID.
SELECT SCOPE_IDENTITY();                         -- recommended (current scope)
SELECT @@IDENTITY;                               -- current session, any scope (avoid)
SELECT IDENT_CURRENT('table_name');              -- last identity for a specific table

-- Common types.
TINYINT (0-255), SMALLINT, INT, BIGINT
DECIMAL(p, s), NUMERIC(p, s)                     -- synonyms
MONEY, SMALLMONEY                                -- 4-decimal-place fixed-point (limited)
DATE, TIME, DATETIME2, DATETIMEOFFSET, SMALLDATETIME
DATETIME                                         -- legacy: 3.33ms precision, 1753-9999 only
VARCHAR(n), VARCHAR(MAX)                         -- 8000 bytes / 2 GB
NVARCHAR(n), NVARCHAR(MAX)                       -- Unicode (always prefer for new code)
UNIQUEIDENTIFIER                                 -- GUID; default via NEWID() or NEWSEQUENTIALID()
BIT                                              -- boolean (0 / 1 / NULL)

-- Old-school: TOP.
SELECT TOP 20 * FROM users ORDER BY created_at DESC;
SELECT TOP 20 PERCENT * FROM users;

-- ANSI: OFFSET ... FETCH (2012+).
SELECT * FROM users
ORDER BY created_at DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- TOP with WITH TIES (rare but useful).
SELECT TOP 10 WITH TIES *
FROM   user_revenue
ORDER  BY revenue DESC;

-- + is the legacy concat operator. Watch type-conversion errors.
SELECT first_name + ' ' + last_name AS full_name FROM users;
-- This errors if any operand is numeric without explicit CAST.

-- CONCAT (2012+) — null-tolerant (treats NULL as empty string).
SELECT CONCAT(first_name, ' ', last_name) FROM users;

-- CONCAT_WS (2017+) — separator, skips NULLs.
SELECT CONCAT_WS(', ', city, state, zip) FROM addresses;

-- STRING_AGG (2017+).
SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) FROM tags;

GETDATE()                                       -- server-local
SYSDATETIME()                                   -- higher precision, server-local
GETUTCDATE()                                    -- UTC
SYSDATETIMEOFFSET()                             -- with timezone offset

-- Parts.
YEAR(d), MONTH(d), DAY(d)
DATEPART(year, d), DATEPART(quarter, d), DATEPART(weekday, d)
DATENAME(month, d)                              -- 'April'

-- Math.
DATEADD(DAY, 7, d)
DATEDIFF(DAY, start, end)                       -- (unit, start, end) — boundary-counting!
EOMONTH(d)                                      -- last day of month
DATEFROMPARTS(2026, 4, 26)

-- Truncate (2022+).
DATETRUNC(MONTH, d)

-- Pre-2022 truncate workaround.
DATEFROMPARTS(YEAR(d), MONTH(d), 1)             -- first of month

-- Format / parse.
FORMAT(d, 'yyyy-MM-dd')                         -- .NET-style format strings
CONVERT(VARCHAR, d, 120)                        -- '2026-04-26 14:30:00'
CONVERT(DATE, '26/04/2026', 103)                -- 103 = "dd/mm/yyyy" style code
TRY_CONVERT(DATE, 'not-a-date')                 -- NULL on failure

-- ANSI cast.
CAST(value AS INT)
CAST('2026-04-26' AS DATE)

-- T-SQL CONVERT (with optional style code for dates).
CONVERT(VARCHAR(20), GETDATE(), 120)            -- '2026-04-26 14:30:00'
CONVERT(VARCHAR, GETDATE(), 103)                -- '26/04/2026'

-- Safe variants — return NULL on failure.
TRY_CAST('not-a-number' AS INT)                 -- NULL
TRY_CONVERT(DATE, 'invalid')                    -- NULL
TRY_PARSE('26/04/2026' AS DATE USING 'en-GB')   -- locale-aware

-- MERGE (full feature set).
MERGE INTO target AS t
USING source AS s
   ON t.id = s.id
WHEN MATCHED AND s.is_deleted = 1 THEN DELETE
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

-- OUTPUT — return rows from a DML statement.
INSERT INTO users (email)
OUTPUT INSERTED.id, INSERTED.created_at
VALUES ('a@x.com');

UPDATE users
SET    plan = 'pro'
OUTPUT DELETED.plan AS old_plan, INSERTED.plan AS new_plan
WHERE  id = 1;

DELETE FROM users
OUTPUT DELETED.email
WHERE  id = 1;

JSON_VALUE(payload, '$.user_id')                 -- scalar
JSON_QUERY(payload, '$.user')                    -- nested object/array
ISJSON(string)                                   -- validate

-- Modify.
JSON_MODIFY(payload, '$.user.plan', 'pro')

-- Parse to rowset (very useful).
SELECT *
FROM   OPENJSON(@json_doc)
WITH   (id INT, email NVARCHAR(200), plan NVARCHAR(20));

-- Build (2022+).
SELECT JSON_OBJECT('id': id, 'email': email) FROM users;
SELECT JSON_ARRAY(name, email) FROM users;

-- Tables and columns.
SELECT * FROM sys.tables;
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('orders');
sp_help 'orders';                                 -- columns + constraints + indexes

-- Foreign keys.
SELECT
  OBJECT_NAME(parent_object_id)     AS child_table,
  OBJECT_NAME(referenced_object_id) AS parent_table
FROM   sys.foreign_keys;

-- Active queries.
SELECT session_id, status, start_time, command, text
FROM   sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle);

-- Most expensive queries.
SELECT TOP 20
  total_worker_time / execution_count AS avg_cpu_time,
  execution_count,
  query_text = SUBSTRING(t.text, qs.statement_start_offset / 2,
                         (qs.statement_end_offset - qs.statement_start_offset) / 2)
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY avg_cpu_time DESC;

BEGIN TRANSACTION;
  -- ...DML...
COMMIT;
-- ROLLBACK;

-- Read uncommitted (dirty reads).
SELECT * FROM orders WITH (NOLOCK);                  -- per-query
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;     -- per-session

-- Other table hints (use deliberately).
WITH (READPAST)                                       -- skip locked rows
WITH (UPDLOCK, HOLDLOCK)                              -- "select for update"-ish

-- Find blocking.
SELECT blocking_session_id, session_id, wait_type, wait_time, command
FROM   sys.dm_exec_requests
WHERE  blocking_session_id <> 0;