Steve Thorpe (st***********@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,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
