SQL SERVER – A Simple Quiz – T-SQL Brain Trick

Today we are going to have very simple and interesting question.

Run following T-SQL Code in SSMS. There are total of five lines. Three T-SQL statements separated by two horizontal lines.

SELECT MAX(OBJECT_ID) FROM sys.objects
______________________________________
SELECT MIN(OBJECT_ID) FROM sys.objects
______________________________________
SELECT COUNT(OBJECT_ID) FROM sys.objects

Now when you execute individual lines only it will give you error as

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '______________________________________'.

However, when you executed all the five statement together it will give you following resultset.

SQL SERVER - A Simple Quiz - T-SQL Brain Trick puzzle_sep

What is the reason of the same?

Please leave your comment as answer. I will discuss the answer of this question on tomorrow’s office hours.

One random correct answer will win my SQL Wait Stats [Amazon] | [Flipkart] | [Kindle] copy – anywhere in the world.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts
Previous Post
SQL SERVER – Next Version of SQL Server ‘Denali’ is Officially Named as SQL Server 2012
Next Post
SQL SERVER – TRACEWRITE – Wait Type – Wait Related to Buffer and Resolution

Related Posts

82 Comments. Leave new

  • In my installation (Denali CTP3) it has been executed fine,
    and even the following one yield no errors:

    SELECT MAX(OBJECT_ID) FROM sys.objects As ______________________________________
    SELECT MIN(OBJECT_ID) FROM sys.objects As ______________________________________
    SELECT COUNT(OBJECT_ID) FROM sys.objects

    Reply
  • Hi
    here ______________________________________ is treated as table alias so that you can use a query like this also.

    select * FROM sys.objects
    ______________________________________
    where ______________________________________.name like ‘%test%’

    Reply
  • No clue …

    Reply
  • Surprised to see the question, which is not at all question but just a discovery of free mind :)

    One side you saying “Now when you execute individual lines only it will give you error as” and the same time you selecting 2 line, one with query and one with ‘——————‘. Why you selecting it? If I replace the line with some other character, it will still report the same.

    Reply
  • Underscore is a Result Separator.
    if you select Query —-> Results —–> Result to Text
    menu you will get this ans as below.

    ———–
    1419152101

    (1 row(s) affected)

    ———–
    4

    (1 row(s) affected)

    ———–
    77

    (1 row(s) affected)

    so if there is any query underscore is ignored.
    But without any query you will get the error.

    Reply
    • Nirav in your result (result in Text) underscore (——) denotes the column name space. place column alias name in any query and then you find the exact result. i.e.
      SELECT MAX(OBJECT_ID) As [Max_ID] FROM sys.objects

      Reply
  • Hi Dave,

    If I execute following queries, they will return same results.
    SELECT MAX(______________________________________.OBJECT_ID) FROM sys.objects
    ______________________________________
    SELECT MIN(______________________________________.OBJECT_ID) FROM sys.objects
    ______________________________________
    SELECT COUNT(___.OBJECT_ID) FROM sys.objects
    ___

    So, they demonstrate string ‘______________________________________’ is table alias.

    Regards,
    Kaiyuan Liang

    Reply
  • I have SQL SERVER 2008R2 Developer Edition.
    when I run those statement , then i did not get any error.

    Its execute perfect.

    And if I run all this together, then Its run fine as well.

    Reply
  • Any entry, which is not an SQL Command is treated as an SP name and SQL server trying to execute the same. If you enter “ssssssss” and try to execute, it will throw the same error Could not find stored procedure ‘ssssssss’.

    As “______________________________________” is not a valid SQL Command, SQL server try to identify a stored procedure with the same name. That is why it throws the error message.

    When we execute the entire commands together, then SQL Server will consider “______________________________________” as an alias for the previous SQL Command.

    If you add one more line like
    SELECT MAX(OBJECT_ID) FROM sys.objects
    ______________________________________
    ______________________________________
    SELECT MIN(OBJECT_ID) FROM sys.objects

    Then it fails with incorrect syntax in the second line.

    Reply
  • if you write underscore in your select statment you will get error.
    try this & get the idea.
    SELECT MAX(OBJECT_ID) FROM ____ sys.objects.

    Reply
  • Varinder Sandhu
    October 19, 2011 12:31 pm

    I have sql server 2008r2 and I am able to run this even individually as well as all together.

    Reply
  • Varinder Sandhu
    October 19, 2011 12:35 pm

    It seems error occurred when sql try to execute

    ______________________________________

    then

    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure ‘______________________________________’.

    Reply
  • Hi Pinal,
    Nice Question.

    When we execute this line ______________________________________, we get the error,
    because when you write any Word or simple any symbol like “______” in SQL Prompt then sql considers it as stored procedure.
    That means for ex. if you write “employee” word in prompt then it searches for employee stored procedure in current database.Because of this when you write ______________________________________ line then server considers it as stored procedure name & we gets the error as
    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure ‘______________________________________’.

    Reply
  • Bhoomi Banerjee
    October 19, 2011 12:53 pm

    If we write SQL statement first followed by ______________________________________ then it takes this as a table alias name and executes this without any error.

    But if we write the ______________________________________ followed by sql statement then it will give an error. It gives an error because it consider it as a procedure and tries to execute the same. Generally to execute a procedure we simply write the procedure name and press F5. Thus in the above case it tries to execute two separate statements and therefore it gives an error.

    Reply
  • It’s a simple table alias. The following statement will execute fine as well…

    SELECT MAX(OBJECT_ID) FROM sys.objects
    hahaha_this_is_a_table_alias_hahaha

    Reply
  • Exec is automatically gets call when you write
    ______________________________________ this statement or even single word also like abc or xyz

    Reply
  • Geetanjali Agarwal
    October 19, 2011 12:55 pm

    Hi,

    When I am running below statement in SQL Server 2008 R2 it’s working fine.

    SELECT MAX(OBJECT_ID) FROM sys.objects
    ______________________________________
    SELECT MIN(OBJECT_ID) FROM sys.objects
    ______________________________________
    SELECT COUNT(OBJECT_ID) FROM sys.objects
    ______________________________________

    But when I run

    SELECT MAX(OBJECT_ID) FROM sys.objects
    ______________________________________
    SELECT MIN(OBJECT_ID) FROM sys.objects
    ______________________________________
    SELECT COUNT(OBJECT_ID) FROM sys.objects
    ______________________________________
    ______________________________________

    It will throw an error
    Incorrect syntax near ‘______________________________________’.

    because you can create only one alias for a table but here in this case we are trying to give more than one alias.

    Check the below statement :

    This statement will work fine.
    SELECT COUNT(OBJECT_ID) FROM sys.objects TableAlias

    This statement will give the same error.
    SELECT COUNT(OBJECT_ID) FROM sys.objects TableAlias TableAlias

    Thanks,
    Geetanjali Agarwal

    Reply
  • Nakul Vachhrajani
    October 19, 2011 12:58 pm

    This one is quite simple, actually. The series of underscores are the table alias. So, if I run the following, it works:

    SELECT MAX(______________________________________.OBJECT_ID) FROM sys.objects
    ______________________________________

    Reply
  • Selvam Krishnan
    October 19, 2011 1:37 pm

    Table alias name

    Reply
  • Hi all,

    When we execute all together, the “______________________________________” is used as table alias.

    Demostration: following run without error
    SELECT MAX(hi.OBJECT_ID) FROM sys.objects
    hi
    SELECT MIN(______________________________________.OBJECT_ID) FROM sys.objects
    ______________________________________

    Really interesting :-) Thanks for make us thinking.
    Guiomar

    Reply
  • SELECT MAX(OBJECT_ID) FROM sys.objects—2137058649
    SELECT MIN(OBJECT_ID) FROM sys.objects—3
    SELECT COUNT(OBJECT_ID) FROM sys.objects—166

    Reply

Leave a Reply