question about indexes

Question

Post reply

question about indexes

Jpotucek

SSCoach

Points: 18583
More actions
December 15, 2008 at 1:17 pm

#94286

I have a table 163,000 rows
CREATE TABLE [UWWorksheet].[PolicyInfo] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[polnum] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[memfname] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[middleinitial] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[firstname] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Riskamt] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mib] [bit] NOT NULL ,
[requestmib] [bit] NOT NULL ,
[requestby] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reviewedby] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[baserating] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[flateextrarating] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[duration] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[exclusions] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[otherexclusion] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[businessdecision] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reconsideration] [bit] NOT NULL ,
[timeframe] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dob] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[occupation] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BirthState] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsbaserating] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsflatextrarating] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsduration] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsexclusions] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsbusinessdecision] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsotherexclusion] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[amendement] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[repository] [bit] NOT NULL ,
[nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reinsurance] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[issued] [bit] NOT NULL ,
[isseddate] [datetime] NULL ,
[reopened] [bit] NOT NULL ,
[reopendate] [datetime] NULL ,
[secondinslastname] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsfirstname] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsmiddleinitial] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[secondinsdob] [datetime] NULL ,
[underwriter] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[decisionmadeby] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
There are TWO indexes on polnum column . one is PK and is clustered . one is non-unique index. Is this a bad thing?? will it help to drop the 2nd?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I just want to be sure what you are asking. You have two indexes on this one column:

[polnum] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

One index is a primary key the other is a non-unique index. First, it has to be unique as it is a primary key. Second, the "non-unique" index is just taking up resources; disk space, processing time, etc. You should drop the "non-unique" index.

Jpotucek SSCoach Points: 18583 More actions · Answer 2

both indexes are on only one column - polnum. What - if any - damage could I cause if I dropped the non-unique - non-clusered index and left the clustered/unique PK index??? the only thing I can think of is if someone hardcoded the index name.. is that even possible????

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Jpotucek (12/15/2008)
What - if any - damage could I cause if I dropped the non-unique - non-clusered index and left the clustered/unique PK index???

There shouldn't be any damage

the only thing I can think of is if someone hardcoded the index name.. is that even possible????

It is, in an index hint. It's not something that should be done though. Perhaps query syscomments and see if you can find any reference to the index before dropping it.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jpotucek SSCoach Points: 18583 More actions · Answer 4

thank you. APP\DEV is looking at the code to see if this particular index is named anywhere before I drop it.