.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Sandeep Singh
Post New Web Links

clustered index myth

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server

Before I go and make myself look stupid by publishing the following in an internal document, can someone confirm whether its true or not (engine stuff not my strongpoint):

An oft-quoted myth is that data is stored on disc in the order of the clustered index key. Not true – data is stored within a page in the order of the clustered index key but those pages are not stored in order.


http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
Jamie Thomson

View Complete Post

More Related Resource Links

Does inserting a non clustered index reduce IO?

I ran a report on my prod server (Performance - Top queries by total IO) and found the following query the 2nd highest.   select * from Table1 where Col1 is null order by Col2, Col3, Col4 Table has 100,000 rows. 1 pk and about 30 columns After running it against engine tuning advisor it advised, if i created a non clustered index on Col1, Col2, Col3 and Col4 it would increase the performance by 77%. But when i run the command just by it self it runs and completes instantaneously. What am i missing? Should i create the index to reduce the IO's as the Advisor suggests?    

Clustered vs NC index storage differences?

Does clustered index takes more disk space than NC for the same filed(s) for large tables? Also is CI takes longer (time) to build than NC if you have large tables? Any microsoft articles supporting these aruguments.

non-clustered index



Can we you create a non-clustered index over a column already having clustered index



Do I really need Clustered Index??


I read in so many articles, some books and listen some expert's words and even microsoft recommended that, A table with Clustered Index will performs better than Heap in most of the cases. I too believed same until today. But today, I saw in my project, out of 2,000 tables we have, only 50-60 tables have CI only, and remaining tables are all heap only. Still our 90% of stored proc. always will takes less than 1 or 2 seconds, even when they tested with millions of data. 

            I never had chance to look into the DB, as i will not work directly with proj. dev. Today only I saw this. i asked one of my colleague about this. He replied that, when insertion/updation/deletions performed, CI will take more time, If it is heap, it wont take more time. Yes, What ever he is telling is true. It needs to adjust the indexes, page splits also might happens...  

a Heap has following advantages over CI.

1. Insertion/Updations/Deletions faster...

        Yes, as its an web application, which will be continously use by the people, no of DML operations will happen. 

2. Coming to Selections, I saw the tables have Non-CI, which almost covers all the queries.... and more over the Because of the Paging implementation, always, only 25-50 records will be selected,  

Query defaults to a Clustered Index Scan instead of using an Index Seek


I have query that behaves much differently the moment I modify the Where clause


The following query runs fast!!





Guid clustered index fast select and slow insert



Don't know if this question is in wrong forum but I didnt find a better place.

I have a table called "CompanyApplications". The first column is a guid and is the PK (not clustered). The other colum is also a guid and is the company identifier. This is a guid because it comes from another table (the "company" table), an old table used for other stuff. Then I have a third and forth column for "ApplicationURL" and "ApplName.

My application will do alot of (since 30 000+ users) "select ApplName, ApplicationURL from CompanyApplications where companyId='<the company guid of the user>' order by ApplName). It will make very few inserts into this table. Only when one is inserting a new application.

I have some questions about this since I know it's not good to have a guid as clustered since it will have SQL Server to always resort the clusted index (because guids are "random"):

1. Is it wrong to have the companyId column as clusted in this example? If so, what is better?

2. I know inserts will be relatively slow when using guid as clustered. But in my case the insert does not need to be fast. Will the slow insert also affect selects from other users. I mean, will select's be slow for other users when the index is resorted becuase one user make an insert?


clustered index



i have a query which is running slow so i've been adding index's to various fields to test to see if i can find any improvements.

i have a problem though as a program runs and instead of updating values deletes a record the by the id field and then re-adds with the new values (yes stupid i know).

what i've noticed is that there is an index on that field set to 'create as clustered'.

I've always avoided the clustered index as i've heard its a bad option, but am wondering that if this value is being deleted and re-created and is set to the clustered mode if this might be a problem?

sql 2000.


Search for Clustered Index in database on varchar, nvarchar column



I have very big database, i am running a Re-index job and it's keep failing throwing everyday error stating "Online index operation cannot be performed on clustered index as index contains column of data type text, nvarchar.

is there any way i can search and change all such datatype of culstered into non-clustered indexes or any better solution i have.

SQL 2005 Inserting into Cluster vs. Non Clustered index table


All things being equal, when inserting data into cluster indexed table, assuming I am inserting in the order of clustered index, is there a difference how sql server will fill in the physical pages compared to if the inserted table had only non-clustered index? 

On a different subject, is the physical location of data on a page effects query performance?  If so, how?

Thanks in advance

Primary, Clustered, IDENTITY key index fragmentation


We have a table with an IDENTITY primary key and a clustered index on this key.  The SQL to create the table is shown below.  After inserting 100 records or less (records aren't deleted or updated, typically), the index is highly fragmented.  This eventually becomes a crucial bottleneck because the table is joined to another table with millions of records to produce a view.

It's easy enough to rebuild the index, but it would be optimal to prevent the fragmentation in the first place.


1) What causes the fragmentation? Is it the fact that the table contains a nvarchar(max) field?  Is it the clustered index?

2) Are there ways to prevent fragmentation in the first place, and if so, how?  I have tried setting PAD_INDEX to ON, and using FILLFACTORS of 30, 50, 80, 90, and 100, all to no avail.  I have also tried converting the nvarchar(max) to a nchar(400).

Thank you.

SQL Server Version: 2008

SQL to create the table, index:

CREATE TABLE [dbo].[MyStrings](
    [StringId] [int] IDENTITY(-2147483647,1) NOT NULL,
    [SecondaryKey] [binary](20) NOT NULL,
    [Text] [nvarchar](max) NOT NULL,

Clustered index gives rise to heavy transaction logging


We recently started purging old data from a table in our production system.  Since then we have experienced excessive transaction logging, system has gone from about 1-3MB every 15 minutes to 5-800MB in the same time with the same number of inserts (data rarely seems to change in this table). 

Table has a clustered index on an ID integer column that automatically increments by 1 for each new record. 

The purging of data is based on a column containing the time of insert plus a status column, so there could be IDs with low values remaining.  The purging job runs once per day and initially removed about 40% of the data in the table.  Now it removes one day every day and we keep a year of data.  The deletions do not seem to create more transaction logging than anything else.

Here are the column datatypes, note the column "data" (ouch) is of type image.  Design of table is from a third party and cannot be changed.

 [ID] [int] IDENTITY(1,1) NOT NULL,
 [jobindex] [nvarchar](100) NULL,
 [sortindex] [nvarchar](100) NULL,
 [metadata] [nvarchar](1000) NULL,
 [data] [image] NULL,
 [readflag] [int] NULL,
 [intjobid] [nvarchar](50) NULL,
 [extjobid] [nvarchar](50) NULL,
 [status] [nvarchar](50) NULL,
 [index1] [nvar

Explicitly defining Clustered Index and NOT NULL constraint on Primary Key column


In the following table definition, is it necessary to explicitly define clustered index and NOT NULL constraint on Primary Key column, since defining Primary Key alone will do the job.

create table t1 (
col1 char(4) Not Null Primary Key Clustered,
col2 varchar(20)

Creating a clustered index on a partioned table - need help understanding effect of filegroup placem


We have a large table ~40 Million rows and about 35 columns.  Things are starting to slow down and i am looking at partioning.  The table has some historical data and most of it can be ignored except for the current year, so I wanted to partion the data on the year column.  So I created a filegroup and ndf/ldf files for each year (ie: 2005,2006,2007, etc...)

The primary key is clustered on an identity column in this table.  When I went to recreate the table I got a warning message that the clustered index would be created on the primary file group as I specified, so i decided to do some research and found this article:


This leads me to believe that creating a clustered idex on the primary file group will prevent true partionting.  Is this true?  If so where should I store my clustered index as this seems to defeat the purpose of partioning then.

Also I was reading something that the clustered index should contain the key column that the data is partioned on, so it should be something like (PriKeyCol, theYear) ? as opposed to just the (PriKeyCol)

Thanks for any help,

Clustered Index & Primary key


I need to understand what is the best practice for me for a Simple Order Entry System.

My Table has two important columns.. Order Number & DATE (When order was placed)

My Order Number is ALWAYS unique so i made it Primary key, there are many others tables which join to this table using Order Number.

Now I use DATE field to return sales within a Date Range...say jan - march orders.

My question is should I change the Primary Key Default Clustered Index on OrderNumber to Primary Key NON CLUSTERED INDEX and then Change DATE to a CLUSTERED INDEX? i want fast performance..please keep in mind orders tables connects to many orders tables and they are joined together by using "Order Number" Column.

Thanks in advance

Toolbox: Manage databases, easier FTP, and clustered caching


This month's products include DatabaseSpy, FileZilla, NCache, and more.

Scott Mitchell

MSDN Magazine October 2007

how to create index for a column


Hi Friends,

                       I need to create the index for a column and accessing that in sqlserver 2005. Please any one help me.


Deleting items from the index (custom protocol handler)

I created a custom protocol handler. I successfully emit entries into the index. I just can't seem to delete them during an incremental crawl.

I tried:

1. no emitting child item during the enumeration of child entries in a folder
2. returning PRTH_E_OBJ_NOT_FOUND from CreateAccessorEx

doing either 1, 2, or both did nothing. The entries are still there.

what i need to do to delete these entries from the index during an incremental crawl?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend