MERGE

In the Ultra Transcenders books

DP-600DP-700DP-800

Each book explains MERGE in context, with comparison tables and the common traps.

Terms in this definition

• Serverless

  Compute tier for single Azure SQL databases that scales automatically, pauses when idle and charges by the second. It is offered in General Purpose and Hyperscale, not Business Critical, and reserved capacity does not apply.

• Event

  Table in Log Analytics where entries from Windows event logs are kept.

• MATCH

  Used in WHERE when querying SQL Graph, it describes how to walk from node to node through edge tables, with patterns written like p1-(f1)->p2.

• Fabric Warehouse

  A relational data warehouse in the classic style, offered as a Fabric item, that supports T-SQL fully, transactions included.

• MERGE INTO

  Upserts rows into a Delta table from a source. Separate clauses decide each row's fate: WHEN MATCHED typically updates, WHEN NOT MATCHED inserts, and WHEN NOT MATCHED BY SOURCE can delete target rows absent from the source.

Related terms

• AB# mention

  Type AB# and a work item number in a GitHub issue, pull request or commit to link Azure Boards items; preceding it with fix, fixes or fixed changes the item's state on merge to the default branch. Azure Repos uses just # and the number.

• Base branch

  Target of a pull request's merge; GitHub uses this branch's copy of CODEOWNERS.

• foreachBatch

  A Structured Streaming output option, called through writeStream.foreachBatch, that hands every micro-batch to your own batch code, which suits MERGE upserts and destinations lacking a streaming writer. Delivery is at least once, so the code should be safe to repeat.

• Fuzzy matching

  Lets Power Query pair up text values that are similar rather than identical, scoring them with Jaccard similarity against a threshold (default 0.80), with options like case-insensitivity. Fuzzy merge relies on it, as does fuzzy grouping in Power Query Online.

• HOLDLOCK

  A table hint that behaves like SERIALIZABLE, keeping key-range locks until the transaction finishes. Adding it to a MERGE upsert prevents two sessions from inserting the same key at once.

• IX lock

  The table lock that data-changing statements (INSERT, UPDATE, DELETE, MERGE, COPY INTO) acquire in a Fabric warehouse. Reads take a Sch-S lock while DDL takes a Sch-M lock that blocks everything else, and current locks can be viewed in sys.dm_tran_locks.

• Join hints

  Hints in SQL that recommend a particular join algorithm, such as BROADCAST, MERGE, SHUFFLE_HASH or SHUFFLE_REPLICATE_NL; they are suggestions only, so the optimiser may choose otherwise.

• NO_CI

  Adding *NO_CI* to a commit message, like [skip azp] or [skip ci], means Azure Pipelines skips CI for that push. Pull request validation of the merge commit is unaffected.