[RESOLVED] When do I USE # AND WHEN ## for TEMPORARY Tables?

[eranfox]

Hello Everybody,
Small Question about Temporary Tables:
I Know that Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
But i just dont understand when and where to use them ->local or global?

Thank you all in advance,
ERAN

PS,SHANA TOVA Everybody (Happy new year in hebrew)

[szlamany]

Your signature says SQL - is this MS SQL or ACCESS you are asking about?

[eranfox]

Hello szlamany,
Thank you for your reply,
Just for the knowledge...
The SQL in my signature stands for: ANSI SQL / T-SQL for MS-SQLServer / GQBE for MS-Access / Embeddedd SQL /ETC...

Well i got the answer from BOL, so thanks anyway...
If i wont find a new job i'll start losing my knowledge in SQLServer...

temporary tables
Local temporary tables
The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.

Global temporary tables
The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.

SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends.
Temporary procedures named with # and ## can be created by any user. When the procedure is created, the owner of the local procedure is the only one who can use it. Permission to execute a local temporary procedure cannot be granted for other users. If a global temporary procedure is created, all users can access it; permissions cannot be revoked explicitly. Explicitly creating a temporary procedure in tempdb (naming without a number sign) can be performed only by those with explicit CREATE PROCEDURE permission in the tempdb database. Permission can be granted and revoked from these procedures

Thanks Again,
ERAN

[szlamany]

Hello szlamany,
Thank you for your reply,
Just for the knowledge...
The SQL in my signature stands for: ANSI SQL / T-SQL for MS-SQLServer / GQBE for MS-Access / Embeddedd SQL /ETC...

Well i got the answer from BOL, so thanks anyway...
If i wont find a new job i'll start losing my knowledge in SQLServer...


Thanks Again,
ERAN

Well...

The reason I asked is that MS SQL Server also offers table variables.

For example:

Declare @TblVar Table (Col1 int, Col2 datetime, Col3 varchar(3))

With that said, since we do all our SQL in STORED PROCEDURES, we find that we never create temporary tables, which are slower then table variables.

The only time a TEMP TABLE is required to be used is when we want to use it with a RESULTSET of data from another STORED PROCEDURE.

Insert into #TempTable Exec SomeSProc - this works

Insert into @TableVar Exec SomeSProc - this doesn't work (yet - hopefully in future SQL versions it will.

[eranfox]

Hello Again szlamany,
Well I know this table variables,
So when i went to an interview last week i didnt understand why they insisted on having knowledge in Temporary tables...

Go figure!

ERAN