Handling of COMMENTS in the GDR


In the GDR you will mainly find large improvements, but it is not everything is better. One area where we regressed functionality is the deployment of comments inside T-SQL procedures, triggers,  functions etc. With the move to a true and only model based system, objects that are stored in dbo.syscomments or sys.sql_modules, are modeled in such a way that we separate out body script from the object name and parameters. We do this for a reason, so we when you rename an object we also update the actual name inside the script body. The nasty side effect is that all comment before the first real statement in the script body and after the last real statement in the script body as lost when deployed.

For example see the following stored procedure which has a comment line on every possible line.

   1:  -- comment line 1
   2:  CREATE PROCEDURE [dbo].[p_Comments] -- comment line 2
   3:  -- comment line 3
   4:       @param1 int = 0, -- comment line 4
   5:       @param2 int      -- comment line 5
   6:  -- comment line 6
   7:  AS -- comment line 7
   8:  -- comment line 8
   9:  BEGIN -- comment line 9
  10:      SET NOCOUNT ON -- comment line 10
  11:      -- comment line 11
  12:      SELECT @param1, @param2 -- comment line 12
  13:      -- comment line 13
  14:      RETURN 0 -- comment line 14
  15:  END -- comment line 15
  16:  -- comment line 16
  17:  GO -- comment line 17
  18:  -- comment line 18
  19:   

If you would inspect how this information is stored inside the model, by looking at the .DBSCHEMA file which is produced by the build step, it looks somewhat like this:

   1:  <Element Type="ISql100Procedure" Name="[dbo].[p_Comments]">
   2:       <Property Name="IsAnsiNullsOn" Value="True" />
   3:            <Property Name="BodyScript">
   4:                 <Value><![CDATA[BEGIN -- comment line 9
   5:      SET NOCOUNT ON -- comment line 10
   6:      -- comment line 11
   7:      SELECT @param1, @param2 -- comment line 12
   8:      -- comment line 13
   9:      RETURN 0 -- comment line 14
  10:  END]]></Value>
  11:            </Property>
  12:            ...
  13:  </Element>

This fragment indicates that the script body starts at the first statement, in this case the BEGIN statement and ends at the END statements. Comments between those to statements are preserved when deploying, the others, comments line 1 through 8 and 15 through 18 did not make it over. Which become very apparent when you look at the deployment script.

   1:  PRINT N'Creating dbo.p_Comments...';
   2:  GO
   3:   
   4:  CREATE PROCEDURE [dbo].[p_Comments]
   5:  @param1 INT=0, @param2 INT
   6:  AS
   7:  BEGIN -- comment line 9
   8:      SET NOCOUNT ON -- comment line 10
   9:      -- comment line 11
  10:      SELECT @param1, @param2 -- comment line 12
  11:      -- comment line 13
  12:      RETURN 0 -- comment line 14
  13:  END
  14:  GO

The comments inside the source code are preserved, but they simply do not make it to the target.

The team is currently working on finding the appropriate resolution for this issue.

-GertD

Comments (4)

  1. SafetyMan says:

    Another problem is this when writting comments or utilizing the comment buttons in SQL 2005.

    We traditional create formatted coments:

    search engine reference:

    sql 2008 comment butons function differently than the sql 2005 comment buttons in sql 2005 are different

  2. SafetyMan says:

    —————————————————————————

    –COMMENTED in SQL 2005 using comment buttons

    –Comment 1 (Example From Doug Lubey Of Louisiana )

    — Comment 2 with 1 tab  (douglubey.com)

    — Comment 3 with 2 tabs

    –After BlankSpace

    — Comment 4 with 3 tabs

    —————————————————————————

    —————————————————————————

    –Using button in SQL 2008 Enterprise studio

    –Comment 1  (THE EMPTY LINE IS NOT COMMENTED)

    — Comment 2 with 1 tab

    — Comment 3 with 2 tabs

    –After BlankSpace

    — Comment 4 with 3 tabs

    —————————————————————————

    To note: I also notice on occasion when working on code using sql server managment studio on SQL SERVER 2005 stored procs…comments appear right before the actuall first character in the line(or first space in the line)…  

    Where as in Studio 2005 had the comments left justified before the first tab in the line….In other words sql 2008 studio ignores the tabs when determing where to place the "–" comment characters.

    Thanks,

    Doug Lubey of Louisiana

    SEARCH ENGINE REFERENCE:

    SQL 2005 comments before tabs

    SQL 2008 ignores tab characters in comments

    SQL 2008 ignors blank lines in comments or when using comment button

  3. jst1699 says:

    hi, having the comments after the BEGIN for objects is fine, but it doesn’t work for views, as there is no BEGIN END.

    thx

    Jag

  4. aseniuk says:

    How do we handle commenting in views, other then putting the comments inside the select statement like

    create view xview

    as

    select

    /*Comment placement is stupid now in 2008*/

    column1

    from

    table