COLLATIONPROPERTY CodePage: Identify Text Encoding

I use COLLATIONPROPERTY CodePage to inspect the encoding associated with a collation. It reports metadata for varchar data. Reading that property doesn’t convert stored text or repair a damaged value.

Inspect a named property

The query supplies two complete collation names and requests their CodePage property. The first uses code page 1252. The second has the UTF8 suffix and reports code page 65001.

These are expected metadata values, not sample character lengths. The query reads no application column and changes no collation. I’d use the output as one input to an encoding review, while keeping the actual source and destination types visible.

WITH Collations AS
(
 SELECT CaseId, CAST(CollationName AS nvarchar(128)) AS CollationName
 FROM (VALUES
 (1,N'Latin1_General_100_CI_AS'),
 (2,N'Latin1_General_100_CI_AS_SC_UTF8')) v(CaseId,CollationName)
)
SELECT CaseId, CollationName,
       CAST(COLLATIONPROPERTY(CollationName,'CodePage') AS int) AS CodePage
FROM Collations
ORDER BY CaseId;

Give the property a stable output type

COLLATIONPROPERTY returns sql_variant because different properties have different underlying types. CodePage is an integer property. The outer cast exposes that value as an int column named CodePage.

I prefer that explicit result contract for a small report. A reader shouldn’t have to infer which property was requested from an unnamed cell. Keeping the collation name beside the number also prevents the property from becoming a detached magic value.

Keep varchar and nvarchar separate

The CodePage property describes the character set used for varchar data. It doesn’t mean an nvarchar column switches to that code page. The query’s Unicode collation-name strings identify collations; they aren’t text being re-encoded.

I’d review the declared column type before interpreting this number. A report that lists only code pages can hide an important type distinction. Encoding, comparison rules and storage capacity need their own labels, even when one collation participates in all three discussions.

Treat UTF-8 support as a version requirement

SQL Server added UTF-8 collation support in SQL Server 2019. The second name therefore requires an engine that supports that collation. Check the target instance rather than assuming every historical installation recognizes the suffix.

A missing feature isn’t a reason to substitute another name silently. That would change the metadata question. I’d retain the intended collation and record its version requirement. An older target environment needs a separate compatibility decision.

Separate metadata from data conversion

Looking up a code page doesn’t change any stored byte. This SELECT merely reports a named collation’s definition. It doesn’t alter a database default, update a column or decode an import file.

If a conversion is required, I review that operation on its own terms. Source bytes, declared types and the destination encoding all matter. A successful metadata lookup alone doesn’t prove that every character survives a separate conversion or fits its destination.

Avoid guessing capacity from the code page

Code page 65001 identifies UTF-8, but it isn’t a character-count multiplier. UTF-8 characters can require different byte lengths. The output therefore contains a code page number, not a predicted storage size for arbitrary text.

I’d measure representative values when sizing a real varchar destination. That is a different test from this property lookup. Keep byte-length evidence connected to the exact text and type. Don’t attach a blanket size rule to the collation name.

Keep the comparison useful

Both complete names remain in the result so another reviewer can repeat the lookup. I wouldn’t shorten them to informal labels before saving the evidence. Small suffix differences can change the property under discussion.

For production review, connect each named collation to the specific column or conversion that uses it. That extra context is outside this two-row example. It turns a correct metadata number into a useful explanation of a real text-storage decision.

Look up the encoding first, then decide what needs to move.

A code page lookup is not a conversion, it is metadata about a named varchar encoding.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.