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)

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

  • When we execute single line,
    ______________________________________

    It gives us error,

    Could not find stored procedure ‘______________________________________’.

    Because sql server find it as database object which does not exists in
    database. So it gives error.

    But when we execute multiple lines it runs properly because “_” is valid
    character in naming convention of store procedures.

    Reply
  • Think this one wasn’t as hard as you thought.
    It is indeed that the underscores are treated as table alias.

    Reply
  • Venkataraman R (@venkataramanr)
    October 20, 2011 3:26 pm

    Here series of “_” is considered as alias for table name. A valid sql identifier can start with _ and can be followed by a series of _. The link talks about valid identifiers in sql. https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-2017. The above statement is considered as three separate sql statements and hence no error is thrown. But, when we try to just execute “_________”, there are no stored procedures by that name and so the execution fails. If there were any stored procedures present by that name, then it would have got succeeded.

    1. SELECT MAX(OBJECT_ID) FROM sys.objects AS
    ______________________________________
    2. SELECT MIN(OBJECT_ID) FROM sys.objects AS
    ______________________________________
    3. SELECT COUNT(OBJECT_ID) FROM sys.objects

    Reply
  • because “_” is valid character in naming convention of store procedures.

    Reply
  • Muhammad Abbas
    October 20, 2011 9:23 pm

    ________ is acting as an alias for table name in first two queries. I also verified it form execution plan.

    Reply
  • _________ is acting as table alias

    Reply
  • Hi Pinal,

    While executing this code in one go, SQL treats _____ as table aliases and do not return any error. But if you put ; at the end of each line and execute again it will give error. Yes, it is treated as table aliases.

    Thanks

    Shekhar Teke
    Sr DBA
    Talkingtech Limited
    NZ

    Reply
  • Yes It is treated as alias name..
    try this query , it works and proves it

    SELECT ______________________________________.object_id FROM sys.objects
    ______________________________________

    Reply
  • Nerds!

    Reply
  • yes bros it considers _________________ as table alias name…..

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

    yes _______ is treated as alias name for table. if we use any alias name after the sys.object then line will show error.

    i like sqlAuthority.

    Reply
  • Abhishek Sahoo
    April 4, 2012 11:33 pm

    That horizontal line is alias for sys.objects

    Reply
  • I have executed the statements both the way and there is no error.
    Is it version specific ?

    Reply
  • You get the error only if you execute 2nd and 3rd lines which are just ___________________.

    There is no problem or bug in SQL Server or any MS product. This is just a (silly) trick no puzzle!

    For a second i thought there is a hidden bug in SQL Server.

    In 2008 it works fine if you select all the lines or 1st, 3rd or 5th individually and hit F5.

    Reply
  • Abul Hasanat Sekh
    April 6, 2016 10:29 am

    If I execute SELECT MAX(OBJECT_ID) FROM sys.objects ______________________________________
    no error will occur as ______________________________________ treated as alias.
    But ______________________________________ SELECT COUNT(OBJECT_ID) FROM sys.objects will give error.

    Another example:
    SELECT MAX(sys.objects.OBJECT_ID) FROM sys.objects ______________________________________
    join sys.objects on ______________________________________.object_id=sys.objects.object_id

    No error occurs..

    Reply
  • It is considered as alias and it is not any operator or reserved key word. That is the reason for not throwing any error.

    Reply
  • Roopesh Kumar
    April 6, 2016 4:50 pm

    “__________________________” is treated as table alias so not giving an error. If you put statement terminator after sys.objects, then it will start giving error.
    For the details, you can check the execution plan, where it will clearly show you the query executed. It includes the “________________” as table alias.

    Reply

Leave a Reply

Menu