473,324 Members | 2,246 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

CS vs CI database

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?

Thank you
Jul 23 '05 #1
4 8424
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
--
Jul 23 '05 #2

"David Portas" <RE****************************@acm.org> wrote in message
news:5c********************@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
Jul 23 '05 #3
serge (se****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
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" <sq*@hayes.ch> wrote in message
news:42********@news.bluewin.ch...

"David Portas" <RE****************************@acm.org> wrote in message
news:5c********************@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

Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: lkrubner | last post by:
I have a webserver through Rackspace. I create a domain. I create an FTP user. I upload some files. I create a database called testOfSetupScript and then I create a database user named setup. I...
0
by: Cherrish Vaidiyan | last post by:
sir, The following are the steps that i followed in setting up standby database on Red hat Linux 9. i am using Oracle 9i. i have followed the steps in this site : ...
6
by: Marvin Libson | last post by:
Hi All: I am running DB2 UDB V7.2 with FP11. Platform is Windows 2000. I have created a java UDF and trigger. When I update my database I get the following error: SQL1224N A database...
8
by: Kamlesh | last post by:
Hi, How do I know the physical database path of a database. When I goto the DB2INSTANCE users's directory (/home/db2inst1), I see following folders: /db2inst1/NODE0000/SQL00001...
1
by: pintur | last post by:
The message is: SQL1036C Errore di I/O durante l' accesso al database. SQLSTATE=58030 what is the proble? what for restore tables? thanks
3
by: josh.kuo | last post by:
Sorry about the subject, I can't think of a better one. I recently wrote some PHP classes that I think might be of interest to this group. Since I have been reaping the benefits of reading news...
0
by: Jack | last post by:
Training Classes for Oracle10g, 9i, 8i Certification training in Oracle10g and 9i: DBA, Developer, Discoverer. training conducted at your location worldwide. Courseware licensing also available....
0
by: Winder | last post by:
Training Classes for Oracle10g, 9i, 8i Certification training in Oracle10g and 9i: DBA, Developer, Discoverer. training conducted at your location worldwide. Courseware licensing also available....
0
by: Laurynn | last post by:
# (ebook - pdf) - programming - mysql - php database applicati # (Ebook - Pdf)Learnkey How To Design A Database - Sql And Crystal Report # (ebook-pdf) E F Codd - Extending the Database Relational...
9
by: Peter Duniho | last post by:
Is there a straightfoward API in .NET that allows for inspection of a database? That is, to look at the structure of the database, without knowing anything in advance about it? For example,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.