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

    Unanswered: Backing up tables

    I use SQL Server 2000 and I create scripts on a daily basis to do the following

    1. Rename a table
    2. Create table with original name and add new field
    3. Copy data from renamed table to new table
    4. Build indexes
    -----------------------------------------
    execute sp_rename ORIGINAL, OLD
    GO

    CREATE TABLE [dbo].[ORIGINAL] (
    [A] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [B] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [C] [int] NOT NULL ,
    [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    INSERT INTO ORIGINAL (A, B, NOTES)
    SELECT A, B, NOTES
    FROM OLD

    CREATE CLUSTERED INDEX [ORIGINAL_A_B] ON [dbo].[ORIGINAL]([A], [B] DESC ) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO
    -----------------------------------------

    ON A TABLE WITH 100,000 RECORDS THIS KIND OF SCRIPT TAKES A LONG TIME TO DO THE INSERT/SELECT PART

    I WOULD USE ALTER/ADD TO UPDATE THE TABLES, BUT MY SUPERIOR INSISTS THAT NOT HAVING TEXT FIELDS AS THE LAST FIELD IN THE TABLE WILL CAUSE QUERKY PROBLEMS WITH HIS SQL STATEMENTS
    DOES ANYONE HAVE ANY KNOWLEDGE THAT THIS COULD BE TRUE?

    IF SO,
    DOES ANYONE HAVE ANY IDEAS FOR DOING THIS SAME PROCESS TO ADD A NEW FIELD (C) BEFORE THE FINAL FIELD (NOTES - TEXT FIELD), AND BACKUP THE TABLE BUT WITHOUT IT TAKING SUCH A LONG TIME ON THE INSERT/SELECT PART?

    Forgive me if I'm asking a lot, but I'm lost and these scripts take way too long for what seems like such a simple task.

    Thanks,
    Mitch

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Wow sounds like he has a DB2 OS/390 v2.3 background

    Look up the datatype in BOL...

    They store pointer values, not the data...it's stored separatley...

    If you don't mind being fired, call him a scrub...

    The order of columns in a table should have very little impact...

    But being anal goes with the job....

    Try a bcp out and in with a format file....

    The reason it's slow is because it's all being logged...the bcp will log only the pages....not every row.....
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EDIT:

    Oh, and btw, I use ALTER
    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.

Posting Permissions

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