The longer/larger your RV, the fewer sites it will fit in. It's not as though there's generally a hard cutoff at 40' (or any other particular length), but bigger does mean fewer sites.
States and parks do vary quite a bit in how well they accommodate large rigs. Even a 35' RV is fairly limited in what Vermont state park campsites it can fit in, for instance; many of the campgrounds were laid out and built by the CCC and so date from a time when tenting was the norm.