Confused between clustered and nonclustered index. Contains 5 doubts.

Question

Hello,

Do the clustered and non-clustered indexes both work on B-Tree? I read that clustered indexes affect the way how the data is physically stored in table whereas with non-clustered indexes a separate copy of the column is created and that is stored in sorted order. Also, Sql Server creates clustered indexes on primary key by default.

Does that mean :

1) Non clustered indexes occupy more space than clustered indexes since a separate copy of column is stored in non clustered?

2) How does the clustered and non clustered index work when we have primary key based on two columns say.. (StudentName,Marks)?

3) Are there only 2 types of indexes? If so, then what are bitmap indexes? I can't seem to find any such index type in Sql Server Management Studio but in my datawarehousing book all these types are mentioned.

4) Is creating clustered or non-clustered index on primary key effecient?

5) Suppose we create clustered index on name i.e data is physically stored in sorted order name wise then a new record is created. How will the new record find it's place in table?

Thanks in advance :)

Answer 1

Indexes are structures stored separately from the actual datapages and simply contain pointers to the datapages. In SQL Server indexes are B-Trees.

Clustered indexes sort and store the datapages in the table according to the columns defined for the index. In SQL Server 2005 you can add additional columns to an index so it should not be a problem when you have composite primary keys. You can think of a clustered index like a set of filing cabinets with folders. In the first draw you have documents starting with A and in the first folder of that draw you may have documents starting from AA to AC and so on. To search for "Spider" then, you can jump straight to the S draw and look for the folder containing "SP" and quickly find what you are looking for. But it is obvious that if you sort all documents physically by one index then you cannot physically sort the same set of documents by another index. Hence, only one clustered index per table.

A Non Clustered index is a separate structure much like the table of contents or the index at the back of a book. So I think I have only answered some of your questions specifically:

1. Yes the index does occupy space but not as much as the original table. That is why you must choose your indexes carefully. There is also a small performance hit for update operations since the index has to be maintained.

2. Your book will mention all the theoretical types of indexes. Bitmap indexes are useful in data warehousing applications or for data that has a few distinct values like days of the week etc. So they are not generally used in your basic RDBMS. I know that Oraccle has some implementations but I don't know much about that.

3. I think that efficiency of an index is determined by how the field is used. It is expected that the majority of the data scanning in your table will be done on the primary key then an index on the primary key makes sense. You usually add indexes to columns that appear in the where clause or the join condition of your queries.

4. On insert the index has to be maintained, so there is a little extra work that has to be done by the system to rearrange things a bit.