From the course: SQL Server Performance for Developers

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Table variables

Table variables - SQL Server Tutorial

From the course: SQL Server Performance for Developers

Start my 1-month free trial

Table variables

“

- [Instructor] One of the more controversial topics around temporary objects is the use of table variables versus the use of temporary tables. While both of these options use TempDB as a storage mechanism for their data, they do have some different considerations, especially around the optimizer and there are some myths around table variables. One of which is that table variable are exclusively in memory objects and don't go to disk. Which that is not true. I have to say, just like everything else in SQL Server, there's a little bit of a caveat because in SQL Server 2014, when in memory LTP was introduced we do have the ability to make a specifically in memory table variable. However, if we don't do that, call in out specifically as in memory, it will go to disk in TempDB. That's just something that's part of this. Table variables, in general, are best used when you know you're always going to have a small set of rows. And when I say small I mean less than a hundred rows. And you want…

Contents