Solved

Sql server index and heap structure

Posted on 2013-06-29
4
626 Views
Last Modified: 2013-07-01
Hello,

I am learning about indexes. I know that index forms a b-tree structure. But - what if there are no index on the table? What structure is this? If we add - let's say clustered index - is b-tree structure added to the table structure? If we delete the index is then whole b-tree deleted and only table structure remains? Everybody is talking about index structures, but one thing it's not clear to me - if everything in sql server is stored in pages - is table without index stored in pages, too? So, when we add index this new index is also stored in pages? How are both structures connected, or how both structures differ?



Frenky
0
Comment
Question by:AntonioRodrigo
  • 2
4 Comments
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 333 total points
Comment Utility
Arguably one of the best discussions on the basics of indexes I've read. It touches on a lot of your questions: http://www.brentozar.com/archive/2012/07/sql-server-index-terms/

Specifics on heaps: http://msdn.microsoft.com/en-us/library/hh213609.aspx


Edit:

Cliffnotes:

But - what if there are no index on the table? What structure is this?  
That would be a heap.

Everybody is talking about index structures, but one thing it's not clear to me - if everything in sql server is stored in pages - is table without index stored in pages, too?
 Yes.


If we add - let's say clustered index - is b-tree structure added to the table structure? If we delete the index is then whole b-tree deleted and only table structure remains?
 Direct from the msdn article:
Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.

How are both structures connected, or how both structures differ?  
I'm going to steal this one from Brent's post:
The difference between clustered and nonclustered SQL Server indexes is that a clustered index controls the physical order of the data pages.  The data pages of a clustered index will always include all the columns in the table, even if you only create the index on one column. The column(s) you specify as key columns affect how the pages are stored in the B-tree index structure. A nonclustered index does not affect the ordering and storing of the data.

How are both structures connected, or how both structures differ?  
I'm going to again grab this one directly from Mr. Ozar:
Clustered index leaf-level pages contain the data in the table. Nonclustered index leaf-level pages contain the key value and a pointer to the data row in the clustered index or heap.
0
 
LVL 3

Assisted Solution

by:knel1234
knel1234 earned 167 total points
Comment Utility
Antonio,

If you want to keep it simple, then think of it like this

At the library, you have books.  At the library, you have a card catalogue.

If you don't have or use the card catalogue, then you need to search the whole library row by row and book by book to find the book you want to grab.

If you use the card catalogue, it gets you to the right location quickly.

You don't need to have or use the card catalogue but it doesn't affect the underlying books.  Just like an index doesn't affect the underlying table.  Note a clustered index can move the pages (books) around but it doesn't destroy them rather it just reorders them.

The point of all this is indexes help get you to books in a more timely manner but there is a cost to having them.  If you add/remove (insert/delete) a book (a row), then you need to add/remove the book physically as well as update the card catalogue too.

This is a very simple analogy but sometimes it is easier to look at things from a different view point.

cheers
knel
0
 

Author Comment

by:AntonioRodrigo
Comment Utility
Thanks for answers, Knel and didnthavename.

Clustered index -> it is physically touching underlying table? So, it's not like that (that was my first thought) that table is table and beside that table we have a b-tree structure and in that structure rows of data included as leafs? If I understand correctly - clustered index physically reorders whole table to be a b-tree with rows as leafs? And before that reordering that same table is heap? Unclustered index does not touching underlying physical structure of the table, even if some columns are included in that index? Like that:

create nonclustered index _index_test_
on tbl1 (name)
include (id, lastname)

What happens with nonclustered index when we include columns? Is data in that case physically copied to the b-tree structure of nonclustered index and underlying table remains untouched?
0
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 333 total points
Comment Utility
If you have a clustered index, you can refer to it as the table. The two are synonymous.  Unclustered indexes contain the key values (and included columns) and have pointers to the row in the heap (if no clustered index) or clustered index.

In your example it would store the data for the key and tack on the data for the include columns. There are differences with how sql server handles included columns and a composite key ( if you had all three as the key instead of one as the key and 2 included). So yes, the data for the key and included columns gets copied into the non clustered index, but it still references the heap for everything else. This is why extensively indexed tables can have slower insert/update times (all indexes must be inserted into/updated as well). And also why Databases designed to handle OLTP workloads are usually as lightly indexed as possible and data warehouses are extremely heavily indexed.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now