Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    11

    Unanswered: 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!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •