February 7, 2012 at 8:37 am
I have to contend with an app that the developers use called WebCodeGen. While I am not familiar with it, i understand it creates C# code and SQL stuff with a few mouse clicks. One of the things that this software does, is create SQL statements for new table creation. So, over the years, tables have been created as a default setting with something similar to this;
CREATE TABLE [dbo].[Client](
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[ClientName] [varchar](255) NOT NULL,
[Address] [varchar](255) NULL,
[City] [varchar](50) NULL,
[StateID] [int] NULL,
[Zip] [varchar](10) NULL,
CONSTRAINT [Client_PK] PRIMARY KEY CLUSTERED
(
[ClientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
So far, so good right? (except for the PRIMARY statement....)
Here is what i am noticing though. Even though webcodegen defines ClientID as a Clustered Index, the index itself is never used in a WHERE statement. The app will use something else, like ClientName in their WHERE statements. Of course, ClientName has a NON CLUSTERED index on it, but SQL is having to maintain the CLUSTERED index.
SO my question is. If i can view via DMV's that these CLUSTERED index are never ever used, is there any harm in dropping them?
My guess is that it is no big deal to drop them, but wanted to bounce this off everyone first.
February 7, 2012 at 8:51 am
It is recommended to have a Clustered Index on all tables (except where there are very few rows)..
So I would not want to drop those clustered indexes.. If you have a choice of better column to be the clustered index then I would say, yes drop this and have a clustered index on another column..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 7, 2012 at 8:57 am
I would very strongly recommend not dropping the clustered index unless you have a better place to put it.
Indexes supporting unique constraints (and primary keys are a specialisation of unique constraints) can never be said to be unused. They are enforcing uniqueness, it's a design aspect of the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 9:02 am
ok, i respect both your opinions and thank you for your input.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply