Advertisements

Archive

Posts Tagged ‘Heap’

SQL Myth | Primary Key (PK) always creates Clustered Index

May 24, 2015 4 comments

… and this a default nature, which can be overrided to create a PK with a Non-Clustered Index instead of a Clustered one.

– What happens when a Primary Key is created on a table?
– If I have a PK on a table will the table be a Heap or not?
– Can I create a PK with a Non-Clustered Index? (this is a big hint)

While Interviewing candidates I’ve confronted them with these type of question, and very few were able to answer these correctly.

–> So, here we will see the default behavior of Primary Keys and how we can override it:

–> Having the “PRIMARY KEY” option inline with the column name: While creating a new table when you specify a “PRIMARY KEY” option inline with the Key Column, by-default it creates a Clustered Index on that table with a PK Constraint on that column.

USE [tempdb]
GO

CREATE TABLE dbo.Employee (
	EmpID		 INT NOT NULL PRIMARY KEY, -- here
	EmpLogInID 	 VARCHAR(255) NULL,
	EmpFirstName VARCHAR(255) NOT NULL,
	EmpLastName	 VARCHAR(255) NOT NULL,
	Gender		 BIT NOT NULL,
	JobTitle	 VARCHAR(255) NULL,
	BOD			 DATETIME NULL,
	DOJ			 DATETIME NULL,
	DeptID		 INT NOT NULL
)
GO

sp_help 'dbo.Employee'
GO

PK_ByDefault
 
The above image shows:
– a Unique Clustered Index created on the [EmpID] column with a name automatically suggested by the DB-engine, and
– a PK Constraint created on the same column.
 


 
–> Overriding this behavior by having a “CONSTRAINT” option: Here we will not create the PK inline with the Key Column [EmpID]. But we will:
– have a separate PK constraint created with Non-Clustered Index for [EmpID] column, and
– an another SQL statement to create a Clustered Index on the [EmpLogInID] column.

DROP TABLE dbo.Employee
GO

CREATE TABLE dbo.Employee (
	EmpID		 INT NOT NULL,
	EmpLogInID 	 VARCHAR(255) NULL,
	EmpFirstName VARCHAR(255) NOT NULL,
	EmpLastName	 VARCHAR(255) NOT NULL,
	Gender		 BIT NOT NULL,
	JobTitle	 VARCHAR(255) NULL,
	BOD			 DATETIME NULL,
	DOJ			 DATETIME NULL,
	DeptID		 INT NOT NULL
	CONSTRAINT [PK_Employee_EmpID] PRIMARY KEY NONCLUSTERED (EmpID ASC) -- here
)
GO

-- Creating the Clustered Index separately on an other column:
CREATE CLUSTERED INDEX [CI_Employee_EmpLogInID] ON dbo.Employee(EmpLogInID ASC)
GO

sp_help 'dbo.Employee'
GO

PK_Override
 
The above image shows:
– a Clustered Index (Non-Unique) created on the [EmpLogInID] column with a name we provided,
– a Non-Clustered Index (Unique) created on the [EmpID] column, and
– a PK Constraint created on the [EmpID] column with a name we provided.
 

So, it is advised to choose your PK & Clustered/Non-Clustered index wisely based upon a proper and justified Business logic. Please do not consider this as a Use Case, but just an example on how to deal with PKs & Indexes.
 

Check the video on Primary Keys:

PK Constraint


Advertisements

What are Records, Pages, Extents, Heap in SQL Server?

October 12, 2011 1 comment

 
I found this very interesting series by Paul Randal on some basic terms of SQL Server Storage Engine at MSDN SQL Server blog. This is a 4 part series and provides information on records, pages, extents and heap structures.

I’ve seen many people asking questions related to these topics on MSDN & other SQL Server forums. And there are very few tech articles on internet and MS BOL regarding these. Paul has very breifly and in layman terms explained these topics.
 

I’d like to share the following topics for my future reference and for my blog viewers:

Part 1. What are Rows/Records?

Part 2. What are Pages?

Part 3. What are Extents?

Part 4. What is a Heap?
 

–> PAGE: A page in SQL Server is an 8 KB data storage area. There are 8 types of pages:

1. Data page
2. Index page
3. IAM (Index Allocation Map) page
4. GAM (Global Allocation Map) and SGAM (Shared Global Allocation Map) page
5. PFS (Page Free Space) page
6. Text/Image page
7. Sort page
8. Differential Bitmap page
9. Bulk-Changed Map page
10. Boot page
11. FileHeader page


Categories: DB Concepts Tags: , , ,