9. SQL Server¶

Intermediate · 15 min read

Microsoft SQL Server, and its cloud twin Azure SQL, run a huge share of enterprise data: banks, insurers, hospitals, retailers and most companies built on .NET. Enterprise GenAI projects (copilots over company data, text-to-SQL over the ERP, RAG over CRM tickets) very often have to talk to it. Its SQL dialect is called T-SQL (Transact-SQL).

Version and how to run these examples

Written for SQL Server 2022 (features from 2025 are marked). Unlike topics 1–6, these examples aren't run on this site's SQLite: start the free Developer edition with Docker and run them yourself.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Str0ng!Passw0rd" -p 1433:1433 \
    --name mssql -d mcr.microsoft.com/mssql/server:2022-latest
docker exec -it mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'Str0ng!Passw0rd' -C

Or use Azure Data Studio / SSMS (SQL Server Management Studio) as the client.

9.1 Connect from Python¶

# pip install pyodbc        (plus Microsoft's "ODBC Driver 18 for SQL Server")
import pyodbc

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost,1433;DATABASE=shop;"
    "UID=sa;PWD=Str0ng!Passw0rd;TrustServerCertificate=yes"    # local dev only
)
cur = conn.cursor()
cur.execute("SELECT id, name FROM dbo.customers WHERE city = ?", "Pune")   # ? placeholders
rows = cur.fetchall()
conn.commit()      # autocommit is off by default

Need	Use
SQLAlchemy URL	`mssql+pyodbc://sa:pass@localhost:1433/shop?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes`
fast bulk insert	`cur.fast_executemany = True` before `executemany`
Azure SQL without passwords	`Authentication=ActiveDirectoryMsi` (managed identity) in the connection string

Driver 18 encrypts by default

ODBC Driver 18 turns Encrypt=yes on by default, so a local server with a self-signed certificate fails to connect until you add TrustServerCertificate=yes. In production, use a real certificate instead.

9.2 T-SQL basics that look different¶

DECLARE @min_amount DECIMAL(10, 2) = 500;      -- variables start with @

SELECT TOP (5) id, amount                      -- TOP instead of LIMIT
FROM dbo.orders                                -- schema.table: dbo is the default schema
WHERE amount >= @min_amount
ORDER BY amount DESC;

SELECT TOP (3) WITH TIES name, salary          -- also returns rows tied with the 3rd
FROM dbo.employees ORDER BY salary DESC;

SELECT id, amount FROM dbo.orders              -- pagination: ORDER BY is required
ORDER BY id
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;
GO                                             -- batch separator (a client command, not T-SQL)

Need	SQL Server	Standard / PostgreSQL
Quote an identifier	`[order]`	`"order"`
Concatenate	`CONCAT(a, ' ', b)` (NULL → '') · `a + b` (NULL → NULL)	`a \|\| b`
NULL fallback	`ISNULL(x, 0)` · `COALESCE(x, 0)`	`COALESCE`
Today / now (UTC)	`CAST(GETDATE() AS date)` · `SYSUTCDATETIME()`	`CURRENT_DATE` · `now()`
Add / difference	`DATEADD(day, 7, d)` · `DATEDIFF(day, d1, d2)`	`d + 7` · `d2 - d1`
Month bucket	`DATETRUNC(month, d)` (2022) · `DATEFROMPARTS(YEAR(d), MONTH(d), 1)`	`date_trunc('month', d)`
Length	`LEN(s)` (ignores trailing spaces) · `DATALENGTH(s)` (bytes)	`length(s)`
Find in a string	`CHARINDEX('@', email)`	`strpos(email, '@')`
List per group	`STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name)`	`string_agg(name, ', ' ORDER BY name)`
Split a string	`STRING_SPLIT('a,b,c', ',')` (a table)	`string_to_array`
Booleans	no boolean type: `BIT` (0 / 1)	`boolean`
Integer division	`7 / 2` = 3	same in Postgres
Greatest of values	`GREATEST(a, b)` (2022)	`GREATEST(a, b)`

DATEDIFF counts boundaries, not durations

