Alter Add - Before Text Datatype

I am constantly updating tables in my database with new fields, a lot of tables have a field with text datatype as the last field in the table.

It's very time consuming to run a script that renames the table, creates a new table with the new field before the text field, and insert into new table using select from renamed table. (SQL BELOW)

execute sp_rename CUSTDEF, CUSTDEF_1030A
GO

CREATE TABLE [dbo].[CUSTDEF] (
[CustDef1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDefNEW] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE INDEX [CUSTDEF_ONE] ON [dbo].[CUSTDEF]([CUSTDEF1], [CUSTDEF2]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

INSERT INTO CUSTDEF (CustDef1, CustDef2, CustDef3, CustDef4, CustDef5)
SELECT CustDef1, CustDef2, CustDef3, CustDef4, CustDef5
FROM CUSTDEF_1030A
GO

What I would like to do is to be able to have sql where I can use an ALTER ADD to add in CustDefNEW before the text field. Is there any way that I can do this, and save time more time than doing an insert/select against 50,000 records.

Thanks alot!The order of columns in a database has no bearing on the perforance. Is your background DB2? It used to be that way for varchars..

And why do you have text columns? How big is the data?

Bigger than 8000 bytes?

And no, ALter Add does manage the order of the columns (at least as far as I understand).

You can do it in EM...I think it'll do all that work for you behind the scenes...

I'm just not too keen about doing work there...see some weird things...

Good Luck

Another idea might be to use a view which looks like what you want...|||Brett,

I think the order can make a difference if you have, say, a long varchar field before the values on which you are searching. The server would have to determine the length of the data for the varchar in each row in order to calculate the offset of any data after it. If you know something that contradicts this, let me know.

In any case, MHawkins19, the TEXT datatype is not even stored in your rowset. All that is stored is a fixed length pointer to the location where the TEXT data is stored. Therefore, it make little or no difference what order your columns are in.

blindman