database case sensitivity? - Andy B

Is there a way to turn on case sensitivity? I.e. If you want to search for
the word "TEST" and not the word "test", what is this feature called (the
case matching that is)?

database case sensitivity? - Dan Guzman

Case sensitivity is determined by the column collation because the collation
determines the comparison rules (case and accent sensitivity).

If you have a case-insensitive column collation, it is easy to force a
case-sensitive compare with an explicit COLLATE specification.  For example:

SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE
Title = 'Application Specialist'
AND Title COLLATE Latin1_General_BIN = 'Application Specialist';

The COLLATE clause renders the expression non-sargable (as does applying any
function to a column).  The example above also specifies the
case-insensitive criteria (no COLLATE) so that indexes (if any) on the
column can be used efficiently.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

database case sensitivity? - Andy B

I just wondered. I asked our web host service if they could make the whole
database case sensitive and they told me that almost all modern day
databases are case sensitive. They wanted me to give them more specs on the
matter so they could look into it. Or is this something that shouldn't be
messed with? I found this little issue out by creating a column in a table:
StateCode char(2) not null and then putting this check constraint on it:
StateCode in ('AL', AK', AR') [this is a shortened list for sanity sake].
Now, I inserted a value into the column: insert into Addresses (StateCode)
values("ak"); It actually was a legal value. How can this be so when I told
it I wanted only capped letters to be valid? Or did I not tell it that?
Guess I am a slight bit confused on the case sensitive thing now...

database case sensitivity? - Dan Guzman

In SQL Server the default is case-insensitive but is configurable.  The
case-sensitivity is specified at 3 different levels:  instance (during
installation), database (during database creation) and column (column
creation).  The instance collation determines case sensitivity for variable
names and the default database collation.  Database collation specifies the
default collation for database objects.

From your description, it seems whomever installed the database overrode the
default and specified a case-sensitive collation.  I think your use case is
a good example as to why a case insensitive collation should be used.

Personally, I don't specify a collation other than the default unless I have
a specific reason to do otherwise.  Generally speaking, a case-insensitive
collation is more natural for users and doesn't require forcing upper/lower
case when storing/searching data.  Regardless of what collation you choose,
be consistent as to avoid a mixed collation environment if possible.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/