Search Sql server index

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 ?
Sam OZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
Sam OZAuthor Commented:
Thanks. But my main question is on how to search the index for a particular value
ste5anSenior DeveloperCommented:
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.