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

Question

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?

Solution 1

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.

Solution 2

When you're creating database objects there are two places you can store comments. Before the object definition (and after any GO statements) and inside the object itself.

USE GODUCKS;
-- This comment will not be preserved
GO
-- This comment precedes the view definition
-- This too 
CREATE VIEW dbo.CommentedView
AS
    -- This comment lives inside the view
    SELECT 1 AS MyColumn;

Hit F5 and then script the view back out. You can see where the comments have/have not been preserved.

USE [GODUCKS]
GO

/****** Object:  View [dbo].[CommentedView]    Script Date: 10/15/2013 8:12:49 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- This comment precedes the view definition
-- This too 
CREATE VIEW [dbo].[CommentedView]
AS
    -- This comment lives inside the view
    SELECT 1 AS MyColumn;

GO

Comments

[Adir D]

You can put comments before the CREATE VIEW statement, but I don't know why you'd want to - makes the comments much easier to lose if someone copies and pastes the view definition...

[James Jenkins]

This was very helpful, but @AaronBertrand reconized that my biggest hurdle was viewer choice, so accpeting his answer. Your answer is still good and addresses the broader question well.

[Hamp]

To address the original question, comments can be added following Aaron's method to edit it in code. It should be pointed out that the comments need to be next to the actual "ALTER VIEW" code or they will not be saved. Comments can be before, interspersed, or after the code (but between the enclosing "GO" statements). If you later edit and save the view using the designer, the comments will still be saved. However, they will be grouped together just before the ALTER VIEW line the next time you use the Script As approach. There does not seem to be any way to view/edit them in the designer.