Accent-Sensitivity in SQL Server
Accent comes
in picture where an application runs most the queries with first name and last
name.
Some
application which frequently include the functionality to support database queries
based on first name and last name, which is a problematic when names contain
accent character and the database is accent – sensitivity
For Example
we have a table Emp_Master where we want to check all the names start with TEST,
the result should be include
TEST
TEST1
TEST123
TEST1234
´TEST12345
TEST´4567
But when
database collation is accent sensitive the result include only
TEST
TEST1
TEST123
TEST1234
If you
noticed above queries has left out all the name which are related with accent which
is not a desire result.
To get a
desire result we can provide two solutions to above
1) Configure the database so that it is
not accent sensitive, change the collation to SQL_LATIN1_GENERAL_CP1_CI_AI
2) Select * from Emp_Master where fname
like ‘TEST%’ collation SQL_LATIN1_GENERAL_CP1_CI_AI
Option 1 is
a permanent solution and the so that when you install any new instance change
the collation second option is based on requirement so that you do not have to
change any database settings
Change the SQL Server Collation
Please remember it is a complex operation
and can perform with below three steps
1) Create the scripts that need to re create the database
2)
Export all your data
3)
Drop all the user database
4) Rebuild master database new SQL Collation Property by using below command
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName
No comments:
Post a Comment