How to create a case-sensitive instance of SQL Server 2000 and How can I make my SQL queries case sensitive?

How to create a case-sensitive instance of SQL Server 2000 and How can I make my SQL queries case sensitive?

1. How to create a case-sensitive instance of SQL Server 2000
To create a case-sensitive instance of SQL Server 2000 follow the bellow steps at the time of installation of SQL Server Setup:

1.1 Run SQL Server Setup to install SQL Server 2000 Components, select Install Database Server, and then click Next at the Welcome screen of the SQL Server Installation Wizard.

1.2 In Computer Name dialog box, Local Computer is the default option and the local computer name appears in the edit box. Click Next.

1.3 In the Installation Selection dialog box, click click Create a new instance of SQL Server, or install Client Tools, and then click Next.

1.4 Follow the directions on the User Information and related screens.

1.5 In the Installation Definition dialog box, click Server and Client Tools, and then click Next.

1.6 In the Instance Name dialog box:
To create a case-sensitive default instance, accept the Default check box and click Next.

1.7 To create a case-sensitive named instance, clear the Default check box and type an instance name.
In the Setup Type dialog box, click Custom, and click Next.

1.8 In the Select Components, Services Accounts, and Authentication Mode dialog boxes, change or accept the default settings, and then click Next.

1.9 Security Note When possible, use Windows Authentication.

1.10 In the Collation Settings dialog box, you have two options:
To make a Windows Locale collation case-sensitive, select Collation designator and then select the correct collation designator from the list. Clear the Binary check box, and then select the Case-sensitive check box.

1.11 To make a SQL collation case-sensitive, select SQL Collations, and then select the correct collation name.
For more information about collation options, click Help. When you finish setting the options, click Next.

1.12 In subsequent dialog boxes, change or accept the default settings, and then click Next.

1.13 When you are finished specifying options, click Next in the Start Copying Files dialog box.

1.14 In the Choose Licensing Mode dialog box, make selections according to your license agreement, and click Continue to begin the installation.

1.15 Click Help for information about licensing, or see your system administrator.

2. How can I make my SQL queries case sensitive?

If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable
(
mycolumn VARCHAR(10)
)
GO

SET NOCOUNT ON

INSERT mytable VALUES('Case')
GO

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

You can alter your query by forcing collation at the column level:

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE'

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'case'

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case'

-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'

If you want to do this in a more global way, instead of modifying each individual query, you can force the collation at the database level, or at the column level, using the ALTER DATABASE and ALTER TABLE commands, respectively. You can see the current collation level on the properties tab of the database server, through Enterprise Manager (if you're going to change this setting, MAKE NOTE OF THIS VALUE):

And you can see the description from running the following query:

SELECT DATABASEPROPERTYEX('', 'Collation')

As changing this setting can impact applications and SQL queries, I would isolate this test first. In SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to:

EXEC sp_help 'mytable'

The second recordset should contain the following information, in a default scenario:

Column_Name Collation
----------- ----------------------------------------------
mycolumn SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:

ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10)
COLLATE Latin1_General_CS_AS
GO

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

If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):

ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CI_AS

If you are stuck with SQL Server 7.0, you can try this workaround, which might be a little more of a performance hit (you should only get a result for the FIRST match):

SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10))

SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10))

SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10))

-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'