I consider myself fairly good at SQL Server but couldn't answer a question. Here are the details.
The other day, I received a call from a former colleague regarding an interview question which he was asked by an interviewer recently and he couldn't answer. The question was about Microsoft SQL Server query.
The earlier company where we used to work together was a small IT consulting firm. There, I was a single point of contact (SPOC) for all SQL Server related questions and issues in relation to the performance optimization.
Obviously, my friend thought that I was probably the best person to contact to know the answer to the question.
I have been working on the Microsoft SQL Server since version 6.5 way back in 1997 and consider myself to be fairly expert on the subject as far as SQL Server development is concerned. I never had any formal education in computer science in general or SQL Server in particular. Having worked on dBase III+ earlier in DOS days, on my own, I just tried to glean the knowledge from experience and help from friends. As I started gaining more and more knowledge about SQL Server, I also became aware of the standards, that is, ANSI-92, etc. The more I knew about it, more I became fond of it and before I knew it, I even became a fanatic about it too!
When I was approached with a query written with *= for joins or joins applied using the WHERE clause instead of the JOIN clause, I used to warn developers not to use that syntax ever again for the rest of their lives and adopt ANSI standards as early as possible.
Coming back to the question that was asked! The question is as follows.
There is a table with 4 rows. Write a single SELECT statement which would return 16 rows. Use of temp table, CTE, Union all, and JOIN is not allowed.
There might be experts out there who would probably answer this question in seconds but somehow, I just couldn't. And I wasn't happy about it. After hours of thinking about it, it dawned upon me that if I cannot think of it, it must be something of a non-standard approach. And I was right. After this realization, I came up with the answer within seconds and immediately called my friend and told him.
The point I am trying to make is different here. We, the fanatics about the standard and proper way of doing things and ardent followers of best practices, tell developers to stay away from such practices. And then, in the technical interview, questions like this are asked. Frankly, I would probably have been left speechless myself and could have gotten rejected for that reason.
The truth of the matter is that I never ever once in my life wrote a non-standard query until this one.