Most of the time, 2FA means using a token generator, such as Google Authenticator, Authy, or similar. They are just apps. This is much safer than SMS because one would need physical access to your unlocked phone to generate a token.
In cases where server security was breached and databases (or database backups or dumps) were accessed, if the TOTP seeds were part of the database (not sure how likely that is, but I'm guessing it's likely), then TOTP is doing nothing for security.
TOTP protects against things like credential stuffing and weak passwords, and is safer than SMS (no hijacking/intercepting), but for database security breaches things aren't so cut and dry.
I wonder if there should be a TOTP-like app which you still register with a site when you first log in or create your account, and which codes are sent to when new logins are needed, but which uses a more secure communication channel than SMS. This gives you the best of both worlds, no? One-time codes not generated from a single plain text seed, communicated to a known client over a secure channel, to prove the initial user is still in possession of the known client?
If your database contents are exposed (or a dump or backup is exposed) unbeknownst to you, those TOTP secrets won't be invalidated, and attackers which get their hands on that have more than they'd have if some other second factor method was in use which didn't rely solely on a long-term shared secret.
And since database exposures can go back a long way (like this one), and TOTP secrets aren't normally rotated over time, then this difference in second factor methods seems interesting.
But since your user auth flow needs access to the unencrypted TOTP seeds, and user info generally lives in a database, for the majority share of infrastructures a compromise of the DB will compromise all TOTP seeds.
Which is not true of some other 2FA mechanisms (which is my point - I never thought about it before, but TOTP has a specific failure mechanism that not all 2FA mechanisms have). I still prefer TOTP to auth codes over SMS, but I wish there was something better (TOTP with seed rotation, auth codes over more secure channels, etc).
Scenario 1: The service stored passwords in some way and TOTP seeds in the database and uses TOTP to authenticate users.
Scenario 2: The service stored passwords in some way and uses some other 2FA mechanism (like unique codes over some secure channel)
You seem to be claiming there is no different between the two, where as I think I'd prefer scenario 2, because in scenario 1, if my password is obtained somehow (the password was not stored securely, I used a weak password, credential stuffing matched my password from other site leaks against the email in this database leak, or any other way) then the attacker can log in to my account on the service right now and bypass TOTP 2FA. In scenario 2, 2FA is still protecting my account.
Regardless, I will keep this 2FA difference in mind going forward, since it is interesting to me; of course, you (and others) are free to ignore my thoughts if you so choose.
You can protect your TOTP/HOTP seeds on the YubiKey using a PIN (which I would recommend anyone to do). This is supported in all the Yubico Authenticator clients.
But: When your YubiKey is stolen/lost, your TOTP/HOTP seeds are gone for good. Make sure you have recovery codes stored in a safe place, e.g. your password manager.