Fundamentally they are the same thing in a different package but they work differently.

SD cards use their own protocol which was extended to go beyond 2 GB up to 32 GB with the introduction of SDHC (there were a few 4GB SD cards but not very compatible) and then to support up to 2 TB with the introduction of SDXC. The SD to SDHC transition if you remember was particularly painful as it took years for most other devices (Readers, Picture Frames, Card Readers, Laptops, etc) to catch-up.

CF cards use the IDE protocol which can index large volumes by using pseudo head, track, sector coordinates. That they just kept working as capacities grew, although FAT32 support is used above 2 GB. That makes the protocol more stable and extensible although the next revision is CFast (Compact-Fast) which is based on the SATA protocol.

The larger physical size of Compact Flash also gives them an edge in capacity and they still have a lead in terms of maximum speed as well. This has historically been significant but the gap is so narrow now that it is mostly a case of legacy.

In terms of camera grades, there are high-end models which each type of memory. The Pentax 645D Digital Medium-Format camera uses SDXC cards, Canon top-of-the-line models accept both CF and SD. This leaves only Nikon to exclusively use CF cards in their high-end models.