The query: SELECT COUNT(*) FROM accounts will run incredibly slow on a table that has half a million records. I thought it would use a key by default? SQL Server could return it in half a millisecond.

Shouldn't a COUNT query be fast?