hanks for the insights.
If anything, what you're saying is reinforcing my recommendation for the Sony FE 50mm f/2.8 Macro. Its IQ is fine for the task at hand (it's very sharp, with little optical problems. You can look at the DXOMark test (from the time they did helpful lens testing regularily):
Lens options for Sony’s full-frame A7 series of mirrorless cameras are increasing all the time, and the new $498 Sony FE 50mm f/2.8 Macro adds another choice in this popular prime lens focal length. Great for general-use photography and portraiture, the Sony FE 50mm f/2.8 Macro also boasts 1:1 macro magnification with a 16cm/6.3-inch minimum focus distance, for high-quality close-up photography. FE-mount lenses also remain compatible with Sony E-mount mirrorless cameras such as the A6300, offeri
www.dxomark.com
This lens will produce as good or better image quality than any of the fast Sigma lenses. For reproduction, you need depth of field, i.e. a fast maximum aperture is simply not needed - nor is it useful. And you'll be using a tripod anyway, so, even less reason to shell out too much for something that's not necessary.
Even as a carry-around, the Sony has a lot going for it: It's much smaller and much lighter than either of the two Sigma lenses. Its only "fault" seems to be that it focuses somewhat slowly (as is common with macro lenses) - so, it's less of a street and event lens, but that's just about it for "issues". It's also quite a bit less expensive. The Sigmas only make more sense if the money's literally burning a hole in your pocket. Also, the FE 50mm Macro will remain useful even if you add another prime later, depending on need and taste. You have a great many options there, not least the wonderful Sigma 35mm f/2 Contemporary (best bang for the buck if you don't need weather sealing, IMO). But I digress ...
Anyhow,if you're after the best prime you can get in that range, get the new Sigma 50mm f/1.2 DG DN - it's spectacular, and that's only from initial testing. I'm sure it'll end up topping most rankings. It's not as useful as a reproduction lens, but it'll tick all other boxes (except for world's fastest AF - but that's not why you buy a f/1.2 lens). From my experience with the Nikon Z 50mm f/1.8, I can tell you that the 5cm closer can make a real difference, provided close-up IQ is good enough (not guaranteed with a normal lens, par for the course with a macro lens).
I'd
definitely stay away from the Sigma 40mm f/1.4. You've invested in a Sony mirrorless body - why weigh yourself down with a humongous DSLR lens? The Sigma 50mm f/1.4 DG DN strikes me as not especially useful for reproduction - with its 45cm minimum focus distance, it's just not made for that, and after the arrival of its f/1.2 stablemate (which sports 40cm MFD, at least), it just doesn't have enough going for it except possibly price - but it's still more expensive than the Sony 50mm f/2.8 Macro, so ...
If you just need a lens for reproduction, John's recommendation is as good as any, even though I'd personally prefer the Nikon 55mm f/2.8 Micro over the f/3.5, but that's just a matter of taste.
The zoom thing, well ... You experienced zoom creep in an aging amateur lens - that's common. It doesn't happen with all zooms, though - and Sony's G lenses are generally very well made and nicely balanced. But of course, there's no walking away from such an experience. Again, this makes me think the Sony 50mm f/2.8 Macro is
the lens to get.
I've had my say, however. Let's see what others recommend!
M.