SQL Server Performance

Ignore constraints

Discussion in 'General DBA Questions' started by SanetteWessels, Jul 6, 2005.

  1. SanetteWessels New Member

    Hi All,

    I would like to find out if there is any command in SQL that will "ignore" any constraints on a table. Reason: I have a table with 100M rows of which only 5M are valid. My steps to get rid of the redundant rows are as follows:

    INSERT INTO <new temp table> only the valid records. (no constraints set up)
    TRUNCATE original table
    INSERT valid records back into the original table. (need to ignore constraints here...)

    I cannot rename the new table to be the original table because the table structure differs from client to client. The script has to be written in a very generic way to allow different table structures and constraints set up per client. The same script will run at all our clients.

    Thanks for your contributions in advance!


    SQL Developer
    JustEnough Software Corporation
    Walking on water and coding of a spec is easy, as long as both are frozen - A friend
  2. dineshasanka Moderator

    use NOCHECK CONSTRAINT to diable the constraint and then CHECK CONSTRAINT again

    check ALTER TABLE of BOL
  3. SQL2000DBA New Member

    As per me you should do it in below way.

    Select * into <new temp table> only the valid records (here u dont have to worry about contraints)

    TRUNCATE original table

    Alter original table with Nocheck option

    INSERT valid records back into the original table

    But before you do, backup your original table.

    Thanks and Regards

    Ravi K

  4. FrankKalis Moderator

Share This Page