DATEDIFF(year, '2025-12-31', '2026-01-01') returns 1: one year boundary was crossed, although only one day passed. For an accurate age or duration, compute in a smaller unit (DATEDIFF(day, …) / 365.25) or compare full dates.

SQL Server 2022 also added GENERATE_SERIES(1, 10), IS [NOT] DISTINCT FROM, the WINDOW clause, LEAST/GREATEST and APPROX_PERCENTILE_CONT.

9.3 Data types that matter¶

CREATE TABLE dbo.customers (
    id          BIGINT IDENTITY(1, 1) PRIMARY KEY,          -- clustered by default
    public_id   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
    email       NVARCHAR(255) NOT NULL UNIQUE,
    name        NVARCHAR(100) NOT NULL,
    balance     DECIMAL(12, 2) NOT NULL DEFAULT 0,
    is_active   BIT NOT NULL DEFAULT 1,
    profile     NVARCHAR(MAX) CHECK (ISJSON(profile) = 1),  -- JSON lives in NVARCHAR before 2025
    created_at  DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
);

Type	Use it for	Trap
`NVARCHAR(n)`	any human text	Unicode. Write literals as `N'नमस्ते'`: without the `N`, non-Latin text becomes `?`
`VARCHAR(n)`	ASCII codes, ids	Unicode only with a UTF-8 collation (2019+)
`DATETIME2(p)`	timestamps	use instead of old `DATETIME` (3 ms rounding, narrower range)
`DATETIMEOFFSET`	instant + offset	when the original time zone matters
`DECIMAL(p, s)`	money	`MONEY` has rounding surprises in division
`UNIQUEIDENTIFIER`	GUIDs	random `NEWID()` as a clustered key fragments the table; use `NEWSEQUENTIALID()`
`json` (2025, Azure SQL)	JSON documents	native binary JSON type; before 2025, `NVARCHAR(MAX)` + `ISJSON`
`vector(n)` (2025, Azure SQL)	embeddings	`VECTOR_DISTANCE('cosine', a, b)`

9.4 Writing data: `OUTPUT`, upserts and `MERGE`¶

OUTPUT returns the rows a statement wrote (like Postgres RETURNING):

INSERT INTO dbo.conversations (user_id, title)
OUTPUT inserted.id, inserted.created_at
VALUES (42, N'Refund question');

DELETE FROM dbo.jobs
OUTPUT deleted.id
WHERE status = 'done' AND finished_at < DATEADD(day, -30, SYSUTCDATETIME());

There is no ON CONFLICT. The safe upsert pattern under concurrency:

SET XACT_ABORT ON;            -- any error rolls back the whole transaction
BEGIN TRANSACTION;
UPDATE dbo.daily_usage WITH (UPDLOCK, SERIALIZABLE)       -- lock the key range, even if missing
SET calls = calls + 1, tokens = tokens + @tokens
WHERE user_id = @user_id AND day = @day;

IF @@ROWCOUNT = 0
    INSERT INTO dbo.daily_usage (user_id, day, calls, tokens)
    VALUES (@user_id, @day, 1, @tokens);
COMMIT;

MERGE does insert, update and delete in one statement, which suits batch loads from a staging table:

MERGE dbo.products WITH (HOLDLOCK) AS t
USING staging.products AS s ON t.sku = s.sku
WHEN MATCHED AND t.price <> s.price THEN UPDATE SET price = s.price
WHEN NOT MATCHED BY TARGET THEN INSERT (sku, name, price) VALUES (s.sku, s.name, s.price)
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT $action, inserted.sku;              -- end MERGE with a semicolon

MERGE needs care

Without HOLDLOCK (serializable), two concurrent MERGEs can both decide "not matched" and insert duplicates, and MERGE has a history of edge-case bugs. Many teams use it only for single-threaded batch loads and the UPDATE + INSERT pattern above for request-time upserts.

9.5 `CROSS APPLY` / `OUTER APPLY`¶

APPLY runs a table expression once per row (the equivalent of LATERAL in Postgres): ideal for top-N per group and for shredding JSON.

