Stream Processors are simple execution cores like that of a cpu except their simpler, have less complex instruction sets, and do less per clock cycle. AMD's SPs and Nvidia's are very different though.
Nvidia - The simple way
Nvidia's SPs are all the same like a CPU, when you write code as many as needed will be used, really simple, SP 1 is the same as SP 2 & 3 & so on.
AMD - Teamwork
You might be wondering why the 4870 has 800SPs while the GTX260 has 216, AMD's SPs are grouped in blocks of 6. The first 4 are the same while the 5th carries FP/INT instructions and the 6th keeps things in check. Theoretically this can more potential yield but the code has to be optimized for the architecture.