SQL Server 2008 Foreign Keys case sensitivity

Is it possible for SQL Server 2008 to have case insensitive data, for instance the following would return data…

SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case'

If mytable.mycolumn had a value ‘CASE’ but for foreign keys to be case sensitive?

  • SQL Server 2005/2008 - Import a fixed width text file via the command line?
  • Using SSIS to build a database from a VS2005/2008 database project
  • Error when creating a Controller in Visual Studio 2012
  • Can't read data from varchar(max) using ADODB
  • Not allowing column values other than what is found in other table
  • SQL Server installation / configuration error
  • 3 Solutions collect form web for “SQL Server 2008 Foreign Keys case sensitivity”

    Case sensitivity is determined by the collation settings of the database. It affects everything, comparisons, foreign keys, etc…

    However, you can change the collation setting on a particular column or columns, to make comparisons with it case sensitive while the rest of the DB stays case insensitive. For example:

    COLLATE SQL_Latin1_General_CP1_CS_AS 

    Now all comparisons with Name will be case sensitive.

    One way to perform a case insensitive comparison given a case sensitive collation is to specify a per column collation cast as part of the query. You may also want to familiarize yourself with collation precedence if you use this method to change collation on the fly. Here is an example of a collation cast being specified at the point of query:

    SELECT Name 
    FROM MyTable
    WHERE Name = 'CASE' COLLATE SQL_Latin1_General_CP1_CI_AS -- Use case insensitive coll.

    The collation of a column can be changed interactively using the Table Designer is SSMS (sorry for the large image):

    enter image description here

    If you want the foreign key to be case sensitive, then just set the column to be case sensitive (and make sure that the key it references uses the same collation). Borrowing the collation from Michael’s answer:

    USE tempdb;
    CREATE TABLE dbo.foo 
      [key] VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CS_AS
    CREATE TABLE dbo.bar 
      [key] VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CS_AS
      FOREIGN KEY REFERENCES dbo.foo([key])
    INSERT dbo.foo SELECT 'Bob';
    INSERT dbo.foo SELECT 'bOB';
    INSERT dbo.foo SELECT 'BOB';
    -- fails:
    INSERT dbo.bar SELECT 'bob';
    -- succeeds:
    INSERT dbo.bar SELECT 'Bob';

    If you want queries against the same column to be case insensitive, you can just specify a COLLATE clause (note it contains _CI_ instead of _CS_):

    SELECT COUNT(*) FROM dbo.foo 
      WHERE [key] = 'Bob';
    SELECT COUNT(*) FROM dbo.foo 
      WHERE [key]  COLLATE SQL_Latin1_General_CP1_CI_AS = 'Bob';

    Absolutely. A lot depends on the SQL Server collation chosen some of which are or aren’t case sensitive.

    Selecting a SQL Server Collation

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.