Regular tuner: can tune what a TV can tune.
CableCARD tuner: can tune what a cable box (provider-supplied) can tune. That's a lot more channels. And, in their native HD, too.
I bought an ATI Digital Cable Wonder for my dekstop just over a year ago, and haven't had any regrets. That pushed me to get the InfiniTV4 for my main Media Center - again, no regrets. And, I've ditched the cable box completely - there's not a one in the house.
Good: watching Sanctuary in HD.
Better: watching Sanctuary in HD, using my Media Center (and its better UI, and its better Guide) to tune it.
As to CableCARD in Canada: it does happen; however, it's on a market/provider decision basis. If you're lucky, your local cable office can make a CC available to you. If you're not (most markets, from what I understand) so lucky, well, no CC for you. And, unfortunately, your CRTC hasn't mandated (because, as I understand, they can't mandate it) making CableCARD available.
Here in the U.S., all cablecos are required (by FCC mandate) to make them available.