-- the 3 largest orders of each customer (OUTER APPLY keeps customers with none)
SELECT c.name, o.id, o.amount
FROM dbo.customers AS c
OUTER APPLY (
    SELECT TOP (3) id, amount FROM dbo.orders
    WHERE customer_id = c.id
    ORDER BY amount DESC
) AS o;

9.6 JSON in T-SQL¶

SELECT id,
       JSON_VALUE(meta, '$.feature')                   AS feature,     -- a scalar
       CAST(JSON_VALUE(meta, '$.tokens.in') AS INT)    AS tokens_in,
       JSON_QUERY(meta, '$.tokens')                    AS tokens_json  -- an object or array
FROM dbo.llm_calls
WHERE ISJSON(meta) = 1 AND JSON_VALUE(meta, '$.feature') = 'chat';

-- array → rows with OPENJSON
SELECT t.id, tag.value AS tag
FROM dbo.tickets AS t
CROSS APPLY OPENJSON(t.meta, '$.tags') AS tag;

-- rows → JSON (handy for building LLM context or API responses)
SELECT TOP (3) id, name, city FROM dbo.customers FOR JSON PATH;

Index a JSON field with a computed column:

ALTER TABLE dbo.llm_calls ADD feature AS JSON_VALUE(meta, '$.feature');
CREATE INDEX ix_llm_calls_feature ON dbo.llm_calls (feature);

9.7 Temporal tables: history for free¶

A system-versioned table keeps every previous version of each row automatically. It's an SCD Type 2 that the database maintains for you, and it's great for auditing what an AI agent changed:

CREATE TABLE dbo.prices (
    product_id INT PRIMARY KEY,
    price      DECIMAL(10, 2) NOT NULL,
    valid_from DATETIME2 GENERATED ALWAYS AS ROW START,
    valid_to   DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.prices_history));

SELECT * FROM dbo.prices FOR SYSTEM_TIME AS OF '2026-09-01T00:00:00';   -- prices on 1 September

9.8 Full-text search¶

CREATE FULLTEXT CATALOG ft_catalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.docs (title, body) KEY INDEX pk_docs;    -- needs a unique key index

SELECT d.id, d.title, k.[RANK]
FROM CONTAINSTABLE(dbo.docs, (title, body), N'FORMSOF(INFLECTIONAL, refund)') AS k
JOIN dbo.docs AS d ON d.id = k.[KEY]
ORDER BY k.[RANK] DESC;

SELECT id FROM dbo.docs WHERE FREETEXT(body, N'how long until I get my money back');

CONTAINS matches words, prefixes ("refund*") and inflections; FREETEXT matches the meaning of a phrase loosely; the …TABLE versions return a RANK. Full-text search is an optional feature: in the Docker image you need to install it separately.

9.9 Indexes and execution plans¶

Clustered index: the table itself, sorted by the key (one per table; the primary key by default). Choose a narrow, increasing key.
Nonclustered index: a separate B-tree. INCLUDE adds columns so the query never goes back to the table.
Filtered index: CREATE INDEX ix_open ON dbo.tickets (created_at) WHERE status = 'open';
Columnstore: column-wise and compressed, for analytics over millions of rows (dashboards, LLM logs).

CREATE NONCLUSTERED INDEX ix_orders_customer_date
ON dbo.orders (customer_id, created_at) INCLUDE (amount, status);

SET STATISTICS IO, TIME ON;      -- logical reads and CPU time per query, in the Messages tab

Read the actual execution plan (Ctrl+M in SSMS):

Operator	Meaning
Clustered Index Scan / Table Scan	reads everything
Index Seek	jumps to the matching rows
Key Lookup	goes back to the table for missing columns: fix with `INCLUDE`
Hash Match / Nested Loops / Merge Join	join strategies
Sort, with a warning icon	spilled to disk: a memory grant problem

Parameter sniffing

SQL Server caches a plan built for the first parameter value it sees. If that was a tiny customer, the plan may be terrible for a huge one. Fixes: OPTION (RECOMPILE) on the statement, OPTIMIZE FOR, or forcing a good plan with Query Store, which also shows you which queries regressed and when.

9.10 Transactions, isolation and locking¶

