n********6
发帖数: 1511 | 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*******d
发帖数: 359 | 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*****d
发帖数: 6045 | 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*****d
发帖数: 6045 | 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********6
发帖数: 1511 | 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*******d
发帖数: 359 | 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)
|
|
