MS SQL Server - Unique Indexes

Author

Unique Indexes

MichaelW

2005-04-29, 8:23 pm

I'm supporting a database that has a Unique Index and yet that column has
duplicate values. How can that happen?

Jens Süßmeyer

2005-04-29, 8:23 pm

What kind of column is this index for (datatype, etc.)

From BOL:

Microsoft® SQL ServerT checks for duplicate values when the index is created
(if data already exists) and checks each time data is added with an INSERT
or UPDATE statement. If duplicate key values exist, the CREATE INDEX
statement is canceled and an error message giving the first duplicate is
returned. Multiple NULL values are considered duplicates when UNIQUE index
is created.

These values you identified must differ from each other in any way, perhaps
there are (trailing) spaces involved ?

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

"MichaelW" < MichaelW@discussions
.microsoft.com> schrieb im Newsbeitrag
news:87D6720D-206F-4AF3-95D9- FA42765DACAD@microso
ft.com...
> I'm supporting a database that has a Unique Index and yet that column has
> duplicate values. How can that happen?

MichaelW

2005-04-29, 8:23 pm

The column is varchar and is used for a usernames. I was using bcp to move
data from tableA to tableB. So there are 3 usernames that are very close in
naming using extended ascii characters like Ã« Ãª Ã¨. I used the native option
for bcp and those 3 characters ended up as e e e in tableB making 3 usernames
the same. So that's why the unique index creation was failing.

"Jens SÃ¼ÃŸmeyer" wrote:

> What kind of column is this index for (datatype, etc.)
>
> From BOL:
>
> MicrosoftÂ® SQL ServerT checks for duplicate values when the index is created
> (if data already exists) and checks each time data is added with an INSERT
> or UPDATE statement. If duplicate key values exist, the CREATE INDEX
> statement is canceled and an error message giving the first duplicate is
> returned. Multiple NULL values are considered duplicates when UNIQUE index
> is created.
>
> These values you identified must differ from each other in any way, perhaps
> there are (trailing) spaces involved ?
>
>
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
>
> "MichaelW" < MichaelW@discussions
.microsoft.com> schrieb im Newsbeitrag
> news:87D6720D-206F-4AF3-95D9- FA42765DACAD@microso
ft.com...
>
>
>

Andrew J. Kelly

2005-04-29, 8:23 pm

Was it created with the IGNORE_DUP_KEY option?

--
Andrew J. Kelly SQL MVP

"MichaelW" < MichaelW@discussions
.microsoft.com> wrote in message
news:87D6720D-206F-4AF3-95D9- FA42765DACAD@microso
ft.com...
> I'm supporting a database that has a Unique Index and yet that column has
> duplicate values. How can that happen?

MichaelW

2005-04-30, 3:23 am

Yes I did, but that will only help you after an index is in place. I was
adding indexes to the new table that already had data in it.

"Andrew J. Kelly" wrote:

> Was it created with the IGNORE_DUP_KEY option?
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "MichaelW" < MichaelW@discussions
.microsoft.com> wrote in message
> news:87D6720D-206F-4AF3-95D9- FA42765DACAD@microso
ft.com...
>
>
>

Jens Süßmeyer

2005-04-30, 3:23 am

If you got a Accent Sensitive collation (_AS) for the database this
behaviour is ok for me, because characters (and index entries differ from
each other)

http://www.databasejournal.com/feat...cle.php/3302341

Try to change the coallation of the database or in detail for the specified
column to a Accent Sensitive format to see wheter that helps.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

"MichaelW" < MichaelW@discussions
.microsoft.com> schrieb im Newsbeitrag
news:3406C072-F977-459B-9F80- 3CABB5568FE4@microso
ft.com...[color=darkred]
> The column is varchar and is used for a usernames. I was using bcp to
> move
> data from tableA to tableB. So there are 3 usernames that are very close
> in
> naming using extended ascii characters like ë ê è. I used the native
> option
> for bcp and those 3 characters ended up as e e e in tableB making 3
> usernames
> the same. So that's why the unique index creation was failing.
>
>
> "Jens Süßmeyer" wrote:
>

MichaelW

2005-04-30, 7:23 am

Thanks for the link. I don't think it is a server collation issue. I think
it is a bcp conversion problem. Has anyone seen this issue with bcp before?
I will be running more tests in the morning.

"Jens SÃ¼ÃŸmeyer" wrote:

> If you got a Accent Sensitive collation (_AS) for the database this
> behaviour is ok for me, because characters (and index entries differ from
> each other)
>
> http://www.databasejournal.com/feat...cle.php/3302341
>
> Try to change the coallation of the database or in detail for the specified
> column to a Accent Sensitive format to see wheter that helps.
>
>
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
>
>
> "MichaelW" < MichaelW@discussions
.microsoft.com> schrieb im Newsbeitrag
> news:3406C072-F977-459B-9F80- 3CABB5568FE4@microso
ft.com...
>
>
>

Jens Süßmeyer

2005-04-30, 7:23 am

Is your issue to create index or to bcp the data out ?

bcp has serveral switches for pumping out native data, an example is -C, the
import routine depends in the data you have. Is it unicode or system native
?

-C codepage
Code page being used by the import file. Only relevant when the data
contains char, varchar, or text columns with character values greater than
127 or less than 32. Use the code page value ACP with ANSI ISO 1252 data,
RAW when no conversion should occur, OEM to use the client's default code
page, or type a specific code page value, such as 850.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

"MichaelW" < MichaelW@discussions
.microsoft.com> schrieb im Newsbeitrag
news:52646E0E-99EA-43BB-BCE2- 449D5F6AA64E@microso
ft.com...[color=darkred]
> Thanks for the link. I don't think it is a server collation issue. I
> think
> it is a bcp conversion problem. Has anyone seen this issue with bcp
> before?
> I will be running more tests in the morning.
>
> "Jens Süßmeyer" wrote:
>

Sponsored Links