SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding – Day 19 of 35

« SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Page Split – Day 20 of 35 »

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding – Day 19 of 35

August 19, 2011 by pinaldave

Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from United States will get Joes 2 Pros Volume 3.

Since the physical storage of data impacts the speed and efficiency of our queries, in tomorrow’s post we will explore how clustered indexes can impact the physical location of data and the way SQL Server retrieves query data. For today we will need to know the basics of the Clustered Index.

The Clustered Index

What is clustering or a clustered index? Let’s take a real life example. The store names in a shopping mall are not lined up in alphabetical order. In other words, Zales could be right next to Benetton. These two stores might occupy Units #410 and #411. Thus, the stores in a mall are clustered by unit number. The clustered index represents the actual physical order of your data. A paperback book (like Joes 2 Pros) is ordered by page numbers. If you locate page 99, then you know exactly where page 100 is. Thus, you could say the pages in a book represent a clustered index and they are clustered by page number.

Data Storage Terms

Think of a memory page as a carton of eggs and think of each egg as a row of data. If you only had two eggs to store, then you would just need one carton. With just two eggs you have 10 available slots for when you insert more eggs. Eventually, you could acquire more eggs than could fit into one carton. What would you do then? You would fill up your first carton and get another one. Once you get to 13 eggs you need 2 cartons to hold them. Similarly, only so many records will fit into one memory page. If more records come in, then SQL Server will use more memory pages for that table.

Records

While a table can have a maximum of just 1024 columns, or fields, it can have trillions of records. What makes a table take up space is the number of records in that table. If a table had no records, then it would take up very little space.

Memory Pages

A page can hold up to 8K of data. If each record takes up 2K of data, then 4 records would fit in each datapage. If such a table had 12 records, then it would use 3 memory pages. In this pseudo example, 12 records from this table would fill up three memory pages.

Clustered Index Data in Memory

In Volume 2 (SQL Queries Joes 2 Pros), you learned that with the use of an ORDER BY clause you can view a table’s records in any order you like. This is true regardless of the order in which the table has its rows stored. If you don’t use an ORDER BY clause, then you get the table’s natural sort order. What is a natural sort order? It is the sequence, from beginning to end, in which each row is stored in the table. You can tell a table how it should store its data or let the table just store data in order as it is entered.

Absent a clustered index or constraints (e.g., a primary key, a foreign key, etc.), the default order of records is the order in which they were entered into the table. The clustered index represents the actual physical order of your data but it does not guarantee order when retrieved.

If we decide to add a clustered index to this table based on the SSN field, then regardless of the order in which you insert these records, each record will be stored in order of SSN. If you insert SSN 888-88-8888 first and then later add 222-22-2222, SQL Server would physically reorder the records in storage so they line up by SSN.

NOTE: Please note that this is Level 100 explanation and how the rows are ordered and B Trees are organized are out of the scope of this article.

NOTE1: This article is just a introduction and not a deep dive into the storage structure. Here is the MSDN page for complete reference.

Question 19

A heap has:

No clustered indexes.
One clustered index.
Many clustered indexes.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in Joes 2 Pros, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 112 Comments

112 Responses

on August 19, 2011 at 7:12 am | Reply Jeff Moden

A heap has:

1. No clustered indexes.

A table with no clustered index is simply defined as a “Heap” in Books Online.

United States
on August 19, 2011 at 7:13 am | Reply Rene Alberto Castro Velasquez

Correct answer is No. 1.
A heap has no clustered indexes.
Rene Castro
El Salvador
on August 19, 2011 at 7:13 am | Reply syed

A table that does not have a clustered index is referred to as a HEAP.
So there is no clustered index for a heap.
SYED
FAIRFAX,USA
on August 19, 2011 at 7:14 am | Reply levpius

A heap has:
1) No clustered indexes.

Heap, by definition has no clustered index

Leo Pius
USA
on August 19, 2011 at 7:16 am | Reply Ramakrishnan Srinivasan

Hi,

The heap is a non-clustered index table/view. Thus, option 1 is right, that is,

A heap has no clustered indexes.

Ramakrishnan RS
Mysore, India
on August 19, 2011 at 7:18 am | Reply Sudhir Chawla

Hi,

A heap or a clustered index determines the physical storage of table data so there can only be one of these per table. So a table can either have one heap or one clustered index.

Option 1 is correct.

Option 3 is invalid because we can’t have more than 1 clustered index on a table and option 2 is invalid because either a table can have a clustered Index or it can be a Heap.

