be using a Case Sensitive or a Case Insensitive SQL Server 2000,
what do I do?
Should i create two databases: 1 CS and 1 CI?
Can't I simply work on 1 CS database all the time and whatever
the person's SQL Server Sensitivity is setup, my database would
work fine? I want to avoid having to have and work on two
databases for this particular purpose.
What happens if someone puts a CS db when their SQL Server
is setup as CI? Would they have a CS db inside a CI SQL Server
environment? Is there a way to convert the CS database to a CI
database by changing the database's properties or something?
Thank youCase-sensitivity of data is determined by the Collation, which is set at the
column-level rather than the database or server level. There's no problem in
principle with mixing different collations on the same server, in the same
database or even in the same table.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:5cqdnViCKOWr8xLfRVn-1w@.giganews.com...
> Case-sensitivity of data is determined by the Collation, which is set at
> the column-level rather than the database or server level. There's no
> problem in principle with mixing different collations on the same server,
> in the same database or even in the same table.
> --
> David Portas
> SQL Server MVP
> --
There is one case where mixing collations can be a problem - joins and
queries on other databases, either on the same server or on linked servers.
I've seen this come up several times with temp tables - if tempdb does not
have the same collation as your user databases, you can have problems such
as queries and joins returning unexpected results, or even "invalid object
name" errors if you have code in procs, views etc. which has temp table
names in different cases (#tmp vs #Tmp).
Probably the 'safest' approach is to develop in a case-sensitive
environment, because you know your code will work in a case-insensitive
one - the reverse is not true (I believe this is what Kalen Delaney
suggested in Inside SQL Server 2000). If you do need some queries to be
case-insensitve, then you can use COLLATE in the query, which is probably
easier to manage than changing collations at the table level.
Simon|||serge (sergea@.nospam.ehmail.com) writes:
> If I have to send a database to someone and that person can
> be using a Case Sensitive or a Case Insensitive SQL Server 2000,
> what do I do?
> Should i create two databases: 1 CS and 1 CI?
> Can't I simply work on 1 CS database all the time and whatever
> the person's SQL Server Sensitivity is setup, my database would
> work fine? I want to avoid having to have and work on two
> databases for this particular purpose.
> What happens if someone puts a CS db when their SQL Server
> is setup as CI? Would they have a CS db inside a CI SQL Server
> environment? Is there a way to convert the CS database to a CI
> database by changing the database's properties or something?
It depends what you mean with "send a database". If you send the physical
database, or a backup thereof, you can work with whichever collation
you prefer. Just make sure that you use "COLLATE DATABASE_DEFAULT"
on all character columns in temp tables and table variables (including
return tables from user-defined functions). As an extra precaution,
you should test that database on a server with a different default
collation than the database.
If you instead send a script of the database, you don't need COLLATE
with your temp tables, as you can assume that the database will be
installed with the default collation of the target server. In this
case you should develop with a case-sensitive collation. Furthermore,
you should use only lowercase names. Or at very least you should
use a naming convention, so you don't have an object called "gadgets"
and another "GADGETS", which would cause errors on a case-insensitive
collation.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi
Simons point about temp tables can be also be mitigated if you create the
temporary tables separately and specify the column collation.
Also consistent use of case throughout your programs/procedures helps with
cache re-use, therefore it is a good idea to make sure code is case
consistent even if you are not using case sensitivity.
John
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:428f7271_3@.news.bluewin.ch...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:5cqdnViCKOWr8xLfRVn-1w@.giganews.com...
>> Case-sensitivity of data is determined by the Collation, which is set at
>> the column-level rather than the database or server level. There's no
>> problem in principle with mixing different collations on the same server,
>> in the same database or even in the same table.
>>
>> --
>> David Portas
>> SQL Server MVP
>> --
>>
>>
> There is one case where mixing collations can be a problem - joins and
> queries on other databases, either on the same server or on linked
> servers.
> I've seen this come up several times with temp tables - if tempdb does not
> have the same collation as your user databases, you can have problems such
> as queries and joins returning unexpected results, or even "invalid object
> name" errors if you have code in procs, views etc. which has temp table
> names in different cases (#tmp vs #Tmp).
> Probably the 'safest' approach is to develop in a case-sensitive
> environment, because you know your code will work in a case-insensitive
> one - the reverse is not true (I believe this is what Kalen Delaney
> suggested in Inside SQL Server 2000). If you do need some queries to be
> case-insensitve, then you can use COLLATE in the query, which is probably
> easier to manage than changing collations at the table level.
> Simon
