refine database -Choose best data type

it always matter to choose suitable data type for the columns in table.

1- choosing a data type is equivalent to define a constraint :

e.g : employee_hiredate : DATE–> if the user enter the string value such as “November” database engine report an error to user.

2- if using an inappropriate data type we will miss the some operation. e.g. “+” for numeric data stand for add and sum whilst for character and string employ for concatenation

3. waste the memory: choosing appropriate type for your data.

e.g choosing an integer type for a field “Flag” which just hold “Y” or “N”  is not an appropriate choice. imagine that there are around 100000 rows . So instead of allocating 200000 bit to “Flag” field,  by choosing Integer type, system will allocate around 1200000 bit which about 800000 are useless.

4- if your data need to present small or very large number with out and they don’t need accuracy then using  Float and Real data type is a wise decision.

5-Fixed type or dynamic one.  (varchar vs char or Nchar vs Nvarchar) , when data need to be updated frequently it is good to choose a fixed type such as char or Nchar, however dynamic one (nvarchar) are good for saving storage .




Published by

Leila Etaati

She has over 10 years’ experience working with SQL server. She was involved in many large-scale projects for big sized companies as SQL server and BI consultant. She worked in Industries including banking financial, power and utility, manufacturing … Leila Etaati, PhD student of Information System department, University of Auckland, MS and BS in computer science. She is a lecturer and trainer in Business intelligence and data base design course in University of Auckland.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>