How do you leave comments in SQL Server 2008 R2 view with SSMS?

According to multiple sources Microsoft , SQL Server Administration Blog | zarez.net adding comments to SQL and doing so with SSMS is a piece of cake. And for the most part they are probably right. But when I log in and create a view I have been unable to leave comments in it.

If I use two hyphens (–) the comments get deleted when I save the view, it does not matter if I am creating it from scratch or updating a view that I created some time ago.

If I try the Edit -> Advanced -> Click ‘Comment Selection’ the Advanced option is not displayed (see screen shot)

Am I missing something or is it just impossible to leave comments in a SQL Server view?

Answer

Stop using the clunky and buggy view designer.

For a new view, just open a new query window and start typing. This will work fine:

USE MyDatabase;
GO

CREATE VIEW dbo.MyView
AS
  -- this view is cool
  SELECT whatever FROM dbo.wherever;

For an existing view, right-click the view and choose Script As > Alter instead. This will give you a much better experience (minus the ability to check and uncheck columns etc).

The various visual designers may look like they’ll save you time (and the intentions were certainly good), but the implementation is terrible, there are all kinds of bugs and limitations, and they really haven’t been improved or even touched in years.