The hard limit on XP/2k is 4GB, though I've heard that has some problems with >3GB. The way computers currently work, it doesn't matter how much RAM you have, as long as it's more than the OS and programs you're running require. In other words, at present the performance difference between 4GB of RAM and 1TB would be minimal - actually, with current technology 1TB would probably perform horribly, since it would have God-awful timings. I have 2GB in my comp, and I don't think I've ever seen more than about 1.3GB actually used.
The theoretical limit for a 64 bit computer is 128TB of RAM, IIRC. I'm kinda doubtful that we'll come close to that limit before the next generation (128 bit) comes about, but it's possible. I'd expect it to be at least a year or two until the enthusiast-type boards start supporting more than 4GB.