All of it in one place — no jumping between files. Your own repeat misses come first, then everything by topic. Cover the right column, work down the left, and click any row you're solid on to dim it.
| Trap | The truth |
|---|---|
| ★★ Chunking strategy — missed twice | Structure exists → structural. Precise match and wide context → hierarchical. No structure + topic drift → semantic. "Quality over ingestion cost" licenses the expensive ones. |
★★ ExpectedSqlException — lost twice | A database test expecting a database error → the answer contains "Sql" + "Exception". Not "Scalar Value", not [ExpectedException]. |
| ★ Always Encrypted "unless they are granted key access" | That clause describes the mechanism's boundary. It is not an instruction to grant access. Descriptions of limits belong in correct answers. |
★ RANK for "top 3 distinct levels" | RANK skips after ties (1,2,2,4) so <=3 loses a whole level. Use DENSE_RANK. |
★ EDIT_DISTANCE for a "normalized 0–100" threshold | That's a raw edit count. The 0–100 ones are the _SIMILARITY functions. |
★ Temporal CONTAINED IN = "active during the period" | It means the row's entire life fit inside the window. A currently-active row can never match it. |
| ★ Scalar UDF vs inline TVF | Missed in both directions. One value inside an expression → scalar. Parameterised rowset → inline TVF + CROSS APPLY. Check the return shape first. |
| ★ Query Performance Insight view | Low CPU + low I/O + high latency = too many round trips. Custom view + Execution count metric. Long Running Queries is wrong — the queries are short. |
★ dotnet build to deploy | Build makes the dacpac and touches no database. Deploying is a separate publish step: sqlpackage /Action:Publish, or azure/sql-action. |
| ★ Return code to carry a row count | RETURN is status only. A scalar alongside a result set = OUTPUT parameter, initialised explicitly. |
| ★ Fabric Copilot with only the usage setting | A named non-US region also needs cross-geo processing — and "delegated" means tenant AND capacity. Four switches. |
★ GET to call chat completions | Retrieval already happened in SQL. You're sending a prompt → POST. GET has no request body. |
| JSON cluster — three losses | Name the verb: construct (JSON_OBJECT/ARRAY/AGG) · convert (FOR JSON) · parse (JSON_VALUE/QUERY/OPENJSON). |
| DML vs DDL trigger | INSERT/UPDATE/DELETE = DML. CREATE/ALTER/DROP = DDL. |
| SNAPSHOT concurrency outcome | First committer wins; the second gets an update conflict. Not blocking, not a deadlock. |
| "Repeated deployments" seed data | Must be idempotent → post-deployment MERGE, never INSERT. |
| Trap | The truth |
|---|---|
| Azure SQL Database can run Agent jobs / linked servers / cross-database queries | No. Those are instance-level → Managed Instance or VM. |
| Managed Instance gives you OS access | No. A named SQL version, OS access or third-party install → VM only. |
| Multiple filegroups on Azure SQL Database | PRIMARY only. Any storage-tiering or filegroup-backup option is wrong on that platform. |
| Fabric SQL database is just another Azure SQL | Its point is OLTP + analytics together — auto-mirroring to OneLake as Delta Parquet, read-only analytics endpoint. |
| GP or BC for a 6 TB database | Both cap at 4 TB → Hyperscale. |
| Business Critical is just "faster GP" | It's also where In-Memory OLTP and the free read replica live. |
| Serverless suits a steady workload | "Continuously allocated" or "must not pause" kills serverless. |
| DTU can express a CPU requirement | It's a blended unit. "CPU/memory baselines" kills DTU → vCore. |
| Elastic pool for one busy database | Pools are for many databases with non-overlapping peaks. |
| Trap | The truth |
|---|---|
| "Partitioning improves query performance" | Only for queries filtering on the partitioning column. Its real value is manageability. |
| N boundaries = N partitions | N + 1. Always. |
| Direction changes the partition count | It doesn't. RIGHT/LEFT only changes which side the boundary value falls on. |
| RANGE LEFT with first-of-month boundaries | Puts the 1st into the previous month. First-of-period → RANGE RIGHT. |
| Building the scheme before the function | Impossible. Function → Scheme → Table. |
| Unique index without the partitioning column | Msg 1908. Unique keys must include it. |
| A non-aligned index is harmless | It compiles, then silently blocks SWITCH. |
| SWITCH into a non-empty partition | Fails. Target must be empty. |
| SWITCH in without a CHECK constraint | Fails. Staging must prove its rows fit the target range. |
SPLIT without NEXT USED | "Partition scheme has no next used filegroup." |
| SPLIT or MERGE on a populated partition | Physically moves data, fully logged, holding locks. Always operate on empty partitions. |
| Partitioning = sharding | Partitioning is within one database. Sharding spans databases. |
| Sliding-window order | SWITCH out → MERGE → NEXT USED → SPLIT. |
| Trap | The truth |
|---|---|
| Fix a Key Lookup by adding key columns | Use INCLUDE — key columns bloat every level of the tree. |
Index on (A,B,C) helps WHERE B = … | Leftmost prefix. Only A, A+B, or A+B+C. |
| Function around a column in WHERE | Non-SARGable → scan. Rewrite as a range, or PERSISTED computed column. |
| NVARCHAR column vs VARCHAR literal | Implicit conversion on the column → scan. Match the types. |
| Clustered columnstore for an OLTP table needing analytics | It replaces the rowstore. HTAP wants nonclustered columnstore. |
| Sequential IDENTITY clustered key is always best | It's the N-U-S-E ideal and the cause of last-page latch contention at extreme insert rates. |
| A B-tree index accelerates vector distance | It can't. Nearest-neighbour in 1536 dimensions isn't a range predicate. |
| An index speeds up a regex or fuzzy predicate | Neither is SARGable. Pre-filter, or use a PERSISTED computed column. |
| REORGANIZE updates statistics | It doesn't. REBUILD does. |
| REBUILD is always online | It takes an exclusive lock unless ONLINE = ON. REORGANIZE is always online. |
| Filtered index will always be used | Needs the right session SET options and a predicate provably covered by the filter. |
| Trap | The truth |
|---|---|
| A big estimate/actual gap means "add an index" | It means update statistics. Fix estimates before tuning indexes. |
SHOWPLAN_TEXT shows actual rows | Estimated only, and it doesn't execute. Actual needs STATISTICS PROFILE / XML. |
| Read a text plan top-down | Most-indented executes first. |
| A Missing Index warning is an instruction | A suggestion that ignores existing indexes and write cost. |
| "No Join Predicate" is a performance hint | It's a bug — an accidental cross join. |
| Elapsed time is the tuning metric | Logical reads — stable between runs. SET STATISTICS IO ON. |
| Spill warnings mean you need more memory | Usually the estimate was wrong → statistics. |
| Query Store and DMVs answer the same question | Query Store = history and plan regression. DMVs = right now. |
| Query Store must be enabled first | On by default in Azure SQL Database. |
| Query Store verb map | Regressed Queries view finds · plan forcing fixes · hints shape · wait stats explain. |
PAGELATCH_EX means a locking problem | A latch is an internal memory-page protection. This is last-page insert contention. |
| Error 1205 | Deadlock → retry with a random delay. |
| Trap | The truth |
|---|---|
| Multi-statement TVFs are fine | Opaque to the optimiser, fixed row estimate. One SELECT → inline TVF. |
| Inline TVF syntax | RETURNS TABLE then RETURN (SELECT …) — no BEGIN/END, no declared table shape. |
| A function can update a table | No side effects. That's a stored procedure. |
| A default on an OUTPUT parameter guarantees a value | Only if the caller omits the argument. "On every execution path" → assign explicitly. |
@@ROWCOUNT is reliable in a RETURN | It reflects the last statement — any inserted statement changes it. |
| Concatenate user input into dynamic SQL | sp_executesql with parameters — injection-safe and plan-reusable. |
| Grant SELECT for least privilege | GRANT EXECUTE on procedures — ownership chaining means no table permission at all. |
| Least privilege = a clever permission | The narrowest boring one. VIEW DEFINITION is metadata only, not data. |
| Trap | The truth |
|---|---|
JSON_VALUE on an array or object path | Returns NULL silently in lax mode — no error. Use JSON_QUERY. |
JSON_QUERY on a scalar path | Returns NULL. Use JSON_VALUE. |
Nesting JSON inside FOR JSON output | Escaped as a string. Wrap in JSON_QUERY. |
JSON_MODIFY with a JSON string value | Same escaping problem. Wrap in JSON_QUERY. |
JSON_MODIFY(…, NULL) | lax = deletes the key · strict = sets it to null. |
AS JSON in OPENJSON on any type | Must be NVARCHAR(MAX). |
OPENJSON column at an array without AS JSON | Silent NULL. |
FOR JSON and NULL columns | Omitted by default → INCLUDE_NULL_VALUES. |
ROOT + WITHOUT_ARRAY_WRAPPER | Cannot be combined. |
WITHOUT_ARRAY_WRAPPER with multiple rows | Objects concatenated without commas — invalid JSON. Pair with TOP 1. |
| Which option for which problem | WITHOUT_ARRAY_WRAPPER = single row. INCLUDE_NULL_VALUES = multi-row nulls. |
JSON_VALUE(...) IS NULL to detect a missing key | Also true when the key exists holding null → JSON_PATH_EXISTS. |
JSON_ARRAY with GROUP BY | It's not an aggregate → JSON_ARRAYAGG. |
JSON_VALUE for a long string | Caps at nvarchar(4000) → OPENJSON … WITH (col NVARCHAR(MAX)). |
JSON_VALUE compared to a number | Returns text → implicit conversion, no seek. CAST + PERSISTED computed column. |
ORDER BY after FOR JSON | Syntax error. FOR JSON is always last. |
| Trap | The truth |
|---|---|
| Regex default is case-insensitive | Default flag is 'c' — case-sensitive. An empty string also means 'c'. |
| Contradictory flags error out | The last character wins. 'ci' insensitive, 'ic' sensitive. |
SELECT REGEXP_LIKE(...) | It's a predicate. Use in WHERE/HAVING/CHECK, or wrap in CASE/IIF. |
| Every regex function needs compatibility level 170 | Only REGEXP_LIKE does; the other scalar regex functions work at any level. None of them exist before SQL Server 2025. (Updated Oct 2026.) |
| All seven regex functions are scalar | Two return tables — REGEXP_MATCHES, REGEXP_SPLIT_TO_TABLE. Need CROSS APPLY. |
| Regex beats LIKE for a simple prefix | LIKE 'ABC%' can seek; regex always scans. |
STRING_SPLIT handles mixed delimiters | Single character only → REGEXP_SPLIT_TO_TABLE. |
| Pattern length is unlimited | Max 8,000 bytes; input LOB up to 2 MB. |
| Trap | The truth |
|---|---|
CONTAINS(col, 'two words') | Syntax error. A phrase needs double quotes inside the string. |
FREETEXT(col, 'red AND blue') | "AND" is treated as a word. FREETEXT has no operators. |
| Multiple full-text indexes per table | One per table. Add columns to it. |
KEY INDEX can be composite | Must be unique, single-column, non-nullable. |
| Stale full-text results | CHANGE_TRACKING is MANUAL or OFF — should be AUTO. |
Full-text RANK sorts ascending | Higher RANK is better → DESC. Opposite of vector distance. |
| All fuzzy functions share a scale | Count (EDIT_DISTANCE) · 0–100 (EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_SIMILARITY) · float (JARO_WINKLER_DISTANCE) · 0–4 (DIFFERENCE). Match the stem. |
| "Distance" and "similarity" sort the same way | Distance: lower better. Similarity: higher better. |
| FREETEXT understands concepts | It matches word forms, not meaning. "Keep coffee hot" won't find "insulated flask" — that's vector search. |
| Trap | The truth |
|---|---|
ROW_NUMBER when ties must be returned | Unique numbers, drops ties. |
DENSE_RANK for deduplication | Tied rows share rank 1 → you'd keep both. Dedup wants ROW_NUMBER. |
NTILE(4) gives quartiles by value | Buckets by row count. Value cut-offs need PERCENT_RANK / PERCENTILE_*. |
| Omitting the frame on a running total | Defaults to RANGE, which lumps tied ORDER BY values. Write ROWS. |
LAST_VALUE without a frame | Returns the current row's value. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. |
FIRST_VALUE needs the same fix | It doesn't — the default frame already starts at UNBOUNDED PRECEDING. |
Window function in a WHERE clause | Not allowed — they run after WHERE. Wrap in a CTE. |
PERCENTILE_CONT returns a real data value | It interpolates. "Must exist in the data" → PERCENTILE_DISC. |
PERCENTILE_* uses ORDER BY inside OVER | Needs WITHIN GROUP (ORDER BY …) OVER (PARTITION BY …). |
PERCENT_RANK and CUME_DIST are the same | PERCENT_RANK excludes peers; CUME_DIST includes them. |
| Trap | The truth |
|---|---|
FROM…TO and BETWEEN…AND are synonyms | FROM's upper bound is exclusive; BETWEEN's is inclusive. |
| Temporal tables need a trigger | No. The engine copies rows automatically. |
| You can TRUNCATE a temporal table | Not while versioning is on. Also can't DROP without turning it off. |
| Temporal proves data wasn't altered | It's a history feature. Proof needs ledger. |
CACHE reserves values for you | An internal performance setting. Reserving is sp_sequence_get_range. |
NO CACHE guarantees a gapless series | Removes one cause. Rolled-back transactions still consume numbers. |
| IDENTITY can span several tables | It can't. That's a SEQUENCE. |
Aggregates can go in WHERE | No — HAVING. The aggregate doesn't exist when WHERE runs. |
ROLLUP(a,b) gives 4 groupings | 3 — it's n+1. CUBE gives 2ⁿ = 4. |
| A NULL in a subtotal row is just a NULL | GROUPING() tells a subtotal placeholder from real NULL data. |
DECIMAL with no parameters | Defaults to DECIMAL(18,0) — every decimal place rounded away. |
| Too many decimals throws an error | It rounds silently. Too many digits before the point throws (Msg 8115). |
| Trap | The truth |
|---|---|
| Graph edges traverse both ways | No. $from_id/$to_id fix the direction; reversing the arrow returns nothing. |
FOR PATH on the anchor node | Only on tables inside the repeating pattern. |
| Selecting a FOR PATH column directly | Only inside WITHIN GROUP (GRAPH PATH). |
MATCH goes in a JOIN | It goes in the WHERE clause; tables are comma-separated. |
+ and {1,5} are the same | + is unbounded; {1,5} caps the hops. |
| Trap | The truth |
|---|---|
| TDE protects from administrators | No. It decrypts transparently for anyone who authenticates. At-rest only. |
| Always Encrypted needs no app change | Needs Column Encryption Setting=Enabled and driver access to the CMK. "No application changes" rules it out. |
| Deterministic is just as secure | Repeated ciphertexts leak frequency. Never on low-cardinality columns. |
| Randomized supports equality search | It supports no operations at all. |
| Deterministic supports range queries | Equality, join, GROUP BY, index only. Ranges need secure enclaves. |
| Deterministic strings work with any collation | They need a BIN2 collation. |
| Column-level encryption hides data from the DBA | No. The server decrypts; key access = plaintext access. |
| CEK and CMK both live in the database | CEK inside (encrypted), CMK outside — Key Vault, cert store or HSM. |
| The three states are interchangeable | Rest = TDE · Transit = TLS · In use = Always Encrypted. |
| Trap | The truth |
|---|---|
| DDM satisfies a compliance requirement | Never. Inference via WHERE / ORDER BY / CAST defeats it. |
| DDM changes the stored data | No. It rewrites output only. |
| DDM is always the wrong answer | Not quite — if the stem says "usability measure, not a compliance control", it's right. |
| An RLS filter predicate prevents bad writes | No. Filter = visibility. Writes need a BLOCK predicate. |
| Block predicate operations are interchangeable | Create → AFTER INSERT · move out → AFTER UPDATE · modify another's → BEFORE UPDATE · delete → BEFORE DELETE. |
| The RLS function can be any function type | Inline TVF with SCHEMABINDING. |
| Views give the same protection as RLS | Bypassable via the base table. RLS is enforced in the engine. |
| GRANT beats DENY if more specific | DENY wins — except db_owner/sysadmin. |
| Trap | The truth |
|---|---|
| A managed identity needs no role assignment | It does. Identity ≠ authorisation. This is the classic 401. |
| Role assignments take effect immediately | Up to 10 minutes, and much longer if the identity gets the role through a group, because its tokens are cached for up to about 24 hours. |
| Assign the role to the Foundry project | Assign it to the Foundry resource (Kind: AIServices). |
| System-assigned identity can be shared | No. Sharing needs user-assigned. |
| Contributor lets you read blobs or secrets | Management plane ≠ data plane. Look for "Data" in the role name. |
| Key Vault satisfies "no API keys anywhere" | The key still exists. "Eliminated" means managed identity. |
| Database-level audit captures failed logins | Authentication is server-level. Use a server-level audit. |
| You must pick one audit destination | Several can run simultaneously — watch for "choose two". |
| Audit destinations | Analyse/alert → Log Analytics · SIEM → Event Hubs · retain years → Storage. |
| Auditing proves data wasn't tampered with | It records events. Proof needs ledger. |
| Service endpoint = private connectivity | The resource keeps a public endpoint. Use a private endpoint. |
| Trap | The truth |
|---|---|
VECTOR_SEARCH is a faster VECTOR_DISTANCE | With TOP (N) WITH APPROXIMATE and a DiskANN index it's approximate (ANN). "Exact" → VECTOR_DISTANCE (ENN). |
VECTOR_SEARCH without a vector index | Still runs, as an exact full scan (kNN). The DiskANN index is what makes WITH APPROXIMATE fast. (Updated Oct 2026.) |
TOP_N inside VECTOR_SEARCH() | Msg 42274 with newer index versions. Use outer SELECT TOP (n) WITH APPROXIMATE + ORDER BY vs.distance. |
| Higher distance = more similar | Lower is more similar. ORDER BY distance ASC. |
| Index metric can differ from query metric | They must match, or the index isn’t used and the query falls back to an exact scan. |
| Different models' embeddings are comparable | No. Change model → alter the column and re-embed everything. |
VECTOR(n) is flexible | Fixed, and must equal the model's dimension count. |
| Vectors are float64 | float32 — 4 bytes per dimension. Edm.Single = single precision = 32-bit. |
| Embed the whole document | Chunk first. One vector for a long document matches nothing well. |
| Overlap supplies broad context | It only prevents boundary loss. Broad context is hierarchical chunking. |
| Vector fields can be filterable in AI Search | Searchable only — not filterable, sortable or facetable. |
| The AI Search key field can be any type | Exactly one, and Edm.String. |
hnsw gives exact results | Approximate. Exact is exhaustiveKnn. |
| RRF averages relevance scores | It fuses rank positions — scores aren't comparable across systems. 1/(k+rank), k≈60. |
| RRF uses an INNER JOIN | FULL OUTER JOIN + ISNULL(...,0). |
| Trap | The truth |
|---|---|
GET for a chat completion | POST. GET has no request body — the prompt has nowhere to go. |
FOR JSON returns a single object for one row | It returns an array. Add WITHOUT_ARRAY_WRAPPER. |
JSON_QUERY to extract the model's answer | The content is a scalar → JSON_VALUE(@response,'$.result.choices[0].message.content'). |
| 404 means a permissions problem | 401/403 = permissions. 404 = wrong URL, usually the deployment name. 400 = bad payload. |
| 429 means something is broken | Rate limiting. @retry_count or raise the quota. |
| Credential name is arbitrary | Must match the URL prefix. |
| Fine-tuning is the way to use your own data | RAG. Fine-tuning is expensive, stale, and gives no citations. |
| "Answer only from context" is enough | Also give the model permission to decline — otherwise it fills the gap. |
| Trap | The truth |
|---|---|
| DAB permissions control which rows a user sees | They control actions. Row filtering = RLS + set-session-context. |
dab add to modify an entity | add creates, update modifies. Running add twice fails. |
Adding a view without key-fields | Mandatory — a view has no PK to discover. |
Giving a stored procedure read | Procedures take execute only; GraphQL mutation if they write. |
Static Web Apps serves DAB at /api | /data-api/ — fixed. /api is Functions. |
| Cosmos DB works with REST | GraphQL only for Cosmos DB. |
| Being in the role is enough | Send X-MS-API-ROLE too. |
| A literal connection string is fine in a private repo | Never. Managed identity, else @env('...'). |
Simulator auth provider in production | Development only — it fakes roles. |
Leaving host.mode: development | Exposes stack traces and schema explorers. Switch to production. |
| L1 cache is shared across instances | L1 is per-instance. Cross-instance needs L1L2. |
| Environment-specific config | DAB_ENVIRONMENT. |
| Trap | The truth |
|---|---|
| A SQL project only compiles files you add | It sweeps up every .sql file automatically. Exclusion must be explicit. |
| SQL70001 | A non-schema statement got compiled. <Build Remove> + <None Include>. |
| SQL71501 / 71502 | Unresolved reference — the object isn't in the project. The model is self-contained. |
CREATE OR ALTER in a SQL project | Not allowed — the model can't hold "or". |
:r is T-SQL | It's a SQLCMD directive, expanded at build time inside the post-deployment script. |
| Multiple post-deployment scripts | One per project. Split with :r. |
| Export creates a dacpac | Export → bacpac (schema + data). Extract → dacpac (schema only). |
| Publish and Import are interchangeable | Publish takes a dacpac; Import takes a bacpac. |
Disable BlockOnPossibleDataLoss to make it work | It's a safety net. Almost never the answer. |
| The GitHub workflow blocks the merge | It only runs. Blocking needs a required status check. (Azure DevOps: the Build Validation policy does both.) |
| upload-artifact deploys the dacpac | It's just the courier. azure/sql-action deploys. |
| Trap | The truth |
|---|---|
| Enabling Copilot usage is enough | A non-US / named region also needs cross-geo processing. |
| "Delegated to capacity admins" means only the capacity setting | Both levels — tenant and capacity. Four switches in total. |
| Copilot can see everything in the workspace | Scope is OPEN or CONNECTED content — including an open CSV export. Not unopened files, raw row data, or environment-variable credentials. |
| Copilot questions need memorisation | They're the easiest part — answerable by reasoning. Bank the time. |
'c' (case-sensitive). REGEXP_LIKE needs compat level 170. Never SARGable.