Search Sql server index

Sam OZ
Sam OZ used Ask the Experts™
I have an Sql server 2014 DB .  As part of data migration ( there are about a million row inserted) ,  I get an index error  can't insert duplicate key with unique index 'IDX_DocIndex'.
The duplicate value is ........
I have good reason to believe the duplicate is not present in the DB .   However , How to debug in  this situation ?   How can I search index to see the  duplicate is present ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Well, I would test it:..

The source data first:

SELECT   uniqueColumnsFromDocIndex ,
FROM     sourceTable
GROUP BY uniqueColumnsFromDocIndex
HAVING   COUNT(*) > 1;

Open in new window

Then: is the destination table empty at the start of the data migration? And how do you transfer the data? Are there data modifying triggers on the destination table?
If the above query does not return a row and there is no further data processing during your migration, then you should run a DBCC CHECKDB to ensure database integrity.

p.s. I'd prefer to name unique constraints with the UQ_ prefix. Cause they are part of the semantic data model, even when the implementation detail means, that we need an index. This helps e.g. when optimizing the database.  Cause normal indices can be removed, when unused. But there can be unique constraints, which are necessary to avoid duplicates, but which are never used in queries.

p.p.s. naming an index IDX_someNameINDEX is redundant.


Thanks. But my main question is on how to search the index for a particular value
ste5anSenior Developer

There is no such thing like doing this. Indices are an implementation detail of SQL Server. You search in the table, the query optimizer decides to use the index or not.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
>> But my main question is on how to search the index for a particular value

No, you can't search an index value but you can search the duplicate values in the table/index by running the query which ste5an provided above with slight modification..
FROM sourceTable
where colname = 'ur_search_value'

Open in new window

can you post the index structure with column names to clarify better..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial