SQL programming table @talbe, #table and @@table

Dear all,

I would like to know what is the diff between @<table> , #<table> and ##<table>

what I know so far is :
1) @<table> store in memory and it drop automatically once the script who call them finish.
2)#<Table> store in tempdb and we have to drop it manually inside that script.
3) I don't know what ##<table > for ? is it going to drop automatically done the script/sp/function who call it finish ? or we have to drop it manually anyway.


and it seems that SQL server can only parallel execute the data if the data is store in #<table> but not in @<table> ? how about ##<table> ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
#table is a local temporary table and ##table is a global temporary table. These are living in tempdb when created. The local temporary table is only visible in the session in which it was created, while the global temporary table is visible in all sessions.

Both are real tables, so almost all the rules and mechanisms apply to them as to ordinary tables. Especially you can create indices when needed to increase performance. And by using table DDL statements such as ALTER TABLE #table;
The query optimizer treats them as normal tables, cause they have statistics.

@table is a variable. It is also stored in tempdb. But as it is a variable, it is only visible to the batch where you have created it. The @table has no statistics, so the query optimizer uses always a row estimate of 1, which may lead to poor query plans.

In SQL Server 2016, you can also declare indices on @table variables, but only inline in the variable declaration. So no table DDL statements could be used to modify it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"#table is a local temporary table and ##table is a global temporary table"

just like the VB and C# programming's private and global variable ?

"@table is a variable. It is also stored in tempdb. But as it is a variable, it is only visible to the batch where you have created it."
" The local temporary table is only visible in the session in which it was created,"

so in this way, it seems @table and #table function in the same way ?  session here and batch here you mean the same ?

"The @table has no statistics, so the query optimizer uses always a row estimate of 1, which may lead to poor query plans."

so no indexs can be create on @table you mean ?
ste5anSenior DeveloperCommented:
Session means connection to the server. Thus:

1.)
Local temp tables are only visible in one session (connection) for one user. But in all batches.
Global temp tables are visible to all sessions for all users. Also in all batches.

Batch: Consider a script in SSMS. Then the session is the connection hold by SSMS query window. A script like

CREATE TABLE #local ( ID INT );
GO

INSERT INTO #local VALUES ( 1 );
GO

SELECT * FROM #local;
GO

Open in new window


contains three batches separated by the SSMS batch terminator GO. When you open a second SSMS query window and run

SELECT * FROM #local;

Open in new window


then you'll get an error. Cause the second window uses a different connection, thus it's a different session. When you'll try the same with a global temporary table ##global, then you're able to query it in the second window.

2. Batch is the scope:

DECLARE @variable TABLE  ( ID INT );

INSERT INTO @variable VALUES ( 1 );

SELECT * FROM @variable;
GO

SELECT * FROM @variable;
GO

Open in new window


After the GO starts a new batch. Thus the second SELECT in the second batch cannot query @variable, cause there is none defined in this batch.

3. Correct.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

marrowyungSenior Technical architecture (Data)Author Commented:
"Local temp tables are only visible in one session (connection) for one user. But in all batches."

so in script 1, if we keep creating batches inside that session, they can ALL see that #table inside that single session, right?

and so what is the best case we should use each of them and why ?
ste5anSenior DeveloperCommented:
There is no "best case".

You need them when to materialize an intermediate result. Depending on the number of rows and the necessary accessibility you take the appropriate. And you need to test the alternatives for performance. Even when there is a rule of thumb, they don't mean that the query plan will be optimal.

Rule of Thumb: When having many rows, temporary tables perform normally better, because the optimizer can use statistics. Even more you can add additional indices to improve query performance.
marrowyungSenior Technical architecture (Data)Author Commented:
then why someone still need @table is we can't use indic on it ?  and parallelism don't work for this kind of variable ?
ste5anSenior DeveloperCommented:
It's a simple declaration, you don't need to clean it up.
marrowyungSenior Technical architecture (Data)Author Commented:
ok, for parallelism, as table partitioning can do it.

