Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


Today I give you more information about table variables in SQL Server (http://msdn.microsoft.com/en-us/library/ms175010.aspx) and their statistics. Check out the table below-

Pawan Khowal - Table Variable Statistics

Pawan Khowal – Table Variable Statistics

Table variables do not have statistics. Using a table variable you can avoids the recompiles, possibly speeding up your processes. That’s regardless of the number of rows.

But, if you are then using a JOIN or a WHERE clause on a table variable, they have no statistics, so the optimizer will assume a set number of rows (1 for all versions prior to 2014 and 100 for 2014 and 2016). This is fine if you have only a few rows, but if you have more than the set number, it can lead to really bad performance caused by bad choices by the optimizer.

Related Posts on Table Variables are –

https://msbiskills.com/2015/07/09/table-variable-estimated-no-of-rows-1-no-statistics/

https://msbiskills.com/2015/06/12/which-one-is-better-temp-table-or-table-variable/

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com

Advertisements