.local is used by zeroconf/rendezvous/bonjour/whatever it is called today, and so its explicit use as a LAN TLD is discouraged. If you try to set it in pfSense, for example, it warns you not to.
$ dig -p 5353 raspberrypi.local @224.0.0.251
; <<>> DiG 9.8.3-P1 <<>> -p 5353 raspberrypi.local @224.0.0.251
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 21640
;; flags: qr aa; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0
;; QUESTION SECTION:
;raspberrypi.local. IN A
;; ANSWER SECTION:
raspberrypi.local. 10 IN A 192.168.1.175
A DNS query packet is to the multicast address 224.0.0.251 and UDP port 5353. All devices on your network that are listening on that address will see the query (e.g. Linux systems with avahi-daemon or Macs running mDNSResponder). The device whose name is raspberryi will respond. RFC6762 should have all the details.
mDNS is cool and all, but I wish they would've picked a slightly-more-specific TLD. ".local" is the natural suffix for LANs, and making computers confused about whether they should resolve that via multicast or standard DNS techniques wasn't very nice. Something like ".mdns" or ".zeroconf" would've been a lot better.
mDNS is roughly the equivalent of going door-to-door in the LAN and asking anybody if they've heard of "Greg", so it I don't think it is that confusing that it gets .local, especially given the circumstances that lead to relying on mDNS in that most LANs don't have authoritative DNS servers.
It's not that the name doesn't make sense, it's just that it collides with a very common TLD for LANs. It shouldn't be squatting on a name humans want to use. Computers are unlikely to do the correct thing when you ping a .local on a network that uses that suffix, because they have to decide how to handle the potential name collision (which is still somewhat frequent, more than 15 years later).
It's the other way around, though? .local's usage on LANs prior to the RFC was squatting on a non-reserved TLD. The RFC finally reserved the TLD for an appropriate usage. The name applies equally to both usages, the usages can live side-by-side, and the RFC is even kind enough to support that (though as you point out frequently don't always work as expected), even though it didn't need to take into account backwards compatibility with "squatters".
.local is used ad-hoc by mDNS (Apple). Last time the issue of naming local-area devices came up on ##networking, the consensus was to purchase a domain name in order to name local network devices without interfering with global name resolution standards, which seems ridiculous.
That's the Microsoft recommendation. I think they're suggesting to use either split-view DNS (i.e., have DNS respond with internal vs external results depending on the source address of the requestor) or use a subdomain for internal hosts (e.g., corp.example.com). I don't think they mean to just buy a public domain and only use it internally.
In a well-configured machine second-level domains for .localhost are also supposed to loopback, per the RFC.
(It's useful as a development tool as you can Virtual Host project1.localhost and project2.localhost to different in development sites if your OS and server support it, which tend to be in similar ways to how they generally support Virtual Hosts.)
6.3. Domain Name Reservation Considerations for "localhost."
The domain "localhost." and any names falling within ".localhost."
are special in the following ways:
1. Users are free to use localhost names as they would any other
domain names. Users may assume that IPv4 and IPv6 address
queries for localhost names will always resolve to the respective
IP loopback address.
[...]
3. Name resolution APIs and libraries SHOULD recognize localhost
names as special and SHOULD always return the IP loopback address
for address queries and negative responses for all other query
types. Name resolution APIs SHOULD NOT send queries for
localhost names to their configured caching DNS server(s).
And goes on to specify how resolvers, authoratitive DNS servers, registrars, etc. should behave.
Another wide-spread non-standarized TLD is .box. It is used by Fritz!Box home routers (https://en.wikipedia.org/wiki/Fritz!Box). They are shipped with a really good operating system and handle the resolution of .box very seamless, integrating all the names of registering DHCP devices (i.e. you can resolve your-iphone.box in your LAN). The TLD stems from the "pun" http://fritz.box.
