Essentially what bearisgray has said pretty much covers it. As I understand it, most of the 'computerized' machines have what you could think of as a 'hard drive' much like your computer where all the basics are stored such as stitch length; decorative stitches and the like.
I have both types - a couple of vintage mechanical machines and a computerized Viking. The thing I love about the Viking (and others of it's type) is you have the ability to easily have needle up/down settings; some with auto tension features; auto cutting features; needle position adjustment; etc. It's not just about embroidery or applique. Mine does have a plethora of decorative stitches but I rarely use them. I bought the machine because of some of the other 'auto' features. It is my go to piecing machine because it's so easy to use.
I hand quilt; don't do art quilts; etc.