Thanks

Sudhir Chawla
New Delhi, India
on August 19, 2011 at 7:19 am | Reply shatrughna kumar

Correct option is #1.

New Delhi
India
on August 19, 2011 at 7:24 am | Reply Reddy

Correct answer is option #1

1.No clustered indexes.

Basically a table that does not have a Clustered index is termed as Heap and it does not store the data in any particular order.

So option #2 and #3 are incorrect.

Thanks.

Country – India.
on August 19, 2011 at 7:33 am | Reply Kalyanasundaram.K

Correct option : 1

A heap has No Clustered index.

I am happy to posted first comment in today Quiz.

Chennai, Tamilnadu, India
on August 19, 2011 at 7:35 am | Reply Rahul Sharma

Ans is Option 2 a heap has only one clustered index.

Rahul Sharma
Noida India
on August 19, 2011 at 7:36 am | Reply Gopalakrishnan Arthanarisamy

Correct Answer is # 1. No Clustered Indexes.

A heap is a table without a clustered index.

Pages within a table store the data rows with structure which helps to search/locate data faster. If the data of table is not logically sorted, in other word there is no order of data specified in table it is called as Heap Structure.

Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India
on August 19, 2011 at 8:06 am | Reply Garrett G

A heap has:

1. No clustered indexes.

United States.
on August 19, 2011 at 8:10 am | Reply Bindish Thakkar

Correct Answer : 1. No clustered indexes.

Thanks,
Bindsih thakkar
USA
on August 19, 2011 at 8:32 am | Reply Ritesh Shah

Answer should be option # 1.

“No Clustered Indexes”.

I have given short note on that on my article.

http://www.sqlhub.com/2011/06/clustered-index-in-sql-server.html
on August 19, 2011 at 8:33 am | Reply Sudeepta Ganguly

Hummm….First to answer today’s question:

A heap has
Answer: 1. No Cluster Indexes.

Sudeepta,
India.
on August 19, 2011 at 8:42 am | Reply Nagaraj Ejanthkar

The answer is option 1.

A heap table has no clustered index unlike the physically sorted data in case of table which has a clustered index.

Nagaraj Ejanthkar
USA
on August 19, 2011 at 8:43 am | Reply SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

[...] Q 19) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple U… [...]
on August 19, 2011 at 8:51 am | Reply Kaushik Modi

Hi All,

A Heap Has
1) NO Cluster Indexes

Heap Getting created when data are not coming in page for storage, on that Remaining Page of data getting stored in Heap where Cluster index is not getting store.

I am from India.
on August 19, 2011 at 8:51 am | Reply Nikhil Mahajan

the correct answer is option 1 ie.

Answer: 1. No Cluster Indexes.

because A table that does not have a clustered index is referred to as a HEAP.

India
on August 19, 2011 at 8:54 am | Reply Jeff Moden

Heh… you’re kidding me. why aren’t folks answers hidden until after the day is done?
on August 19, 2011 at 9:07 am | Reply Razeena

Correct option is 1: No clustered index.
A table that does not have a clustered index is referred to as a HEAP (Ref.mssqltips.com).

Razeena
India
on August 19, 2011 at 9:08 am | Reply Aditya Bisoi (@AdityaBisoi07)

Question 19
Ans —- A heap has : No clustered indexes.

Chennai, INDIA
on August 19, 2011 at 9:09 am | Reply Rajneesh Verma

Hi,
As described in MSDN “If the table is a heap, which means it does not have a clustered index”. It clearly said that Heap has no clustered Index.

#Answer 1 is the correct answer.

Thanks….
Rajneesh Verma
(INDIA)
on August 19, 2011 at 9:10 am | Reply Michael H

Hey Pinal,

The correct answer is option #1, a heap has no clustered indexes.

Heaps are rarely ever okay and are normally frowned upon.

Michael,
Florida, United States
on August 19, 2011 at 9:20 am | Reply shekhar gurav

Correct Answer option No. – 1)No clustered indexes.

shekhar gurav.
country : India
on August 19, 2011 at 9:25 am | Reply Uday Kumar B R

Question 19

A heap has:

Answer is:

1. No clustered indexes.
on August 19, 2011 at 9:25 am | Reply Karthikeyan.S

Correct option : 1

A heap has No Clustered index.

Chennai, India
on August 19, 2011 at 9:35 am | Reply Hardik Doshi

