How to define "charset" of a table in SQL Server?

Question

In Mysql to define the charset of a table we can use so:

Mysql:

create table user_details (...) default character set = utf8;

How can I do the same in SQL Server?

Answer 1

First, UTF-8 is a encoding and not a charset. The charset to be chosen depends somewhat on the chosen encoding. The same goes for the text conversion and search rules.

Not possible, SQL Server does not support this encoding. Put the text in a spine nvarchar. If that’s the case, you can specify a collate among those available, but none of them will use UTF-8 encoding.

If you need the data in this encoding you need to make the application handle to use UTF-8 and deliver it as you wish, but the data will not be saved in this format. A specific client for SQL Server could even treat this transparently, I don’t know if any does it.

If it’s absolutely necessary I could use the varbinary and do the coding on your own, but I doubt this is better than the conversion.

Documentation.

Answer 2

You will need to change the column setting to be NVARCHAR. to have Unicode support.

I found a very interesting example on the internet to show how you can do this

Code:

USE Test
GO

/* Create Test table with non-unicode column */
IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Test
END

CREATE TABLE dbo.Test (Col1 VARCHAR(20))
GO


SELECT character_set_name,collation_name FROM information_schema.columns WHERE table_name = 'test'

/* Change to support unicode and ensure collation */
ALTER TABLE dbo.test ALTER COLUMN col1 NVARCHAR(20) COLLATE latin1_General_CI_AS
GO

SELECT character_set_name,collation_name  FROM information_schema.columns 
WHERE table_name = 'test'

In short, you will have to convert Column Varchar for Nvarchar