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?

Answer 1

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.

Answer 2

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.

Answer 3

Technically, or perhaps I should say, academically, the order in which columns are added to a table, or the order in which they are stored in the database's internal storage model, should not be of any concern to you. You can use an "Order By" clause in your SQL queries to control the order that columns or computed expressions appear in the output of any query you run. Internally the database is free to store the actual data any way it sees fit to optimize storage, and or help align data elements with disk and/or memory boundaries.

Answer 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.

Answer 5

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