Menu
Why are temporary tables faster than table variables for joins in SQL Server?
NOTE: In both scenarios the tables have PK, and the tables are joined with other "physical" tables through the PK.
316
So table variable is faster then temporary table. ⇒ Temporary tables are allowed CREATE INDEXes whereas, Table variables aren't allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.
Advantages of Temporary Tables You can create a temporary table and insert, delete and update its records without worrying about whether you have sufficient rights to change data in permanent tables, or whether you might be accidentally doing so.
This biggest difference is that a CTE can only be used in the current query scope whereas a temporary table or table variable can exist for the entire duration of the session allowing you to perform many different DML operations against them.
As its name indicates, temporary tables are used to store data temporarily and they can perform CRUD (Create, Read, Update, and Delete), join, and some other operations like the persistent database tables.
Both are persisted in Tempdb; however, performance problems come into play because the optimizer does not maintain statistics on table variables. This is problematic because the optimizer is ALWAYS going to assume there is 1 row in your table variable. Obviously this can really screw up a query plan, especially when there are lot of rows in your table variable. I wouldn't use a table variable to store anything more than a 1000 or so rows; otherwise, the performance could be unpredictable.
188
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
