SQL Server - Case Sensivity and Accent sensivity determined by a Collation

Sql server 2005/2008 support Unicode characters so you can present your data in any language so for example if your Database collation is (a Latin) and you want to store Arabic language words you need to define a Unicode data type like(nchar,nvarchar,ntext) to stored your data properly and to avoid the question marks problem (???) except if your database collation is Arabic and in this case you does not need to define your columns as a Unicode type you can use the type like(char,varchar,text) to store Arabic language because in this case the collation is responsible to display your character language.
Note:
SQL Server 2005/2008 allows you to make collation choices at the server, database, column, and expression level.
Case Sensivity and accent sensivity determined by a Collation so let's see how we can define the case sensitive and accent sensitive by using collation for two main languages Arabic and English.

--This example show the Case sensitive for English Language
DECLARE @Name1 varchar(25) ='SQL SERVER' 
       ,@Name2 varchar(25) ='sql server' 

--Here do not consider the Case sensitive        
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CI_AS = @Name2 COLLATE SQL_Latin1_General_Cp1_CI_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Case sensitive
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CS_AS = @Name2 COLLATE SQL_Latin1_General_Cp1_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

the result of the above script as follwoing:
They are the same
They are not the same

--This example show the Accent Sensitive for Arabic language
DECLARE @Name3 nvarchar(25) ='أ' 
       ,@Name4 nvarchar(25) ='آ' -- try to change it to ؤ – ئ – إ –ء-

--Here do not consider the Accent Sensitivity       
IF(@Name3 COLLATE Arabic_CS_AI= @Name4 COLLATE Arabic_CS_AI)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Accent Sensitivity
IF(@Name3 COLLATE Arabic_CS_AS= @Name4 COLLATE Arabic_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

the result of the above script as follwoing:
They are the same
They are not the same