Do atoms in a molecule actually keep rotating freely in case they have sigma bond?
You really are referring to molecular conformations:


  Conformation, any one of the infinite number of possible spatial arrangements of atoms in a molecule that result from rotation of its
  constituent groups of atoms about single bonds.


Below are given two possible (out of many!) conformations of the ethane $\ce{C2H6}$ molecule:





The single $\sigma$ bond allows free rotation around the inter-nuclear axis of the two $\ce{C}$ atoms. One consequence is that around the single bond no cis-trans isomers can exist.

But if we add a $\pi$ bond, so we have a double bond and ethene $\ce{C2H4}$, then the methylene groups can no longer freely rotate about the bond.

That's because the electron probability density added by the $\pi$ bond doesn't lie on the inter-nuclear axis, but above and below it. Double bonds, and by extension also triple bonds, are rigid, with respect to rotation of the atoms they bond. This can give rise to cis-trans isomers.

Below: highly schematised rendition of the electron density of a $\pi$ bond: