Move a hierarchyid subtree while keeping stable node identifiers. Check the whole branch, repeated requests, missing endpoints and cyclic destinations.
SQL SERVER – Create Check Constraint on Column
A junior developer wrote a trigger to block invalid data. A Check Constraint is simpler. My example limits PostalCode length in AdventureWorks and tests it.
SQL SERVER – Create Default Constraint Over Table Column
A DEFAULT constraint supplies a value when an INSERT omits the column. I test omitted values separately from explicit NULL.
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database – Part 2
Joffery improved my script to find tables with foreign key constraint in a database, adding the ON UPDATE and ON DELETE rules and a little ordering.
Drawing a Diagram of an Existing Database
A diagram of an existing database starts with real foreign keys, uses SSMS to reveal relationships, and records the important paths a picture misses.
SQL SERVER – Generate Foreign Key Scripts For Database
Madhaiyan Seenivasan’s stored procedure lets you generate foreign key scripts for a whole database, handy when you drop all foreign keys and add them back.
SQL SERVER – Add Column With Default Column Constraint to Table
Add a NOT NULL column with a named default Column Constraint in one ALTER TABLE statement. I always name constraints instead of letting SQL Server pick one.