 |
|
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)Search
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:
Two very common questions are:
For adding a new column in the middle of the column list, yes, Enterprise Manager allows you to do it:
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:
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.
| 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?
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.
- Stored Procedures / SP:Starting
- TSQL / Exec Prepared SQL
- TSQL / SQL:BatchStarting
| 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?