Old film cameras have low-light performance that is limited by the film you can load into them. Unfortunately, high speed film was never all that great. Digital imaging has significantly improved performance at very high ISO values -- laughably high, if you have been doing this long. I have some very nice prints shot under candlelight at ISO 12,800, and they make good 16x20s with almost no visible noise (grain). And these were made with a previous generation Canon camera that is not the best low-light model on the market.
I don't know anything about the Sigma Foveon cameras, sorry. For low light shooting, most of the current prosumer level cameras will do very well at 3200 or 6400 or higher, or pair them with a fast prime lens just like the old days.
(Just checked a review of the SD9 - is it really from 2002? Wow. Back then not many cameras did well above ISO 1600. You may be very pleasantly surprised at the capabilities of current cameras.)
Good luck and happy shooting.