Total Page Preview:   000000034231

Can we Create Clustered Indexes without Primary key and non clustered index with primary key?



Yes we can create the clustered index without primary key. Also, primary key and clustered index can be on different columns in the same table. 

  • Primary key does not allow NULL, where in clustered index allow NULLs.
  • Clustered index without primary key creates an Unique, PRIMARY KEY NONCLUSTERED index on the table.
  • Primary key is a constraint and clustered index is an index so, logically they are both different entities in RDBMS. As a matter of fact, this is possible in one condition, when clustered index is created before the primary key on the table otherwise primary key will by default create the clustered index with it and only one clustered index is allowed per table.
Example create cluestered index without primary key:
--Create table
CREATE TABLE tblEmployee
         EmpId INT NOT NULL
        ,EmpName VARCHAR(25)
--Create clustered index without primary key
CREATE CLUSTERED INDEX ixcEmployee ON tblEmployee (EmpName)
--Create primary key with none clustered index
--Data insert employee table without name order
INSERT INTO tblEmployee VALUES (1, 'Umakant', 30)
INSERT INTO tblEmployee VALUES (2, NULL, 25)
INSERT INTO tblEmployee VALUES (3, 'Ajay', 15)
INSERT INTO tblEmployee VALUES (4,'Dilip', 25)
INSERT INTO tblEmployee VALUES (5, 'Brijesh', 25)
--Fatch data from employee with name order because on name column created cluestered index
SELECT * FROM tblEmployee

Example with Screenshot:

Can we create Clustered Index Without Primary Key
Thanks !

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn :


I’m not that much of a internet reader to be honest but your sites really nice, keep it up! I'll go ahead and bookmark your website to come back later on. Cheers
I visit day-to-day some blogs and information sites to read articles, but this weblog gives quality based articles.
Thanks for ones marvelous posting! I definitely enjoyed reading it, you will be a great author. I will be sure to bookmark your blog and will eventually come back from now on. I want to encourage continue your great posts, have a nice morning!




Facebook User: