I have been given the task to correctly detect and change the data types of all columns in a database that have been incorrectly assigned as nvarchar. The types this DB will contain are BIT, INT, DECIMAL, DATE, CHAR, and nvarchar. My first thought is to use dynamic SQL that contains a CASE. Another thought was to cycle through the types using casts and convert progressively using skips with error handling.
1 Answers
What does correctly detect and change mean? For NVARCHAR, I would guess it means to change the column definition to VARCHAR if all of the data already in the table can be represented as VARCHAR. Even that is slightly vague, because the answer depends on the encoding used. And the encodings supported by your DBMS depends on which one you're using.
So, first, make sure you know what encoding you're expected to use. Although they're not the same, frequently you'll see documentation that refers to collation rather than encoding.
Then, to guard against trouble, the safest approach is to verify the conversion can be done without losing information. In general, if you can convert from encoding X to encoding Y and back again to X, and the value is unchanged, the conversion preserves information.
select A,
cast(A as varchar) as Av,
cast( cast(A as varchar) as nvarchar) ) as Avn
from T
where A <> cast( cast(A as varchar) as nvarchar) )
If no rows are returned, then (in SQL Server, at least) the column can be safely converted with ALTER TABLE.
The process can be automated, somewhat. Using the system tables, you can find all columns defined as NVARCHAR. You can generate the above code for all of them, one by one, and collect the results. Once you're convinced the answer is correct, you can use the same information to generate ALTER TABLE statements or similar, as your DBMS requires.
Related