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 which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Page Split – Day 20 of 35

Related Posts

112 Comments. Leave new

  • 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

    Reply
  • Correct Answer is : Option 1.No clustered indexes.

    — Venkatesh Prabu G || Bangalore || India

    Reply
  • 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

    Reply
  • Correct ans is : 1

    Shefalee – Ahmedabad – India.

    Reply
  • answer 1)No Clusteres indexes
    krishan kumar mishra
    india

    Reply
  • Uday Bhoopalam
    August 19, 2011 7:23 pm

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

    Uday
    USA

    Reply
  • Option 1 is correct

    Reply
  • Leonardo Guerrero (@Cibek)
    August 19, 2011 7:46 pm

    The answer is option 1
    No clustered indexes

    Leonardo

    Country: Chile

    Reply
  • The answer is No clustered indexes.

    Anderson
    Country: Brazil

    Reply
  • No clustered indexes.
    Option 1

    David
    USA

    Reply
  • savitur Badhwar
    August 19, 2011 11:04 pm

    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

    Reply
  • A. ArulPrakash
    August 19, 2011 11:57 pm

    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

    Reply
  • dilipkumarjena
    August 20, 2011 12:23 am

    Correct Option is option 1 .

    A heap has No clustered indexes.

    DIlip Kumar Jena
    Country : INDIA

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

    Hima Devata
    USA

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

    Thanks,
    Manik Dey
    India

    Reply
  • The answer is option 1 – No clustered Indexes

    Reply
  • No clustered indexes.

    USA

    Reply
  • Correct answer is the 1st option.
    A heap has no clustered indexes.

    Reply
  • 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.

    Reply
  • 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)

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version