locked
turn case sensitivity off sql server 2005 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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/
    Monday, January 17, 2011 11:26 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    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 blog
    Monday, January 17, 2011 11:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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/
    Monday, January 17, 2011 11:26 PM