Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they are
case sensitive?
Thanks in advance.
John Steen
It appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>
|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a crit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a crit :
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:

> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they are
> case sensitive?
> Thanks in advance.
> John Steen
>
|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
Hugo Kornelis, SQL Server MVP
|||Thanks, Hugo. And you're right, I do need to learn to write more than simple
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:

> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
>
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>