A heap has:

1. No clustered indexes.

A table with no clustered index is simply defined as a “Heap” as mentioned in MSDN help: http://msdn.microsoft.com/en-us/library/ms189051.aspx

Country: India
on August 19, 2011 at 9:39 am | Reply Nikhildas

Correct Answer option #1

A heap table means that no clustered index on it.

Cochin,INDIA
on August 19, 2011 at 9:40 am | Reply kkmjssate

Answer 1) is correct a heap has No Clustered Index
krishan kumar mishra
India
on August 19, 2011 at 9:44 am | Reply Pratik Raval

Correct answer is Option 1.

A heap has no clustered indexes.
on August 19, 2011 at 9:50 am | Reply yangamuniprasad

Correct Answer is option 1

1.No clustered indexes

Cheers,
Prasad Yangamuni
INIDIA (PUNE)
on August 19, 2011 at 9:58 am | Reply sreeram

Answer : 1. No Clustered Indexes.

A heap is a table without a clustered index. Heaps have one row in sys.partitions, with index_id = 0 for each partition used by the heap. By default, a heap has a single partition. When a heap has multiple partitions, each partition has a heap structure that contains the data for that specific partition. For example, if a heap has four partitions, there are four heap structures; one in each partition.

Sreeram
Indian
on August 19, 2011 at 10:11 am | Reply Mahmad Khoja

The correct answer of this question is option 1
that is A heap has No clustered indexes.

As we know that the table which has no clustered index defined is referred to as heap.

Mahmad Khoja
INDIA
AHMEDABAD
on August 19, 2011 at 10:12 am | Reply Sunil (India)

Heap has No clustered indexes.
on August 19, 2011 at 10:13 am | Reply varun4sql

Heap has no clustered index

Varun R
India
[linked removed]
on August 19, 2011 at 10:14 am | Reply Naomi

The correct answer is 1 – the heap has no clustered index.

I am from USA
on August 19, 2011 at 10:14 am | Reply Sunil (India)

Heap has No Cluster Indexes.

Sunil Darji
India
on August 19, 2011 at 10:19 am | Reply kulwant kumar

Correct option : 1

A heap has No Clustered index.

kulwant kumar delhi india
on August 19, 2011 at 10:21 am | Reply sanket

No clustered index ..

Sanket K.
Mumbai,India
on August 19, 2011 at 10:26 am | Reply Ranjit kumar

Correct answer is Option 1

A heap has no clustered indexes.

Ranjit
India, Hyd
on August 19, 2011 at 10:38 am | Reply Pranav Kumar . Poondla

Pranav Kumar . Poondla
India

The correct answer is 1st option.
A heap has no clustered Indexes.
on August 19, 2011 at 10:43 am | Reply Abhi

Hi.

Ans:1. Heap is having no clustered Index

Abhi
India
on August 19, 2011 at 11:05 am | Reply manasdash

1 no clustered index
on August 19, 2011 at 11:08 am | Reply Narendra

The Correct Answer is Option – 1

A Heap Does not have any indexes.

Thanks,
Narendra(India).
on August 19, 2011 at 11:19 am | Reply Geetika Bhambri

The Answer is option 1, i.e.

A heap has:

1. No clustered indexes.

Reason:
If a table is created with no indexes, the data rows are not stored in any particular order. This structure is called a heap. So, Heap has no clustered indexes.
While,Clustered indexes sort and store the data rows in the table based on their key values. Because the data rows are stored in sorted order on the clustered index key, clustered indexes are efficient for finding rows.

Geetika Bhambri
Ahmedabad(Gujarat)-INDIA
on August 19, 2011 at 11:21 am | Reply Rajesh Mohanrangan

Correct Answer : 1. No clustered indexes.

Thanks,
Rajesh
From India
on August 19, 2011 at 11:23 am | Reply Partha Pratim Dinda

Ans:
correct option is 1.
1.No Cluster Index.
According to the definition of heap, a table with out cluster index is known as heap.
so 2nd option is invalid .
3rd option also incorrect (A table can have only one clustered index and multiple non clustered index).

Partha
India
on August 19, 2011 at 11:26 am | Reply tej narayan maurya

a heap has no clustered index. option one is correct.

A table that does not have a clustered index is referred to as a HEAP
on August 19, 2011 at 11:31 am | Reply GurjitSingh

Correct answer is 1
Heap has no clustered index.

City: Baroda
Country: India

