I was lucky enough to stumble across one of the special run all-stainless P229s new/old stock a couple of years ago. It was manufactured in 04-06 based on the serial number, and the model number on the case is P229-40-S. I have it pictured here next to my alloy frame .357 SIG P229, which was manufactured in 94-95.
I love my classic SIGs, and still have a handful from the late 90s and early 2000s when they were turning out elegant, high-QC handguns. I carried 9mm P229s exclusively from 2006 until around 2011, and was impressed with their reliability and shootability. To answer the OP's question, yes they are effective reliable, durable, and accurate. Yes, they are also somewhat capacity-to-size ratio inefficient, but not enough to be a handicap if the gun works well for you. It's similar in size to a G45, with a loss of 2 rounds in 9mm if you use the Mec-Gar 15 rounds. With the original 13 round mags (assuming we are talking about a short extractor older gun) you lose 4 rounds.
In .40/.357, I feel it is an excellent choice. However, with the 5th Gen. Glocks addressing the downsides of the previous generations of .40s, I don't know that it is as strong a contender over Glock that it used to be. Of course, H&K and S&W also make solid .40s if that is what you are looking for.
Now the downsides:
SIG QC is hit or miss, and I would want a lot more vetting with a current-production gun than with the older pistols. Likewise, replacement parts for an older gun will be newer-production MIM, which, while perfectly serviceable in other models, is not as good as the older machined small parts with SIGs.
I also have not handled a current production gun that has the amazing double-action triggers that my older German guns have, particularly the mid-to-late 90s samples.
The .40 and .357 are out of production. While still readily available, particularly with LE trade-ins, replacement parts will likely get harder to find as time goes by. .40 mags can be difficult to find new, and last I checked Mec-Gar was only producing 14-round mags with essentially a +2 floorplate for the .40/.357 guns.
For me, my SIGs are relegated to passion of the gun shooting, and not something that I routinely carry or put high round counts through. A Beretta 92 Compact, particularly worked over by LTT, would address all of the downsides that SIG has, in a package that is truly G19-sized, and if I were looking for a metal frame DA 9mm pistol for carry, that's probably the way I would go. If I were looking for a .40, my first choice would probably be a Glock 22/23 (since I primarily shoot Glocks), or a USP/USP Compact if I wanted DA. At the same time, if I found myself in a gunfight with one of my SIGs, the only complaint I would have would be losing it to an evidence locker.