I wrote The Last Service Pack a few years ago, thinking that SQL Server 2016 SP2 might end all large updates. At the time, Microsoft was moving to a Cumulative Update process, with the aim of releasing small patches for each version every couple of months. They've done a good job of that, and SQL Server 2017 now has CU 26 with more coming.
With little fanfare, we got Service Pack 3 for SQL Server 2016 recently, with a long list of fixes. Even Pedro Lopes notes this is the final service pack for any version. That means I expect that in the next few years, I'll start to encounter people working with technology who have no idea what a service pack is or what those are used for.
Visual Studio has "updates". VS Code and ADS just tell you constantly they need an update, with no deal designation about versions. SSMS has slowed their pace of changes, but the tool really just gets a new version every few months. With Windows we get a large update periodically, but those seem to be called a May 2021 update (or something similar).
I wonder if we are moving towards the era of commercial software being continuous, with updates being released continuously and available for install by customers. Certainly this is the way many of us build software inside organizations, constantly enhancing and fixing code and deploying it out. We often don't give our customers much choice in whether we deploy changes, and I suspect commercial software is going this way in many instances.
I not-so-fondly remember digging into Service Pack changes and trying to test them against applications, sometimes for weeks before a deployment across an estate. These days, I tend to apply cumulative updates a few weeks late, after ensuring I don't see many reports of issues on the Internet. I don't mind keeping up to date, but I don't like to be the first one to do so.
I don't know that I care if Service Packs go away. I've gotten comfortable with Windows updates, Cumulative updates, and even the random changes in VSCode/ADS that seem to come monthly. I don't see applications crashing often enough to stop trusting most vendors. Hopefully that feeling continues.