Canard
People still tell me things like “I only put 100 rows in table variables”, and think that’s the only consideration for their use.
There are definitely times when table variables can be better, but 100 rows is meaningless.…
Category: Temporary Tables
Things SQL Server vNext Should Address: Common Table Expression Materialization
Repetition Is Everything
I know what you’re thinking: this is another post that asks for a hint to materialize CTEs.
You’re wrong. I don’t want another hint that I can’t add to queries to solve a problem because the code …
Things SQL Server vNext Should Address: Local Variable Estimates
And Then The World
I’ve written at length about what local variables do to queries, so I’m not going to go into it again here.
What I do want to talk about are better alternatives to what you currently have …
SQL Server Spool Operators Are Just Crappy Temp Tables
But My Tempdb
Using the scenario from yesterday’s post as an example of why you might want to think about rewriting queries with Table Spools in them to use temp tables instead, look how the optimizer chooses a plan with …
What’s Really Different About In-Memory Table Variables In SQL Server?
Kendra, Kendra, Kendra
My dear friend Kendra asked… Okay, look, I might have dreamed this. But I maybe dreamed that she asked what people’s Cost Threshold For Blogging™ is. Meaning, how many times do you have to get asked a …
Tracking Row Changes With Temporal Columns In SQL Server
Extra, Extra
Temporal tables are cool, but keeping all that history can be stressful. Change Tracking adds overhead to every transaction, and requires Snapshot Isolation to be successful. Change Data Capture can also run into problems scanning the transaction log, …
Don’t Be Afraid Of tempdb In SQL Server
It’s There For A Reason
When tuning queries, one pattern I see over and over again is people running crazy-long queries. Maybe they worked well-enough at some point, but over the years they just kept getting slower and slower.
Sometimes …
Making The Most Of Temp Tables Part 4: Batch Mode
Le Big Zoom Zoom
When you have queries that need to process a lot of data, and probably do some aggregations over that lot-of-data, batch mode is usually the thing you want.
Originally introduced to accompany column store indexes, it …
Making The Most Of Temp Tables Part 3: More Opportune Indexes To Make Queries Go Faster
I Know You
You have too many indexes on too many tables already, and the thought of adding more fills you with a dread that has a first, middle, last, and even a confirmation name.
This is another place where …