I am fixing issues on a new database, set up by a third party.

SIDE NOTE: I have just joined the company that originally hired the third party. The new database is less than two weeks old and was launched in my first few days on the job.

This database was supposed to match the schema of the old database, unfortunately the third party forgot to include quiet a few Constraints, triggers, and indexes.

Both databases are SQL Server 2012

I am looking to alter a table that currently doesn’t have the primary key it had in the old schema. Below are the Create Table Statements simplified

Current Production

CREATE TABLE [dbo].[Table](
    [field] [varchar](255) NULL,
    [data] [varchar](255) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO

The table from the old Database

CREATE TABLE [dbo].[Table](
    [id] [int] NOT NULL,
    [field] [varchar](255) NULL,
    [data] [varchar](255) NULL,
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]
GO

Would it have a major impact to ALTER the production table and add back in the Primary key along with an index that exists in the old Database? Is there a best practice guide for this?
Is this the appropriate sql for altering the production table

ALTER TABLE [dbo].[Table] ADD PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Is there a programming reason why the third party developer might have decided to go with and IDENTITY instead of replicating the primary key in the new schema?

EDIT: My plan based off the advice given in the responses

Considering the processing the query will need to execute I will be running the Alters in off hours. I decided to go with an ALTER instead of the emptying the table and repopulating it based off the business needs of my environment.

I have confirmed that the IDENTITY Column is currently Unique by using this query

SELECT DISTINCT(id), COUNT(id)
FROM Table
GROUP BY id
HAVING Count(id) > 1

If the above remains true I will then be running the following during non peak hours:

ALTER TABLE [dbo].[Table] ADD CONSTRAINT PK_Table PRIMARY KEY CLUSTERED
(
    [id] ASC
)

Answer

Adding a CLUSTERED INDEX to a table is costly. It’ll force the pages to be reordered, which means it’s going to be IO intensive, which depending on the table could take some time. It’s likely going to lock the table for a while if so.
Yes, that’s the correct syntax, however, I recommend giving your Key a name. That changes the syntax to:

ALTER TABLE [dbo].[SampleTable] ADD CONSTRAINT PK_SampleTable PRIMARY KEY CLUSTERED (ID ASC);
We can’t answer for other people’s decisions I’m afraid.

What is the impact of altering a table and making an Identity column the Primary key?

EDIT: My plan based off the advice given in the responses

Advertisement

Answer