Many temporary objects are employed in the day-to-day transactions of medium or large OLTP databases. These objects can be temporary tables, temporary variables, session variables, temporary views (common table expressions), and so on.
Let’s see what these objects do.
Temporary tables, as the name implies, are used to temporarily store data. These tables can be used for CRUD (Create, Read, Update, and Delete), join, and other actions similar to regular database tables. This means that temporary tables can function similarly to physical tables in a variety of ways, giving us greater flexibility. In these tables, for example, we can define constraints, indexes, and statistics. Temporary tables are useful for storing instantaneous result sets that are accessed repeatedly.
Temporary tables are deleted when the session that created them ends, or they can be explicitly deleted by users.
Table variables, like temp tables, are used to temporarily store data. This functions as a variable and is only present for a specific batch of query execution. When it comes out of the batch, it is dropped.
The scope of Table Variables is confined to the current batch and Stored Procedure, whereas the scope of a Temp Table is much broader.
You can build a temp table with SELECT INTO, which is faster to write and may let you deal with changing datatypes over time because you don’t need to describe your temp table structure ahead of time. Table variables must be defined in the same way that regular tables are. The data types you define must correspond to the values you insert.
Temporary tables can use CREATE INDEX, but table variables cannot. Temporary tables are a fantastic solution if you have a huge amount of data that will be faster accessed by index. Using Table Variables within user-defined functions expands the use of those functions. If you’re writing a function, use table variables over temp tables unless you have a strong reason not to.
Both temporary tables and table variables are stored in tempDB.
A session variable is a named memory variable that may be accessed using SQL commands. A user-defined session variable is accessible to any active SQL query running on the database management where it was defined. A user-defined session variable has a value that is unique to that session and is associated with that session.
Temporary View (Common Table Expression):
A common table expression (CTE) is a named temporary result set that may be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used as part of a SELECT query in a CREATE VIEW.
CTEs, like database views and derived tables, make it easier to develop and maintain complex queries by increasing readability and simplifying them. This decrease in complexity is accomplished by dissecting normally complex queries into simple components that can be used and reused in recreating the query.
When you need to refer to a derived table many times in a single query, when you don’t want to create a view in the database, or when you want to execute the same calculation across multiple query components, you’ll utilize CTE. Read more about CTE.
Hope you find this article helpful.
For examples and use case scenarios please check the next articles.