I don't have a lot of experience when it comes to the depth buffer, but I have been messing around with it and some sprites. Some of which have transparency and I have noticed that when the depth buffer is enabled I get some "weird" results

When the depth buffer is on (the orange sprite has a Z-index of -2 and red star sprite has a Z-index of -1). I get this weird "cut out" of where I expect things to be transparent, but really its just the clear color

When the depth buffer is off (since the depth buffer is off the Z-index does not matter, so I reordered the way sprites are drawn). The sprites are drawn as I expected

So I'm guessing this is where the whole "sort by depth" comes in that I have heard about? Which makes me wonder is there a point to enabling the depth buffer at all when rendering sprites since wouldn't sorting by depth simulate what the depth buffer does? Is the depth buffer more meant for 3D objects?

You can sort sprites perfectly without using the depth buffer, which is not possible with 3D models in many cases. The depth buffer could still help you, however if you want to avoid drawing pixels that would be occluded if you sort your sprites to render front ones first and back ones last. This can be a win even with simple pixel shaders because the rasterizer can reject large blocks of pixels so they don't even invoke any pixel shader work and skip blending/writing to render target. This way you can't do alpha blending properly, because occluded pixels will not be rendered at all, so blending will only happen for the front-most sprite with the render target clear color. The other gotcha is that you shouldn't use alpha testing either because that means the shader modifies depth and so the early-z reject will be disabled. However, you probably need to have alpha testing, so I would have separate shaders for alpha-tested and non alpha-tested sprites, to try to overcome this issue.

For a sprite based game I would only worry about this if you have many sprites overlapping (more than a few hundred I'd say) and you are experiencing that you are fill-rate bound. You are probably fill rate bound if lowering the resolution significantly increases your frame-rate.