SQL - Encrypt a Column of Data

Question

Hello All,

What DataType should be a column to stored Encypted Data?

Can we have varchar instead varbinary => does there will have any odd consequences if we use varchar

Thanks

Answer 1

It must be varbinary. Using varchar instead will result in all sort of odd behavior in the client applications. Using nvarchar may hit invalid unicode combination. And most importantly there is absolutely not a single reason why one would use varchar instead of varbinary for this.

Answer 2

You should use BINARY(n) or VARBINARY(n) data types in SqlServer. There are equivalent but differently named data types for other DBMSes.

Using VARCHAR you will encounter problems if the client and server work on different code pages as the system will attempt to translate between code pages. If your database is storing or your cliuent is set to use character data as utf-8 or unicode you will encounter further problems as certain bit patterns have a drastic effect on how the strings are interpreted.