Question about creating mssql temporary tables...

S

stoymigo

Senior Member
Joined
Dec 11, 2008
Messages
975
Reaction score
26
Hello
I use stored procedures in crystal reports, the sp has code that creates and drops a temporary table.
My question is, say if two users on different pcs create reports at almost the same time will there be a conflict?

I can't test now, because I'm not on a networked environment.
Thanks
 
I would check like this if the table existed

IF OBJECT_ID('tempdb..#tt1') IS NOT NULL
DROP TABLE #tt1
Click to expand...

I would run this at the end of the script of course, after the data manipulation.
 
Sounds like the same table could be dropped by a user while another user is populated. Will cause waits etc. performance issues.

Can you create the table using a name such as sessionid(Oracle term, don't know if your db as a similar)?

It does mean that you need to be able dynamically set the table name in the query as well. I don't know CR can do that.

The reason I don't suggest using the login name is that users can have multiple sessions, but if your env doesn't allow this, then the login name as the table name is an option.
 
Don't use temp tables.
Table variables are a much better option.

When I need to gather data for any kind of query, I first try via a Common Table Expression, otherwise a table variable. Never ever use temp tables anymore.
 
each time a temp table is created, it is assigned a unique ID, even though it is created with the exact same name

e.g.
----------------------------------
CREATE TABLE #Skywalker (
SkywalkerID int
)

select name from tempdb..sysobjects where name like '#Skywalker%'

drop table #Skywalker
----------------------------------
e.g. it could be created as
#Skywalker__________________________________________________________________________________________________________000000000144
and next time its run it could then be created as
#Skywalker__________________________________________________________________________________________________________000000000145



Global temporary tables are a different story, e.g. ##Skywalker, which will then be accessible by others...
 
Last edited:
You won't get a conflict.....

as jxharding says, they're suffixed with a unique identifier in the tempdb.
 
jxharding said:
each time a temp table is created, it is assigned a unique ID, even though it is created with the exact same name

e.g.
----------------------------------
CREATE TABLE #Skywalker (
SkywalkerID int
)

select name from tempdb..sysobjects where name like '#Skywalker%'

drop table #Skywalker
----------------------------------
e.g. it could be created as
#Skywalker__________________________________________________________________________________________________________000000000144
and next time its run it could then be created as
#Skywalker__________________________________________________________________________________________________________000000000145



Global temporary tables are a different story, e.g. ##Skywalker, which will then be accessible by others...
Click to expand...

Thanks for that explanation!
Looks like I don't have to worry
 
Maybe the OP should be reconsidering why he has to create a temp table for a user report? I'm assuming this because if the performance if the query.

Don't know how many users there are, but opening up the possibility of users creating and dropping tables on the fly can't be best practice.

Possibly need to look into some fancy SQL. Google the "with" clause. You add it at the beginning if your select.

With mywithclause as (select col1, col2 from table1 where table1.col3='something'),
anothermywithclause as (select col1, col2 from table2 where table2.col4 ='somethingelse')
Select table9.col1, table9.col2, a.col1 a1, b.col2 b2
From table9, mywithclause a, anothermywithclause b
Where table9.col7 = a.col1
And table9.col9 = b.col2;

In other words, the bits before your main select replace your temp table. This example shows how you can use 2 of the them. Note that you can join to the first one in the second. Note how you reference them in the main select joins. Obviously the joins in each with clause can have the same complex joins as any select.

This is an oracle example, but with clause is SQL-99 compliant and I'm sure recent SQL server versions can do this, with a few tweaks.

Another option, which is often used in Oracle, are inline views, I'm not sure if SQL server can do them.
 
zippy said:
Maybe the OP should be reconsidering why he has to create a temp table for a user report? I'm assuming this because if the performance if the query.

Don't know how many users there are, but opening up the possibility of users creating and dropping tables on the fly can't be best practice.

Possibly need to look into some fancy SQL. Google the "with" clause. You add it at the beginning if your select.

With mywithclause as (select col1, col2 from table1 where table1.col3='something'),
anothermywithclause as (select col1, col2 from table2 where table2.col4 ='somethingelse')
Select table9.col1, table9.col2, a.col1 a1, b.col2 b2
From table9, mywithclause a, anothermywithclause b
Where table9.col7 = a.col1
And table9.col9 = b.col2;

In other words, the bits before your main select replace your temp table. This example shows how you can use 2 of the them. Note that you can join to the first one in the second. Note how you reference them in the main select joins. Obviously the joins in each with clause can have the same complex joins as any select.

This is an oracle example, but with clause is SQL-99 compliant and I'm sure recent SQL server versions can do this, with a few tweaks.

Another option, which is often used in Oracle, are inline views, I'm not sure if SQL server can do them.
Click to expand...

Called a Common Table Expression.
 
zippy said:
Maybe the OP should be reconsidering why he has to create a temp table for a user report? I'm assuming this because if the performance if the query.

