Create Constraint

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

Thanks Erland / Percy

Your replies are much appreciated.

Ron