Do I need a Primary Key if it's already an Index

Question

I have a bunch of tables with no Primary Key. Some do already have a non clustered index comprised of what would be the Primary Key (ClaimID, LineNBR). The columns used are currently set to accept NULLs so i would need to alter that. Is there value in creating a Primary Key for this and would that make having the non clustered index on the same columns superfluous? Is it best to leave them with just the non clustered index, delete the non clustered index and make it a Primary Key, or add the Primary Key AND keep the non clustered index?

Answer 1

Table usage patterns are the main driver of if you need them or not. In OLTP workloads, you usually do want them. If you're just dumping data into these tables and reporting off them in big ol' scans, they're likely less valuable.

And while you may not need a Primary Key at all, adding a clustered index can help you avoid problems that arise when tables are Heaps (tables without clustered indexes).

Again, usage patterns will matter most: if you never update or delete data stored in heaps, you won't hit these specific problems:

Forwarded Fetches

• How To Fix Forwarded Records

• Identify and Correct SQL Server Forwarded Records

• How Forwarded Records are Read and Processed in a SQL Server Heap

• Forwarding and forwarded records, and the back-pointer size

Captive Pages

• DELETE on a heap table

• Heaps, Deletes, and Optimistic Isolation Levels

• SQL Server: Inside a DELETE operation in Heaps

• SQLskills SQL101: Why does my heap have a bunch of empty pages?