Database Collation: Case Sensitive or Insensitive

Question

Database Collation: Case Sensitive or Insensitive

Jason Selburg

SSC-Insane

Points: 24641
More actions
November 7, 2011 at 7:52 am

#245692

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

CS = Annoying as hell, even with intellisense. This is from the dev standpoint of course.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

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.