Any suggested link on who this works and any step by step guild on how to create it. convert the existing table to partition one ?
ste5anSenior DeveloperCommented:
Huh? How does your last post fit into the temp table question?
marrowyungSenior Technical architecture (Data)Author Commented:
marrowyungSenior Technical architecture (Data)Author Commented:
Dear sir,

I can see that you are a very good SQL developer, right? are you good on parallel programming ?  are you good on change while loop and cursor to bulk select programming so that I can get ride of while loop and cursor?

and good programming books that bring anyone to/from intermediate to expert ? by what I mean expert they are already architecture !

any link/reference on how to change while loop and cursor to bulk select programming so that I can get ride of while loop and cursor?
ste5anSenior DeveloperCommented:
Experience :)

To get rid of cursors and loops, btw this is of called RBAR (row-by-agonizing-row), you need think in sets.

For example when you have an UPDATE in the loop, then try to JOIN in the cursor tables.

A must-have book: Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan
marrowyungSenior Technical architecture (Data)Author Commented:
"
To get rid of cursors and loops, btw this is of called RBAR (row-by-agonizing-row), you need think in sets."

yeah, this is what I mean, someone coach me this so that I can get ride of RBAR !

"A must-have book: Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan"

oh, I have this in my books list, thanks, but it should not cover everything from getting ride of RBAR and parallel programming, right ?

how about these books by the same author:

1) http://www.amazon.com/gp/product/0735658366?psc=1&redirect=true&ref_=ox_sc_act_title_2&smid=ATVPDKIKX0DER
2) http://www.amazon.com/gp/product/0735685045?psc=1&redirect=true&ref_=ox_sc_act_title_5&smid=ATVPDKIKX0DER

both your suggestion and these books is not going to overlap, right?

if I am not coming from developer background but try to learn more on SQl develepement and from time to time copy script, modify script, which one should I read ?

"To get rid of cursors and loops, btw this is of called RBAR (row-by-agonizing-row), you need think in sets.

For example when you have an UPDATE in the loop, then try to JOIN in the cursor tables.
"

any link for me to reference on how to convert the already while looped and cursored query ?
ste5anSenior DeveloperCommented:
Itzik Ben-Gan's books are all really good. They overlap, but as the focus on different things, this is not a problem or wasted money.

Just search for "t-sql rbar"..
marrowyungSenior Technical architecture (Data)Author Commented:
"Itzik Ben-Gan's books are all really good. They overlap, but as the focus on different things, this is not a problem or wasted money."

it automatically means to me that I really has to buy all, any reading sequence you can suggest for my 3 x books ? I don't want to jump !

"
 Just search for "t-sql rbar".. "

lovely ! or "get ride of t-SQL rbar", right?

I searched this :

http://searchsqlserver.techtarget.com/tip/Avoid-cursors-in-SQL-Server-with-these-methods-to-loop-over-records

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx

but not that complete!

by the way, someone compare while loop and cursor, which one is better from your point of view, can't see why a lot of expertist on web demo keep using cursor !
marrowyungSenior Technical architecture (Data)Author Commented:
other than these 3 x books, any other books you can suggest like how to parallel programming? tuning ?

any good book to coach us on how to do read the execution plan and then quickly found out which index has to be drop/add to speed up everything ?
ste5anSenior DeveloperCommented:
Just start with them. There is one book for beginners:

SQL and Relational Theory - How to Write Accurate SQL Code

from C.J. Date, which gives you insight into the relational theory behind SQL.
marrowyungSenior Technical architecture (Data)Author Commented:
seems the same as this one:

http://www.amazon.com/gp/product/1449316409?psc=1&redirect=true&ref_=ox_sc_act_title_6&smid=ATVPDKIKX0DER

this is already in my list too but ISBN is different !

I heard this one include the data modelling too and is the best one ?
ste5anSenior DeveloperCommented:
Correct. It also shows how relational modelling and SQL querying work hand in hand.
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.