Post reply

Constraints and indexes

  • October 2, 2009 at 7:03 am

    #73386

    When loading data into a table and I drop the index in order to get better performance. Does this effect the constraints? If so how? Someone tried to tell me that when the index is dropped then data is loaded that you can have orphaned records in junction tables. My understanding is that the index is a file used for retrieveing and finding data quicker and the constraints are a totlly seperate entity.

  • October 2, 2009 at 7:27 am

    #1060905

    Dropping an index will not affect foreign key constraints other than a table scan will be used to access the data if the index is not present.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • October 2, 2009 at 7:35 am

    #1060915

    Thanks

  • October 2, 2009 at 8:19 am

    #1060960

    dndaughtery (10/2/2009)

    When loading data into a table and I drop the index in order to get better performance. Does this effect the constraints?

    Foreign key constraints, no. Unique and primary key constraints, yes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 2, 2009 at 8:32 am

    #1060968

    When I recreate the index are the primary constraints and unique constraints recreated as well or do I need to run a script to recreate them as well?

    I run an Exe SQL Task to drop index, then load data in a data flow task, then recreate the index in another Exe SQL Task.

  • October 2, 2009 at 8:43 am

    #1060978

    dndaughtery (10/2/2009)

    When I recreate the index are the primary constraints and unique constraints recreated as well or do I need to run a script to recreate them as well?

    If you explicitly drop the primary key constraint or a unique key constraint, you have to explicitly recreate the constraint. If you try to run DROP INDEX on an index enforcing a primary key or unique constraint, you will get an error.

    DROP INDEX pk_RestrictedLogins ON RestrictedLogins

    Msg 3723, Level 16, State 4, Line 1

    An explicit DROP INDEX is not allowed on index 'RestrictedLogins.pk_RestrictedLogins'. It is being used for PRIMARY KEY constraint enforcement.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 18, 2009 at 7:30 am

    #1080897

    I am getting that error. How do I fix it or get around it?

  • November 18, 2009 at 7:35 am

    #1080901

    Please post new questions in a new thread and give some detail a to what you're trying to do and exactly what error you're getting.

    Thanks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 18, 2009 at 7:41 am

    #1080908

    Done. A new post with info has been added.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply