Determine varchar content in nvarchar columns

Question

I have a bunch of NVARCHAR columns which I suspect contain perfectly storable data in VARCHAR columns. However I can't just go and change the columns' type into VARCHAR and hope for the best, I need to do some sort of check.

I want to do the conversion because the data is static (it won't change in the future) and the columns are indexed and would benefit from a smaller (varchar) index compared to the actual (nvarchar) index.

If I simply say

ALTER TABLE TableName ALTER COLUMN columnName VARCHAR(200)

then I won't get an error or a warning. Unicode data will be truncated/lost.

How do I check?

Answer 1

Why not cast there and back to see what data gets lost?

This assumes column is nvarchar(200) to start with

SELECT *
FROM TableName
WHERE columnName <> CAST(CAST(columnName AS varchar(200)) AS nvarchar(200))