QUESTON # 1
----------------------
I'm confused about the SQL collation, what is it actually for?. I
created a new database and later checked my SQL collaction it is set
to SQL_Latin1_General_CP1_CS_AS. The problem what i have now is
accessing the field names / tablename has become case sensitive. No
idea how this happend.

for ex. SELECT CHANNEL_NAME from table1 does not work
select Channel_Name from Table1 works

I'm getting mad about this, why is the field and tablenames have
become case sensitive. how do i make it to normal i mean in case
sensitive.

QUESTON # 2
----------------------
I'm making a content database for news management. Example, A single
article would have English, Arabic and spanish content. So my table is
as below.

Table # 1

ID Bigint
English_Title Nvarchar
Spanish_Title Nvarchar
Arabic_Title Nvarchar
English_Content Nvarchar
Spanish_Content Nvarchar
Arabic_Content Nvarchar

is it advisable to do so?, i made it as above as i want the data
related to one article (of all languages) to be stored in a single row
of a table. Now what should be the collation for my database in this
case. is collation related to storing of content also.

Please help me....

#1. The collation pages determine the order in which your data is
sorted and (as you discovered) whether your database (both the data in
your tables AND the catalog object names themselves (e.g. columns,
tables, etc) are case sensitive. Unless you have a very strong
reason, I would go with a case insensitive page. You can choose the
default collation page for your database when you create it. However,
you can always override the default on a column-by-column basis (once
again, I wouldn't do this unless you have a particularly pressing
need). To change the collation of existing columns, look up the ALTER
statement in BOL.

If you try to join on columns with differing collation pages, you will
get a collation page conflict. I prefer to fix the tables to avoid
this issue, but if that is not possible, you can change collation on
the fly in your join, like this:

SELECT
<list of column_names>
from
<table_1> as a
inner join
<table_2> as b
ON a.<column_whatever> = <b.some_column> COLLATE
<collation_page_name>

The above statement will change the collation for b.some_column for
the purposes of the join only. I would expect some kind of
performance penalty, but I tested the statement on a very large join,
and didn't notice a significant difference. It's much better to have
all the columns collated the same to begin with, but it's handy to
know about changing on the fly.

#2. I would recommend against the approach you outline, and instead
go for a table like this: CREATE TABLE CONTENT_ITEM_TITLE
(CONTENT_ITEM_ID (int) NOT NULL, LANGUAGE_CD varchar(2) NOT NULL,
TITLE_TX varchar(75) NOT NULL). ALTER TABLE CONTENT_ITEM_TITLE ADD
CONSTRAINT CONTENT_TITLE_PK primary key clustered CONTENT_ITEM_ID,
LANGUAGE_CD). CONTENT_ITEM_ID would be a foreign key to the
CONTENT_ITEM table and LANGUAGE_CD should be a foreign key to a table
with all the whose primary key is the ISO code for each language.

The advantages are:
1. It will be very easy for you to add or drop languages this way.

2. You won't have to deal with NULLS in cases where you're missing a
title in a particular language. NULLs complicate queries because they
represent an unknown truth-value and this do not evaluate to either
true or false. You thus have to put extra stuff into your query like
"OR <column> is NULL". But if you have other conditions in your WHERE
and they are connected with AND operators, then you have to logically
punctuate to avoid unexpected results. Also, if you do outer joins
and the joined (e.g. the "outer" table) has NULLs in a column of
interest for selectivity, you can be in for a surprise. This is
especially true if you use the old style join syntax. But even if you
use the ANSI syntax (which is much better), it's still possible to put
the filter in the wrong place (e.g. WHERE clause instead of Join
Condition) and be confused by the results.

3. Your SQL queries will be exactly the same, regardless of which
language the user wants. You don't want to change columns in your
SELECT list (e.g SELECT spanish_title. . . versus SELECT
english_title . . .) simply because the user has chosen a particular
language. With the table that I have outlined, your query is always
of the form "SELECT <column list> from CONTENT_ITEM as ci INNER JOIN
CONTENT_ITEM_TITLE as ct ON ci.<content_item_id> =
ct.<content_item_id> AND ct.LANGUAGE_CD = <whatever the user wants>

Thanks,

Bill

Thanks,

Bil

Althaf (althafexcel (AT) gmail (DOT) com) writes:
As Bill said, the database collation affects both data and metadata.

I like to point out that since you intend to use multiple languages and
use Unicode, you should not use an SQL collation, but you should use a
Windows collation. For nvarchar an SQL collation behaves just like a
Windows collation, but for varchar SQL collations have its own specific
rules. This can lead to unexpceted surprises, not the least with
performance.

Bill made a good point that if you store the strings in a table keyed by
ID and language, you have something which is more extensible and easier
to use.

However, if you need to sort and index these values, that approach raiaes
some problems. With separate columns you can set the appropriate collation
for each column, Latin1_General_CS_AS for English, Modern_Spanish_CS_AS for
Spanish and Arabic_CS_AS for Arabic.






--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx