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

Top 5 Contributors of the Month
Melody Anderson
Post New Web Links

Primary, Clustered, IDENTITY key index fragmentation

Posted By:      Posted Date: November 20, 2010    Points: 0   Category :Sql Server

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,

View Complete Post

More Related Resource Links

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)

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

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.

Check Index Fragmentation query

Hi, I have ran the following query to view the fragmentation on my large table with 1000+ pages. Now the results I got are moslty at index level 0 and 1 and most objects are fragmented 90+%. My question to the experts is that the query is displaying fragmentation mostly at Index level 0 and 1. Do I need to look or just Level = 0 is fine? The query is SELECT DB_NAME(SDDIPS.[database_id]) AS [database_name], OBJECT_NAME(SDDIPS.[object_id], DB_ID()) AS [object_name], sddips.[object_id] as [Object_ID] ,SSI.[name] AS [index_name], SDDIPS.partition_number, SDDIPS.index_level ,SDDIPS.index_type_desc, SDDIPS.alloc_unit_type_desc, SDDIPS.avg_fragmentation_in_percent], SDDIPS.[page_count] FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'detailed') SDDIPS INNER JOIN sys.sysindexes SSI ON SDDIPS.OBJECT_ID = SSI.id AND SDDIPS.index_id = SSI.indid WHERE SDDIPS.page_count > 1000 AND avg_fragmentation_in_percent > 15 AND index_type_desc <> 'HEAP' ORDER BY OBJECT_NAME(SDDIPS.[object_id], DB_ID()), index_id

MVC is validating primary key which is identity

Hello,   I am following this tutorial : http://www.asp.net/mvc/tutorials/getting-started-with-mvc-part4 I am where you "Create" an entry  in the table, i have deleted the Id field, as Id is my primary key, and Is Identity = Yes, Identity Increment = 1, Identity Seed = 1. When i hit the create button, the Id value is being validated, giving an invalid error. If i add the Id column back to the page, and fill in any number and click create, the entry is added to the database, with the Id value incremented (the typed in value does not matter). I believe this must have to do with the Movies.Designer.cs... but not sure how to fix it. Id should not be validated as it auto-incraments, but the model is looking for a Id value.... How do i fix this?   Thanks, -Mike

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.

Index fragmentation script runs but yet fragmentation remains high


I have an index maintenance job that runs frequently, what I find is that despite the fact that the script runs, fragmentation is still quite high on the tables. I am using the standard approach of having  a rebuild if fragmentation is over 20 and if its between 5 and 20 then it reorganises the page.

Is this usual for such a behavior ?

clustered index myth


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

Cluster index and primary key


Hi Team,


To day i went one DBA interview 


There that guy is asking what is clustered index and 

a : it is pointing to leaf node.

what is primary key ?

a: it is dont allow null values and duplicate values 

when ever we have create clustered index  in a table automatically primary key is created right  ? -- i am bit confussion here 


all redy atable have primary key  again we have to created clusted index on that id col is it possible or not i am trying here but here it is geeting error.


can you please explaine me step by step the above points plz..














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

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

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