SQL SERVER – Bad Practice of Using Keywords as an Object Name – Avoid Using Keywords as an Object

Madhivanan is SQL Server MVP and very talented SQL expert. Here is one of the nugget he shared on Just Learned.

He shared a tip where there were two interesting point to learn.

  1. Do not use keywords as an object name
  2. [read DHall's excellent comment below]

He has given excellent example how GO can be executed as stored procedure. Here is the extension of the tip. Create a small table and now just hit EXEC GO; and you will notice that there is row in the table.

Create Stored Procedure
CREATE PROCEDURE GO
AS
SELECT
1 AS NUMBER

Create Table
CREATE TABLE T1 (ID INT)

Now execute following code
INSERT INTO T1(ID)
EXEC GO;

Now when selecting from table it will give us following result:
SELECT *
FROM T1

Now see following resultset:

So without inserting any data we inserted the data, well indeed good puzzle but very bad practical practice. Every body should be aware of this gotcha and avoid it. Thanks Madhivanan for teaching this interesting learning.

Republishing here with authorization of Just Learned.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – Bad Practice of Using Keywords as an Object Name – Avoid Using Keywords as an Object

  1. The second point you make isn’t exactly accurate. The semicolon has nothing (or very little) to do with executing the GO stored procedure. The code works the same even without the semicolon as long as EXEC and GO are on the same line or if [GO] is wrapped in square brackets.

    However, if the GO proc name is the only thing (other than an optional comment) on the line following following the EXEC command, then the SQL query window will try to interpret it as a batch delimiter if it’s not followed by a semicolon or wrapped in square braces.

    In other words, the following SQL will work as expected

    INSERT INTO T1(ID) exec GO — Note no semicolon
    SELECT * FROM T1

    — and so will this:
    INSERT INTO T1(ID) exec
    GO; — Note the semicolon
    SELECT * FROM T1

    — and so will this:
    INSERT INTO T1(ID) exec
    [GO] — Note the square brackets but no semicolon
    SELECT * FROM T1

    — but this won’t:

    INSERT INTO T1(ID) exec
    GO — Note no semicolon
    SELECT * FROM T1

    Another evidence that GO is different from SQL reserved word is to duplicate your experiment using [TABLE] as the proc name instead of GO. You’ll note that unlike the word GO, the word TABLE must be wrapped in square brackets so that TSQL will allow it to be an object name. For GO, the square brackets are optional.

    — The following code works fine
    CREATE PROCEDURE [TABLE]
    AS SELECT 1 AS NUMBER
    GO
    INSERT INTO T1(ID) exec [TABLE] — Note the square brackets
    SELECT * FROM T1

    — but this generates a syntax error
    CREATE PROCEDURE [TABLE]
    AS SELECT 1 AS NUMBER
    GO
    INSERT INTO T1(ID) exec TABLE — Note no square brackets
    SELECT * FROM T1

    As you mention in your first point, using keywords in this manner can be trouble, and should generally be avoided for many reasons, but it especially helps in this case to understand that GO is not a reserved word in TSQL, but only in some of the tools (like SSMS) that process SQL queries in batches.

    The following excerpt from SQL BOL should help clarify the difference between the GO batch delimiter and true SQL reserved words.

    GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

    SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

    A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

  2. DHall,

    Thank you for your comment. Your comment is much more informative and complete compared to original blog posts :)

    Thanks for spending time and sharing this with us.

    Good Learning and I will say again that I just learned!

  3. Really a nice observation as well fact.

    Pinal , can we have an article on debuggin T-SQL in SSMS.
    I am googling on it , but couldn’t get any simpler article. I anticipate your’s one will be of great help.

    -Lalit

  4. You can change the batch delimiter used by SSMS from “GO” to “xyzzy”, or “8”, or pretty much anything you want. Jonathan @fatherjack Allen did a demo at SQLBits recently showing some of the weirdness that can happen if you set it to a number…

    In SSMS, go to “Tools”, “option”, “Query Execution”, “SQL Server”. And there you have the option to change the batch separator. See http://msdn.microsoft.com/en-us/library/ms180280.aspx

  5. Pingback: SQLAuthority News – An Year Worth Remembering and Looking Forward to Better Next Year « SQL Server Journey with SQLAuthority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s