Development

This week, we’re all about foreign keys. Yesterday’s post covered scripts to set up the Stack Overflow database to get ready, eliminating data that would violate FK relationships. You Had Two Jobs! Let’s say I wanted to implement Foreign Keys to do two things If a user deletes their account, all of their badges, comments, and…

In A Foreign Key, In A Foreign Table Much of what people want from foreign keys, like referential integrity and join elimination, are only as guaranteed as much as SQL Server can trust your constraints (and even then…). The same goes for check constraints, too. Thankfully, things like Primary Keys and Unique Constraints are sort…

Red Skies At Night I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes there’s a decent reason, something like:…

SQL Server automatically chooses when to divide your query’s work across multiple CPU cores. It makes that decision based on your query’s cost. To see it, let’s throw 1,000,000 tiny rows in a table: Transact-SQL CREATE TABLE dbo.Timeless(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(20)); INSERT INTO dbo.Timeless (Stuffing) SELECT TOP 1000000 'Stuff' FROM sys.all_columns…

What Is Skewed Parallelism? When queries go parallel, some assumptions get made: There’s enough work to keep multiple threads busy Each thread will get an equal amount of work to do The ‘equal amount of work’ part is particularly important, because in a parallel plan, each thread gets an equal share of memory up front.…

Way Back When I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name — query_plan_profile. There are currently some rather unfortunate limitations to the event in the GUI. Right now, there’s no query plan tab so you…

The preview of the next version of SQL Server Management Studio is out, and it has a radical improvement to query plans that will shock and amaze you. It’s best to just show you: Estimated vs actual rows You can see the estimated and actual number of rows right there on the query plan just…