What impact can different cursor options have?

SQL Sentry's @AaronBertrand discusses the default cursor options in SQL Server and why, in many cases, you'll want to override them.