SQL Table with Primary Key and Indexes

When we create a sql table with a primary key, we need to understand what happens behind the scene and how indexes are affected by it.

Let's go through a simple example and touch each topic as we go through it.

Let's create a table called country with following structure.

Create table dbo.Country 
(Country_ID int Identity (1,1) NOT NULL Primary Key
, Name Varchar(40)  NOT NULL
, Alpha_2_Code_Name Varchar(2)
, Alpha_3_Code_Name Varchar(3)
, Numeric_Code int NOT NULL
, ISO_3166_2_Code Varchar(20)
);

And insert following test data in this table just created.

Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Afghanistan','AF','AFG',004,'ISO 3166-2:AF')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Åland Islands','AX','ALA',248,'ISO 3166-2:AX')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Albania','AL' ,'ALB',008,'ISO 3166-2:AL')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Algeria','DZ' ,'DZA',012,'ISO 3166-2:DZ')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('American Samoa','AS','ASM',016 ,'ISO 3166-2:AS')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Andorra','AD' ,'AND',020,'ISO 3166-2:AD')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Angola','AO' ,'AGO',024,'ISO 3166-2:AO')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Anguilla','AI' ,'AIA',660,'ISO 3166-2:AI')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Antarctica','AQ','ATA',010,'ISO 3166-2:AQ')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Antigua and Barbuda','AG' ,'ATG',028 ,'ISO 3166-2:AG')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Argentina','AR','ARG',032,'ISO 3166-2:AR')

Now if we look at the table structure, this is how it looks like:

Now we look at index physical state by querying this function, we see following

SELECT page_count, index_level, record_count, index_depth
FROM sys.dm_db_index_physical_stats(db_id(N'Data_Base_Name'),object_id(N'dbo.country'),NULL, NULL, 'Detailed');

Let's see how this changes when we delete data from our table and how the index page reflects in that change.


1. DELETE FROM dbo.Country

Now re-run your above query

SELECT page_count, index_level, record_count, index_depth
FROM sys.dm_db_index_physical_stats(db_id(N'Data_Base_Name'),object_id(N'dbo.country'),NULL, NULL, 'Detailed');

So here basically we see that record count are deleted but page_count, and index_depth  remain same.

2. TRUNCATE table dbo.Country

After we truncate table, re-run your script.



SELECT page_count, index_level, record_count, index_depth
FROM sys.dm_db_index_physical_stats(db_id(N'Data_Base_Name'),object_id(N'dbo.country'),NULL, NULL, 'Detailed');

Here we see that everything has been truncated.

So when we truncate table, indexes is automatically dropped but structure definition remain same.