Temp Table

/*#,## and @ tables are stored int*/

DROP TABLE #TEMP_SINGLE_HASH

CREATE TABLE #TEMP_SINGLE_HASH
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into #TEMP_SINGLE_HASH
SELECT 'Calendar',100,50 UNION
SELECT 'Watch',1200,25 UNION
SELECT 'Shirt',1099,100 UNION
SELECT 'Laptop',45000,50 UNION
SELECT 'Phone',15420,75

select * from #TEMP_SINGLE_HASH

/*
This table stored in database disk storage; This scope only in this window

Try to run this (select * from #TEMP_SINGLE_HASH) in another window

you will get,

Msg 208, Level 16, State 0, Line 1
Invalid object name '#TEMP_SINGLE_HASH'.
*/

/*
But moreover, this table like normal physical table.. So u can add primary,uniquey key, and non-clustered index

So we can perform large data with this #table
*/

/***********************************************************************************************************************************/
DROP TABLE ##TEMP_SINGLE_HASH

CREATE TABLE ##TEMP_SINGLE_HASH
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into ##TEMP_SINGLE_HASH
SELECT 'Calendar',100,50 UNION
SELECT 'Watch',1200,25 UNION
SELECT 'Shirt',1099,100 UNION
SELECT 'Laptop',45000,50 UNION
SELECT 'Phone',15420,75


/*## is the Global temp table.. any user can use it.

Try to run this (select * from #TEMP_SINGLE_HASH) in another window
--Query will run

This scope lost until the session is closed

Close this window
Try to run this (select * from #TEMP_SINGLE_HASH) in another window'

Msg 208, Level 16, State 1, Line 1
Invalid object name '##TEMP_SINGLE_HASH'.

Like #table u can add primary,uniquey key, and non-clustered index

*/

/***********************************************************************************************************************************/


/*This is like variable; you need to run all this at same time*/

/*This table is variable; so its stored in memory*/

DECLARE @TEMP_SINGLE_HASH TABLE
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into @TEMP_SINGLE_HASH
SELECT 'Calendar',100,50 UNION
SELECT 'Watch',1200,25 UNION
SELECT 'Shirt',1099,100 UNION
SELECT 'Laptop',45000,50 UNION
SELECT 'Phone',15420,75


SELECT * FROM @TEMP_SINGLE_HASH

/**Check  this*/

DECLARE @TEMP_SINGLE_HASH TABLE
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into @TEMP_SINGLE_HASH
SELECT 'Calendar',100,50

begin tran
delete from @TEMP_SINGLE_HASH
rollback


SELECT * FROM @TEMP_SINGLE_HASH

/*Rollback not happen, transaction will not affect this.. because it stored in memory*/

DROP TABLE #TEMP_SINGLE_HASH

CREATE TABLE #TEMP_SINGLE_HASH
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into #TEMP_SINGLE_HASH
SELECT 'Calendar',100,50

begin tran
delete from #TEMP_SINGLE_HASH
rollback

select * from #TEMP_SINGLE_HASH


/*Rollback happen, transaction will affect this.. because it stored in disk storage*/

/*Like #table u can add primary,uniquey key, but u cant add non-clustered index*/
/*
So use # for larger process
and @ for smaller process
*/