29 Dec 2017

What is temporary table in sql server


Q. What are temporary tables? when to use them?

Ans. Temporary tables are used to store any data on a temporary basis. in Tempdb.
used to perform data calculations, data formatting operations for reporting.

create table #table1
(
col1 int,
col2 int,
col3 int
)  -- Local temporary table
insert into #table1 values (10,10,10), (20,20,20)

select * from #table1


Local temp tables:- Local temp tables are auto removed from temp-db once you close the session.and local temp tables are represent by single # symbol.

-session means this query windows

create table ##table1
(
 col1 int,
 col2 int,
 col3 int
)  -- global temporary table

insert into ##table1 values (10,10,10), (20,20,20)

select * from ##table1


Global temp tables:- global temp tables are auto removed from temp-db once you close the connection.and global temp tables are represent by ## symbol.


connection means server level authenticated entry to the database.
             
ssms >> server name >> connect close ssms: connection close


for better reuse: permenant tables are recommended.

for faster performance: temp tables are recommended.




No comments:
Write comments

Popular Posts

Popular Posts

Translate

Total Pageviews