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

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:

  1. No clustered indexes.
  2. One clustered index.
  3. 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)

About these ads

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

  1. A heap has:

    1. No clustered indexes.

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

    United States

    Like

  2. 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

    Like

  3. 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.

    Like

  4. Correct option : 1

    A heap has No Clustered index.

    I am happy to posted first comment in today Quiz.

    Chennai, Tamilnadu, India

    Like

  5. 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

    Like

  6. 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

    Like

  7. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  8. 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.

    Like

  9. 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

    Like

  10. 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)

    Like

  11. 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

    Like

  12. 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

    Like

  13. 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

    Like

  14. 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

    Like

  15. 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

    Like

  16. 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

    Like

  17. 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

    Like

  18. 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

    Like

  19. 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.

    Like

  20. 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)

    Like

  21. 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

    Like

  22. 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

    Like

  23. 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

    Like

  24. 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

    Like

  25. 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

    Like

  26. 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

    Like

  27. 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

    Like

  28. 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.

    Like

  29. 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)

    Like

  30. Pingback: SQL SERVER – SSQL Architecture Basics – Core Architecture Concepts – Book Available for SQL Server Certification Journey to SQLAuthority

  31. Pingback: SQL SERVER – Clustered Index and Primary Key – Contest Win Joes 2 Pros Combo (USD 198) – Day 3 of 5 « SQL Server Journey with SQL Authority

  32. A heap has
    Ans: No clustered Indexes

    If a table has no clustered index, then it is called heap, or else the storage model is called clustered index (B-Tree).

    India

    Like

  33. Pingback: SQL SERVER – Winners – Contest Win Joes 2 Pros Combo (USD 198) « SQL Server Journey with SQL Authority

  34. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s