//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: General SQL Server & Access Articles :: How do I change column order in a table structure?


How do I change column order in a table structure?

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 programming—you 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.

Related Articles

Can I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?
Could I get some help with JOINs?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change the order of columns in a table?
How do I concatenate strings from a column into a single row?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
How do I solve 'ADO Could Not Find The Specified Provider'?
Should I use BETWEEN in my database queries?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why doesn't SQL Server allow me to separate DATE and TIME?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?

 

 


Created: 8/5/2004 | Last Updated: 10/5/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (229)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...