With SQL Server 2008 came new version of MS SQL Server Management Studio that added much anticipated IntelliSense, but even now it's not putting products like SQL Assist or SQL Prompt out of market and it doesn't work with pre-2008 databases even if you use latest Management Studio.
If you would like some more power features in Management Studio, new version of previously mentioned SSMS Tools Pack from Mladen of I want some moore fame is your poison.
My favorite features include:
- query history,
- insert statement generation,
- CRUD statement generation,
- full database search,
- running single script on multiple databases,
- new query template.
But there is more, depending on your needs.