In one sentence, what is T-SQL, and where do you typically run it?
T-SQL (Transact-SQL) is Microsoft SQL Server’s SQL dialect: ANSI SQL plus extensions for procedural logic, metadata, administration, and server-specific features. You use it in SSMS, Azure Data Studio, sqlcmd, Agent jobs, stored procedures, and embedded application SQL.
What is a batch in SQL Server, and what is GO?
A batch is a set of statements sent together and compiled as a unit. GO is a client batch separator (SSMS, ADS; sqlcmd recognizes it) — not T-SQL itself. Some statements must start a batch or cannot share a batch with others (e.g. CREATE VIEW edge cases — verify BOL).
Bucket these into DDL, DML, or DCL: CREATE TABLE, SELECT, GRANT, MERGE, TRUNCATE TABLE.
DDL — define/change objects: CREATE / ALTER / DROP TABLE, INDEX, PROC, VIEW, FUNCTION; TRUNCATE TABLE is often treated like DDL operationally. DML — rows: SELECT, INSERT, UPDATE, DELETE, MERGE. DCL — permissions: GRANT, DENY, REVOKE.
Name core query building blocks you’d list in a panel (joins, paging, windows, upsert).
SELECT with WHERE, GROUP BY / HAVING, ORDER BY, TOP or OFFSET … FETCH. Joins: INNER, LEFT/RIGHT/FULL OUTER, CROSS JOIN, CROSS/OUTER APPLY for table-valued expressions. Window functions with OVER (PARTITION BY … ORDER BY …). MERGE for upsert (know concurrency / trigger caveats).
When would you prefer EXISTS over IN for a subquery?
For existence checks, EXISTS is often preferred — it short-circuits on a match and avoids large IN lists; IN vs EXISTS tradeoffs come up in correlated vs non-correlated subquery discussions.
What is a CTE, and what is its scope?
A CTE (WITH name AS (…)) is a named, in-query result referenced in the same outer statement (SELECT, INSERT, UPDATE, DELETE, MERGE). It is not a physical table — only that one statement can use it; a new statement needs its own WITH.
How do chained CTEs work?
List multiple CTEs separated by commas: WITH a AS (…), b AS (SELECT … FROM a …) SELECT … FROM b. Later CTEs can reference earlier ones in the chain.
How does a recursive CTE work, and what must you guarantee?
Anchor query UNION ALL recursive part that references the CTE. You must ensure termination (e.g. a WHERE that eventually returns no rows). Uses: org charts, BOMs, paths in graphs.
CTE vs #temp / @table vs subquery — how do you contrast them in an interview?
CTE — logical; optimizer may inline like subqueries — not a promise of “materialize once.” #temp / @table — real tempdb objects; #temp can have indexes; session or procedure scope. Subquery — inline; CTE wins readability when the same step is reused by name in one statement.
Why is MERGE “tricky,” and what would you say about it on a panel?
MERGE is powerful for upsert but has gotchas with triggers, concurrency, and behavior details — know there is controversy in the community; test carefully and understand exact semantics for your version.
EXEC('…') vs sp_executesql — which do you prefer for dynamic SQL, and why?
Prefer sp_executesql when you need parameterization and plan reuse; raw EXEC with string concat risks injection and plan cache churn. Interview line: “I use sp_executesql for dynamic SQL when I need parameters.”
Name key programmability pieces beyond basic SQL (variables through modules).
DECLARE / SET / SELECT @var, IF/ELSE, WHILE, TRY/CATCH, THROW / RAISERROR, BEGIN TRAN / COMMIT / ROLLBACK, SAVE TRAN, XACT_ABORT (know it affects batch abort / atomicity). Modules: stored procedures; functions (scalar, inline TVF, multi-statement TVF — inline often better for inlining); triggers (DML/DDL).
Catalog views vs DMVs — what are they for?
Catalog views (sys.tables, sys.indexes, sys.columns, …) describe schema and objects. DMVs (sys.dm_exec_*, sys.dm_os_*, sys.dm_tran_*) expose runtime state: sessions, plans, waits, locks — pair with performance tuning.
What is DBCC, and what’s the operational caution?
DBCC includes integrity checks (CHECKDB, CHECKTABLE) and informational commands. Some commands are heavy — run in maintenance windows, not casually in production peaks.
Why do session SET options matter — name a few that interviewers like?
They can change results and plans. Examples: ANSI_NULLS, QUOTED_IDENTIFIER (needed for certain object definitions like indexed views at create time), ARITHABORT (can affect plan behavior with indexed views). READ_COMMITTED_SNAPSHOT is database-level — ties to blocking. Line: “Session options matter — I don’t memorize every flag, but I verify consistency when debugging weird plan or NULL behavior.”
How do you handle version-specific T-SQL features in an interview?
Newer versions add syntax (STRING_AGG, JSON, graph, window enhancements, etc.). Be honest about your footprint (2017 vs 2019 vs 2022) and say you’d check BOL for the exact version you’re targeting.
Give a closing “T-SQL in one breath” line for a panel.
“T-SQL is SQL Server’s procedural SQL: DDL for objects, DML for data, batches and GO, transactions, modules, and DMVs for ops. I prefer sp_executesql for dynamic SQL when I need parameters.”
