Tips: Why we should have Ever Incresing Primary Key / Clustered Index

Introduction

Hello and welcome to the final part of this running series, which started with the Primary key in SQL Server, then followed up by question and answers about primary key/clustered index then details about indexes, internals of the index, Fragmentation in index, Statistics IO in SQL Server and detail articles on why primary key should be narrow, static, Unique and ever increasing  ...

After this article we will try to compile all the articles for  users so it would be easy to navigate to all articles and to make a one place to get all the information, if we are missing anything you can comment in the comment section in any of our article we will try to improve it.


This is a relatively small article in which we will show you, why the Primary Key should be ever-increasing with an example.
In this article we will create two tables one will consist random insertion in the primary key/clustered index and in the other sample table, we will insert the key values in the sequence and check the fragmentation in the both tables so here we go, below is the script to insert some random numeric data in the newly created NotEverIncreasing table:

--------------------------------------SCRIPT TO GENERATE DUMMY DATA



-----------CREATE DUMMY TABLE

BEGIN TRAN
IF Object_ID ('NotEverIncreasing','U') is not null
drop table NotEverIncreasing

CREATE TABLE [DBO].[NotEverIncreasing] (
Primarykey BIGINT NOT NULL ,
Keycol VARCHAR(50) NOT NULL
,SearchCol INT not  null
,SomeData char(20)not  null ,

Keycol2 VARCHAR(50) NOT NULL
,SearchCol2 INT
,SomeData2 char(20),


Keycol3 VARCHAR(50) NOT NULL
,SearchCol3 INT
,SomeData3 char(20) )



ALTER TABLE DBO.NotEverIncreasing
ADD CONSTRAINT PK_NotEverIncreasing PRIMARY KEY (Primarykey) With (MaxDop=1)

TRUNCATE TABLE DBO.NotEverIncreasing

------------------------------------CREATE DUMMY TABLE START

------------------------------------INSERT DATA IN DUMMY TABLE

DECLARE @LoopCount INT =0
WHILE (@LoopCount <100)
BEGIN
INSERT INTO [DBO].[NotEverIncreasing]


SELECT TOP (70)
N ,
n,
n
,'Some text..'
,
n,
n
,'Some text..'

,

n,
n
,'Some text..'

FROM Numbers  N  Where  NOT EXISTS
( SELECT 1 FROM DBO.NotEverIncreasing NE WHERE  NE.Primarykey = N.N )
 ORDER BY newID ()
 SET @LoopCount =@LoopCount +1
 print @LoopCount
 END

/*

--Link to generate the number table
--http://www.dotnetfunda.com/codes/show/8271/create-number-table-in-sql-server

*/

------------------------------------INSERT DATA IN DUMMY TABLE
------------------------------------ADD PRIMARY KEY IN DUMMY TABLE


SELECT
OBJECT_NAME(IPS.OBJECT_ID) Table_Name, Name,index_type_desc,index_level,
avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count

FROM sys.dm_db_index_physical_stats
(db_id(), object_id ('NotEverIncreasing' ), null ,null,'DETAILED')AS IPS
 INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
 Where
index_level=0

commit

Before we interpret the details of above result let me explain you the script, you can see in the above  script we are using loop the reason of using the loop is, so we can insert data randomly in the table,
for that we are using our number table which we have been using for this series, we are using newid() in the script so we can get a new set of random rows for the script, we are using TOP 70 because we have seen so far in this series for this kind of schema of table structure is able to get 70 to 80 rows per page. We have no control of the randomness over the data in the table, so when you will run the above script on your SQL Server setup probably you could see some other numbers.






Now look at the above result from the DMV, look at the fragmentation (external fragmentation) label, its highly fragmented index.
If you haven`t followed the series and wanted to know what is fragmentation how it occurs, what are the disadvantages of it you can look into this link , don`t forget that, reads might be not be a problem in the advance storages (SSDs, NVMEs etc) but an event of occurrence of fragmentation is still expensive and can cost you a lot in heavy OLTP workload.

Ok, so let us continue this article and again insert some data in the same schema but this time we will insert some incremental data in the Primary Key/Clustered index.

--------------------------------------SCRIPT TO GENERATE DUMMY DATA
-----------CREATE DUMMY TABLE

BEGIN TRAN
IF Object_ID ('EverIncreasing','U') is not null
drop table EverIncreasing

CREATE TABLE [DBO].[EverIncreasing] (
Primarykey BIGINT NOT NULL ,
Keycol VARCHAR(50) NOT NULL
,SearchCol INT not  null
,SomeData char(20)not  null ,

Keycol2 VARCHAR(50) NOT NULL
,SearchCol2 INT
,SomeData2 char(20),


Keycol3 VARCHAR(50) NOT NULL
,SearchCol3 INT
,SomeData3 char(20) )




ALTER TABLE DBO.EverIncreasing
ADD CONSTRAINT PK_EverIncreasing PRIMARY KEY (Primarykey)


------------------------------------CREATE DUMMY TABLE START

------------------------------------INSERT DATA IN DUMMY TABLE

DECLARE @LoopCount INT =0
WHILE (@LoopCount <100)
BEGIN
INSERT INTO [DBO].EverIncreasing


SELECT TOP (70)
N ,
n,
n
,'Some text..'
,
n,
n
,'Some text..'

,

n,
n
,'Some text..'

FROM Numbers  N  Where  NOT EXISTS
( SELECT 1 FROM DBO.EverIncreasing NE WHERE  NE.Primarykey = N.N )
 ORDER BY N.n ASC
OPTION (MAXDOP 1)
 SET @LoopCount =@LoopCount +1
 print @LoopCount
 END

/*

Link to generate the number table
--http://www.dotnetfunda.com/codes/show/8271/create-number-table-in-sql-server

*/

------------------------------------INSERT DATA IN DUMMY TABLE
------------------------------------ADD PRIMARY KEY IN DUMMY TABLE


SELECT
OBJECT_NAME(IPS.OBJECT_ID) Table_Name, Name,index_type_desc,index_level,
avg_fragmentation_in_percent fragmentation ,avg_page_space_used_in_percent Page_Density ,page_count,Record_count

FROM sys.dm_db_index_physical_stats
(db_id(), object_id ('EverIncreasing' ), null ,null,'DETAILED')AS IPS
 INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
 Where
index_level=0
COMMIT

Now  you can see in the above result set that Clustered Index is well maintained with very little fragmentation.

Conclusion

So we can conclude from the above demo that ever-increasing key is desirable in Primary Key / Clustered Index.
So choose your primary key wisely just not only the basis of that fact that primary key is a constraint and maintains unique data in the table and enable the foreign key (child / parent) relationship amongst the tables.
in the end I would like to say if you guys want me to explain the article in more detail, i would come back and edit the article.