hierarchyid GetAncestor returns a logical ancestor path. Its zero-distance result is the original node, while a request beyond the root returns NULL.
SQL SERVER – Concurrency Problems and their Relationship with Isolation Level
What are the four concurrency problems, like lost updates and dirty reads, and how does each one relate to the isolation level you pick in SQL Server?
SQL SERVER – Demo Script – Keeping CPU Busy
A demo script for keeping CPU busy: I wrote this query at an event to simulate high CPU usage, and it pushed CPU from 10% to 80%.
EXISTS: A Row Containing NULL Still Exists
EXISTS answers whether qualifying rows exist. Keep missing values separate from missing rows and preserve the intended correlation.
Sorting Correctly: Collation and ORDER BY
Collation and ORDER BY decide case, accent, and text-number order; choose rules explicitly when one report needs different sorting.
SQL SERVER – Automation Process Good or Ugly
Is an automation process good or ugly? For T-SQL Tuesday I list the good side of automation in SQL Server, like SQL Agent jobs, and ask for your help.
OUTPUT INTO: Record Old and New Values in One Write
OUTPUT INTO records old and new values during a write, while the transaction determines whether the change survives. Test NULL transitions, unchanged values, rollback and target failures before treating captured rows as committed history.