some time there is need to convert a variable to specific data type
in Microsoft SQL server 2008 R2
Cast : select CAST(2000 as varchar); you indicate the target type
Covert, beside the type you can choose the style of the conversion
select convert (DATE,’11/17/2014′,101); (date is data type, 101 is the date american style )
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 .
imagine