[Closed] Programmer/DBA question

The first question to ask is what makes a unique row in the table.

Does seem a bit odd. If Index2 uniquely identifies a row with two atttibutes then that sounds it should be the primary key. If it doesn't then it can't be unique.

Any ideas?

And just to add, if you remove the unique constraint from index 2 then it is superfluous and can be removed completely.

developers love indexes (indices? -indexes just sounds wrong to me). That and functions. I bleeding hate functions; saved 35 mins on one process changing from a function to a simple table lookup.

i have one table in one DB that has 150Gb of data and 560Gb of indexes. They refuse to entertain the thought of removing them, despite me showing they're not used.

Which brings me to my main point (sorry, rambling). Check whether the index is being used [url= https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/ ]here[/url]; if not, get rid. If it is, you can tell what is using it, and then see whether that should.

The PK does uniquely identify the record - 100% sure of that.

On the system I'm working on, a index is required if one wants to retrieve in that seq (sounds crazy but its true - I think its a throwback from AS400 days). Its not necessary there for performance. So Index 2 is probably used somewhere in the application/business logic. However I can't see why it need to be unique.

I think the original developer made a boo boo.

The PK does uniquely identify the record - 100% sure of that.

In which case index 2 is enforcing a different constraint to the PK and it should be removed.

The existing PK will still utilise the index if only column A and B are provided.

What problems is it causing?

The majority of the time - jut AttributeB alone is enough to be a alternate key. Once in a very blue moon - its not.

Were looking at making a change to the system where the blue moon could happen much more often.

Index 2 has been on the system for 15 years at least. It has caused problems in the past - and there have been bandaids and work-arounds applied. No one has suggested to just remove the unique constraint - because no one has taken the time to try and understand it....until now.

Thanks all - I'll recommend the unique constraint be removed.

I'll recommend the unique constraint be removed.

Remove the index completely - it's not needed.

if you need it for the retrieval of data in sequence (DB2?), are you sure that data sequence doesn't care about the uniqueness of C, but needs it for A and B? Without knowing DB2 too well, it's hard to tell, but it is superfluous from a T-SQL aspect.

I hear ya but can't do that - Somewhere in the system it will be hard coded to use Index 2. If I remove it the app will fail to execute irrespective of whether the DB uses it or not for the fetch.

Cheers.

Is it possibly not being used as an index but is rather a cheat to ensure you only ever have one value of attribute C for a pair of attribute A & B? If you remove it then you have the possibility of more than one value of C and maybe that is the no no?

But I'm trying to understand why the original programmer made Index 2 unique. Any ideas?

Been a long time since I had anything to do with DB2 but, could it be that when the table was originally set up it was thought that A,B and C were required to make a row unique, but at a later date it was realised that actually only A, and B were required. If it's a large table changing the primary key of a table can be very time consuming and could mean down time or delays you couldn't afford, but adding a new index that effectively enforces A and B as a unique key for a record would be done in the background and not have any significant affect on the systems performance while it was being created...