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.

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 (http://blog.SQLAuthority.com)

About these ads

80 thoughts on “SQL SERVER – A Simple Quiz – T-SQL Brain Trick

    • It was as posted. When I replaced with other characters, then it didn’t work. So, is it another comment character similar to ‘-‘

    • I have executed the statements both the way and there is no error.
      But try to executed the statements with line it get give error.
      Msg 2812, Level 16, State 62, Line 1
      Could not find stored procedure ‘______________________________________’.
      Is it version specific ?

  1. 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.

  2. Hi all,

    I try to run that code in both scenario. After that, i add a new line at the top of that existing code

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

    It displays the Messages tab as

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

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    But in Results tab displays correct answer.

    I think even if run the sproc no need to add prefix Exec or Execute keyword.
    Like that we try to run a single ___________________. (or any text)

    It says :

    Could not find stored procedure ‘______________________________________’.

    so, It does not consider any lines or text below the Select statement. If the line or text above the Select statement returns error message and result also.

  3. hi pinal…

    i executed the above statement individually and it is working fine…!
    why should there be any error ?

    Thanks and Regards
    Akiii

  4. When we execute this line ______________________________________, we get the error, because it doesnot belong to anyone, but if we execute all of them “______________________________________” behave like alias.

  5. This ______________________________________ line treated as sys.object alias name.

    We cannot execute any column name or dabase object name without any command like SELECT,INSERT,UPDATE,DROP

  6. 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 column. That is the reason it is not throwing any error.

  7. if we assign some alias deliberately at the last. Following statements wont work.

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

    But in the following way, its considering the line as alias.

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

    Reason behind giving the following message:

    Could not find stored procedure ‘___________’.

    ‘_’ is part of T-SQL Identifiers.

    Here you can find its explanation:
    http://books.google.com/books?id=qf7Wuhnq3ZQC&pg=PA52&lpg=PA52#v=onepage&q&f=false

    Regards,
    F. Ahmed

  8. Hi pinal,

    This can can happen in SQL Server, and can be due to a number of reasons. For example:

    1. The user you are connecting as does not have SELECT, UPDATE, INSERT, DELETE, EXEC permissions on the object;

    2.You are not logging in / connecting as the user you expect;

    3.You are referencing the object without an owner name prefix (or with the wrong owner name);

    4.You are connected to the wrong database;

    5.You are, in fact, spelling the object’s name incorrectly.

  9. 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

  10. 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%’

  11. 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.

  12. 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.

    • 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

  13. 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

  14. 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.

  15. 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.

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

  17. It seems error occurred when sql try to execute

    ______________________________________

    then

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

  18. 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 ‘______________________________________’.

  19. 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.

  20. 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

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

  22. 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

  23. 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
    ______________________________________

  24. 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

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

  26. 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

  27. 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.

  28. 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

  29. 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

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

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

  31. 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.

  32. 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

  33. 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

  34. When execute all five statements together ‘______________________________________’ treated as table alias

    because table alias can be assigned either with or without the AS keyword.

    you can also write as simple as ……

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

    reference by http://msdn.microsoft.com/en-us/library/ms187455(v=sql.90).aspx
    (Using Table Aliases)

    Thanks,

    Alpesh Gorasia
    (India)

  35. 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

  36. 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

  37. 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

  38. 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.

  39. 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.

  40. 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. http://msdn.microsoft.com/en-us/library/ms175874.aspx. 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

  41. 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

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

    SELECT ______________________________________.object_id FROM sys.objects
    ______________________________________

  43. 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.

  44. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

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