Clever Geek Handbook

Create a table if it doesn't exist

Considering the following:

if object_id('MyTable') is null create table MyTable( myColumn int )

Is it not possible for two separate callers to be able to evaluate object_id ('MyTable') as null and therefore tried to create a table.

Obviously one of the two callers in this scenario will fail, but ideally neither caller should terminate but should block and the other should create a table, then the blocked caller will see object_id ('MyTable') as not equal null and continue.

Where can I apply exclusive locking so that I don't block more than is absolutely necessary?

+2


source to share


4 answers


After your initial check, use a catch attempt when creating the table, and if the error is that the table already exists, continue, if not, you will have a big problem.



+2


source


Typically CREATE TABLE

run from installation and installation scripts, it is unreasonable to expect installation scripts to allow parallel installations from separate connections.



I recommend that you use the private application lock obtained at the beginning of the installation and update procedure, see sp_getapplock

.

+1


source


I don't think you should be worried about this.

DDL statements are not executed in a transaction. Also, the second caller will fail if the table has already been created by calling from the first caller.

0


source


I do not allow users to create tables. In general, this is bad practice. If they need to insert data, the table is already there. If you are worried about two people creating the same table, are you also worried about whether their data overlaps? I don't know what your prophet is doing, but if he deos something like delte records, if the table exists and then insert, then you might have strange results if two users were at the same time. In general though, if you need to create an ea table at runtime, this is usually a sign that your design needs work.

0


source






More articles:


All Articles