A table without clustered index is considered as Heap Table?

Question

I'm trying to know that a table without Clustered Index considered as Heap Table and then table with out Primary key what it called as ??. If both are not there then it will be called as Heap Table. Primary key will give Logical Order and then what clustered Index will give or they both will do the same operation.

It is not learning I just tried to know deep dive but every one have their own versions. So I came back to my community experts. I have done lot of reading on this so please consider it as valid question.

A table without a PK is called a **minefield**. A table can have a PK but no clustered index. A table can also have a clustered index but no PK. Or your table can have a PK which is not a clustered index, but another field (or set of fields) can be the clustered index. — alroc, Sep 05 '15 at 02:45
@alroc When you create a primary key, it will create a clustered index for you behind the scene, this is how sql server enforce constraints(Primary Key, Unique Constraint), for primary keys it creates unique clustered index. — M.Ali, Sep 05 '15 at 03:11
@M.Ali that depends upon how you create the PK. It is possible to define a [PK which is not a clustered index](http://blog.sqlauthority.com/2013/02/10/sql-server-primary-key-and-nonclustered-index-in-simple-words/). You can only have one CI on a table, so you can create a CI which is **not** the PK. — alroc, Sep 05 '15 at 11:29
@alroc Is this `minefield` really a known term used for tables without PK. I tried searching for this term but didn't get any relevant citations. Can you please help me with some references or citations? — RBT, Mar 28 '18 at 07:00
@rbt what it means is that a table without a primary key is risky from a performance and data integrity perspective and should only be used in specific circumstances where everything has been carefully considered. — alroc, Mar 28 '18 at 10:08

score 1 · Answer 1 · edited Mar 28 '18 at 07:08

A table with no clustered index is a Heap Table. A Heap Table has no order and the row are stored as they are inserted.

A table with clustered index is a Clustered table. A clustered table is arranged as per the key fields included in the cluster index.

When a primary key is created it creates a clustered index ONLY if none is present.Can also create a primary key with non clustered index by specifying explicitly.

A table can have a maximum of Only One Clustered Index. For a clustered index when a rows are inserted or updated the index is rearranged to maintain the order in the index.

Whereas for heap table, the rows are inserted or updated without any arrangement i.e. they are stored in the pages that are available to SQL server for the said table. Hope this clears some doubt of yours, feel free to ask for any clarity.

RBT · Answer 2 · 2022-10-02T11:16:31.460

I'm trying to know that a table without Clustered Index considered as Heap Table

That is correct.

and then table with out Primary key what it called as ??.

There is no special or common name for a table on which you haven't defined a primary key. Primary key is a constraint that you can put on the data being inserted into the table. It is optional. It is for you to decide whether you need it or not.

If both are not there then it will be called as Heap Table.

Yes. That is correct. Primary key being absent from a table has no relevance in this case. What matters is that whether it has a clustered index or not. Since it doesn't have clustered index so it will certainly be called a Heap Table.

Primary key will give Logical Order and then what clustered Index will give or they both will do the same operation.

I've never heard this term Logical Order w.r.t. storage of data/rows of tables in SQL Server. There is a term Physical Storage Order though. Primary key has no say in ordering/storage. It only decides uniqueness of records. Primary key is a constraint which enforces unique values for a column for each row being inserted into the table e.g. you can't have two employees having same ID 1 if you've primary key constraint on the ID column of Employee table. It will cause failure of insertion of a record into the table if constraint isn't being met.

Clustered index decides Physical Storage Order of the rows of a table on the disk. It also causes alteration in storage of data corresponding to rows whenever a row gets added/updated in the table. The alteration is done to enforce the physical storage ordering. Let's say you've an Employee table with below columns:

ID
Name
Salary

You define a clustered index on ID column. So this is how rows of the table will be stored on disk:

╔════╦══════════════╦═════════╗
║ ID ║  Name        ║ Salary  ║
╠════╬══════════════╬═════════╣
║  1 ║ Jeff Atwood  ║  111    ║
║  2 ║ Geoff Dalgas ║  222    ║
║  3 ║ Jarrod Dixon ║  333    ║
║  4 ║ Joel Spolsky ║  444    ║
╚════╩══════════════╩═════════╝

Now you update the record having ID 2. You change its ID to 5. Had it been a Heap table (no clustered index) then no reordering of data storage would be required. The row will get updated in-place (Refer screenshot)

╔════╦══════════════╦═════════╗
║ ID ║  Name        ║ Salary  ║
╠════╬══════════════╬═════════╣
║  1 ║ Jeff Atwood  ║  111    ║
║  5 ║ Geoff Dalgas ║  222    ║ <- No physical movement of the affected row
║  3 ║ Jarrod Dixon ║  333    ║
║  4 ║ Joel Spolsky ║  444    ║
╚════╩══════════════╩═════════╝

But this story changes in case you've a clustered index defined on ID column. A hole will get created where the row with ID 2 was present. And the entire row with changed ID 5 will moved to the bottom of the table (Refer screenshot below). Such updates cause fragmentation of table storage which can be resolved by index reorganizing or index rebuilding:

╔════╦══════════════╦═════════╗
║ ID ║  Name        ║ Salary  ║
╠════╬══════════════╬═════════╣
║  1 ║ Jeff Atwood  ║  111    ║
║<HOLE causing fragmentation> ║
║  3 ║ Jarrod Dixon ║  333    ║
║  4 ║ Joel Spolsky ║  444    ║
║  5 ║ Geoff Dalgas ║  222    ║ <- Physical movement of the affected row
╚════╩══════════════╩═════════╝

M.Ali · Answer 3 · 2015-09-05T02:58:44.220

0

Primary Key is a constraint , when you create a primary key it actually creates a clustered index behind the scenes to enforce the Primary key constraint.

In simple words , A Primary is actually A clustered index on a table.

And yes when there is no clustered Index/Primary Key define on the table it is called HEAP.

Create a table without a primary key column and check in SSMS it will have no constraints or Indexes for that table.

CREATE TABLE Index_Test(ID INT NOT NULL)
GO

Check in SSMS object explorer:

Now create this ID Column A PRIMARY KEY and see what happens:

ALTER TABLE Index_Test
ADD CONSTRAINT PK_ID PRIMARY KEY (ID)
GO

Now refresh the table in SSMS and expand all nodes to see whats there:

I only added the Primary Key constraint but it actually created a Clustered Index for me.

edited Sep 05 '15 at 02:58

answered Sep 05 '15 at 02:47

M.Ali

67,945
13
101
127

if you are going to create primary key by default clustered index will come but is it necessary to have clustered for every primary key or vice versa – mohan111 Sep 05 '15 at 02:48
When you create a primary key, it will create a clustered index for you behind the scene, this is how sql server enforce constraints(Primary Key, Unique Constraint). – M.Ali Sep 05 '15 at 02:51
table have clustered index and doesn't have primary key then what will be the impact – mohan111 Sep 05 '15 at 02:52
@Jonathan Having a clustered index will sort the data and then it is not a heap. The only difference in a UNIQUE CLUSTERED INDEX and a PRIMARY KEY is that Unique Clustered Index will let you store One null value where as Primary key will not let you store any null values . – M.Ali Sep 05 '15 at 03:04
1

@M.Ali that statement is not entirely true. A primary key is clustered *by default* but **may be unclustered**. So it is possible for there to be two differenced between a UCI & a PK - the `NULL` constraint *and* the clustered nature. – alroc Sep 05 '15 at 11:32
@M.Ali, the primary key index will not be clustered by default only if a clustered index already exists on the table. An important point is that primary key and unique constraints are logical concepts, physically implemented with unique indexes in SQL Servre that may or may not be clustered. – Dan Guzman Sep 05 '15 at 18:01
@DanGuzman yes if there is already a clustered index on the table Primary key will have to be a non-clustered index as there can be only one clustered index, I was talking about the default behaviour of Primary keys in the context the OP has asked the question , Does the OP need to create a clustered index after he/she has created a primary key and the answer should be no, the primary key constraint will create one for you. – M.Ali Sep 05 '15 at 18:10

A table without clustered index is considered as Heap Table?

3 Answers3