We help IT Professionals succeed at work.

SQL Server collation for unicode data

Medium Priority
1,224 Views
Last Modified: 2012-05-06
What should be the collation for the unicode data
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
To Store Unicode Data, there is no need to consider Collation.
You have to store it in NChar/ NVarchar / NText instead of Char/ Varchar / Text columns.

Collations can be anything, It considers the order in which you need to sort your data.
colloation determines among other things the sort order and acsent definition, so you need to choose the collation you need for your data even though you use unicode

Author

Commented:
please help to let me know

1) what are things to keep in mind for making standardize UTF-8(client and server)
2) Any editor which will support to edit ASP pages for UTF-8
3) Things to keep in mind while importing data in backend for UTF-8 database

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Run this query:

SELECT * FROM fn_helpcollations()
WHERE description LIKE '%unicode%'

Choose the Collation of your choice

And this will give you more insight of your other requirements.

Author

Commented:
where is the defination for the function fn_helpcollations?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
It is a table valued function and returns all types of Collation available in SQL server.

Author

Commented:
Please let me know the things to keep in mind while importing data in backend for UTF-8 database
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
As I mentioned earlier, you can choose any of the collation listed by the query provided earlier to suit your needs:

SELECT * FROM fn_helpcollations()
WHERE description LIKE '%unicode%'

In SQL Server, If you choose Nvarchar / Nchar / Ntext in any of the collations listed above, you can successfully store and retrieve UTF-8 characters.

When importing data, specify the data type as SQLNCHAR instead of SQLCHAR. This will handle your unicode data perfectly

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.