Thanks
GurjitSingh
on August 19, 2011 at 11:31 am | Reply shekhar bahuguna

A heap Has
No clustered Index.
As Heap are the table that has no clustered index. As data is stored in no order.

Thanks
Shekhar Bahuguna
Delhi India
on August 19, 2011 at 11:31 am | Reply Shubhangini

Heap has no Cluster Index..

A heap has no order of data specified in the table..
- on August 19, 2011 at 12:28 pm | Reply Shubhangini
  
  Heap has no Cluster Index..
  
  A heap has no order of data specified in the table..
  
  India, Hyderabad
on August 19, 2011 at 11:34 am | Reply Rajesh

The option 1 is correct .

we call a table as “HEAP” when it doesn’t have a clustered Index .
So option is correct .

Hyderbad ,
India.
on August 19, 2011 at 11:36 am | Reply abhishek mishra

Correct Answer 1
1)No Clustered Index
A heap is a table without a clustered index. Heaps have one row in sys.partitions, with index_id = 0 for each partition used by the heap. By default, a heap has a single partition. When a heap has multiple partitions, each partition has a heap structure that contains the data for that specific partition. For example, if a heap has four partitions, there are four heap structures; one in each partition

Abhishek Mishra
INDIA, NOIDA
on August 19, 2011 at 11:38 am | Reply Mohd Thoufeek

The Correct answer Is : #1

Mohd Thoufeek
chennai
india
on August 19, 2011 at 11:57 am | Reply Somnath

Correct option : 1

A heap has No Clustered index.

Somnath Desai
- on August 19, 2011 at 11:58 am | Reply Somnath
  
  From India
on August 19, 2011 at 12:14 pm | Reply Keval

Correct option is 1

No cluster Indexes

india
on August 19, 2011 at 12:25 pm | Reply chakradhar

A heap has:

1) No clustered indexes.

is the correct option.

country-India
on August 19, 2011 at 12:29 pm | Reply Rahul

Correct Answer is #1
on August 19, 2011 at 12:32 pm | Reply Raghava

Correct answer is : 1. No Cluster Indexes

INDIA
on August 19, 2011 at 12:33 pm | Reply Ritesh Choksi

Answer is option 1
Indai
on August 19, 2011 at 12:48 pm | Reply Siva Kumar

Correct Answer is 1.

A heap does not contain cluster indexes, A table which does not contain any idexes is called heap.

Sivakumar.

India.
on August 19, 2011 at 12:56 pm | Reply Shree

The definition of a heap is “a table without a clustered index” . It doesn’t matter if the table has 0 or 10 non-clustered indexes; as long as there is no clustered index on the table, it’s called a heap.

To put it a slightly different way: any table that is not associated with a clustered index is called a heap.

A heap is identified by index_id = 0 in the sys.indexes and a clustered index is identified by index_id =1.

Correct answer is :

Option 1 :

1)No Clustered Index

Thanks,

Shree

Bangalore India
on August 19, 2011 at 12:58 pm | Reply Pankaj

Correct Answer is Option 1 (No clustered Index)

Option 2 : Incorrect
If clustered index exist , its called clustered table.

Option 3 : Incorrect
Reason : table can have only one Clustered Index.
on August 19, 2011 at 12:59 pm | Reply Pankaj

Correct Answer is Option 1 (No clustered Index)

Option 2 : Incorrect
If clustered index exist , its called clustered table.

Option 3 : Incorrect
Reason : table can have only one Clustered Index.

Pankaj
Mumbai(India)
on August 19, 2011 at 1:02 pm | Reply Malay Shah

Correct option is #1.

Malay Shah
India
on August 19, 2011 at 1:18 pm | Reply Anish Shenoy.P

Hi Sir,

1. is the correct answer.

That is A heap has no clustered index.

I remember you had earlier explained this in one of your blogs

If the data of the table is not logically sorted, in other words there is no order of data specified in the table it is called as Heap structure.

Your earlier blog post about heap structure :
http://blog.sqlauthority.com/2008/03/30/sql-server-introduction-to-heap-structure-what-is-heap/

P.Anish Shenoy,
INDIA, Bangalore, Karnataka
on August 19, 2011 at 1:34 pm | Reply madhivanan

Answer 1
1. No clustered indexes.

Country : India
on August 19, 2011 at 2:45 pm | Reply Sale A. A.

A heap has no clustered indexes, therefore the correct option is 1.

