Can we create clustered index without using pk?

Question

Hi Frnds Can we create clustered index without using pk? What Will happen when will not crate pk on a table? How many pk create on a table.. I know only One but someone asked ME if an another column Like name display same records so how to find this?

Shahriar Nikkhah · Answer

YES WE CAN What Will happen when will not crate pk on a table? A: Not a good idea but its possible, it will not look professional How many pk create on a table.. A: Only one PK per table I know only One but someone asked ME if an another column Like name display same records so how to find this? A: Question not so clear Can we create clustered index without using pk? A: YES YES YES, Generally we have 3 types of database design 1- DBs for " transactional databases " ( like banks/accounting software....) 2- DBs for " BI DW " 3- DBs that needs to accomedate both " Transaction and BI " anyways in the BI DW design you will see lots of PKs that are NON CLUSTERED and lots of Natural Keys that are CLUSTERED, WHY? well it all depends on the data, keys , requirement, and the designer. My suggestion try to understand dabase/table design and check list you sould know about Surregate Keys Business Keys PK FK Natural Keys Indexes fields INCLUDE fields Mandatory fields How indexes work Lookup tables and Keys Star schema design Snow flake design good luck Nik

Sandy · Answer

Hi Anjali, Yes, We can create clustered index other than the Primary column. Follow these link https://www.c-sharpcorner.com/UploadFile/ff2f08/create-cluster-index-other-than-primary-key-column-in-sql-se/ It might help. Thanks Sandy

Mohan Arumugam · Answer

No - why? The clustered index will still store the actual data pages at its leaf level, (initially) physically sorted by the name column. The index navigation structure above the leaf level will contain the name column values for all rows. So overall: nothing changes. The primary key is a logical construct, designed to uniquely identify each row in your table. That's why it has to be unique and non-null. The clustering index is a physical construct that will (initially) phyiscally sort your data by the clustering key and arrange the SQL Server pages accordingly. While in SQL Server, the primary is used by default as the clustering key, the two do not have to fall together - nor does one have to exist with the other. You can have a table with a non-clustered primary key, or a clustered table without primary key. Both is possible. Whether it's sensible to have that is another discussion - but it's technically possible. Update: if your primary key is your clustering key, uniqueness is guaranteed (since the primary key must be unique). If you're choosing some column that is not the primary key as your clustering key, and that column does not guarantee uniqueness, SQL Server will - behind the scenes - add a 4-byte (INT) uniqueifier column to those duplicates values to make them unique. So you might have Smith, Smith1, Smith2 and so forth in your clustered index navigation structure for your Smith's.

Can we create clustered index without using pk?

Answers (3)