SQL SERVER – Strange Error Related to Alias

Just another day I got an email from a client who wanted to hire me urgently for their SQL Server Performance issue on the production server. They immediately paid for Comprehensive Database Performance Health Check and we got online to look at their strange error related to alias.

Error Related to Alias

The issue was indeed very strange and after looking at the issue and with the potential solution, I was even not sure why they had to hire me to solve this issue. The client had one piece of code which worked fine when they ran all the 3 statements together. However, when they were running each of the code separately, they were getting the error. They were not able to understand the behavior of SQL Server in this case and though they are in a deep trouble, which prompted them to hire me.

Let us see the code which the clients were very much worried.

Run the following three statements together and you will see that they all run just perfectly fine and there are no errors.

SELECT 1
'------------------'
SELECT 2

Now run each of the commands of the previous code separately and see that the second line will give you an error.

SELECT 1

'------------------'

SELECT 2

Now when you run above script one line at a time, you will notice that when you run the second line it will say

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘——————‘.

The reason for this one is very simple, when you run all the three scripts together, the second script which would give error normally when you run it by itself actually becomes the alias for the result of the first query. That is why there is absolutely no error however when you run each of the lines individually, the second line is actually not valid syntax and it gives an error.

I really felt awkward to take money from my client when the issue was very trivial. When I offered them to refund the money, they proposed that they would be happy if I can help them with SQL Server Performance Tuning Practical Workshop – Recorded Classes. I eventually offered them a full license for unlimited viewing for them, which they gladly accepted it.

Let me know if you have any question or have such puzzles, I will be happy to publish them on your behalf.

Reference: Pinal Dave (https://blog.sqlauthority.com)