Don't know how many users there are, but opening up the possibility of users creating and dropping tables on the fly can't be best practice.

Possibly need to look into some fancy SQL. Google the "with" clause. You add it at the beginning if your select.

With mywithclause as (select col1, col2 from table1 where table1.col3='something'),
anothermywithclause as (select col1, col2 from table2 where table2.col4 ='somethingelse')
Select table9.col1, table9.col2, a.col1 a1, b.col2 b2
From table9, mywithclause a, anothermywithclause b
Where table9.col7 = a.col1
And table9.col9 = b.col2;

In other words, the bits before your main select replace your temp table. This example shows how you can use 2 of the them. Note that you can join to the first one in the second. Note how you reference them in the main select joins. Obviously the joins in each with clause can have the same complex joins as any select.

This is an oracle example, but with clause is SQL-99 compliant and I'm sure recent SQL server versions can do this, with a few tweaks.

Another option, which is often used in Oracle, are inline views, I'm not sure if SQL server can do them.
Click to expand...

I create a temporary table so I don't have to create a physical table, this is not a multi user environment yet, but it's unlikely to ever be a high performance application.
So I'm a junior, and a temporary table solves the problem of not having to create an extra physical table and not having enough sql experience to write one whole query to generate the report.

Thread has been answered brah.
 
stoymigo said:
I create a temporary table so I don't have to create a physical table, this is not a multi user environment yet, but it's unlikely to ever be a high performance application.
So I'm a junior, and a temporary table solves the problem of not having to create an extra physical table and not having enough sql experience to write one whole query to generate the report.

Thread has been answered brah.
Click to expand...

Just trying to help.. Brah
 
stoymigo said:
I create a temporary table so I don't have to create a physical table, this is not a multi user environment yet, but it's unlikely to ever be a high performance application.
So I'm a junior, and a temporary table solves the problem of not having to create an extra physical table and not having enough sql experience to write one whole query to generate the report.

Thread has been answered brah.
Click to expand...

With that attitdue you'll be staying a junior for sometime.
 
If you're worried about that then I'd suggest using a user-defined table type instead, it's within the scope of the stored procedure only, destroy's automatically and can be passed from one stored procedure to another if you have to.

Barring that, lemme learn you something:

#temp table is within the scope of the stored procedure
##temp table is scoped globally

NB the # in front of the temporary tables

Good practice is to always drop the #temp table you're working with after the stored procedure is finished. There shouldn't be any errors with a simple "insert into #temp from table123". This will save you a query to check if the object already exists.

I've never come across a #temp table screwing up for multiple users if they all query at once, you do get a performance hit though, depending on how your query is formatted, especially if it has to go to disc/temp table like you're forcing it to. That is why CTE's might be more beneficial for you in this case just BTW.

Watch out for ##temp tables which is global (one user can change another's data). Way back in 2002/3 when I first started with MSSQL 2000, I thought I was clever by naming them ##temp instead of having to change the actual name in a massive stored proc. Turns out I created more problems than a quick solution.
 
Oh and with an attitude of not trying to better yourself or your skills and doing things the RIGHT way, you'd have more problems down the line that you can cope with.

You sound like every other tool I had to take over for in the past 5 years. They code so ****, that when their normal work and the bugs they need to fix get too much, claim "2 years or more experience" and gets a higher paying job instead. Leaving all those issues to the other guy...
 
AcidRaZor said:
Oh and with an attitude of not trying to better yourself or your skills and doing things the RIGHT way, you'd have more problems down the line that you can cope with.

You sound like every other tool I had to take over for in the past 5 years. They code so ****, that when their normal work and the bugs they need to fix get too much, claim "2 years or more experience" and gets a higher paying job instead. Leaving all those issues to the other guy...
Click to expand...

My low salary doesn't permit me to not improve myself. I know my code is not the best, but I know I'm innovative, recently I created a dynamic importer that allows code-less modifying of whole-file imports or just modifying per extra fields. And this is not just one file to one table but multiple tables..as in crm related tables.
Hopefully you don't jump to thinking I'm arrogant but what I just said, I confirm my code might not be the best, but I like being innovative,my ceo has been happiest with me the last two weeks for the initiative I show.
Also I am studying which has helped me learn a little more how to grasp a system when you don't have some1 spoon feeding you.

Anyway I'll say thanks & sorry to zippy, I can investigate the performance enhancements he told me for my importer, just not what this thread is originally intended for.
 
Yea dude, low salary is a bitch. Keep on learning and implementing the new things, regardless of deadlines or time allotted to the project. That's how I learned and I still push those boundaries.

Also start using the Execution Plan to check your queries, it'll suggest indexes and show you where bottlenecks can occur. I forgot about it and started using it again the other day, got my one query from 15 min down to less than a second (for 1.6 million records across 16 databases)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X