473,230 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

Indexes vs Clustered Indexes

What is the difference please?
Sep 17 '06 #1
1 1830
On Sun, 17 Sep 2006 18:34:59 GMT, Curt wrote:
>What is the difference please?
Hi Curt,

SQL Server has two types of indexes, clustered and non-clustered.

The similarity is that both are organised as a B-tree, with root and
intermediate pages holding indexed values and pointers to pages on the
next lower level.

On the lowest ("leaf") level, pages in a clustered index contain the
values of the indexed columns of each individual row in the table; in
addition, the values of all other columns are also stored in the same
leaf page. This means that all data in a table is stored in the leaf
pages of a clustered index - this is the reason why only one clustered
index per table is allowed, and why in simplified descriptions, the
table data is said to be "stored in clustered index order". This is only
true as long as you realise that the order is logical, achieved by
following pointer chains - in reality, the data will probably be
scattered all over the sectors of your hard disk, or even spread over
multiple spindles.

In a non-clustered index, the leaf pages also contain the values of the
indexed columns of each row - but in this case, these values are only
accompanied by a pointer to where the full row is stored. If the table
has a clustered index, this pointer is the clustered index key. If the
table has no clustered index (such a table is called a "heap"), this
pointer is a combination of file number, page number, and row number of
the page, pointing to the original location of the row. The row might
have moved; in that case a forwarding pointer in the original location
will point to the current location.

--
Hugo Kornelis, SQL Server MVP
Sep 17 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Chris | last post by:
Any help would be appreciated. I am running a script that does the following in succession. 1-Drop existing database and create new database 2-Defines tables, stored procedures and functions...
4
by: eXavier | last post by:
Hello, I have query joining several tables, the last table is joined with LEFT JOIN. The last table has more then million rows and execution plan shows table scan on it. I have indexed columns...
2
by: David Sharp | last post by:
I've been doing some experiments with speeding up copying tables of approximately 1 million rows between databases using BCP and BULK INSERT. I noticed that the total time for removing the...
1
by: Steve_CA | last post by:
Hi, The more I read, the more confused I'm getting ! (no wonder they say ignorance is bliss) I just got back from the bookstore and was flipping through some SQL Server Administration...
9
by: Igor | last post by:
Is there a way to force optimizer to use indexes without hints? (some server setting or index type...) I'll give an example to clarify : I have a table with fields Customer_Code char(10) not...
7
by: robertbrown1971 | last post by:
I just inherited a Java application with a fairly complex data model that does not yet have any indexes except those on primary keys. It is still in development and before I get to do any...
5
by: shelleybobelly | last post by:
Hi, I have a new job. It needs to drop and re-create (by insert) a table every night. The table contains approximately 3,000,000 (and growing) records. The insert is fine, runs in 2 minutes. The...
2
by: Lyle Fairfield | last post by:
'Property Clustered As Boolean 'Member of DAO.Index Private Sub IsThereaClusteredIndex() Dim tdf As DAO.TableDef Dim idx As DAO.Index For Each tdf In DBEngine(0)(0).TableDefs For Each idx In...
0
by: Utahduck | last post by:
In a message posted some time ago somebody had asked if there was a maximum number of indexes. I don't know if that was answered or not but I stumbled across the answer recently. The number is...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.