Investments

  Let's say we have the following table: 
 

CREATE TABLE dbo.blat  (      blatDescription VARCHAR(32),      blatID INT  )  GO

 
Two very common questions are:

• How do I make blatID the first column in the table?
• How do I add a new column and put it between blatDescription and blatID?

There is no extension to ALTER TABLE that allows you to specify the ordinal position of a new column (either for adding a new column or moving an existing column). 
 
For adding a new column in the middle of the column list, yes, Enterprise Manager allows you to do it:

• right-click the table name
• choose Design Table;
• right-click the name of the column appearing immediately AFTER the desired new column location;
• choose Insert Column;
• give the new column a name and datatype;
• click the Save button on the toolbar.

But do you have any idea what it does behind the scenes to accomplish this? You can see what is really going on by firing up Profiler, and starting a new trace, capturing the following events:

• Stored Procedures / SP:Starting
• TSQL / Exec Prepared SQL
• TSQL / SQL:BatchStarting

Here is what I found when I tried to add a CHAR(10) column named foo, between blatDescription and blatID: 
 

use [testUserLogin]  SET TEXTSIZE 2147483647    select fg.tablefg, fg.textfg, ft.catname, OBJECTPROPERTY(object_id(N'dbo.blat'), 'TableTextInRowLimit') from (select t.id, t.groupname tablefg, ti.groupname as textfg from (select s.groupname, i.id from sysobjects o, (sysindexes i full outer join sysfilegroups s on i.groupid=s.groupid) where (o.id = object_id(N'dbo.blat')) and ((o.type = 'U') or (o.type = 'S')) and i.indid in (0,1) and i.id = o.id ) t full outer join (select s.groupname, i.id from sysobjects o, (sysindexes i full outer join sysfilegroups s on i.groupid=s.groupid) where (o.id = object_id(N'dbo.blat')) and ((o.type = 'U') or (o.type = 'S')) and i.indid=255 and i.id = o.id ) ti on t.id = ti.id ) fg full outer join (select c.name as catname, object_id(N'dbo.blat') as id from sysfulltextcatalogs c where c.ftcatid = objectproperty(object_id(N'dbo.blat'), 'TableFulltextCatalogId') ) ft on fg.id = ft.id    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    SET TEXTSIZE 2147483647    sp_MShelpcolumns N'dbo.blat', null, 'id', 1    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    SET TEXTSIZE 2147483647    sp_MStablechecks N'dbo.blat'    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    SET TEXTSIZE 2147483647    sp_MShelpindex N'dbo.blat', null, 1    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    SET TEXTSIZE 2147483647    sp_MStablerefs N'dbo.blat', N'actualtables', N'both', null    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    SET TEXTSIZE 2147483647    select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'blat', NULL, NULL) xp where xp.name in (N'MS_Description', N'MS_Filter', N'MS_OrderBy', N'MS_SubdatasheetName', N'MS_LinkChildFields', N'MS_LinkMasterFields', N'MS_SubdatasheetHeight', N'MS_SubdatasheetExpanded')     SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    SET TEXTSIZE 2147483647    select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'blat', N'column', N'blatDescription') xp where xp.name in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_NumberOfDecimals', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList')     SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    SET TEXTSIZE 2147483647    select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'blat', N'column', N'blatID') xp where xp.name in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_NumberOfDecimals', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList')     SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    BEGIN TRANSACTION    SET QUOTED_IDENTIFIER ON    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE    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    IF @@TRANCOUNT > 0 COMMIT TRAN    set implicit_transactions off    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    DBCC USEROPTIONS    IF @@TRANCOUNT > 0 COMMIT TRAN    set implicit_transactions off    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    EXECUTE sp_MStablespace N'dbo.blat'    IF @@TRANCOUNT > 0 COMMIT TRAN    set implicit_transactions off    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    select distinct object_name(d.id), user_name(OBJECTPROPERTY(d.id,'OwnerId')), (cast(case when OBJECTPROPERTY(d.id, N'IsEncrypted') = 1 then 0x01 else 0x00 end as int) & 0x01) | (cast(case when OBJECTPROPERTY(d.id, N'IsIndexed') = 1 then 0x02 else 0x00 end as int) & 0x02) | (cast(case when OBJECTPROPERTY(d.id, N'IsView') = 1 then 0x04 else 0x00 end as int) & 0x04) | (cast(case when OBJECTPROPERTY(d.id, N'IsScalarFunction') = 1 then 0x08 else 0x00 end as int) & 0x08) | (cast(case when OBJECTPROPERTY(d.id, N'IsTableFunction') = 1 then 0x10 else 0x00 end as int) & 0x10) | (cast(case when OBJECTPROPERTY(d.id, N'IsInlineFunction') = 1 then 0x20 else 0x00 end as int) & 0x20) from sysdepends d where d.depid = object_id(N'dbo.blat') and (OBJECTPROPERTY(d.id, N'IsView') = 1 or OBJECTPROPERTY(d.id, N'IsScalarFunction') = 1 or OBJECTPROPERTY(d.id, N'IsTableFunction') = 1 or OBJECTPROPERTY(d.id, N'IsInlineFunction') = 1) and OBJECTPROPERTY(d.id, N'IsSchemaBound') = 1    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    sp_helptrigger N'dbo.blat'    IF @@TRANCOUNT > 0 COMMIT TRAN    set implicit_transactions off    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    select convert(int, case ObjectProperty(object_id(N'dbo.blat'), N'IsAnsiNullsOn') when 1 then 0x1 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.blat'), N'blatDescription', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.blat'), N'foo', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end) | convert(int, case ColumnProperty(object_id(N'dbo.blat'), N'blatID', N'UsesAnsiTrim') when 1 then 0x4 when 0 then 0x8 else 0x00 end)    IF @@TRANCOUNT > 0 COMMIT TRAN    set implicit_transactions off    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    select id from sysobjects where id = object_id(N'dbo.Tmp_blat')    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    BEGIN TRANSACTION    EXECUTE sp_MSobjectprivs N'dbo.blat'    select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'blat', NULL, NULL) xp where xp.name not in (N'MS_Description', N'MS_Filter', N'MS_OrderBy', N'MS_SubdatasheetName', N'MS_LinkChildFields', N'MS_LinkMasterFields', N'MS_SubdatasheetHeight', N'MS_SubdatasheetExpanded')     select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'blat', N'column', N'blatDescription') xp where xp.name not in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_NumberOfDecimals', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList')     select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int)from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'blat', N'column', N'blatID') xp where xp.name not in (N'MS_Description', N'MS_Format', N'MS_InputMask', N'MS_NumberOfDecimals', N'MS_AllowBlanks', N'MS_Caption', N'MS_DisplayControl', N'MS_RowSourceType', N'MS_RowSource', N'MS_BoundColumn', N'MS_ColumnCount', N'MS_ColumnHeads', N'MS_ColumnWidths', N'MS_ListRows', N'MS_ListWidth', N'MS_LimitToList')     CREATE TABLE dbo.Tmp_blat      (      blatDescription varchar(32) NULL,      foo char(10) NULL,      blatID int NULL      ) ON [PRIMARY]    IF EXISTS(SELECT * FROM dbo.blat)      EXEC('INSERT INTO dbo.Tmp_blat (blatDescription, blatID)          SELECT blatDescription, blatID FROM dbo.blat TABLOCKX')    DROP TABLE dbo.blat    EXECUTE sp_rename N'dbo.Tmp_blat', N'blat', 'OBJECT'    exec @retcode = sp_validname @newname    COMMIT    IF @@TRANCOUNT > 0 COMMIT TRAN    set implicit_transactions off    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    sp_MShelpcolumns N'dbo.blat', null, 'id', 1    IF @@TRANCOUNT > 0 COMMIT TRAN    set implicit_transactions off    SET TEXTSIZE 64512    use [testUserLogin]    use [testUserLogin]    set implicit_transactions on    SET TEXTSIZE 2147483647    sp_MStablechecks N'dbo.blat'    IF @@TRANCOUNT > 0 COMMIT TRAN    set implicit_transactions off    SET TEXTSIZE 64512    use [testUserLogin]    select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (case when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.id, N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id, N'TableHasActiveFulltextIndex'), ObjectProperty(o.id, N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id, N'tableisfake')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id, N'IsQuotedIdentOn')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id, N'IsAnsiNullsOn')=1) then 1 else 0 end) from dbo.sysobjects o, dbo.sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and i.indid < 2 and o.name not like N'#%' and o.id = object_id(N'[dbo].[blat]') order by s1, s2

 
UGLY!!! And of course it doesn't take very long on a table you just created. But if your table actually has data, and you have primary/foreign keys and other constraints, default values, triggers, etc. etc. this can take a lot longer and become a lot more complex. Just look at all the properties some of those selects are retrieving, and realize that for every one of them, the engine has to decide what to do about it. It's almost like a choose your own adventure book! Is column order really this important to you? 
 

---

The main problem here is that people are stuck with some idea that column order is relevant. In most cases, it isn't. If your application is relying on column order, then there is probably a problem with the design. In most cases, this is simply lazy programmingyou either have code that uses SELECT * and then processes the data based on ordinal position, or you have an INSERT statement that assumes order and neglects to list column names).  
 
There are a few scenarios where this is relevant. For example, if you are obtaining data files from elsewhere and the format changes, than any tables using BULK INSERT or BCP may need to change to accommodate the new file format (alleviating the need to write a parsing tool that restructures the file to look more like the old format). 
 
If you have application code that can't eliminate these requirements, or you absolutely need the columns in a specific order, my first suggestion is to use a view. For the above table, we can swap the order of the columns in the view, so that external references can rely on the order you intended: 
 

CREATE VIEW dbo.seeBlatCorrectly  AS      SELECT blatID, blatDescription          FROM dbo.blat  GO

 
You can also drop and re-create the table, like Enterprise Manager does, or create a new table with the desired structure and column order, and DTS or BCP OUT/IN the data to the new table.

[ Comment, Edit or Article Submission ]