question

Luke-7978 avatar image
0 Votes"
Luke-7978 asked Luke-7978 commented

Does a SQL Server collumn with Latin1_General_100_CI_AS_SC_UTF8 collation use the 65001 code page?

Hi

I've just upgraded to SQL Server 2019 Express, when I have a column of varchar type with a collation of Latin1_General_100_CI_AS_SC_UTF8, does this give that column the 65001 code page?

Thanks

Luke

sql-server-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
1 Vote"
DanGuzman answered Luke-7978 commented

Yes, collation Latin1_General_100_CI_AS_SC_UTF8 collation uses code page 65001. You can use the COLLATIONPROPERTY T-SQL function to determine the code page for a given collation:

 SELECT COLLATIONPROPERTY(N'Latin1_General_100_CI_AS_SC_UTF8','CodePage') AS CodePage;
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, I had to check as SSIS insists that my varchar column is code page 1252, so it's basically forcing me to use nvarchar, which is overkill for what I need.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ErlandSommarskog commented

Data type varchar is ASCII and can store max 255 different characters; much to less for UTF. Use nvarchar instead

Regarding the mention collation see
https://cloudblogs.microsoft.com/sqlserver/2018/12/18/introducing-utf-8-support-in-sql-server-2019-preview/

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Data type varchar is ASCII and can store max 255 different characters; much to less for UTF. Use nvarchar instead

That's plain wrong. Data type varchar is not ASCII, it is in a code-page of some multi-byte character set. Some of these code pages only have 255 characters. Others have a lot more. For instance all the code pages with UTF8 in the name.

But this is nothing new with SQL 2019 and UTF8 collations. Collations for East Asian languages supports, by necessity, more than 255 characters in varchar.


1 Vote 1 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Luke-7978 commented

Hi @Luke-7978,

Please try the solution from the similar thread How do I fix the Code Page in SSIS Lookup Transformation to be 65001?


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @Cathyji-msft , that is exactly my problem.

0 Votes 0 ·