The difference ... how shall I start?
A rear mech should be able to handle both the big-big and small-small combinations, but with modern drivetrains those differences can be so big that an SG simply can't deal with them because the cage isn't long enough. It would either drag the chain on the small-small combination or get pulled apart on the big-big.
As for knowing which one you need :
Count the difference in teeth between the largest and smallest chainrings (for example if you have a 42-32-22 crankset, you need to do 42-22 => 20 teeth difference).
Do the same for the cassette (for example 28-12 => 16 teeth difference).
Now add those numbers together (in my example : 20 + 16 = 36 total)
If the number is below 33, you can use an SG (mid-cage) without worrying. If it's between 33 and 43, use an SGS (long-cage).
EDIT : Of course you CAN run an SG mech on the example I used, but you'd have to be very careful. Accidentally shifting into the wrong combination could kill the chain or mech, or cause the mech to self-destruct and ruin the hanger, chainstay or even tear off any integrated hanger.