Total Page Preview:   000000026623

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)
        ,Age TINYINT NOT NULL
)
 
--Create clustered index without primary key
CREATE CLUSTERED INDEX ixcEmployee ON tblEmployee (EmpName)
 
--Create primary key with none clustered index
ALTER TABLE tblEmployee ADD PRIMARY KEY (EmpId)
 
--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 : https://www.linkedin.com



Comments

Barfield
04-Jul-2019
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

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: