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