Is Data Always Sorted On Primary Key (Clustered Index) In SQL Server ?

Whenever we create a primary key on a table and we generally get data sorted on primary key defined. but that’s not always the case. Let’s examine it.

Create Table and Populate Data With Following Script

If Exists(Select * From sys.Objects where object_id=object_id('Records'))
Drop Table Records
go
 

CREATE TABLE [dbo].[Records](
    [Id] [int] IDENTITY(1,1) Primary Key  NOT NULL,
    [EmpName] [varchar](20) NULL,
    [AccountNo] [varchar](12) NULL,
    [IsActive] [tinyint] NULL,
    [Salary] [decimal](22, 2) NULL,
    [Department] [varchar](50) NULL
) 
GO
 
INSERT [dbo].[Records] ([EmpName], [AccountNo], [IsActive], [Salary], [Department]) 
VALUES 
('Kuldeep Bisht', N'012398760987', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Vishal Verma', N'987026749876', 3, CAST(6700.00 AS Decimal(22, 2)), N'BPO'),
('Rakesh Kumar', N'876894326754', 1, CAST(4500.00 AS Decimal(22, 2)), N'TESTING'),
('Sandeep Verma', N'786798564329', 1, CAST(9800.00 AS Decimal(22, 2)), N'IT'),
('Revinder Singh', N'999867543987', 1, CAST(7850.00 AS Decimal(22, 2)), N'TESTING'),
('Seema Singh', N'876549879898', 2, CAST(4560.00 AS Decimal(22, 2)), N'BPO'),
('Ashutosh', N'785640987567', 1, CAST(7680.00 AS Decimal(22, 2)), N'IT'),
('Devesh Gupta', N'776623458123', 2, CAST(6600.00 AS Decimal(22, 2)), N'TESTING'),
('Satinder Negi', N'768098723456', 1, CAST(3500.00 AS Decimal(22, 2)), N'BPO'),
('Meenakshi', N'009812346785', 1, CAST(9800.00 AS Decimal(22, 2)), N'BPO'),
('Manoj Pandey', N'767689900145', 3, CAST(7800.00 AS Decimal(22, 2)), N'IT'),
('Sanjay Rana', N'980765430974', 1, CAST(8800.00 AS Decimal(22, 2)), N'SALE')
Go

Since we have primary key on Id so data is sorted on Id columns when we pass select query.You can see in screen shot below.

Now Let’s Create a Non Clustered Index on Department Column

Create NonClustered Index NCI_Records_Id On Records(Department)
Go

Now pass select query on Table Again. You Get the Same Result.

 

But Now Let’s just select 2 columns Id and Department and see the resule.

Interestingly you can see that even after having Primary Key (clustered index) on Id  columns data is being sorted on non-clustered index. You must take care of this with using proper order by clause whenever sorting of data is required. Do not just rely on Primary Key.