If I specify a delete of a record in a table, using SQL under Enterprise
Manager or SQL Query Analyzer, based on the string value of a field, and
two records differ only in case regarding that string value, both
records get deleted. Evidently Sql Server is case insensitive in regards
to the delete ( or update ). I do not want that and wish to make Sql
Server case sensitive in this regards. How do I do that ?

Edward wrote  on Wed, 26 Jul 2006 09:17:27 -0400:

Case sensitivity is dependent on the collation in use. You should be able to
specify the collation in the DELETE or UPDATE queries, or you could set the
collation on the column itself.

Dan

Daniel Crichton wrote:
Is there an easy way of just changing the collation on my DELETE or
UPDATE statement to specify case-sensitivity without having to change
anything else ? If so, how would I do it ?

Furthermore I want to specify all data for specific tables of a database
to be case-sensitive. Is there a way to do that other than to have to
change all my SQL statements accordingly.

Yes, you can change the table definition to a different collation. Check BOL
for ALTER TABLE.

It may be efficient to drop any existing indexes, including clustered
indexes, before changing the collation, and then rebuild the indexes after.

Show quoteHide quote

Edward Diener (ediener@no_spam_incomm.com) writes:
DELETE tbl WHERE col COLLATE Latin1_General_CS_AS = '<value>'

   SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name + ' ' +
          t.name + '(' + ltrim(str(c.length)) + ') COLLATE ' +
          'Latin1_General_CS_AS ' +
          CASE columnproperty(c.id, c.name, 'AllowsNull')
               WHEN 1 THEN ''
               WHEN 0 THEN 'NOT '
          END + 'NULL'
   FROM   sysobjects o
   JOIN   syscolumns c ON o.id = c.id
   JOIN   systypes t ON t.xtype = c.xtype
                    AND t.xusertype <= 255
   WHERE  t.name LIKE '%char'
     AND  o.xtype = 'U'

Copy and paste the result. When you're done do:

   ALTER DATBASE db COLLATE Latin1_General_CS_AS

In practice it is not that easy. If there are indexes on any column,
they have to be dropped first. One way is to script all indexes from
Enterprise Manager, and then drop all indexes. It gets even more
complicated if a character colunm is in a primary key which is referenced
by a foreign key from somewhere.      

But the essence is that you can determine per column level what collation
to use. A collation both includes language-specific rules, and things
like case-sensitivity. You can also choose when you install SQL Server
to install a case-sensitive collation as the default collation.

Latin1_General_CS_AS is only one of many collations. You may want
to do

   SELECT serverproperty('Collation')

and then use that collation with CI changed to CS.

Requirement to use Management Studio for local instances
Stored Procedures in Integration Services
Cannot filter members in Management Studio Browsing a Cube
Sql 2005 can't update maintenance plans?
backup remote database to local hard drive or local database?