sql-serverchardb2column-types

SQL Server datatype for DB2 column "Char() for BIT Data"

I googled but failed to find an answer for the following question.

I have a DB2 database column of datatype “Char(100) for BIT Data“. It stores encrypted value of employee ID. I need to store this data in SQL Server.

  1. What should be the datatype for this column in SQL Server?
  2. Is there any formatting needed before inserting into SQL Server?
Solution

  • The column in your DB2 table is an ordinary character string, except that the for BIT Data part tells DB2 to treat that string as arbitrary binary data, rather than text. This matters mainly for sorts and comparisons. DB2 (as normally configured) would sort and compare strings alphabetically. A capital A would come before a lowercase b. But for BIT Data strings are compared by the underlying numeric value of the characters. Capital A would come after lowercase z.

    In SQL server, there is a BINARY datatype for this, so you would probably use BINARY(100). No formatting should be necessary, as you probably want the value of raw binary data to stay exactly the same.