Using table variables and temporary tables

Table variables and temporary tables serve the same basic purpose: to store an intermediate resultset to be used by a subsequent query. Database developers use these to break down complex joined queries that typically are not very efficient.

We have mentioned before how the way a query is written can severely compromise SQL Server's ability to optimize a query efficiently in the little time it has to do it.

This means that a complex T-SQL query can be broken down into simpler T-SQL statements that store intermediate results before being used to join with other tables. Imagine a developer needs to build a query in the AdventureWorks sample database that returns the sales quota data by year for each ...

Get Learn T-SQL Querying now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.