Regarding it not being there for all integrations, I’m curious which ones you’re missing it on? I just looked at the 33 integrations I have setup via GUI, none that are using local polling or cloud polling were missing the option. You can tell the what class your integrations are by visiting the documentation from the same 3-dot menu and visiting the “Documentation” link and looking for the IoT device class in the details in the upper right corner of the page (this should exist for all core integrations). For instance Hue is “Local Push”: Philips Hue - Home Assistant. Anything that is Cloud Push or Local Push wouldn’t use polling, so no need for a switch. If there are core polling integrations available for GUI setup that don’t allow disabling the polling so that we can control it ourselves, we should open an issue on them 
Regarding your other concerns: In my case, not with sql(since I don’t actually have any need for them), but with other sensor types, it did take me a bit of time to adjust to the change when moving away from scan_interval, but I haven’t felt limited by the change or like the overhead was large. Most integrations don’t require adjusting away from their defaults, but I don’t feel like I’ve been treated like Kindergartner because of that. Quite the opposite, with the move away from the naive scan_interval and the option to disable the polling and control updates myself, they’ve empowered me to make those decisions and do it flexibly. It’s just done from a different place in the gui (e.g. not where the device/entity is configured, but in an automation). It could be better documented though, no doubt!
Integrations can also be made (by the devs) to intelligently adjust the polling, btw. For instance, I use the custom NFL integration that will poll very frequently during live games of configured teams, but very infrequently the rest of the week, and it “just works™”, no custom setup work or any automation needed to benefit from that.
sql is one specific case where, as you stated, one might want very different polling periods for different types of queries, and that’s actually why I feel like the flexibility to set them up in the gui, with an automation to adjust the poll period actually works better than configuring in yaml. That includes no longer needing to restart the core if you need to make a change to the period. Just to be clear, when I say it’s better, for sql specifically I am not addressing the problem with very large queries and formatting them in the smallish text box…that’s really a whole other topic 