• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

Index on field that is primary key

I have inherited a database. There is an index that I believe exists by mistake and want to know if there is any other possible reason it exists before dropping it.

The table has a primary key. In addition, it has an ascending index on that primary key. I see no reason that this key exists.

Is there any reason why I should not just drop it?
0
jasimon9
Asked:
jasimon9
  • 4
  • 3
  • 2
2 Solutions
 
robertjbarkerCommented:
A primary key is a clustered, unique, and by default asscending, index.
Are you sure the index you are looking at is not the primary key index?
0
 
robertjbarkerCommented:
At least that is how a primary key is shown in MS SQL Server.
0
 
jasimon9Author Commented:
I am using SQL server, as you direct your comment.

Yes, I am sure that in addition to the primary key (clustered, unique, ascending). The other index is on the same field and is ascending. Therefore it seems to serve no purpose and only to add overhead.

So I want to drop it.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
robertjbarkerCommented:
It would not seem reasonable to have an index that duplicates the primary key.

The only reason I could think of is that it is possible to direct a query to use a specific index. If someone has done that somewhere in a query or stored procedure, that code would break. To write such code might be unwise. That doesn't mean it hasn't been done.
0
 
jasimon9Author Commented:
I am very confident that there would be no such code. And if it broke it--we could quickly remedy it.

Based on your comment I am going to just go ahead and drop that index. And will award points. Will let you know if there is an adverse outcome--otherwise, you can assume everything went fine.
0
 
Arthur_WoodCommented:
there is no reason for a second index on the primary Key, but in turn, its existence does not affect anything, so why worry about it?

AW
0
 
jasimon9Author Commented:
I don't like leaving a mess around waiting for something to go wrong. I like to keep a "tidy" system.

Are you saying the overhead (space and update time) is negligible?

0
 
Arthur_WoodCommented:
yes, it is .
0
 
jasimon9Author Commented:
I am going out of town for a few days. Won't be able to evaluate the responses until new week.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now