Fixed types versus Dynamic types

When designing a table you have to choose what data type you want to use for each attribute (column).

You can choose between a fixed type and a dynamic type for storing string values.

The fixed types are CHAR, NCHAR and BINARY. With fixed types you specify the max number of characters that can be stored in the column and the database will always use storage for the number of characters you specified even if the value you store in that column is less than what you specified. For example if you create a column called FirstName as a CHAR(30) then the database will always use storage for 30 characters even if the column contains a value like James in it. This means that no data shifting is required when new records are inserted which makes update statements perform better but it requires more space in the database. So for attributes that get updated frequently it’s best to use fixed types.

With dynamic types the database uses storage for the value stored in the column plus a couple of bytes for offset information or 4 bits with row compression. So for a column that has widely varying sizes of strings you can save a lot of storage by using dynamic types which will result in faster read performance because there is less storage used. So for attributes that are used in SELECT queries a lot it’s best to use variable length types.