question on indexes?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

question on indexes??

Author

Topic

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2008-12-15 : 15:20:17

SQL Server 2000 sp4

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 ,
	[note] [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?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-15 : 15:22:25

why there is a need of non-unique index on column which is already primary key Clustered?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-12-15 : 15:23:36

Is that the only column in both the indexes? You can drop the non-clustered index if you have large number of queries based on the polnum and the column itself is highly selective.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

itsonlyme4
Posting Yak Master

109 Posts

Posted - 2008-12-15 : 15:37:05

Good question so deep!!!! "why there is a need of non-unique index on column which is already primary key Clustered?"

I have no idea. I didn't write the app or design the database.

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????

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-15 : 16:01:51

Like Dinakar said you need to drop that non-clustered index (non-unique).The Pk is already highly selective and there is overhead for maintaining same column as unique and non-unique when you have toomuch DML operations.You need to see if your unexperienced developers have implemented hard-coded .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources