It is of course possible to run a secure website without having a Content-Security-Policy. There are, however, some reasons it makes sense:
- First, if you configure it reasonably strictly, it's possible to make certain security problems, like cross-site scripting attacks, harder or nearly impossible. This does typically require eliminating inline scripts and restricting external scripts to load only from trusted domains.
- You can also, more generally, require your pages to only function over HTTPS, since a robust CSP header will reject non-secure origins for loading external resources. Thus, a page accidentally loaded over HTTP would be devoid of images, scripts, and functional forms. While this would be a bad user experience, you'd at least avoid a lot of security problems in such a case.
I would say that now, in 2021, adding one is a best practice. Many sites do not, but because CSP headers do provide defense in depth for a wide variety of problems, it would be prudent to add one. I have no knowledge or interest in SEO and so couldn't speak to it, even it were on topic.