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

Neerajprasadsharma
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 1050 red flag
Rating: 4 out of 5  
 1 vote(s)

In this we will learn the importance of ever increasing clustered index / primary key in SQL Server.
Recommendation
Read How To Create Primary Key In SQL Server before this article.

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.





Page copy protected against web site content infringement by Copyscape

About the Author

Neerajprasadsharma
Full Name: Neeraj Prasad Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here: https://www.sqlshack.com/author/neeraj/ https://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=12524731 https://www.mssqltips.com/sqlserverauthor/243/neeraj-prasad-sharma-/

Login to vote for this post.

Comments or Responses

Posted by: Jerbrown on: 2/19/2018 | Points: 25
Usefull tips, thanks a lor for that)
Posted by: NEERAJPRASADSHARMA on: 2/19/2018 | Points: 25
I am glad you like it :)
Posted by: Robbieweeks on: 2/23/2018 | Points: 25
I still don't get it - why do we need it?
Posted by: Tomwoodie on: 2/23/2018 | Points: 25
absolutely agree
Posted by: Vickicasey on: 3/5/2018 | Points: 25
nice guide

Login to post response

Comment using Facebook(Author doesn't get notification)