ansaurus

Question

CANNOT create unique index in SQL Server, how to know what index is the index ID referring to

Answer 1

+1 A:

Insert print statement before every significant step (say, create unique index) in the script and you're done.

It's usually done like this:

if @@error <> 0
   PRINT '@@error is ' + ltrim(str(@@error)) + '.'
else
   print 'Index IX_... successfully created'

Denis Valeev 2010-09-22 07:21:17

Thank you Denis. That is an option but it still requires to roll back all the changes and test again. Besides, I will need to remove that statement for each significant step once the cause is identified.

newguy 2010-09-22 07:26:41

@newguy Why would you want to remove it?

Denis Valeev 2010-09-22 07:35:39

No idea. I haven't done that before. I think it might take more time to produce the output so what if I run 20 of the similar scripts every day?

newguy 2010-09-22 07:39:00

@newguy Unless you put this `print` statement in a while loop with millions of iterations there's no harm done.

Denis Valeev 2010-09-22 07:41:50

If every query starts with "CREATE UNIQUE INDEX" uses this statement then they will all look the same when an error occurs. I am still not able to know where the error is from. I'll probably need to count the number of the "CREATE UNIQUE INDEX" queries and count to see what position does the error prints out. So I don't think this is a very good idea. Is there any better way?

newguy 2010-09-23 00:28:59

@newguy You're missing the point. You will know exactly which index it is because you will specify that in the error message OR you can deduce by looking at the last success message that printed out.

Denis Valeev 2010-09-23 05:52:49

Thanks I used this to find out where the error is coming from.

newguy 2010-09-24 01:16:16

Answer 2

+1 A:

You say a script with thousands of lines, eh?

My advise: put a print("Test") in the middle and see wether the error occurs before or after. And then again in the middle of the middle etc. until you find the place that is causing you the troubles.

Yves M. 2010-09-22 07:27:19

Answer 3

A:

The table you're working on already contains data; and the data isn't unique with regard to your new index.

Example:

 col1 | col2 | col3
====================
 foo  | 1    | q
 bar  | 2    | w
 bar  | 3    | e
 bar  | 2    | r

In the above table, you couldn't create a unique index on (col1,col2), exactly because the data in it would be non-unique (multiple rows with (bar,2)). The script can't know which of those "duplicate" rows is actually needed. There are three options available to it:

create a UNIQUE index with duplicate rows (invalid, as it's not unique any more)
delete the duplicate rows (unsafe, how can it know which rows are needed?)
do nothing and throw an error (safest option, you are here)

What you can do to resolve this:

run a query to find duplicates - if you group the rows by those columns used by the index, some of the groups will have multiple rows. Those are your duplicates; you need to somehow eliminate their duplicity.

Piskvor 2010-09-22 07:28:57

Yes I know. The problem is I don't know which table and which index the error is referring to.

newguy 2010-09-22 07:33:47

@newguy: I have misunderstood the question then. Sorry.

Piskvor 2010-09-22 07:42:24

Answer 4

A:

If you are running this script in SSMS, just double click on the error and it will take you to the line of code that has caused the error...

ck 2010-09-22 07:38:05

It says `Line 1` right in the message, so it's probably a dynamic sql thing.

Denis Valeev 2010-09-22 07:45:39

IIRC, a `GO` causes the line number to reset back to 1, so this should still work

ck 2010-09-22 08:07:19

You are right, there is a `GO` after each query.

newguy 2010-09-22 23:50:33

ansaurus

tags:

views:

answers:

CANNOT create unique index in SQL Server, how to know what index is the index ID referring to

What you can do to resolve this:

related questions