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 (https://blog.sqlauthority.com)
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.
Think this one wasn’t as hard as you thought.
It is indeed that the underscores are treated as table alias.
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
because “_” is valid character in naming convention of store procedures.
________ is acting as an alias for table name in first two queries. I also verified it form execution plan.
_________ is acting as table alias
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
Yes It is treated as alias name..
try this query , it works and proves it
SELECT ______________________________________.object_id FROM sys.objects
______________________________________
Nerds!
yes bros it considers _________________ as table alias name…..
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.
That horizontal line is alias for sys.objects
I have executed the statements both the way and there is no error.
Is it version specific ?
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.
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..
It is considered as alias and it is not any operator or reserved key word. That is the reason for not throwing any error.
“__________________________” 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.