Data-Type Conversion

The way the Upsizing Wizard converts data types can have an enormous effect on the Access user interface and application. The data-type conversion can even cause the application to fail or to produce incorrect results.

The Upsizing Wizard converts all Access text data types (i.e., fixed-length character) to SQL Server variable-length character (varchar) data types. Generally, because Access text fields store white space and varchar fields don't, this conversion from text to varchar character types is beneficial—most text fields have at least as much white space as they have characters. For example, in Access, the data type of the Employee table's LastName field is text(20), and the upsizing process converts it to a SQL Server data type of varchar(20). If the average last-name length is nine characters, the storage requirement is 10 bytes—9 bytes for the name, plus 1 byte for the length of the data in storage.

A problem occurs with text fields that don't usually have much white space, such as in the original Northwind database Orders table CustomerID field. This field was an Access text(5) data type and was mapped to a SQL Server varchar(5). Every entry in the Orders table has a CustomerID, which is always five characters, so the field is always completely filled—it has no extra white space. After CustomerID is converted to a SQL Server varchar(5), each CustomerID requires 6 bytes of storage (5 for data plus the 1 length byte) plus extra processing cycles to decode and encode a variable-length character field each time the data moves between storage and memory. Although this combination (lack of storage space and extra processing cycles) doesn't sound like a big problem for small data sets, it could quickly become a performance drag and storage-capacity problem for large data sets.