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.
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.
Example:
CREATE TABLE #tbl_localTemp
(sRoll Int,
sName Varchar(50),
sClass Int)
GO
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 ##.
Example:
CREATE TABLE ##tbl_localTemp
(sRoll Int,
sName Varchar(50),
sClass Int)
GO
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.
Table variable
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.
Example:
DECLARE @tblvrbl_local TABLE
(sRoll Int,
sName Varchar(50),
sClass Int)
GO
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
we can not create index on table variable unlike temporary table.
ReplyDeleteDECLARE @tblvrbl_local TABLE
Delete(sRoll Int primary key,
sName Varchar(50),
sClass Int)
GO
when you create a primary key you get an index
Why cant we use table variable for large data or datasets? Any good reason? And if there is a limit then what is the max number of size (in bytes) which table variable supports?
ReplyDeletethese steps are easy for understand
ReplyDeletedotnet training in chennai
thanks for sharing this valuable post, you said this table can be created during the run-time... actually i have need to create viz this format
ReplyDeleteDOT NET Training Institutes in Chennai
Nice blog, here I had an opportunity to learn something new in my interested domain. I have an expectation about your future post so please keep updates.
SAP PP Training In Chennai
Thanks for your wonderful post.It is really very helpful for us and I have gathered some important information from this blog.If anyone wants to get Dot Net Training Chennai reach FITA, rated as No.1 Dot Net Training Institutes in Chennai.
ReplyDeleteThanks for sharing this valuable information to our vision.
ReplyDeleteccna course in Chennai
Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.Angular training in chennai
ReplyDelete
ReplyDeleteYour blog is really awesome. Thank you for your sharing this informative blog. Recently I did PHP course at a leading academy. If you are looking for best PHP Training Institute in Chennai visit FITA IT training academy which offer real time PHP Training in Chennai.
Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me..I get a lot of great information from this blog. Thank you for your sharing this informative blog. Mysql Training in chennai | Mysql Training chennai | Mysql course in chennai | Mysql course chennai
ReplyDeleteParis airport transfer - Parisairportransfer is very common in Paris that provides facilities to both the businessmen and the tourists. We provide airport transfers from London to any airport in London and also cruise transfer services at very affordable price to our valuable clients.
ReplyDeleteParis taxi
Paris airport shuttle
paris hotel transfer
paris airport transfer
paris shuttle
paris car service
paris airport service
disneyland paris transfer
paris airport transportation
beauvais airport transfer
taxi beauvais airport
taxi cdg airport
taxi orly airport
The information's about Technically and Non Technicaly both are Really useful...Keep posting
ReplyDeleteJava training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery
Great Information. Thanks for Sharing
ReplyDeletesql server hosting
kralbet
ReplyDeletebetpark
tipobet
slot siteleri
kibris bahis siteleri
poker siteleri
bonus veren siteler
mobil ödeme bahis
betmatik
İYO
شركة صيانة افران بالاحساء l89a4JOnDg
ReplyDeleteشركة تسليك مجاري بالاحساء WZQPtgjQpv
ReplyDeleteشركة تنظيف بالاحساء j8S38WzR6p
ReplyDeleteشركة مكافحة حشرات في دبي n2YMMezaKD
ReplyDelete