1

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?

David
  • 19,577
  • 28
  • 108
  • 128
  • No it's not because I know the case sensitivity of the db, but I want to know if it's possible to have varying sensitivities (one for the db, and one for foreign keys) – David May 01 '12 at 13:53
  • You asked a question on an answer below, then retracted it. I'll answer anyway: As long as your `WHERE` clauses don't involve the keys, yes, you can have case sensitivity on keys but not on where clauses against other data. You set a case-sensitive collation for the key columns and leave the rest of the data as case insensitive. In cases where you want where clauses involving the keys to not care about case sensitivity (though I'm not sure why), then use `WHERE key COLLATE LIKE '%foo%';` – Aaron Bertrand May 01 '12 at 14:00
  • @Siva I don't understand your question. Two different WHERE clauses that mention different columns with different collations? Or WHERE col_in_collation_A = col_in_collation_B? Like any collation conflict you need to deal with it. You can tell any column or expression to be interpreted in a specific collation. – Aaron Bertrand May 01 '12 at 14:02
  • See here: http://stackoverflow.com/a/1224381/905651 – weenoid May 01 '12 at 13:49
  • @Siva I believe that depends on whether the collations are compatible. Many aren't, in which case you get a collation conflict error and need to work around it by specifying the collation on one column or the other. I have never heard of there being a precedence, but I've never done any research into that, so it may just be ignorance. – Aaron Bertrand May 01 '12 at 14:20

3 Answers3

6

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:

ALTER COLUMN Name VARCHAR(50)  
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

Michael Goldshteyn
  • 71,784
  • 24
  • 131
  • 181
  • FWIW you didn't really address how to treat queries against this column (e.g. `WHERE Name = 'BoB'`) case insensitive, which was part of the question. – Aaron Bertrand May 01 '12 at 14:25
3

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;
GO

CREATE TABLE dbo.foo 
( 
  [key] VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CS_AS
  PRIMARY KEY
);

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';
GO

-- fails:
INSERT dbo.bar SELECT 'bob';
GO

-- succeeds:
INSERT dbo.bar SELECT 'Bob';
GO

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';

----
1

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

----
3
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

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

Selecting a SQL Server Collation

Circumlocution
  • 57
  • 5