T-SQL Add Column In Specific Order

Question

Im a bit new to T-SQL, Coming from a MySQL background Im still adapting to the different nuances in the syntax.

Im looking to add a new column AFTER a specific one. I've found out that AFTER is a valid keyword but I don't think it's the right one for the job.

ALTER TABLE [dbo].[InvStockStatus]
ADD [Abbreviation] [nvarchar](32) DEFAULT '' NOT NULL ;

This is my current query, which works well, except it adds the field at the end of the Table, Id prefer to add it after [Name]. What's the syntax im looking for to represent this?

Solution 1

You can't do it like that

for example if you have a table like this

create table TestTable(id1 int,id3 int)

and you want to add another column id2 between id1 and id3 then here is what SQL Server does behind the scene if you use the designer

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestTable
    (
    id1 int NULL,
    id2 int NULL,
    id3 int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestTable SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
     EXEC('INSERT INTO dbo.Tmp_TestTable (id1, id3)
        SELECT id1, id3 FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestTable
GO
EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
COMMIT

As you can see if you have a lot of data this can be problematic, why does it matter where the column is located? just use

select col1,col2,col3 from table

Solution 2

While from a functional database perspective it is correct that the tuples can appear in any order.

However a database does not exist inside a vacuum. There is always a human that will want to read the table schema (dbas, devs) and get there head around it and maintain or write queries against it.

In the past, I've used conventions for a table's columns such as ordering a table with

1. primary key(s) first
2. then foreign keys
3. then frequently used columns
4. then other columns
5. and lastly audit related columns

and these help when scanning a table. Unfortunately, it appears you have to jump through hoops to maintain any order, so now I have to question whether it's worth having and maintaining these conventions. My new rule is just add it to the end.

If you are really worried about order from a readability perspective, you should create your own 'readability' views (perhaps in a different schema) in any order you feel like. You could have multiple views of the same table (one for just the core columns and another including stuff that usually isn't relevant).

It would be nice to be able to be able to re-order columns in SQL Server database diagrams (as a display thing only), but this isn't possible.

Solution 3

The sequence of columns is really irrelevant in a strict (functional) sense, in any RDBMS - it's just a "nicety" to have for documentation or humans to look at.

SQL Server doesn't support any T-SQL commands to order the columns in any way. So there is no syntax in T-SQL to accomplish this.

The only way to change that is to use the visual table designer in SSMS, which really recreates the whole table from scratch, when you move around columns or insert columns in the middle of a table.

Solution 4

You should always add fields only at the end. You should select fields in the order you want, but never restructure an existing table to add a column inthe middle. This is likely to break some things (where people did dumb things like select * or inserts without specifying the columns granted people shouldn't do those things, but they do).

Recreating the table can be a long time-consuming process for no gain whatsoever and can cause lots of user complaints and lockups while it is going on.

Solution 5

The schema comparison tools I have seen will create a new table with the desired ordering and then copy the data from the old table to the new one (with some renaming magic to make the new one resemble the old). Given how akward this approach is, I figure there isn't a T-SQL statement to add a new column in a specific place.

View more solutions