Database Collation: Case Sensitive or Insensitive
-
I'm looking for input/opinions on setting database collation with respect to CASE SENSITIVITY.
Simply put, What are to pros and cons to setting a database to Case-Sensitive vs. Insensitive.
**Yes, I do know the general obvious answers, I'm looking more for opinions and standards of practice arguements.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
I have one client that put their database in case sensitive collation prior to running the installation scripts;
our app has a typical search screen kind of thing, so the search form ends up creating some dynamic SQL, like UPPER(LASTNAME) LIKE 'SMITH%' to work around that issue...where the Real data is 'Smith' and we can't use the index on the column;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
CS = Annoying as hell, even with intellisense. This is from the dev standpoint of course.
-
I completely agree it's annoying as hell. What I'm looking for are arguments for and against.
I am 100% against CS, FYI.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
The only other argument I have is when you finally find a script that does what you need on the web, 95% of the time it'll fails because of the case of the objects or columns names.
That's about as good as I can find for positive (can't run web code without reading it).
Obviously withstanding the obvious annoyance that comes with that.
-
Jason Selburg (11/7/2011)
I completely agree it's annoying as hell. What I'm looking for are arguments for and against.I am 100% against CS, FYI.
You are asking specifically about case-sensitivity for database collations and not column collations?
Unfortunately setting the database collation makes identifiers case-sensitive. This is an awkward limitation of SQL Server. If you are a software vendor then you may have to support unknown collations on your customers' machines, which can cause your code to fail if you develop and test against a case-insensitive collation (because identifiers in code use the same collation as the database setting). So if you are a software vendor or anyone else who doesn't control the collation of the target server / target database then it's sensible to develop and test with a CS collation. It would be a useful improvement if Microsoft provided a separate setting for case-sensitivity of identifiers.
Regarding column collations. Column collation should be determined by business requirements. CS makes perfect sense for many things and not for others. I don't think developers and database professionals need to have an opinion for or against case-sensitivity.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply