Temp tables and Primary Keys
-
I have a couple of stored procedures in which I am building a couple of temporary tables. (A table variable won't work because of the number of records that could possibly be included.) I would like to add a primary key to one of the temp tables for the select query at the end of my stored procedure that returns my data.
My question is, if I create a Primary Key, will I get an error if two people happen to be running this at the same time. I know I can't create two permanent primary keys with the same name but I don't know how this applies to temporary tables.
The chances that two people are going to be hitting this at the exact same time are pretty low but it can, and will happen. I am having a problem trying to get a setup where I can test this. Because of the uncertainty of time, I can't make it happen at the exact same time.
If this is a problem, does anyone have any suggestions on how to get around it?
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
temporary tables, assuming this isn't a ##mytable and it a #mytable, are unique to each person. Even if you create the same name, SQL server will change the name internally to #mytable_@%fsdsfeww, or something like that. so there won't be a conflict.
If this is a "permanent" table that you are using in a temporary way, i.e., filling with data and then erasing when the process is done, you can add a SPID column to make it unique for each person.
-
Create the PK in line with the create table statement.
create table #tmpMytable (col_id integer not null primary key
, col2 datetime not null default getdate()
, .... )
or
create table #tmpMytable2 (col_id1 integer not null default 0
, col_id2 datetime not null default getdate()
, col2 varchar(128) not null default ''
, .... ,
PRIMARY KEY CLUSTERED
(
col_id1
, col_id2
 
)This way sqlserver will determine the pk-index-name random and you will not have the name-collision problems.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply