View Complete Post
Can we you create a non-clustered index over a column already having 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,
I have query that behaves much differently the moment I modify the Where clause
The following query runs fast!!
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?
hi,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.thanks
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.
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.
Hello Forum Members,
I have a table with 10K rows which does not have any indexes.But when I select the table properties,click on storage I see index space has a value and is not null as i thought. Will sqlserver add deafult index space even if there are no indexes on
the table?? Please advise.
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
On a different subject, is the physical location of data on a page effects query performance? If so, how?
Thanks in advance
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).
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,
CONSTRAINT [PK_MyStrings] PRIMARY KEY CLUSTERED ([StringId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECO
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,
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,
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 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,
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.
Let go of your traditional relational database thinking if you want to understand how Windows Azure Table storage works, says Julie Lerman. Luckily for you, she's done the hard work to grasp the new concepts and help you get up to speed.
MSDN Magazine July 2010