December 14, 2007 at 11:14 am
Is it possible to set just the PK Constraint of a table to be case sensitive?
If so, how?
Why do you ask? Because I am migrating an Oracle table to SQL and the PK Constraint in Oracle is case sensitive and in SQL it is by deafult not.
Thanks
December 14, 2007 at 11:33 am
Set the collation of the PK column to a case sensitive collation.
December 14, 2007 at 11:40 am
OK, I am in the Colation screen and I selected "Windows Collation", selected "Dictionary Sort" and checked "Case Sensitive".
Now what Collation Country/Language do I select in the drop down list under "Windows Collation"? I don't see English, USA or America.
December 14, 2007 at 12:30 pm
You should read about collations in SQL Server Books Online.
December 14, 2007 at 1:12 pm
Yes, I would highly recommend that you investigate collations further before making any changes.
Two major aspects that you need to be aware of are:
1) SQL collations are marked for deprecation
2) if you mark the column as using a different collation to the server collation, you may have additional work to do when dealing with temporary tables.
December 14, 2007 at 6:06 pm
1) SQL collations are marked for deprecation
What should you use instead?
--Jeff Moden
December 17, 2007 at 9:36 am
If it's "deprecated" why the hell is there then? Why doesn't SQL allow case sensitivity to a column? Oracle does! Why can't SQL allow this feature with the default language it selects when it's installed? Where the hell is English, America or US when you select a damn language?
This is frustrating as hell. I'm starting to think converting this DB to SQL from Oracle is a mistake. SQL seems to have too many dumb quirks about it.
December 17, 2007 at 10:03 am
Deprecated means it might not be supported in FUTURE versions of SQL server. they're giving you time to switch your code around. Some features are marked as deprecated for several versions.
As to collation support being deprecated - that's the first I've heard of it. Matt Stockham - where did you see that? There's nothing included in the "deprecated list" I saw that mentioned that. The only thing mentioning deprecation and collation were 3 specific collations being dropped.
As for language switching - there's no reason you can't use simple syntax like:
use language English -- that's US english
go
use language British -- that's British english
go
Finally - stick with it... You are starting to sound like me when I'm sitting in front of Oracle. It's probably just a matter of familiarity. They ARE different, so expecting one to act like the other...well - ain't going to happen so no sense in howling at the moon.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 17, 2007 at 10:39 am
Ha, good question .... I thought it was from an article on this site, possibly a post, and I think I remember linking to some Microsoft document. I haven't been able to find anything in a search yet, I'll try again later. It's quite possible that I misread or my memory is faulty .... if so, I apologize for misleading.
SQL collations are intended for backwards compatibility (according to BOL and the installation screen I believe) and don't cover all the combinations that can be obtained using Windows collations (according to SQL2005: the Storage Engine). Seems odd that a SQL collation is the default for machines using US English - almost perpetuating the need for itself.
As far as Oracle is concerned - how does it handle sort orders, character sets etc? Presumably you can apply the same settings to SQL.
December 17, 2007 at 3:07 pm
Well - like I said - let me know if you have any luck finding it. I just haven't seen anything on it, and might need to scramble on a few things if that IS true.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 18, 2007 at 6:25 am
Here's another SQL Server "quirk" I ran into when we were converting a DB2 database to SQL 2005. We made the collation case sensitive, because we were loading data, and received primary key violations. It turned out that the data was case sensitive, so we had to change it in our DB. As a consequence, then all of our SQL statements had to be case sensitive (table names and column names) as well.
December 18, 2007 at 9:41 am
Did tou make colation change at the SQL Server level, DB level, Table level or Column? I would like to know because we made the change at the Column level in one table. If it makes the entire table case sensitive then we will not be able to use colation at the column level.
Thanks
December 18, 2007 at 12:14 pm
I believe we changed the collation at the DB level. I just did a quick test where I had a table with a varchar PK, changed it to a case sensitive collation, and it seemed to have no effect on the select statement. Sorry for causing unnecessary worry, but it caused a great deal of hassle when we converted this particular DB, because I was not expecting the behavior that we saw. In this case it looks like it is doing the logical thing.
December 18, 2007 at 1:15 pm
No worries. We ran into a problem with the first collation change at the column level. We had to remove it and put it back because other PK/FK and Constraints conflicted with it. We're going to have to deal with it at the applicaion end..........I hope.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply