Artemis, I was re-watching the SG-1 pilot the other day, and I'm pretty sure she was introduced as "Captain Carter" -- well, after they had the general talk about "Sam Carter" so as to make everyone think she was a he, for maximum character-intro effect...
Brenda, Stargate is frequently excellent, with very strong characters and a very richly-textured universe. Plus it's set in modern America so the characters get to joke about Star Trek, etc.
If you were starting from scratch, I'd recommend you watch the Stargate movie first, then head over to the TV-show pilot and try not to notice that all the characters are now played by different actors
Some of the early episodes suck, but... not much worse than Smart Kids. It can be very confusing if you come in on one of the later seasons, though. ("What are Jaffa, and why don't they like the Tok'ra?" and "Why is Sam calling that alien 'Dad'?" were some of my personal puzzlements)
PJ