SQL SERVER – A Simple Quiz – T-SQL Brain Trick | SQL Server Journey with SQL Authority

« SQL SERVER – Next Version of SQL Server ‘Denali’ is Officially Named as SQL Server 2012

SQL SERVER – TRACEWRITE – Wait Type – Wait Related to Buffer and Resolution »

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

October 19, 2011 by pinaldave

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

Posted in Pinal Dave, PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 77 Comments

77 Responses

on October 19, 2011 at 7:16 am | Reply Arthur Uspensky

you are aliasing sys.objects table with ______________________________________

this line (or what’s in it) is irrelevant so long as it is not a reserved keyword.
- on October 20, 2011 at 12:50 am | Reply Krish
  
  It was as posted. When I replaced with other characters, then it didn’t work. So, is it another comment character similar to ‘-’
on October 19, 2011 at 8:23 am | Reply Roji P Thomas

The ___________ is treated as a table alias.

-rojipt
on October 19, 2011 at 8:48 am | Reply sajeev

Sir , I am able to run this even individually as well as all together in sql server 2008 R2.

Regards,

sanjeev Kumar
on October 19, 2011 at 9:03 am | Reply sharing360

Hi Pinal,

I have executed the statements both the way and there is no error.
Is it version specific ?
- on October 19, 2011 at 9:06 am | Reply sharing360
  
  If i am selecting the “______________________________________” and executing then its giving this error:
  Msg 2812, Level 16, State 62, Line 1
  Could not find stored procedure ‘______________________________________’.
- on October 19, 2011 at 11:12 pm | Reply Abhay Chaudhary
  
  Same here ..on 2k8 it does not give any error..
on October 19, 2011 at 9:05 am | Reply Rahul Pandey

Ohh… Come on Pinal.

This doesn’t look like SQL test but rather brain teaser.
on October 19, 2011 at 9:34 am | Reply sharing360

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.
on October 19, 2011 at 9:39 am | Reply Kalyanasundaram.K

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.
on October 19, 2011 at 9:42 am | Reply Harshal Mistry

Good one! Table aliasing with ___
on October 19, 2011 at 9:51 am | Reply Naief M Madathadi

no errors Sir
on October 19, 2011 at 10:00 am | Reply Akiii

hi pinal…

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

Thanks and Regards
Akiii
on October 19, 2011 at 10:01 am | Reply vivekjogu

Hi pinal,
i have executed in both ways.
No errors.
and i am using ssms express edition.
on October 19, 2011 at 10:06 am | Reply Csharper

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.
on October 19, 2011 at 10:21 am | Reply Sundar

Sir, I am able to run both individually and combined…Thanks for your info..I expect more questions from you like this…
on October 19, 2011 at 10:31 am | Reply Ratan Sharma

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
on October 19, 2011 at 10:39 am | Reply Dinesh

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.
on October 19, 2011 at 10:46 am | Reply Sudhesh Sidhu

i executed the query both way in SQL 2008 R2 working fine
on October 19, 2011 at 10:53 am | Reply farhan ahmed

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
on October 19, 2011 at 10:56 am | Reply sreeram

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.
on October 19, 2011 at 10:59 am | Reply DERRIC LYNS

When executing that long line without SQL STATEMENTS, it appears to the SQL SERVER as a stored procedure, which however is nowhere to be found.

So it returns the error.
on October 19, 2011 at 10:59 am | Reply Megha Goyal

SQL Server considers ‘______________________________________’ as unicode.

SQL server does not take inputs as unicode unless specified explicitly with -u.
on October 19, 2011 at 11:20 am | Reply Geri Reshef

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
on October 19, 2011 at 11:49 am | Reply Mahmad Khoja

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%’
on October 19, 2011 at 12:00 pm | Reply Mamta

No clue …
on October 19, 2011 at 12:03 pm | Reply Ashish

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.
on October 19, 2011 at 12:03 pm | Reply Nirav Gajjar

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.
- on October 19, 2011 at 9:59 pm | Reply Ubaid Shahid
  
  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
on October 19, 2011 at 12:06 pm | Reply Liang Kaiyuan

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
on October 19, 2011 at 12:08 pm | Reply sid

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.
on October 19, 2011 at 12:14 pm | Reply ambilykkAmbily

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.
on October 19, 2011 at 12:16 pm | Reply Nirav Gajjar

if you write underscore in your select statment you will get error.
try this & get the idea.
SELECT MAX(OBJECT_ID) FROM ____ sys.objects.
on October 19, 2011 at 12:31 pm | Reply Varinder Sandhu

