Inserting column between other columns in SQL Server using script

Question

I am trying to alter a table in SQL server with a script. In the past I have always done this kind of thing through a GUI, but now I need to generate a script to do it for customers.

I have an SQL Server database table that is like this:

MyTable
-------
ColA int NOT NULL
ColB int NOT NULL
ColC int NOT NULL
ColD VARCHAR(100)

The primary key is defined across ColA, ColB, and ColC.

I want the SQL script to change the table like so:

MyTable
-------
ColA int NOT NULL
ColB int NOT NULL
ColX int NOT NULL  (new column, default 0 for existing data)
ColC int NOT NULL
ColD VARCHAR(100)

The primary key would now be defined by ColA, ColB, ColX, and ColC.

This is easy to do through SQL Server GUI. But when I have it generate a script from that, it seems unnecessarily complex. Basically, the script creates a temporary table with the new schema, copies all the data, indexes, and constraints from the old table into the temp table, deletes the old table, then renames the new one to the name of the old one. In addition, it has lines like this:

ALTER TABLE dbo.Tmp_MyTable ADD CONSTRAINT
    MyTable21792984_ColC_DF DEFAULT ((0)) FOR ColC

I'm concerned that these random-looking numbers there (i.e. 21792984) will not be the same on all customer database instances. They look like something that the SQL server generates when creating the database that would be unique to each instance.

Is there a more straight-forward way of changing the table through SQL commands? I've looked online but what I've found is mostly basic and/or generic.

Update: From the answers I have received, it looks like the difficulty lies in putting the new column "in between" two columns. I've realized it doesn't really matter what order the columns are in (if I am wrong feel free to leave an answer correcting me). In my case, the change is much simpler if I just add the column to the end of the table, and nothing in the code is relying on the specific column order.

Answer 1

If you are inserting a field into the middle of the table, you essentially have to drop the current table and recreate it which is what sql server is doing.

The random numbers are making sure the constraint has a unique name. If you keep the script and run it on multiple databases, then they will all be the same. If you are going to modify each one through the gui, then yes they will most likely be different.

To modify the primary key, all you need to do is find out the primary key constraint name and drop it. Just add a new constraint defining the primary key. This is assuming you don't have the primary key listed as a foreign key somewhere else.

Answer 2

If you want the column in the middle like that, that's what you have to do. I've found the scripts it generates to be pretty good about only including the neceessary work.

If you were to add a column at the end for instance, you would only need to do:

ALTER TABLE ADD COLUMN ColX int NOT NULL DEFAULT(0)

And you would see this in the script it generates.

As far as altering the primary key, it can be dropped (on all existing columns) and recreated without rewriting the table, but if it's clustered or not you would probably get a different script.

Answer 3

There isn't another way to insert a column in a SQL Server table "in between" existing columns - you need to build a temp table and rebuild the old table. That said, column order shouldn't matter - are you sure that the column needs to be inserted in order?

Likely your best bet is to just use the GUI, script it out, and then change the constraint name to something reasonable within the script. You're right that the numerical constraint name isn't ideal, and it's not a best practice to allow SQL Server to determine your object names.

Answer 4

Take the sample code & modify it. If you want the column to land in a particular place, you'll have to go the temp table redefine/rename route. You can name the indexes and constraints as you like this way, also.

Answer 5

What tool do you use to generate script. I use Red-Gate Sql compare tool, and it works beautifully.

Answer 6

If in your CREATE TABLE you just specified, say, PRIMARY KEY (ColA, ColB, ColC), you were telling SQL Server that you didn't care about the name of this constraint, that you'd never have to refer to it directly -- so SQL Server was fully justified in generating some semi-random-looking unique name for it, like the MyTable21792984_ColC_DF that rightly worries you (though that particular one seems to be a ColC-specific single-column constraint, the same kind of naming will apply to other constraints).

While this issue is probably too late to fix for your current schema, it would help you in the future if you followed the principle of always name your constraints -- as it's generally quite possible that you may need to refer to them in the future, so you want the name to be fully under your control, just like the name for any other schema object (table, column, and so forth). In this case, using a clause like CONSTRAINT PK_MyTable PRIMARY KEY (ColA, ColB, ColC) in the CREATE TABLE would have helped. (If the GUI tools you're using for your DBA tasks don't let you control this kind of things, they're not suitable tools for a DBA: find better ones!-).

Answer 7

It's not a good practice to depend on any sort of "natural" or "inherent" ordering of columns in a database table. All columns should be referenced by name in any officially generated queries to return columns by name in the order specified by the query. If that rule isn't followed, any future schema changes are an absolute nightmare as the system code may need to be changed every time the database schema is updated.

The only annoying thing here would be when running one-off queries using SELECT * FROM ... on production/test/development databases hand-coded by users and having your new column show up on the end of the column list. However, I believe many query tools will let you reorder the columns from a sort of GUI.

Answer 8

I just want to point out why you never ever want to use the GUI to insert a column inthe middle of an existing table. When you do that it, creates a speatare new table, moves the data from the old table, renames the old table, renames the new table to the old tablename and drops the old table. This is bad enough ifyou havea small databaset. In a production world where tables can be quite large, you could be locking the users out of access to the table for several hours. Any database design where the order of columns in the database needs to be rearranged when a new column is added is a database headed for disaster. Becasue there are people who insist on doing this though, it is another reason why Select * is also a problem waiting to happen. You really don't want the state to show up in your zip column in a report because someone rearranged the columns in the table and you relied on select * from the column order.