0

Sample script:

CREATE TABLE [dbo].[Countries](
    [Id] [nvarchar](2) NOT NULL,
    [Name] [nvarchar](100) NULL
 CONSTRAINT [PK_dbo.Countries] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I was told by a DBA that I should not include the Primary Key constraint in the table create script but I don't really understand.

Could anyone tell me why and what is the best practice in this situation (for example: create table first and add primary key later in alter table)?

Thanks,

  • Don't think i matter but I'm not a DBA. What did the DBA tell you the reason was when you asked why?. – Mikael Eriksson Mar 10 '16 at 6:36
  • @MikaelEriksson: unfortunately the DBA is from another organization, so I could not ask him directly – Kien Chu Mar 10 '16 at 6:37
1

Maybe he wants to load the table with data and then create the index after. That way index fragmentation and statistics would be in good shape.

With that said a good choice of clustering key would keep fragmentation low during the data load. Kimberly Tripp has written a few posts on topic of choosing appropriate clustering keys.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.