Re: SQL7: case sensitivity
From: Steve Kass (skass_at_drew.edu)
Date: 04/19/04
- Next message: Uri Dimant: "Re: Cursor question"
- Previous message: Uri Dimant: "Re: Timeout expired when add new record"
- In reply to: Jochen Daum: "SQL7: case sensitivity"
- Next in thread: Lars Broberg: "Re: SQL7: case sensitivity"
- Reply: Lars Broberg: "Re: SQL7: case sensitivity"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Apr 2004 01:30:46 -0400
Jochen,
While upper and lower case characters are stored differently in SQL
Server tables, and their binary representations can be distinguished.
But distinguished doesn't mean distinct from the point of view of a
UNIQUE constraint. It is the database collation that determines whether
x = y evaluates to true or to false for two strings x and y, and the
comparison x = y is what's used to determine if non-null strings are to
be considered as distinct or not. If a case-insensitive collation is in
place (this is the default instance collation, which extends to
databases for which no collation was specified upon creation), then x =
y evaluates to true if x and y differ only in case, and you will see a
violation of the unique constraint if you try to put such values in a
unique-indexed column.
I believe that in SQL Server 7 you can specify the collation on a
per-database level, so you may wish to store this data in a database
with a case-sensitive collation.
However, I don't know what rules Unix uses to determine if two strings
are identical or not. If Unix makes a binary comparison of the strings,
you could consider storing the filenames in a varbinary(...) column
instead of varchar(...).
Steve Kass
Drew University
Jochen Daum wrote:
>Hi,
>
>I have a table, which is supposed to store filenames stored on a Unix
>filesystem. I seem to be having problems with case sensitivity there:
>
>I can store two files, with the same name and different spellings, eg.
>test1.txt and Test1.txt
>
>But I cannot set a unique index on the columns that stores the
>filename, it reports duplicates for this file.
>
>Can someone point me to options?
>
>Thanks in advance,
>
>Jochen
>
>
>
>
- Next message: Uri Dimant: "Re: Cursor question"
- Previous message: Uri Dimant: "Re: Timeout expired when add new record"
- In reply to: Jochen Daum: "SQL7: case sensitivity"
- Next in thread: Lars Broberg: "Re: SQL7: case sensitivity"
- Reply: Lars Broberg: "Re: SQL7: case sensitivity"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
