I want to create a database where the table names / column names / SP names
are NOTcase sensitive but where the data in the tables is, so that I can
build a unique index where 'test' and TEST' is accepted as different.

I have tried Installing SQL with a Collation designator with the Case
Sensitive option checked
- this caused all sp names / column names / table names to be case
sensitive - not what I want.

I have also tried installing SQL and selecting a SQL Collation and picking
an option from the drop down list. - again this cause everything to be case
sensitive - not desired

Do I have to install SQL with a non case sensitive collation, then set each
column in the table to be case sensitive? What if any are the problems I am
likely to come across?

Thanks

Steve

Hi

>
> Do I have to install SQL with a non case sensitive collation, then set
each
> column in the table to be case sensitive? What if any are the problems I
am
> likely to come across?
>

AFAIK this is your only "safe" option. As I am an adocate of using a source
code control system to maintain database object, I would have scripted all
the tables anyhow, and therefore changing colation is a function of the
editor. If you are consistent in changing the colation then I don't envisage
any problems apart from possible data migration problems if they are not
compatible (but I suspect they will be!).You will also have to make sure
that set collations in temporary tables see "Mixed Collation Environments"
in books online.

An alternative would be to force the collation when doing your querysee the
examples for "Collation Precedence" in Books online.

John

Steve Thorpe (stephenthorpe@nospam.hotmail.com) writes:
> I want to create a database where the table names / column names / SP
> names are NOTcase sensitive but where the data in the tables is, so that
> I can build a unique index where 'test' and TEST' is accepted as
> different.
>...
> Do I have to install SQL with a non case sensitive collation, then set
> each column in the table to be case sensitive? What if any are the
> problems I am likely to come across?

The best strategy may be to let the server collation be case sensitive,
but create the database with a case-insensitive collation and then declare
each column to be case-insensitive. Then you don't have to bother about
columns in temp tables and table variables, as they will use the system
collation. However names of temp tables will be case-sensitive.

By setting the collation of the database to be case-insensitive,
procedure names, variable names, table names etc are case insensitive.

Note however that variables takes their collation from the database
collation, so if @a = 'TEST' and @b = 'test', @a is equal to @b.

Personally, I'm a strong advocate of that development should always take
place in a case-sensitve environment. If you develop case-insensitive,
and you product is to deployed in a shop where case-sensitivity is the
law, you may get tons of problems.

A collorary of this, is that since it may be a nuisance to remember
whether you called a table OrderDetails or Orderdetails, I stick to
lowercase for all names.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp