In this article I am trying to collects some facts related to temporary table and table variable. So let's start about it.
The concepts of the temporary tables, which help developer a great, relax to maintain T-SQL statements. This table is created on run time and can do the all kind of operations that normal table can do. But based on the type of table the scope is limited.
The temp tables are created in the tempdb database. So before starting about temp table, let's take a quick look about tempdb database.
It is a system database global resource that is available to all users. Some facts are mentioned bellow.
1. Temporary use objects that are explicitly created such as, global and local
temporary tables, temporary stored procedure, table variables or cursors.
2. Internal objects created by SQL Server database engine. For example work
tables to store intermediate result for spools or sorting.
3. Row versions that re generated by data modifications transactions in a database
that use read-committed using row versioning isolation or snapshot isolation transaction.
4. Row version that are generated by data modification transactions for feature.
Type of temporary table
Based on behavior and scope the temporary table is divided into two categories.
1. Local Temp Table
2. Global Temp Table
Local temp table
Local temp tables are only available to the current connection for the user and automatically deleted when the user disconnects from instances. It is started with # sign.
CREATE TABLE #tbl_localTemp
Global Temp table
Once the table has been created by a connection, like a permanent table it then available to any user by any connection. It is only be deleted when all connections have been closed. Global temp table name starts with ##.
CREATE TABLE ##tbl_localTemp
Some points we must remember related to temporary table
1. As they are created in a separate database named tempdb, so additional overhead and can causes performance issue.
2. Number of rows and columns need to be minimised as we needed.
Alternate of the temporary table is table variable. Which can do all kinds of operation we can do with the temporary table. It is always useful for less data. If result set returns large amount of data we use the temporary table.
DECLARE @tblvrbl_local TABLE
Difference between Temp table and Table Variable
The main differences are mentioned bellow.
1. Table variable are transaction neutral. They are variables and are not bound to a transaction
2. Temp table behave same as normal table and are bound by transaction.
Hope you like it.
Posted by: MR. JOYDEEP DAS