Redian新闻
>
请教sql server temptable # 和 ##
avatar
请教sql server temptable # 和 ### Database - 数据库
n*6
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 ##?
avatar
p*d
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 ##?

avatar
c*d
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
avatar
c*d
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

avatar
n*6
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 -- 如果#就不需要。
但是我不知道两种用法有何利弊。在实际应用中哪些情况下推荐用#,哪些情况下推荐
用##。
avatar
p*d
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)

相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。