Quite efficient, but I think there's fees for the merchant in this case.
With 3D Secure, you provide the normal card data (i.e. card number, expiration, name, CVV/CVC) to the merchant and are then redirected to an authentication form from your card issuer. There, you'll be asked to either provide a password or, as you described, input a confirmation code from a text.
With Sofort, you're entering your login data to your e-banking account on a login page served by Sofort. They, in turn, use it to log in to your e-banking using a simulated browser and send a wire transfer. You lose control, however, of what else they do: once they are logged in to your e-banking, they can check your previous transactions, send other transfers or they might change your mailing address...
I suppose the merchant decides wether to use this or not by trying to find a balance between user experience and fraud risk.
In our case I think the limit is set right above the usual purchase amount (we sell movie tickets). It's low enough that a fraud wouldn't hurt us too badly and there's not much incentive for it either. Also, most of the clients don't have to fiddle with 3dsecure (in my case I would have to cary a fob around, which I never do), so it's a better experience for them.
If someone tries to buy a lot of tickets at once, they are more likely to be doing something fishy so we use 3dsecure.
Do they offer any non SMS options?
An incredible pain. I hate it. ApplePay for the web is far superior.
I suspect it depends on your bank. Back when i used Crédit Agricole, i was indeed forced to do SMS auth, which is inferior.
Most of the time, non 3d secure purchases take a little more time to go through and if the amount is higher than your regular spendings or the charge happened to be in the middle of the night, banks ask for confirmation via SMS anyway. If you go with 3D secure, it just works instantly.
All banks provide virtual credit card numbers with predefined limits too though.