Re: SQL7: case sensitivity

From: Lars Broberg (lars.b_at_elbe-data.nothing.se)
Date: 04/19/04 

Date: Mon, 19 Apr 2004 10:24:25 +0200

PMFJI,
In SQL Server 7 you specify the collation on the SERVER level (this is
changed in SQL Server 2000 where you can specify it down to column level). 

-- 
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
"Steve Kass" <skass@drew.edu> wrote in message 
news:%23RbPl9cJEHA.208@tk2msftngp13.phx.gbl...
> 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
>>
>>
>>
>

Relevant Pages

  • RE: Advice on Create a SQL Server
    ... Use the default collation. ... domain, and running on a windows domain account, it is better to run under ... Windows Authentication mode. ... to SQL Server using a username/password. ...
    (microsoft.public.sqlserver.setup)
  • RE: Advice on Create a SQL Server
    ... Use the default collation. ... Mode" - it depends on how you will be connecting to your database. ... domain, and running on a windows domain account, it is better to run under ... to SQL Server using a username/password. ...
    (microsoft.public.sqlserver.setup)
  • Re: Installing SQL_Latin1_General_CP1_CI_AS collation order??
    ... Tibor Karaszi, SQL Server MVP ... "Ward Horsfall" wrote in message ... >> rebuildm.exe) interfaces and a collation designator, ...
    (microsoft.public.sqlserver.setup)
  • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... but even MORE generic than the XDb1 implementation, ... disadvantage that 'john', 'John' and 'JOHN' are treated as three distinct ... Since this is the default collation, I assume that your database will use ... I have SQL Server on my desktop; ...
    (comp.object)
  • Re: Russian Language support in SQL SERVER
    ... SQL Server supports languages on a couple of levels. ... specifying a collation defines what language rules are used for ... Cyrillic_general Windows collation that supports the Russian language. ...
    (microsoft.public.sqlserver.server)