Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

A look at comments

Everyone knows that we should include comments in our code right? On the other hand the vast majority of us don’t use them as much as we should, myself included. Believe it or not I actually got a job based on the comments I had put into the tech test. It was between me and another person, both of us finished the tech test in about the same amount of time, with code that was similar enough to make for a difficult decision between us. However, I had carefully commented my code (for my own use more than anything) and I ended up with the job. And in case anyone is wondering how I know it was the comments that got me the job, the manager told me after I had been there for a while. On that note here is a brief look at comments in T-SQL.

Here is some common knowledge about comments

  • In SQL Server to comment a single line (or part of a line) you can use a double dash.
  • --This is a single line comment
    PRINT 'This is an example ' -- of commenting part of a line
    -- This is
    -- An example
    -- Of commenting
    -- Out a block
    -- Of code
     
  • And to comment a block of code you use /* to start and */ to finish the block.
  • /*This is a single line comment*/
    PRINT 'This is an example ' /* of commenting part of a line*/
    /* 
    This is
    An example
    Of commenting
    Out a block
    Of code
    */

    Not as common knowledge about comments

  • If you highlight a section of code and hit ctrl-k, ctrl-c then SSMS will automatically put a double dash in front of every line that is even partially highlighted, effectively block commenting the section.
  • If you highlight a section of code and hit ctrl-k, ctrl-u then SSMS will automatically un-comment (remove the double dash) every line that is even partially highlighted.
  • Note: For the above two “tricks” the double dash goes at the front of the line even if you only have part of it highlighted.
  • A comment block has to be closed even if it is inside of another comment block.
  • This works

    /*   /* */   */

    This does not

    /*   /*   */
  • /* can be commented out outside of a block comment but not inside of one.
  • This works

    -- /*
    PRINT 'Playing'
    PRINT 'with'
    /*
    PRINT 'block'
    PRINT 'comments'
    */

    This does not

    /*
    PRINT 'Playing'
    PRINT 'with'
    --/*
    PRINT 'block'
    PRINT 'comments'
    */

    Some common comment usages

  • Comments right after a declared variable can help define what an abbreviation is or what values should be used.
    DECLARE @EmplrName varchar(50)-- Employer Name
    DECLARE @Status varchar(10) -- Hold, Ready, Complete 
  • A comment “line” can be used to divide sections of code. This is particularly helpful if you include an actual comment to define the section.
  •  -----------------------------------------------------
    -- Declare and initialize variables
    Code here
    -----------------------------------------------------
    -- Process order information
    Code here
  • Not all lines need to be all dashs. Play with other characters to see what you prefer.
  •  -----------------------------------------------------
    --===================================================
    --***************************************************
    /***************************************************/

    A couple of comment lines, maybe with a descriptive comment in the middle, can provide extra emphasis to a section of code.

    --===================================================
    --=========== Load and process employees ============
    --===================================================
  • Formatting your comments with your code is much easier to read and makes it clearer which line or section of code you are describing in your comment.
  • -- This is a loop
    WHILE 1=1
    BEGIN
    	-- This is what the loop does
    	PRINT 1
    END
  • A comment block at the top of a stored procedure is a good place for a general description of the SP, comments about the parameters, and over time what changes were made and when.
  • /*************************************************************
    ** This stored procedure is really important.  It does stuff
    ** Parameter list
    ** @StuffToDo -- What do you want the stored procedure to do
    ** @WhenToDoIt -- When do you want it done
    **************************************************************
    ** 10/20/2000 - Fixed bug that made nothing happen.
    ** 11/21/2001 - Put bug back in because to much was happening.
    *************************************************************/

    Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, comments, language sql, microsoft sql server, sql statements, T-SQL

    Comments

    Leave a comment on the original post [sqlstudies.com, opens in a new window]

    Loading comments...