(Sale, Nigeria)
on August 19, 2011 at 2:54 pm | Reply Karan

Corret answer is 1

1:No clustered indexes.

Karan
India
on August 19, 2011 at 3:31 pm | Reply Revathy Mani

Heaps are tables that have no clustered index
on August 19, 2011 at 3:33 pm | Reply Ashish Kadam

A heap has:

1. No clustered indexes.

Ashish Kadam – Pune – India
on August 19, 2011 at 3:37 pm | Reply Ashish Kadam

Hi Pinal,

my question is regarding to Day 18. that STDistance() gives us two different points/places shortest distance but how we get/calculate actual distance?

Thanks.
Ashish Kadam
on August 19, 2011 at 4:22 pm | Reply Shilpa Sharma

Correct Option is 1.

Shilpa
India.
on August 19, 2011 at 4:30 pm | Reply Basavaraj

Correct Answer is: 1. No clustered indexes.

Thanks,
Basavaraj
India
on August 19, 2011 at 5:08 pm | Reply Ravichandra Patel

Correct answer is Option 1.

A heap has no clustered indexes.
on August 19, 2011 at 5:47 pm | Reply Don

By definition, a heap has no clustered indexes, so option 1 is the correct answer.

Country: United States
on August 19, 2011 at 6:08 pm | Reply Deb

Answer is 1: No clustered indexes

Per the MSDN documentation” a heap is a table without a clustered index.”

Thanks!
Deb- USA
on August 19, 2011 at 6:15 pm | Reply Diljeet kumari

The Correct Answer is Option 1.
A heap has : No clustered indexes.

Explanation:

A table that does not have a clustered index is referred to as a HEAP and a table that has a clustered index is referred to as a clustered table.

A clustered table provides a few benefits over a heap such as physically storing the data based on the clustered index, the ability to use the index to find the rows quickly and the ability to reorganize the data by rebuilding the clustered index.

Why and what is a HEAP:

1)In heap the data is not stored in any specific order.

2) Particular data can not be retrieved fast, unless there are also non-clustered indexes created on it.

3) Data pages which stores the records are not linked, so sequential access needs to refer back to the index allocation map (IAM) pages

4) Since there is no clustered index, additional time is not needed to maintain the index.

5) Since there is no clustered index, there is not the need for additional space to store the clustered index tree

6)These tables have a index_id value of 0 in the sys.indexes catalog view

Diljeet Kumari
Country : India
on August 19, 2011 at 6:18 pm | Reply Jatin Dutta

Answere 19 : A
A heap has no clustered index as by defination a heap is just a collection of records without any order.

Jatin Dutta

Country : India
on August 19, 2011 at 6:30 pm | Reply Ramesh

A heap has:

1. No clustered indexes.
on August 19, 2011 at 6:37 pm | Reply Matt Nelson

Thanks Pinal, your examples in this post make it easy to visualize what a clustered index is and how it works in simple terms.

my answer: 1. A table that has no clustered index is called a heap.
Matt Nelson, Pittsburgh, PA, USA
on August 19, 2011 at 6:38 pm | Reply gvprabu

Correct Answer is : Option 1.No clustered indexes.

– Venkatesh Prabu G || Bangalore || India
on August 19, 2011 at 6:50 pm | Reply Bill Pepping

Hi Pinal,

Challenge:
Question 19
A heap has:

1.No clustered indexes.
2.One clustered index.
3.Many clustered indexes.

Correct Answer:
The correct answer is choice #1

Explanation:
As per Microsoft documentation: A heap is a table without a clustered index.

Country:
United States

Thanks for the knowledge!

Regards,

Bill Pepping
on August 19, 2011 at 6:57 pm | Reply shefalee

Correct ans is : 1

Shefalee – Ahmedabad – India.
on August 19, 2011 at 7:02 pm | Reply kkmjssate

answer 1)No Clusteres indexes
krishan kumar mishra
india
on August 19, 2011 at 7:23 pm | Reply Uday Bhoopalam

Correct answer #1. A table without a clustered index is a heap.

Uday
USA
on August 19, 2011 at 7:37 pm | Reply jagdish

Option 1 is correct
on August 19, 2011 at 7:46 pm | Reply Leonardo Guerrero (@Cibek)

The answer is option 1
No clustered indexes

Leonardo

Country: Chile
on August 19, 2011 at 8:35 pm | Reply Anderson

The answer is No clustered indexes.

Anderson
Country: Brazil
on August 19, 2011 at 9:08 pm | Reply David Brust