I have sql server 2008r2 and I am able to run this even individually as well as all together.
on October 19, 2011 at 12:35 pm | Reply Varinder Sandhu

It seems error occurred when sql try to execute

______________________________________

then

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘______________________________________’.
on October 19, 2011 at 12:49 pm | Reply Sunny Jagtap

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 ‘______________________________________’.
on October 19, 2011 at 12:53 pm | Reply Bhoomi Banerjee

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.
on October 19, 2011 at 12:53 pm | Reply David

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
on October 19, 2011 at 12:54 pm | Reply Sunny Jagtap

Exec is automatically gets call when you write
______________________________________ this statement or even single word also like abc or xyz
on October 19, 2011 at 12:55 pm | Reply Geetanjali Agarwal

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
on October 19, 2011 at 12:58 pm | Reply Nakul Vachhrajani

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
______________________________________
on October 19, 2011 at 1:37 pm | Reply Selvam Krishnan

Table alias name
on October 19, 2011 at 1:46 pm | Reply Guiomar

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
on October 19, 2011 at 1:58 pm | Reply tushar Jaykar

SELECT MAX(OBJECT_ID) FROM sys.objects—2137058649
SELECT MIN(OBJECT_ID) FROM sys.objects—3
SELECT COUNT(OBJECT_ID) FROM sys.objects—166
on October 19, 2011 at 2:00 pm | Reply tushar Jaykar

I am getting output for individual query
on October 19, 2011 at 2:02 pm | Reply Vishal

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

Good one……
on October 19, 2011 at 2:55 pm | Reply narenshukla

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
on October 19, 2011 at 3:04 pm | Reply Oneil

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.
on October 19, 2011 at 3:41 pm | Reply Jav Iqb

this is treated as alias name that is why erro not coming.
on October 19, 2011 at 3:46 pm | Reply yuvrajgautam

“______________________________________” is being used as table alias
on October 19, 2011 at 6:08 pm | Reply miteshmca

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
on October 19, 2011 at 6:30 pm | Reply Ramesh Vagh

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
on October 19, 2011 at 6:30 pm | Reply miteshmca

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

SELECT MAX(______________________________________.OBJECT_ID) FROM sys.objects
______________________________________
on October 19, 2011 at 7:33 pm | Reply Doug Osborne

It takes the ____ as the Alias Name
on October 19, 2011 at 7:45 pm | Reply Atul

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.
on October 19, 2011 at 8:04 pm | Reply Rene Alberto Castro Velasquez

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
on October 19, 2011 at 8:19 pm | Reply Erik Linthorst

The line is the alias for the table name.
on October 19, 2011 at 8:19 pm | Reply Rewards Generator

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
on October 19, 2011 at 8:53 pm | Reply Dharmendra

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
on October 19, 2011 at 9:14 pm | Reply Alpesh Gorasia

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)
on October 19, 2011 at 10:13 pm | Reply TJ

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
on October 20, 2011 at 4:12 am | Reply Rodney Phillis

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
on October 20, 2011 at 10:28 am | Reply shailesh

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
on October 20, 2011 at 10:33 am | Reply srinivas d

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.
on October 20, 2011 at 10:38 am | Reply shailesh

after little bit R & D, it table alias only……
on October 20, 2011 at 10:40 am | Reply Rahul Dewada

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.
on October 20, 2011 at 12:36 pm | Reply Paul Berndsen

Think this one wasn’t as hard as you thought.
It is indeed that the underscores are treated as table alias.
on October 20, 2011 at 3:26 pm | Reply Venkataraman R (@venkataramanr)

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
on October 20, 2011 at 6:28 pm | Reply Sunil Gupta

because “_” is valid character in naming convention of store procedures.
on October 20, 2011 at 9:23 pm | Reply Muhammad Abbas

________ is acting as an alias for table name in first two queries. I also verified it form execution plan.
on October 21, 2011 at 5:52 am | Reply bandhu

_________ is acting as table alias
on October 21, 2011 at 6:27 am | Reply Shekhar Teke

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
on October 21, 2011 at 6:50 pm | Reply hunt

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

SELECT ______________________________________.object_id FROM sys.objects
______________________________________
on October 27, 2011 at 2:36 am | Reply john

Nerds!
on November 14, 2011 at 12:08 pm | Reply Piyush

yes bros it considers _________________ as table alias name…..
on December 6, 2011 at 1:28 pm | Reply Desh Raj

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.
on April 4, 2012 at 11:33 pm | Reply Abhishek Sahoo

That horizontal line is alias for sys.objects