Another look at table variables

Becoming overly enthusiastic about a new SQL Server feature can backfire if you don’t do some testing. One example is the table variable introduced with SQL Server 2000. At the time, there was a myth that table variables would always perform better than temp tables with the reasoning that, by definition, variables are stored in memory. It was common knowledge that temp tables are stored in tempdb, but many assumed that a variable would not be. It was easily proven that table variables are stored in tempdb just like temp tables.

I consulted at a couple of shops where developers had fully embraced the use of table variables to store intermediate results in stored procedures. At one customer, table variables were so prevalent that there must have been an entire project devoted to replacing temp tables with table variables in stored procedures regardless of how many rows involved. (There are other, better uses for table variables such as table-valued parameters and use in table-valued functions.)

Why are there some performance issues with table variables? While certain indexes can be included in the table variable definition due to primary keys and unique constraints, there are no distribution statistics on table variables. The optimizer estimates that all table variables have a cardinality of one row which could negatively affect the plan, especially when thousands of rows are involved. Even though indexes could be in place, they don’t matter with just one estimated row.

In 2014, Microsoft added the ability to create memory-optimized table variables. Finally, table variables could truly live in memory, but to do so requires some work on the SQL Server instance. There must be a filegroup in place for MEMORY_OPTIMIZED_DATA, and you must create a user-defined table type for each possible table variable schema. Assuming you have those prerequisites in place, you just need to declare a variable of the type and then populate it with rows. It’s easy if you have the types defined, but that’s going to take some planning!

Finally, in 2019, Microsoft took care of the cardinality issue as part of the Intelligent Query Processing features. This new feature is called “table variable deferred compilation.” Basically, the optimizer adjusts the plan based on the actual table variable row counts. There are still no statistics, but it’s more likely that it will choose a more appropriate plan based on the actual number of rows.

Table variables were enthusiastically embraced by lots of folks as the answer to many performance problems. The reality was quite different. Luckily, Microsoft has improved this feature over time, and it’s worth taking another look.

Kathi Kellenberger

Join the debate, and respond to the editorial on the forums

We'll step through the process of using Flyway Teams to support database branching and merging, where the team split the development effort into isolated, task-based branches, and each branch has its own development database.

Flyway's approach to database migrations is based on strict versioning, but there is a limit to what a single process can do to prevent 'drift'. This article explains how drift can happen, and why you also need source control and external processes that log changes, to prevent it.

Database versioning brings more control to team-based database development and helps avoid many of the errors that often creep into database delivery. This article explains all the requirements of a semantic versioning system for databases, from specifying the format of the version numbers, to deciding where to store them and how to process and compare them.

Matt Gordan is next in the hot seat. We discuss how Matt keeps the lights on while overseeing many data related projects and how his team know the more they are plugged into projects, the better support the can offer.

We've released a minor version of SQL Monitor, v12.1, that includes two exciting new features: ‘Tagging’ and a ‘Current Activity’ page. In a nutshell: tags allow you to record aspects of a server so that you have information at your fingertips, and the Current Activity page directly queries the monitored server when you open it to get the most up-to-date information possible. It’s all part of our commitment to making SQL Monitor the best monitoring tool out there!

Restoring a TDE enabled backup is straightforward but requires some certificate preparation steps

A monster’s worse fear is being found. – Riche...

We had a case over the weekend where our automated...

Hey, this seems harmless. Seriously, don’t SET DEADLOCK_PRIORITY HIGH. Even setting it to low can be a bad idea. I’ve heard people say, “Well, I’ve got a big process...

Welcome to the second recipe of this Extended Even...

In this article, we are going to learn how we can migrate the tables with a foreign key to memory-optimized tables of the SQL database. In my previous article,...

Monitoring SQL Server encryption when there is a SQL Server service restart

In this tip we look at a simple script you can use to rebuild all SQL Server indexes for all specified databases.

In this article we look at how to query and read the SQL Server log files using TSQL to quickly find specific information and return the data as a...

In this article we cover how to run the same command against all SQL Server databases using sp_MSforeachdb along with several examples of how to use it.

In this article we look at 3 different ways to find the TCP IP port that SQL Server is using for an instance.

Performance Considerations | Large Tables | Delete Operations - In this article we look at different ways to most effectively delete large amounts of data from a SQL Server...

I have been in Azure DevOps pipelines a lot recent...

Export Azure SQL Database is a commo...

We recently took a look at creating a MySQL database with AWS RDS. Today, let’s go through the steps of logging in. Creating Our Connection We’ll use MySQL Workbench...

Managing your entire SQL Server estate, on premises, in the cloud or a hybrid, with instant problem diagnosis, intelligent and customizable alerting has never been more vital. Discover how Redgate’s SQL Monitor enables all this from a single pane of glass.

Redgate's Security Lead, Rob Chipperfield, will be joined by a panel of peers to discuss this mindset change of Continuous Compliance Automation, and the impact it can have on the daily operations, and business demands of your organization.

In honor of today, 2/22/2022, I thought I’d shar...

A boxplot—sometimes called a box and whisker plot... ...

Across all major RDBMS products, Primary Key in SQ...

With the recent public preview release of Synapse-...

SIN: Returns the sine of the given angle. https://...

Learn about the differences in SQL Server, Oracle and PostgreSQL on how to use variables for ad hoc queries and stored procedures.

IN and NOT IN subqueries are frequently used in Or...

In this article we look at things you should know to help tune and optimize your SQL Server queries.

In this tip we look at the SQL Server NOLOCK hint and how it impacts query results, both good and bad.

My favourite – and it seems many other people’...

In this article we look at creating a Power BI gro...

When deciding if Power BI is the correct reporting...

In this article we look at how to create Python function and various aspects of creation and use to simplify coding.

A short post this week. While I was helping some friends recently, we experienced a curious thing where as soon as an application was started up, it was immediately...

Most of us have used the REPLACE function several ...

The SQL language is used across many relational da...

If you are just getting started with writing SQL Server queries take a look at this article for examples to select, insert, update and delete data.

Introductions There are many ways to express queries in SQL. How different rewrites perform will largely be a function of: You not doing anything ridiculous Queries having good indexes in place The optimizer...

In this tip we look at a simple query you can use to get a list of parameters for all stored procedures and functions in a database.

Learn how to use the SQL LIKE operator to search for various string patterns within SQL Server data. This covers several examples of how the SQL LIKE statement can...

Learn how to use time series data in SQL Server by using financial security data from Yahoo Finance and Stooq.com.

For SQL beginners, there’s a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in...

In this tip we look at how to write SQL Server stored procedures to handle input parameters, output parameters and return codes.

I recently saw a question about the Azure Data Stu...