Just How Does SQL Server Define A Unique Index

Question

Just How Does SQL Server Define A Unique Index

JC-3113

SSCrazy Eights

Points: 8371
More actions
October 12, 2010 at 3:00 pm

#229625

Hi Folks
I am trying to manually import an Oracle table into SQL Server
In SQL Server:
I created the table
I used BCP to import the data
Now i am trying to put a UNIQUE index on the table using (parentid,ownerid,name) of the table
SQLServer will not allow it because it looks like it does not seem to distinguish between upper and lower case names:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DTREE' and the index name 'DTREE_PRIMARY'.
The duplicate key value is (-14997325, 14997325, 1 Vote for slide 44).
I dumped the data from Oracle:
DATAID PARENTID OWNERID NAME
---------- ---------- ---------- ------------------------
14996579 -14997325 14997325 1 Vote for slide 44
14995417 -14997325 14997325 1 vote for slide 44
In Oracle this was considered a UNIQUE index
In SQL Server it does not seem to be true. Is it ?????
How do i get SQLServer to distinguish that these two records are not the same when i try to index the table
Thanks
Jim

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

The default collation in SQL Server is case insensitive. You need to change the collation of either the columns, the table, the database, or the server to a case sensitive collation if you want to mimic the Oracle behavior. The level at which you need to reset the collation will depend on your requirements for this data. If you have other tables you are importing and want this whole db to mimic Oracle, you might want to change it at the DB level. It can be quite confusing, in my opinion to have columns and tables with mixed collation. In any case you probably need to take a look at collation in BOL.

And then again, I might be wrong ...
David Webb

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Hi David

well..looking up these collation codes (which you need a PHD to review them all)

I have

SQL_Latin1_General_CP1_CI_AS

but i do not see what it should be chaged to in reviewing

http://msdn.microsoft.com/en-us/library/ms143508(v=SQL.100).aspx

any idea on what i can do ?

Thanks

Jim

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

This should be valid:

SQL_Latin1_General_Cp1_CS_AS

as an alternative, per:

http://msdn.microsoft.com/en-us/library/ms180175(v=SQL.90).aspx

Are you running 2008?

And then again, I might be wrong ...
David Webb

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

You can get the valid ones for 2008 by:

select * from fn_helpcollations()

And then again, I might be wrong ...
David Webb

lukus_g SSC Eights! Points: 958 More actions · Answer 5

are you running an application from this DB?

i'd exercise caution before changing a collation from case insensitive... (if your Oracle was indeed case sensitive then there should not be an issue.)

If it is an issue to move to case sensitive perhaps consider using another value in your key, like the DATAID field you have in your Oracle output, which looks like a unique row id.

norie Right there with Babe Points: 789 More actions · Answer 6

http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 7

JC-3113

SSCrazy Eights

Points: 8371

October 13, 2010 at 8:31 am

#1236254

HI David

thanks

i will look into these documents

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

Hi luckus_g

it is not an issue to change it

i am in just testing creating tables and indexes and importing data right now

so i can start over after i make the change

Thanks

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 9

JC-3113

SSCrazy Eights

Points: 8371

October 13, 2010 at 8:35 am

#1236259

Thanks norie

will take a look

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 10

I am running the tests on windows server 2008 with sql server 2008

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 11

Hi Folks

I made the change to the database after dropping all tables as it would not allow me change the COLLATE value until I did so

a follow-up question and why

it seems that changing the database from case insensitive to case sensitive causes the database tables to become unknown

when trying to select or do an sp_help on them:

can someone tell me why the heck Microsoft would do something so stupid.

I just want to change the data, not every object along with the data in the database

Microsoft treats data and objects the same when it comes to case sensitivity???

Any incite is appreciated

Thanks

Jim

David Webb-CDS SSCoach Points: 17398 More actions · Answer 12

Yes, once a db is case sensitive, it's ALL case sensitive. I don't see a lot of case sensitve SQL Server installations in the wild.

You should be able to apply the case sensitivity at a more granular level (table or column). What happened when you tried that?

And then again, I might be wrong ...
David Webb

WayneS SSC Guru Points: 95409 More actions · Answer 13

David Webb-200187 (10/12/2010)
This should be valid:
SQL_Latin1_General_Cp1_CS_AS

JC-3113 (10/12/2010)
I have
SQL_Latin1_General_CP1_CI_AS

Just a quick note: the "_CI_" in the second one indicates case-insensitive.

The "_CS_" in the first one indicates case-sensitive.

JC-3113 (10/13/2010)
Hi Folks
I made the change to the database after dropping all tables as it would not allow me change the COLLATE value until I did so
a follow-up question and why
it seems that changing the database from case insensitive to case sensitive causes the database tables to become unknown
when trying to select or do an sp_help on them:
can someone tell me why the heck Microsoft would do something so stupid.
I just want to change the data, not every object along with the data in the database
Microsoft treats data and objects the same when it comes to case sensitivity???
Any incite is appreciated
Thanks
Jim

Once the database is in a case-sensitive collation, then:

CREATE TABLE MyTest (col1);

SELECT * from mytest; -- this will fail - no such table

SELECT Col1 from MyTest; -- this will fail - no such column

And I haven't tested this, but it's quite possible (if your db is named MyOracleDBinSS), that

SELECT * from myoracledbinss.dbo.MyTest

will also fail.

Once a database is case sensitive, you have to be sensitive about what case you're using.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 14

Hi WayneS

I guess I found that out, but why ?

Why would you make the object(s) case sensitive at all ?

I just want the data to be case sensitive

are you telling me that a table name called "Table1" is not the same as one called "table1"

if that is the case

what an impact that is on typing in sql

Thanks

Jim