#temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

#temp table

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-26 : 22:28:12

Hi guys, i had been told that whenever 2 user create #temp table with the same name will cause error, is there any way to prevent this happen?will table variable outcome this problem?or should i use global temp table?

Hope can help...but advise to wait pros with confirmation...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-26 : 22:38:34

not true. every user can create #temp with same name. it is scoped to the user session.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-26 : 22:53:37

...............................................what the...
ok
...
now i been told for the first time that...the whole company will only share 1 USER!!!
how do i handle if 2 people log into the server using same user and create the same temptable?

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-26 : 22:59:17

The temp table is by session / connection not by user.

Just try to open 100 query window and create temp table of the same name

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-26 : 23:08:41

sifu...means that when 2 people execute the sto-pro at the same time using same user id/pass, no error will be detected?
my situation is
1)everybody remote desktop to the server(not sql server 05).
2)click on the application(which run the same connection string in the application because executing the application in the server)
3)execute report(in report create tmptable for cursor)<-- will here error appear?

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-26 : 23:23:08

no error. The cause every user will be on separate connections

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 00:08:02

hmmm i tried with 500k rows of record, it seems to be 2 people connecting to 1 sql server with 1 connection string are not allowed with 1 temptable name

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 00:16:46

sifu....can u help me create 1 DML with 100000 record to show different speeds of table variable + temp table...i seems to be showing same result =.=""

Hope can help...but advise to wait pros with confirmation...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-27 : 00:31:37

it's something else. post the error msg you're getting

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 00:35:38

quote:
Originally posted by waterduck

hmmm i tried with 500k rows of record, it seems to be 2 people connecting to 1 sql server with 1 connection string are not allowed with 1 temptable name

Hope can help...but advise to wait pros with confirmation...

are you using local temp table (eg #temp) or global temp table (eg ##temp) ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 00:37:23

quote:
Originally posted by waterduck

sifu....can u help me create 1 DML with 100000 record to show different speeds of table variable + temp table...i seems to be showing same result =.=""

Hope can help...but advise to wait pros with confirmation...

use table variable when the number of records are small else use temp table.

You can use F_TABLE_NUMBER_RANGE to generate the number of records required

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 02:13:04

[code]CREATE TABLE #Tempfun
(
a INT PRIMARY KEY,
b CHAR(1)
)
DECLARE @Tempfun TABLE
(
a INT PRIMARY KEY,
b CHAR(1)
)

DECLARE @c INT SET @c = 0

WHILE @c < 100000
BEGIN
if @c > 1000 and @c < 2000
BEGIN
INSERT INTO @Tempfun VALUES (@c, 'A')
INSERT INTO #Tempfun VALUES (@c, 'A')
END
if @c > 2000 and @c < 3000
BEGIN
INSERT INTO @Tempfun VALUES (@c, 'B')
INSERT INTO #Tempfun VALUES (@c, 'B')
END
if @c > 4000 and @c < 5000
BEGIN
INSERT INTO @Tempfun VALUES (@c, 'C')
INSERT INTO #Tempfun VALUES (@c, 'C')
END
if @c > 6000 and @c < 7000
BEGIN
INSERT INTO @Tempfun VALUES (@c, 'D')
INSERT INTO #Tempfun VALUES (@c, 'D')
END
if @c > 8000 and @c < 9000
BEGIN
INSERT INTO @Tempfun VALUES (@c, 'E')
INSERT INTO #Tempfun VALUES (@c, 'E')
END
if @c > 10000 and @c < 9000
BEGIN
INSERT INTO @Tempfun VALUES (@c, 'F')
INSERT INTO #Tempfun VALUES (@c, 'F')
END
SET @c = @c + 1
END

SELECT * FROM @Tempfun WHERE b = 'B' and a < 5000
SELECT * FROM #Tempfun WHERE b = 'B' and a < 5000

DROP TABLE #Tempfun [/code]
i try with this...but both seems to be same...

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources