Tags
26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to handle large number of insertions in SQL Server?, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, Spools in Execution Plan?, Spools in SQL Server, sql - How do I avoid functions like UPPER in where clause, SQL - Spools, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, SQL Performance Tuning and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL Server Interview Question - How to handle large number of insertions in SQL Server?, SQL SERVER Interview questions, SQL Server Optimization - Parallel Execution Plans, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Server Query Optimization: Parallel Plans, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?
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
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
Improving my SQL BI Skills 
You must be logged in to post a comment.