2

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?

4

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

Captive Pages

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.