Create Constraint

ron barlow 411 Reputation points
2023-01-31T21:39:26.81+00:00

Hello

I've got a table, that I need to import approx 400 million records. I've read that it's quicker to drop constraints/Indexes, import the data and recreate the constraints/Indexes. Dropping the constraint is straight forward, but I'm not sure about recreating it.

When /I script out the table, the constraint is

CONSTRAINT [PK_TableTest] PRIMARY KEY CLUSTERED ( [ColName] ASC ) )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

How do I script the code above as a Create Index/Constraint?

Many thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,257 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,811 Reputation points
    2023-01-31T22:15:10.3066667+00:00

    Yes, it is correct that you if you want to large volumes of data, you can gain quite some speed by disabling indexes and constraints and re-enable them afterwards.

    But there is one exception: the clustered index. My experience is that is faster to have the clustered index in place when you do the load. And you example is exactly that: a clustered index.

    Else, to disable all CHECK and FOREIGN KEY constraints is one single statement:

    ALTER TABLE tbl NOCHECK CONSTRAINT ALL
    

    To re-enable them, you run:

    ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT ALL
    

    And, no, there is no typo, but it should be CHECK twice.

    To disable an index, you say:

    ALTER INDEX ix ON tbl DISABLE
    

    You need to run this once for all non-clustered indexes: Whether they also are constraints does not matter.

    To re-enable them, you can run

    ALTER INDEX ix ON tbl REBUILD
    
    

    for each index. You can also re-enable all in a single command:

    ALTER INDEX ALL ON tbl REBUILD
    

    But this will also rebuild the clustered index, so this will take longer time.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-02-01T03:11:26.22+00:00

    Hi @ron barlow

    Like Erland said, you're using clustered indexes in your code.

    In addition to the solution provided by Erland, you can also refer to the content in this link.

    https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver16

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. ron barlow 411 Reputation points
    2023-02-02T20:11:39.21+00:00

    Thanks Erland / Percy

    Your replies are much appreciated.

    Ron

    0 comments No comments