would it be possible to use the SD card as the storage location for the sketch code?
Technically, since the ATmega is capable of self-programming, the answer is yes.
Practically, the answer is no. Since there is no way to run code from the external device, you would be required to copy code from the SD card to flash, run the code, then reprogram the flash with whatever code you need to run next. This is slow, complicated, and the flash has a limited number of write cycles, which means the ATmega would die an early death.
maybe in a dual core setup.
I think this has more potential, but not in the way you say. The dedicated ATmega would provide FAT filesystem support and interface to the SD card. FTDI has already done this with the Vinculum, although with a USB interface and not directly to SD.
-j