The default isolation is READ COMMITTED with locking: readers wait for writers. Most modern systems turn on Read Committed Snapshot Isolation (RCSI) so readers see the last committed version instead of blocking. It's on by default in Azure SQL Database.

ALTER DATABASE shop SET READ_COMMITTED_SNAPSHOT ON;

WITH (NOLOCK) is not a performance trick

NOLOCK = READ UNCOMMITTED: you can read uncommitted data that later rolls back, skip rows, or read the same row twice during page splits. Wrong totals in a report are worse than slow ones. Use RCSI instead.

A worker queue (like SKIP LOCKED elsewhere):

WITH next_job AS (
    SELECT TOP (1) * FROM dbo.jobs WITH (UPDLOCK, READPAST, ROWLOCK)
    WHERE status = 'queued'
    ORDER BY id
)
UPDATE next_job SET status = 'running'
OUTPUT inserted.id, inserted.payload;

Error handling in T-SQL:

BEGIN TRY
    BEGIN TRANSACTION;
    -- ... statements ...
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;                         -- re-raise to the caller (error 1205 = deadlock victim: retry)
END CATCH;

9.11 Stored procedures and safe dynamic SQL¶

Enterprise databases often expose stored procedures instead of tables. For a GenAI agent, that's good news: each procedure is a safe, pre-approved tool.

CREATE OR ALTER PROCEDURE dbo.get_customer_orders
    @customer_id BIGINT, @days INT = 30
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, amount, status, created_at
    FROM dbo.orders
    WHERE customer_id = @customer_id
      AND created_at >= DATEADD(day, -@days, SYSUTCDATETIME());
END;
GO
EXEC dbo.get_customer_orders @customer_id = 42, @days = 7;

When SQL must be built dynamically, keep the values as parameters with sp_executesql, and wrap names with QUOTENAME(), never plain concatenation:

DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) FROM dbo.orders WHERE status = @status';
EXEC sp_executesql @sql, N'@status NVARCHAR(20)', @status = N'paid';

9.12 SQL Server interview checklist¶

TOP, TOP WITH TIES and OFFSET … FETCH.
NVARCHAR vs VARCHAR, the N'…' prefix and DATETIME2.
OUTPUT, a race-safe upsert, and MERGE with HOLDLOCK.
Top-N per group with CROSS APPLY.
JSON_VALUE, OPENJSON and indexing a JSON field.
Clustered vs nonclustered vs columnstore; Seek vs Scan vs Key Lookup.
Parameter sniffing and Query Store.
RCSI vs NOLOCK; a READPAST queue; TRY…CATCH with THROW.

Interview questions¶

Clustered vs nonclustered index?

A clustered index is the table, with rows stored in key order, so there can be only one. A nonclustered index is a separate structure holding the key plus a pointer (the clustered key) back to the row, and a table can have many. Queries that need columns missing from a nonclustered index do Key Lookups, which INCLUDE columns avoid.

Why is WITH (NOLOCK) dangerous?

It reads uncommitted data, so a report can include changes that were rolled back, miss rows or count them twice while pages split. It hides blocking rather than fixing it. Read Committed Snapshot Isolation gives non-blocking reads of committed data instead.

A stored procedure is fast for one customer and slow for another. Why?

Most likely parameter sniffing: the cached plan was compiled for the first value, whose data distribution differs. Confirm with the actual plan (estimated vs actual rows) or Query Store, then use OPTION (RECOMPILE), OPTIMIZE FOR, better statistics or a forced plan.

How would you let an LLM agent query a SQL Server ERP safely?

Give it a login with EXECUTE permission only on a small set of reviewed stored procedures (or SELECT on curated views), never db_owner. Pass every value as a parameter, use row-level security for per-user data, set a query timeout, run against a readable secondary replica, and log every call.

Practice¶

Create dbo.orders with a clustered key and a nonclustered index with INCLUDE, then compare SET STATISTICS IO logical reads before and after.
Write the UPDATE + INSERT upsert and fire it from two sessions at once: no duplicates.
Shred a JSON array of tags with OPENJSON and count tickets per tag.

Next: Pandas — work with query results as DataFrames.