Towering Queries

Temporal tables are awesome. But they're kind of a pain to work with during those initial stages of development when you're constantly dropping and re-creating objects whole. The process of dropping a temporal table isn't super complicated, just: Disable versioning on the main table Drop the main table Drop the history table To that end, I wrote this stored procedure, marked as a system object (using sp_msmarksystemobject) which you can build which will do those three tasks for you automatically. Even if you don't choose to use the full script, you can still de-construct it for the logic. use master go set nocount on go /***************************** PROC: dbo.sp_droptemporaltable create table Util.dbo.Test ( ID int primary key clustered, StartDate datetime2 generated always as row start, EndDate datetime2 generated always as row end, period for system_time (StartDate, EndDate) ) with (system_versioning = on (history_table = dbo.Test_History)) exec Uti...

There seems to be a lot of confusion around the differences between  temp tables and table variables (in the classic sense; I'm not talking about in memory table types or anything here). Some people say you should only use table variables. Some people say you should only use temp tables. Most people caution a more nuanced approach. Here I want to cover what I think are the biggest issues in an attempt to shed some light on this surprisingly tricky topic. Statistics This is usually the most important factor when considering which one to use. It boils down to two facts, and everything else flows from these: Temp Tables maintain statistics, Table Variables do not. Table Variables Because table variables maintain no statistics, the Query Optimizer always assumes the contain exactly one row. As a result, any joins against a table variable, unless explicitly told to behave otherwise will (probably) always be a nested loop join. As long as the number of rows in the table varia...

Momma sed don't name constraints on temp tables. But why? How SQL Makes Temp Tables Globally Unique   When you first build a temp table, SQL stores the definition of that temp table away  and every subsequent time you create it, it's actually a clone of that one, original table definition. To allow each individual connection to create a clone of the table, it has to do something about the name so that it won't collide with itself. Let's see what that looks like if object_id ( 'tempdb.dbo.#test' ) is not null drop table #test create table #test (     Id int primary key clustered ) Here, I've created a pretty basic temp table, with an unnamed, primary key on the table. Lets look at the meta data in information_schema (you can use system catalogs also, but for illustration purposes, it's a little easier for me to use information_schema here).     select table_name     from tempdb . information_s...