Just Drop It

I decided to try the free SQL Azure trial so that I could be conversant in the environment. I migrated an old personal project to the platform so that I had an actual data set. I chose the 1GB Web database as my testing environment. My understanding is that the current differentiation between Web and Business is database sizing. However, this doesn’t eliminate the probability of some future feature differentiation.

Once I set up the basics, it was time to load the data. I encountered an interesting issue.  Running some variation of the following code:

CREATE TABLE test_table (
test_table_id INTEGER NOT NULL,
test_table_value VARCHAR(255)
);
INSERT INTO test_table (test_table_value) VALUES (1,'Test1');

I received the following error:

Msg 40054, Level 16, State 1, Line 6
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Good, bad, or somewhere in between, I usually create my primary key and clustered index after I’ve inserted data, but oh well, let’s try again:

CREATE TABLE test_table (
test_table_id INTEGER NOT NULL,
test_table_value VARCHAR(255),
CONSTRAINT pk_test_table_tt_id PRIMARY KEY CLUSTERED (test_table_id)
);
INSERT INTO test_table (test_table_value) VALUES (1,'Test1');

Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘test_table’ in the database.

Bummer. Apparently tables without clustered indexes are allowed in this version – they’re just not allowed to have data. Let’s clean up the code a bit:

IF OBJECT_ID('test_table') IS NOT NULL
    DROP TABLE test_table;
CREATE TABLE test_table (
test_table_id INTEGER NOT NULL,
test_table_value VARCHAR(255),
CONSTRAINT pk_test_table_tt_id PRIMARY KEY CLUSTERED (test_table_id)
);
INSERT INTO test_table (test_table_id,test_table_value) VALUES (1,'Test1');

(1 row(s) affected)

Perfect. However, due to some discoveries a bit downstream, I need to move away from a surrogate key toward a composite key. No problem, right? First I’ll add the new columns for the composite, Then I’ll drop my key, then I’ll drop my column, then I’ll add a new clustered primary key constraint on the composite columns.

(At this point it is important to note that my keys are coming from other tables since the original data that I had was denormalized).

ALTER TABLE test_table ADD [test_table_keycol_1] INTEGER NOT NULL DEFAULT 1;
ALTER TABLE test_table ADD [test_table_keycol_2] INTEGER NOT NULL DEFAULT 1;

and now to drop the primary key on the table:

ALTER TABLE test_table DROP CONSTRAINT pk_test_table_tt_id;

Msg 40054, Level 16, State 2, Line 1
Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

Msg 3621, Level 0, State 0, Line 1
The statement has been terminated.

Why hello old friend, we meet again. At this point, I have two options: create a new table with the correct constraints, insert the requisite data, drop the old table, and rename the new table to the name of the old table, or I could just reload the data properly. Because I am an experimenter, I chose both.

The first option was pretty straightforward and worked just fine. However, to execute the second option, I had to follow the steps below:

TRUNCATE TABLE test_table;
ALTER TABLE test_table DROP CONSTRAINT pk_test_table_tt_id;
ALTER TABLE test_table DROP COLUMN test_table_id;
ALTER TABLE test_table ADD CONSTRAINT pk_test_table_keycol1_keycol2
    PRIMARY KEY CLUSTERED (test_table_keycol_1,test_table_keycol_2);

Then I had to reload the requisite data.

The takeaway from all of this? It seems that SQL Azure only enforces these version-specific limitations when DML is executed. It will allow DDL to complete that will not be functional when DML is executed against the structures that it creates unless the version-specific requirements are already considered.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s