case sensitive in MS SQL

[TheBao]

I have a table (USERINFO) in MSSQL database with 2 columns: USR, PWD

Let say I have 1 record: ABC, ****

Now when I perform these:
Select * from USERINFO where USR = "ABC"
Select * from USERINFO where USR = "aBc"
.....
I always get 1 record. How do I compare the string case sensitive so that only

Select * from USERINFO where USR = "ABC"

will return 1 record.

Thanks.

[vb_dba]

You can change the sort order on the server, which will allow you to perform this type of comparison. The only problem is that all of your data and database objects will be case sensitive. In otherwords, if you have a table named Customers, then Select * from customers will fail, as will Select * From CUSTOMERS. If you want to proceed with this, I got these instructions from Books Online on how to change the sort order:

Follow these steps to rebuild the databases and maintain their contents.

Back up the definitions of objects you want to preserve.
Use SQL Server Enterprise Manager to create scripts for all of the objects in the user databases and master database.

Export data from the user tables.
Use Microsoft Data Transformation Services (DTS) or the bcp bulk copy utility to unload the user data.

Rebuild the master database.
Run the Rebuildm.exe command prompt utility. You can select new character set, sort order, and Unicode values and rebuild the master database with these new settings.

Create new user databases.
Use SQL Server Enterprise Manager or the CREATE DATABASE statement to re-create the user databases.

Create objects using the scripts created earlier.
Use SQL Server Query Analyzer to run the scripts you created earlier.

Import data into the user tables.
Use DTS or BCP to load data back into the user tables.

Chris