请教sql server temptable # 和 ## - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

请教sql server temptable # 和 ##

请教sql server temptable # 和 ### Database - 数据库

n*62008-10-15 07:10

1 楼

Both can be used for temp table as a staging table. In user created stored
procedures, when and why do you use #table and ##table? What do you consider
in making a choice?
In my understanding,
##table exists until the server restart.
#table exists within the life cycle of the stored procedure.
Since in the end of the stored procedure the staging table (# and/or ##)
will be dropped, what makes the difference in choosing # or ##？

p*d2008-10-15 07:10

2 楼

好像#是local,##是global.
我也没用过##.不知道啥时候用.有人讲讲?

consider

【在 n********6 的大作中提到】

: Both can be used for temp table as a staging table. In user created stored
: procedures, when and why do you use #table and ##table? What do you consider
: in making a choice?
: In my understanding,
: ##table exists until the server restart.
: #table exists within the life cycle of the stored procedure.
: Since in the end of the stored procedure the staging table (# and/or ##)
: will be dropped, what makes the difference in choosing # or ##？

c*d2008-10-15 07:10

3 楼

copy from sql doc
The two types of temporary tables, local and global, differ from each other
in their names, their visibility, and their availability.
Local temporary tables have a single number sign (#) as the first character
of their names; they are visible only to the current connection for the user
; and they are deleted when the user disconnects from instances of Microsoft
® SQL Server™ 2000.
Global temporary tables have two number signs (##) as the first characters
of their nam

c*d2008-10-15 07:10

4 楼

对于local temp table很好理解
对于global temp table,可以被所有的用户访问没问题
但是这句话"they are deleted when all users referencing the table disconnect
from SQL Server",我觉得有问题
我做的试验(1,2...6,7是步骤)
1. A session
create table ##demo
2. B session (could be same or different user from A)
select * from ##test -- table exists
3. C session (could be same or different user from A)
insert into ##test -- table exists
4. B session
select * from ##test -- table exists and could see new row
5. close A session
6. B session
select * fr

【在 c*****d 的大作中提到】

: copy from sql doc
: The two types of temporary tables, local and global, differ from each other
: in their names, their visibility, and their availability.
: Local temporary tables have a single number sign (#) as the first character
: of their names; they are visible only to the current connection for the user
: ; and they are deleted when the user disconnects from instances of Microsoft
: ® SQL Server™ 2000.
: Global temporary tables have two number signs (##) as the first characters
: of their nam

n*62008-10-15 07:10

5 楼

我都用过，用于stored procedure，然后作为scheduled job。都是按照以下步骤：
IF OBJECT_ID ('tempdb..##table')>0 -- 如果#就不需要。
DROP TABLE ##table -- 如果#就不需要。
step1: load data to # or ##,
step2: clean/process,
step3: load to final table/destination.
step4: drop ##table -- 如果#就不需要。
但是我不知道两种用法有何利弊。在实际应用中哪些情况下推荐用#，哪些情况下推荐
用##。

p*d2008-10-15 07:10

6 楼

我是能不用global就不用，有人讲讲打个比方啥时候该用这个。

disconnect

【在 c*****d 的大作中提到】

: 对于local temp table很好理解
: 对于global temp table,可以被所有的用户访问没问题
: 但是这句话"they are deleted when all users referencing the table disconnect
: from SQL Server",我觉得有问题
: 我做的试验(1,2...6,7是步骤)
: 1. A session
: create table ##demo
: 2. B session (could be same or different user from A)
: select * from ##test -- table exists
: 3. C session (could be same or different user from A)