convert data type

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 )





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 .