No clustered indexes.
Option 1

David
USA
on August 19, 2011 at 11:04 pm | Reply savitur Badhwar

A Heap has:

Answer:
1. No clustered indexes.

Explanation:
If the data of table is not logically sorted, or there is no order of data specified in table, it is called as Heap Structure.

Each Heap (non-clustered index) defined on the table will have a corresponding entry in a sysindexes table with indid between 2 and 254, pointing to first IAM (Index Allocation Map) page.

IAM pages are not used to search through data or index pages, but only to allocate and deallocate them.

Savitur Badhwar
USA
on August 19, 2011 at 11:57 pm | Reply A. ArulPrakash

Answer => 1
A heap or a clustered index determines the physical storage of table data so there can only be one of these per table. So a table can either have one heap or one clustered index.

Option 3 => is invalid because we can’t have more than 1 clustered index on a table
option 2 => is invalid because either a table can have a clustered Index or it can be a Heap.

Country : USA
on August 20, 2011 at 12:23 am | Reply dilipkumarjena

Correct Option is option 1 .

A heap has No clustered indexes.

DIlip Kumar Jena
Country : INDIA
on August 20, 2011 at 1:06 am | Reply Hima

Answer to today’s question is Option#1.
A heap is a table without clustered index.

Hima Devata
USA
on August 20, 2011 at 1:25 am | Reply Manik Dey

Hi Pinal,
The correct answer is option 1.
Explanation:- A heap is not having clustered indexes.

Thanks,
Manik Dey
India
on August 20, 2011 at 2:20 am | Reply scott

The answer is option 1 – No clustered Indexes
on August 20, 2011 at 3:23 am | Reply JOHN

No clustered indexes.

USA
on August 20, 2011 at 7:33 am | Reply Divya

Correct answer is the 1st option.
A heap has no clustered indexes.
on August 20, 2011 at 12:59 pm | Reply Monalisa Das

Heap Is without Clustered index. So the First option is correct.
Reason : Clustered index stores data according to its physical order. For eg, Dictionary. It stores data according to the alphabet order. Where as in Heap, data is stored according to its partition. For Eg, Facebook. The pics we upload are stored in a directory name Photos. Similarly we have messages which are specifically stored in Message directory irrespective of the order.
on August 20, 2011 at 2:46 pm | Reply Monalisa Das

Heap Is without Clustered index. So the First option is correct.
Reason : Clustered index stores data according to its physical order. For eg, Dictionary. It stores data according to the alphabet order. Where as in Heap, data is stored according to its partition. For Eg, Facebook. The pics we upload are stored in a directory name Photos. Similarly we have messages which are specifically stored in Message directory irrespective of the order.

Country : INDIA
(sorry forgot to mention country in last post)
on August 20, 2011 at 7:59 pm | Reply Gordon Kane

1.No clustered indexes.

Gordon Kane
USA
on August 20, 2011 at 10:33 pm | Reply vinay (@vinayprasadv)

Answer is :

1.No clustered indexes.

Vinay
Pune,India
on August 21, 2011 at 12:22 am | Reply Praveen G. Ramrakhiyani

Correct Answer:1..No clustered indexes.

Explanation:
Table without clustered index is called “HEAP”, which would not have logically sorted data.

Praveen G. Ramrakhiyani
Mumbai,
India
on August 21, 2011 at 11:52 am | Reply pinaldave

Q 19) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding – Day 19 of 35

A.) 1 No clustered indexes.

Winner from USA: Michael H

Winner from India: Sudeepta Ganguly

I thank you all for participating here. The permanent record of this update is posted on facebook page.
on August 21, 2011 at 6:29 pm | Reply saket kumar

Heap stores data without clustered index, i think 1st option is correct.

Heap contains one LOB_Data allocation per partition.
on August 25, 2011 at 10:08 am | Reply Azhar Iqbal

A Heap table has no Clustered Index.
Option 1 is Correct.
Azhar Iqbal From Pakistan.
on August 26, 2011 at 5:22 am | Reply matt.bowler

Option 1; a heap has no clustered index.
on August 29, 2011 at 7:02 am | Reply SQL SERVER – SSQL Architecture Basics – Core Architecture Concepts – Book Available for SQL Server Certification Journey to SQLAuthority

[...] Q 19) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple U… [...]
on August 29, 2011 at 5:03 pm | Reply Palanivelrajan

1) No clustered indexes.