Case Sensitive vs. Insensitive

Question

Post reply

Case Sensitive vs. Insensitive

bridgt01

SSCommitted

Points: 1890
More actions
November 13, 2003 at 3:37 pm

#64697

Is there a way to create a database that is case sensitive? Books online shows how to create an instance that is case sensitive but I'd simply like to create a db that is case sensitive.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

You can set the default collation of a database when you create it.

--Jonathan

bridgt01 SSCommitted Points: 1890 More actions · Answer 2

bridgt01

SSCommitted

Points: 1890

November 13, 2003 at 4:06 pm

#481990

I see that in BOL now....thanks

Lowell SSC Guru Points: 323518 More actions · Answer 3

To expand on this issue I was hoping to read more specifics, and followed up on this myself:

If I was creating a new database, what collation would I use to make it case sensitive?

use this statement from the BOL to see what collations are available:

SELECT *

FROM ::fn_helpcollations()

In my db there are 753 collations returned by this statement.

most of them are for foreign languages, but which one would I use?

To answer my own question but leave it for others to read,

my current databases are SQL_Latin1_General_CP1_CI_AS.

I found this out by choosing a table with a varchar field, and using EM to script the database. the collation is part of the definition for the varchar field.

The closest match for a case-sensitive version of that collation is from the function above:

SQL_Latin1_General_CP1_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data.

and lastly, to create a simple database with that collation as it's default:

CREATE DATABASE mytest

collate SQL_Latin1_General_CP1_CS_AS

From there, you'd want to add a table with a varchar field,and do test selects where name='BOB' vs name='Bob' or something similar

Also note that tablenames and field names are case sensitive in this situation as well;

These statements work just fine, and create two tables with similar names:

create table Whatever(TESTNAME varchar(20),TestName varchar(20))

create table whatever(TESTNAME varchar(20),TestName varchar(20))

Edited by - Lowell on 11/14/2003 07:49:50 AM

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!

rstone SSCertifiable Points: 6209 More actions · Answer 4

If case sensitivity is required and the collation of the db or column is not case sensitive, a cast can be used to change it without db redesign. Changing the collation in an existing db is risky - every query would be at risk of behaving differently. A work around in an order by, for example,

select top 100 last_name

from employees

where last_name like 's%'

order by (cast(last_name as varchar(50)) COLLATE Latin1_General_BIN)

I also tried the SQL_Latin1_General_CP1_CS_AS collation in the order by. It worked on one server and did not on another - I did not have time investigate why. However, even when the order by did not work in a case sensitive fashion using SQL_Latin1_General_CP1_CS_AS, the compares did. Can anybody explain this for me?

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

quote:
If case sensitivity is required and the collation of the db or column is not case sensitive, a cast can be used to change it without db redesign. Changing the collation in an existing db is risky - every query would be at risk of behaving differently. A work around in an order by, for example,
select top 100 last_name
from employees
where last_name like 's%'
order by (cast(last_name as varchar(50)) COLLATE Latin1_General_BIN)
I also tried the SQL_Latin1_General_CP1_CS_AS collation in the order by. It worked on one server and did not on another - I did not have time investigate why. However, even when the order by did not work in a case sensitive fashion using SQL_Latin1_General_CP1_CS_AS, the compares did. Can anybody explain this for me?

OP's question referred to creating, not changing a database.

I just cast to varbinary when I need case sensitivity with case-insensitive data.

--Jonathan

robinhc SSCommitted Points: 1617 More actions · Answer 6

We use Latin1_General_BIN. Watch out if you use temp tables in stored procedure are your system tables (tempdb) have a different collation sequence.

Steven. SSCrazy Eights Points: 9806 More actions · Answer 7

I use Latin1_General_BIN as well. As robinhc says, watch anything that uses the tempdb if the database collation is different to the server collation.

Also naming standards help a lot, as object names are case sensitive as well.

Steven