I often carry my SPG card with me depending on where I am and what my plans might be. If you happen to eat/drink at an SPG property, having the card on you makes it quite simple for them to fill out the food & beverage receipt to get your Starpoints. (Not even sure what they'd do otherwise—probably a long wait while they went to the front desk to look up your SPG number and confirm identity and such, if they were even willing to do that without the physical card present.)

I've never actually showed it at check-in though, except at Le Méridien Grand Pacific Tokyo. I nicely asked if they'd been able to upgrade me (they hadn't already, and they did so after I asked), then they said they needed to see my SPG card and proceeded to go photocopy both sides of it. (Other than that my stay was generally wonderful, and the staff was great. Except for a minor billing issue adding a day to the stay and being overcharged, but a quick call to Corporate Customer Care and visiting the front desk again cleared that up fairly easily. I blame that one on toothing problems of it being a Le Méridien property absorbed into Starwood and not on them openly trying to screw guests over as some properties obviously do.)