| Author |
Topic |
|
jparker
Posting Yak Master
118 Posts |
 Posted - 2004-12-15 : 11:55:11
|
| If an sql statement has a create table command in it what happens if two concurrent users tried creating a table at the same time? Would it fail or would the second instance of the table get renamed and then the proceeding drop command alter to the new name change? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-15 : 11:57:56
|
it would fail.Go with the flow & have fun! Else fight the flow  |
 |
|
|
jparker
Posting Yak Master
118 Posts |
Posted - 2004-12-15 : 12:01:05
|
| So how would you work round this?i.e. if you needed a stored procedure that required a create temp table command but the computation took a couple of minutes how would you get round the problem of concurrent access to this statement |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-15 : 12:09:12
|
| use a table variable...Go with the flow & have fun! Else fight the flow |
|
|
|
jparker
Posting Yak Master
118 Posts |
Posted - 2004-12-15 : 12:12:16
|
| Sorry do you mind giving an example to clarify what you mean by a "table variable" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-15 : 12:17:39
|
| declare @MyTable table (id int, col1 varchar(20))insert into @MyTable(id, col1)select 1, 'adf' union allselect 2, 'jj65u5' union allselect 55, 'jtrjr' this works in sql server 2000 not in 7.0Go with the flow & have fun! Else fight the flow |
|
|
|
jparker
Posting Yak Master
118 Posts |
Posted - 2004-12-15 : 12:19:33
|
| thank you very muchPresumably you could also do it like this : -create table #Quoted ( job_id int, est_id int, supplier_id int, quoted decimal(10,2), po_id int) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-15 : 12:30:01
|
| yesGo with the flow & have fun! Else fight the flow |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-15 : 13:28:43
|
| A local temp table is only around for the length of the process and is isolated to that process. No other process will se it's instance of that temp table, so no, it will not fail.But use the table variable anyway if your volume of data is not huge.Brett8-) |
|
|
|
|
Create Table