Is it necessary to have a Primary Key on the table? [Resolved]

Posted by Neerajprasadsharma under Sql Server on 5/21/2017 | Points: 10 | Views : 222 | Status : [Member] | Replies : 1
Is it necessary to have a Primary Key on the table?

I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.


Responses

Posted by: Neerajprasadsharma on: 5/21/2017 [Member] Bronze | Points: 25

Up
1
Down

Resolved

We should find best candidate key to create a primary key, because Primary Key is not just a constraint , by default it's a Clustered Index as well, and offcourse Referential Integrity as well (parent child relationship using foreign key).
Now if we have a clear picture in our mind what exactly the Primary Key is then only we can decide that does we required the primary key.
This answer assumes when we are referring the Primary Key that means Primary Key Constraint with a clustered index.
This decision should taken by the very experienced DBA/Developers, those have a thorough knowledge of database designing,
Constraints and indexes with all the risk involve with it.

Are we choosing right candidate key(s) for primary key?

Not Forced: usually on the internet the advice you would get that you must have a Primary Key and its partially true.
YES,in most of the cases you should have a Primary Key , but not with the pile of columns,as far as i know in SQL Server
We can create a composite Primary Key with maximum 16 columns, but that doesn`t mean having Primary Key on the maximum number of columns,
Yes, maybe a business rule wants to have Unique NOT NULL key on more columns (it is hard to define "more column", most of the times it depends on data size, but needed thorough investigation, hate to say but it depends)
But that doesn`t mean to have a primary key on that, primary key column(s) has to be with non-clustered index leaf level
(to make a link with clustered index key) and it will unnecessarily add pages, more pages = more expensive IO and CPU. This problem can easly be solved with UNIQUE KEY with NOT NULL, as with previous Answer we said that we can have all the functionality of the Primary key without having primary key on the table.
Should not update frequently: if your Primary Key column updates frequently, then it's more prone to page split and fragmentation,nyou should check its fragmentation level, there are plenty of scripts online which will tell you how much your index if fragmented. If the index if highly fragmented then even sequential reads would not sequential, unnecessary it would be random and random and on magnetic rotating device
Random reads and much slower that sequential reads, even on the latest and expensive SSD random reads are not as fast as sequential reads.


Should be ever increasing: if primary key would not not ever increasing than whenever a key try to insert in between two keys and there is no room on the page, then the page has to split. Lots of page split lead to fragmentation and we have already written the problem with fragmentation.

Above is the explanation of which key should choose as Primary Key.
Now the second and asked question can be answered is it mandatory to have a primary key?

Staging Tables: sometimes you stores data for some intermediate process which you might require in later stages in that case you might not need (not always) a primary key on the table.
Temp Table/variable: like the staging table, we use temp tables/ variable to hold the data for temporary on that case we might not require (not always) to have a primary key.
LOG Table: sometimes DBA/DEVELOPERS creates LOG table that also may not require primary key.

In SQL Server a table without a Primary Key called HEAP, and you know what a heap is:).
I won`t go to that extent, but it's not mandatory to have a primary key on some systems, it depends on the requirement. but if we have no idea what the work load with we are going to deal with, what would the data and table structure,
Hardware type etc, we would suggest having a primary key on the table. This is the way you always get the same advice all
over the internet.

But there are some cases, usually with very large sized (TBs) databases where we look for alternatives. If you want to learn more there is an excellent detailed explanation on Thomas Kejser blog you can check out.
http://kejser.org/clustered-indexes-vs-heaps/

To wrap you this answer, I would say most of the time answers go to the "It depends" directions, so you first go with the Traditional approach, if that makes problem do not stick with it, look for alternatives.

Thanks for your time :)





Other Related Question in series:
Can We Create Primary Key with Clustered Index in SQL Server?
How to create a Primary Key without Clustered index?
Why SQL Server by default creates clustered index on the Primary Key?
Can we create a Primary Key without clustered index?
Can we create Primary Key without any index?
Can we create Primary Key on more than one column?
Can we have a functinality of Primary Key without creating a Primary Key?
Why Primary Key doesn`t accepts null values?
What is the need of Primary Key?
Is it mandotary to have a Primary Key in the table?

We will try to answer all the above questions and link all of them to each other,
We welcome everybody to contribute to this question answer series.

I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

Neerajprasadsharma, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response