Database Data Type Usage and Optimizing Data Fields

While designing a Data warehouse or a database an Architect should Data Field spend space like
money, closed handed only spend as much is required.

Following is some guidelines I use to follow –

For String Type Data Types -  

1.      Don’t use Unicode data type unless its really required.

2.      If String length is fixed like if code length is fixed and always going to be that way than use Char instead of Varchar.

3.      User Varchar when its variable length data is there like name. but always cap it with some fixed number like for namecase let say varchar(50) should be sufficient.

4.      Avoid use of “MAX”, define a length.

For Integer type Data Type -

Following Ranges for different Integer data types are available in SQL SERVER -

 http://msdn.microsoft.com/en-us/library/ms187745.aspx

Use wisely, if you are not going to get huge numbers than you don’t need to use big int.
Use Integer for SK of medium size dimension. You can use Tinyint for static
dimension’s Sks.

For Numeric or Decimal Data Types –

Try to use Numeric fields.
Avoid using Float data types for financial data type as they are approximate number data
types and round off values. Use of numeric with correct precision and scale
will help here.

For Date and Datetime Data Types –

Simple if you don’t need timestamp or don’t have timestamp don’t use date time field,
just use date filed.