Click here to Skip to main content
Click here to Skip to main content

Tagged as

Alter table - Add new column and set the position

, 1 Apr 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Introduction...
Introduction

One question I received quite often. How to change the order of the column in database table? It happens many times table with few columns is already created. After a while there is need to add new column to the previously existing table. Sometime it makes sense to add new column in middle of columns at specific places. There is no direct way to do this in SQL Server currently. Many users want to know if there is any workaround or solution to this situation.
 
Background

First of all, If there is any application which depends on the order of column it is really not good programming and will create problem for sure in future. In ideal scenario there should be no need of order of column in database table. Any column can be any where and it can be used for any data process (INSERT, UPDATE, SELECT).
 
There are few cases where order of column matters. Let us see the valid and invalid cases of dependence on order of column.
 
If there is application of BULK INSERT or BCP the order of column matters when creating the table. This is valid reason for the case where order of column matters. If there is insert statement where column names are not specified the order of column will create issue, this case demonstrate lazy developer and inappropriate coding style.
 
If you really want to insert your column at any specific place. There are two different ways to do that.
 
Using the code

Here the code that do the job:
 
DROP TABLE TestTable
 
GO
 
EXEC sp_configure 'allow updates',0
 
GO
 
RECONFIGURE
 
GO
 
CREATE TABLE TestTable(
 
identcol INT IDENTITY(1,1),
 
col1 INT,
 
col2 INT,
 
col3 INT,
 
CONSTRAINT pk_TestTable PRIMARY KEY NONCLUSTERED (identcol ASC))
 
CREATE CLUSTERED INDEX idx_TestTable ON TestTable(col2 DESC)
 
INSERT TestTable(col1, col2, col3)
 
SELECT 1,8,9 UNION ALL
 
SELECT 2,7,11 UNION ALL
 
SELECT 3,6,10 UNION ALL
 
SELECT 4,5,12
 
EXEC sp_configure 'allow updates',1
 
GO
 
RECONFIGURE WITH OVERRIDE
 
GO
 
ALTER TABLE TestTable DROP CONSTRAINT PK_TestTable
 
GO
 
ALTER TABLE TestTable ADD F1236 CHAR(1) NOT NULL DEFAULT ''
 
GO
 
DECLARE @myindex int = 2
 
GO
 
DECLARE @NewFieldIdx int
 
GO
 
SELECT sc.colorder into @NewFieldIdx
 
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
 
WHERE so.name = 'TestTable' and sc.name = 'F1236'
 
GO
 
UPDATE sc
 
SET sc.colorder =
 
CASE
 
WHEN sc.colorder = @NewFieldIdx THEN @myindex
 
WHEN sc.colorder > @myindex-1 THEN sc.colorder + 1
 
ELSE sc.colorder
 
END,
 
sc.colid =
 
CASE
 
WHEN sc.colid = @NewFieldIdx THEN @myindex
 
WHEN sc.colid > @myindex-1 THEN sc.colid + 1
 
ELSE sc.colid
 
END
 
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
 
WHERE so.name = 'TestTable'
 
GO
 
ALTER TABLE TestTable  ADD CONSTRAINT PK_TestTable PRIMARY KEY (identcol,F1236)
 
GO
 
EXEC sp_configure 'allow updates',0
 
GO
 
Points of Interest

The script has the capability to change table columns order in SQL Server for new added fields.



License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

otomazeli
Web Developer
Canada Canada
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141030.1 | Last Updated 1 Apr 2010
Article Copyright 2010 by otomazeli
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid