This guide will help you pick the right SDCard. It is opinionated and primarily based on my subjective experience as a photojournalist over the last 15 years. There is a brief section on other cards at the end.
In theory the UHS rating indicates the speed limit of a card. However, it depends on what your device can support. Although these interface levels are downward compatible, the price difference is huge! Getting UHS-II when you really only need UHS-I is a waste of money. You will get about double the capacity for your money when using UHS-I over UHS-II.
Even if your camera supports UHS-II, I do not usually recommend it. In the original spec, UHS-I supports roughly 100MB/s and UHS-II supports up to 300MB/s. However, manufacturers have pushed this. Premium UHS-I cards can do 200MB/s. Those are still more affordable and give you more space the money with similar performance.
Let's say you have a Sony A7 III which does support UHS-II. It can shoot 4k video up to 100Mb/s. Note that the "b" is lowercase. That stands for "bit", while an uppercase "B" stands for "Byte". It takes 8 bits to 1 Byte. This 4k Video is really only 12.5 MB/s. As you can see, this is very well within the UHS-I spec.
Most likely your camera manual will tell you to have a minimum V- or U-rating. If it does not mention anything V-30 will be sufficient. This means the camera can store 30MB/s. Double the rate you'd need on a A7 III 4K 30fps. If you want to shoot more frames, 8k or uncompressed video higher V-ratings will become relevant. In those cases your manual will mention it.
A raw photo will give you about 30 Megabytes. Those can be stored at a rate of 3 per second with the UHS-I pec, and the camera does have a buffer for bursts. With a premium UHS-I card, you can expect 6 photos per second. Unless you are shooting sports, you will be fine. With JPG, you do not need to worry.
If you choose UHS-II, make sure that you also have a card reader that uses the standard. Downloading data to your computer is probably the biggest benefit of UHS-II cards. You can expect 30% of time saving. When working with large videos or huge amount of photos under time pressure this can be worth it for professionals. Otherwise just go and get a coffee.
Next up, I recommend picking your brands. Mostly, I think they are the same; there are basically two groups. There are established brands for professional photographers like SanDisk, Transcend, and Lexar. (There is an extra take on Sony towards the end.)
Memory cards can easily be overlooked. You buy them once - usually with a new camera, and then they just do the job. This is why I feel investing a few bucks in a known brand is the way to go. Over time, the price difference will not only be negligible, but most brands do offer warranty for extended periods.
To be fair I think many manufacturers can make good memory cards. The issue is more in quality control. The brands I mentioned before are the market leaders among professionals. Any loss of data can diminish their reputation. The volume of cards they make is enormous. The combination of those two things will give them an advantage in quality control. The question is not if any other brand is just as good. The question is if your card out of the many made will develop a defect with your precious photos on or not. That doesn't come down to the cards specs itself, but in making sure solder points are state of the art, glue used is withstanding time and quality control is working perfectly.
I only recommend affordable brands for hobby projects where the data is not that important.
After selecting the UHS-Bus and filtering by your preferred brands, it is time to select price and size. I do recommend going as big as you want to afford. In my experience, it's good to not reformat cards after every shoot. I prefer to keep some rolling backup in my camera. Shooting with a 512GB card will allow you quite some backlog. If your computer breaks before you have made a recent backup, you will be able to recover your material.
SD Cards are pretty flimsy. Another reason to have a big card is to avoid swapping cards. When swapping cards, chances are higher to lose a card or to break it. Snapping off the write-protect notch can ruin your day. Better have a huge card and only take it out at home.
Basically, click on the price header of our memory card table to sort by price and then see how far you are willing to go.
If you followed my advice and picked only recommended brands, all the cards shown will be U3 rated. They will all be perfectly fine for photos and video up to 4k.
However, you will notice there are more expensive cards. Lexar has some X-number, and SanDisk will present Ultra, Ultimate, and Extreme Pro. If you are willing to spend more money, those can be a good option. SanDisk Extreme Pro, for example, features 30 Years of warranty.
Also, the read speeds when downloading to your computer will be noticeably faster. If you are willing to spend more, the faster cards are worth it. For shooting, those will only help you with long bursts e.g., shooting sports or if you are going over normal 4k video.
There is a line that is different from all: Sony Tough. Those cards deviate from the spec to a small degree. They do not have the write-protect toggle - the one that nobody uses and can ruin your day when it falls off. Some other features are changed to make the card more sturdy, too. However, those cards come at a premium.
If you absolutely have to swap cards in the field very often, you might consider this. Let's say you shoot weddings and every hour your assistant picks up the card to process photos. In similar cases, these cards are worth looking into. In any other case, just get a much bigger card and take it out less often.
I generally do not recommend planning to buy more than you currently need. Nobody knows which type of card will be needed in the next generation of cameras. Cards get faster, bigger, and cheaper every year. Don't buy some faster card hoping you will need it eventually.
CF cards aren't really common anymore. Just pick one of the few brands still available that you have heard of.
For CFE, prices are very high. You are most likely using professional equipment. Check the manual of your camera for the needed specs and get exactly that without going over. Chances are your next camera will need something else anyway.