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

  • I am getting output for individual query

    Reply
  • T-SQL Optical illusion……. :)
    ______________________________________ is considered table alias……

    Good one……

    Reply
  • when i executed all the five statement together it works ok
    and in the result panel it seprates ,the answer
    and individuly it goes to find table or stored procedure with same name
    so it gives u an error……
    naren

    Reply
  • I believe that the ‘___________ ….’ or any other (as long as it’s not a reserved keyword) is treated as a table alias and hence the behaviour.

    Reply
  • this is treated as alias name that is why erro not coming.

    Reply
  • “______________________________________” is being used as table alias

    Reply
  • when you executed all the five statement together ___________ is treated as a table alias. you can check it by using as like
    from sys.objects as ___________

    Mitesh Modi

    Reply
  • Hello Friends,

    When it is executed as whole it creates the alias so it will not give any error.

    If line is executed independently it looks into the system database as well as in the current selected database for the SP name like that because SP is the the only object in the sqlserver which can be executed independently so, sql considers it as an SP and it will not find the SP for the same name and gives an error.
    If you create the SP with the name as ______________________________________ it will return correct result.

    Thanks,
    Ramesh

    Reply
  • you can also use this query also like that (alias tablename.columnname)

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

    Reply
  • It takes the ____ as the Alias Name

    Reply
  • SQL server is unable to recognize the line ______________________________________, if this line is before the SQL Statement SQL Server is treating this line as a Stored Procedure and searching for this object but its does not exists, that is the reason it is throwing the error, it is also treating the line and select query are different parts. when we are executing all the statements at a time, the line is treating as an alias of the Table. That is the reason it is not throwing any error.

    Reply
  • Rene Alberto Castro Velasquez
    October 19, 2011 8:04 pm

    Hi Pinal!

    Well, I think ___ is just an alias, and to be sure I tested it with the following query:

    select Firstname, Lastname, LocationID __ from Employee

    (the table Employee and fields I’m using come from the book “Beginning SQL Joes 2 Pros”)

    That is the same as:

    select Firstname, Lastname, LocationID AS __ from Employee

    So, that line is acting as an alias for the table sys.objects

    Rene Alberto Castro Velasquez
    El Salvador

    Reply
  • Erik Linthorst
    October 19, 2011 8:19 pm

    The line is the alias for the table name.

    Reply
  • Rewards Generator
    October 19, 2011 8:19 pm

    Are the underscores the name given to the alias?

    If not, then I’m not sure, thought i’d give it a shot though! :P

    Reply
  • 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
    ______________________________________

    Output are :
    (No column name)
    1195151303
    (No column name)
    3
    (No column name)
    74

    Regards
    Dharmendra

    Reply
  • When Executing the

    SELECT MAX(OBJECT_ID) FROM sys.objects
    ______________________________________

    The compiler treats it as a single statement and hence takes line as a table alias

    Reply
  • Rodney Phillis
    October 20, 2011 4:12 am

    The underscore “_” is a valid character in the name of a Store Procedure so the entire underscore line is considered to be the name of a Stored Proc. Open a new query window and just type _ and run it. You get the error.

    Simple really

    Reply
  • I have no clear idea why this is happening but i am 995 sure that –To specify that a command continues on the next line, we use a hyphen (continuation character) after all the text on the line to be continued.

    So in this case when we are executing all together, its working fine because its treating all the lines it continuation but executing single line giving error because its not getting the next executable statement — is correct posted by sharing360

    Reply
  • Hi Pinal,

    Regarding the question below is the answer

    Anything in the batch first line in query window it will treat as a stored procedure

    1.)
    For example:

    in query window just run ABCDEFGH it returns the same

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

    2.)
    And also we can create a stored procedure with line(nothing but underscore)

    create procedure _____
    as
    begin
    select getdate()
    end

    3.) It will execute in batch because first line is not there and 2nd it will omit

    4.) We can execute stored procedure with just name and f5
    it will exeute and no key words required.

    Reply
  • after little bit R & D, it table alias only……

    Reply

Leave a Reply