Answered by:
turn case sensitivity off sql server 2005

Question
-
I've inherited a project that has a few quirks. One of them is this case sensitive sql server. I've read that it has to do something with the colation. How do I turn this off for the entire database? I want it to be case INSENSITIVE.Monday, January 17, 2011 11:04 PM
Answers
-
You are right that collation determines case-sensitivity. Collation is specified at the instance, database or column level. The instance collation determines case-sensitivity of variable names and labels while the database collation determines sensitivity of identifiers and literals. Case-sensitivity of database data is determined by the column collation, which inherits the database collation when no explicit collation is specified.
It is necessary to reinstall SQL Server or rebuild the system databases in order to change the instance collation. You can change the database collation with ALTER DATABASE, but there are some caviats. First, you might run into identifier name conflicts when you change from case-sensitive name to case-insensitive collation. Also, this does not change the collation of existing columns. You'll need to alter the individual columns or recreate the tables as desired.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/- Edited by Dan GuzmanMVP Monday, January 17, 2011 11:28 PM fixed typo
- Proposed as answer by Alex Feng (SQL) Friday, January 21, 2011 6:11 AM
- Marked as answer by Alex Feng (SQL) Tuesday, January 25, 2011 11:54 AM
Monday, January 17, 2011 11:26 PM
All replies
-
You can not turn it off. The best solution may be re-installing SQL Server with the correct collation.
Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
My blogMonday, January 17, 2011 11:24 PM -
You are right that collation determines case-sensitivity. Collation is specified at the instance, database or column level. The instance collation determines case-sensitivity of variable names and labels while the database collation determines sensitivity of identifiers and literals. Case-sensitivity of database data is determined by the column collation, which inherits the database collation when no explicit collation is specified.
It is necessary to reinstall SQL Server or rebuild the system databases in order to change the instance collation. You can change the database collation with ALTER DATABASE, but there are some caviats. First, you might run into identifier name conflicts when you change from case-sensitive name to case-insensitive collation. Also, this does not change the collation of existing columns. You'll need to alter the individual columns or recreate the tables as desired.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/- Edited by Dan GuzmanMVP Monday, January 17, 2011 11:28 PM fixed typo
- Proposed as answer by Alex Feng (SQL) Friday, January 21, 2011 6:11 AM
- Marked as answer by Alex Feng (SQL) Tuesday, January 25, 2011 11:54 AM
Monday, January 17, 2011 11:26 PM