Double-teaming a player will put more defensive pressure on the player selected, reducing his shot attempts/efficiency and possibly forcing more turnovers.
On the other hand, if a player is double-teamed, that means somebody is open - giving the open player(s) more open shots than they'd normally see.
Setting a player to 'always' will result in an attempt to double-team him when he has the ball. Setting a player to 'leading scorer' means that you will only attempt to double-team him if he is the leading point scorer for his team.
Keep in mind that the defense won't be able to successfully execute the double-team on every possession.