SQL-The Confession - How did I learn about execution plans?

This post is the first in a series about physical join operators,

please be sure to check out:

Part 2 - SQL- Nested Loops Operator

I thought to jump directly to talk about the physical operators, but I do believe this series deserve a good foundation.

The confession.

Some time ago, I interviewed at a well-known tech company for a position that seemed to be my dream job, the responsibilities for this position were a perfect match (60 % SQL Developer and 40% .NET Developer). During the interview, they asked a bunch of technical questions about .NET, web services and other stuff but nothing related to SQL Server.

After several hours of interviewing with several different people, the Head of Development came into the conference room, he told me that DBA team is working on some urgent stuff and they are not able to finalize the interview that day, but he would like to schedule a phone call to finalize the interview, I agreed and left the place with a piece of paper that somebody handed to me, it was the schedule for final interview.

The three questions.

Next day, DBA Team called me, they apologized for previous day, one of the DBAs told me that in compensation they only have three questions for me, I smiled and agreed (I was thinking “easy peasy lemon squeezy”)… I was so wrong and I will never forget the two questions they asked me, yes two since I did not make it to the third one.

Please see below and try to do not laugh too loud =P.

DBA: Are scans bad? Question #1

Silly Victor: It depends! Yes I used that one and worked like charm, but I did not know the answer.

DBA: Yes, I depends.

DBA: What are the three physical join operators? Question #2

Silly Victor: Three?

DBA: Yes, three.

Silly Victor: Inner Join…

DBA: Physical join operators!

Silly Victor: Left Join…

DBA: Thank you Victor, that’s all the questions that I have for you. Do you have any questions for me?

Silly Victor: No, thank you.

Postmortem.

How did I feel after all?

A bad interview can leave you feeling frustrated and upset, I truly was very upset and angry with myself, but this was nothing compared to how stupid I felt that day.

Why do I failed?

Because I was not humble, I thought that since I was able to write complex queries, I had mastered SQL Server.

Can I fix it?

No, we cannot change the past, but we can prevent it in the near future.

What was the third question?

I have no idea and I am not sure if I am ready to answer that one, but I am doing my best every day.

Conclusions.

• Be humble and help others as much as you can.

• If you don’t know the answer, please do not try to make up stuff, is better to said something like “I don’t know, but I am willing to learn”

• Never give up, no matter how you feel, please never give up.

• Keep learning, you never stop learning.

• SQL Server is kind of Science.

I promise that in next series I am going to talk about Nested Loop Join.

Enjoy!!

SQL-How to capture the newly generated identity values after a multi-row insert?

I recently received a very interesting question, somebody asked if there is a way to capture the newly generated identity values after a multi-row insert into a table?

Answer: Using the OUTPUT clause.

SQL supports an OUTPUT clause, which you can use to return information affected by an INSERT, UPDATE, DELETE or MERGE statement.

Any reference to columns in the table being modified must be qualified with the keyword INSERTED or DELETED, the keyword is a just a prefix that specifies which values to output.

INSERTED reflect the value after the UPDATE, INSERT, or MERGE.

DELETED reflect the value before the UPDATE, DELETE, or MERGE.

The Lab.

Let’s prepare the environment, to do so run the following script to create a table:

OUTPUT Clause in INSERT Statements

In this example, we are going to insert 20 records into the table that we have created and output the inserted data to the user.

The OUTPUT clause is placed between the INSERT INTO and VALUES, please noted that I am using the keyword INSERTED as prefix.

Results:

OUTPUT Clause in DELETE Statements

In this example, we are going to delete all the records where ‘myValue_2’ is greater than 8 and output the deleted data to the user.

In a DELETE statement, you add the OUTPUT clause between the DELETE and WHERE clause, please noted that I am using the keyword DELETED as prefix.

Results:

OUTPUT Clause in UPDATE Statements

In this example, we are going to update ‘myValue_1’ where ‘myValue_1’ is equal to ‘myValue_2’ and output the updated data to the user.

In a UPDATE statement, you add the OUTPUT clause between the SET and WHERE clause, please noted that I am using both keywords, this is very important since DELETE is use to output the previous value and INSERTED is use to output current value.

Results:

Bonus: OUTPUT INTO temporal table.

In this example, we are going to output the inserted data into a temporal table.(#MyAudit).

Results:

Enjoy!!

SQL-How to generate a sequence of dates between two given dates?

I recently received a very interesting question, one of my connections in LinkedIn asked if I know how to generate a sequence of dates between two given dates?

The first thing that came to my mind was to create a calendar table since they are very useful, he told me that that sounds good, but he really wants to know how to write the query.

This is what he was looking for:

Now let us explore three different methods which will generate the sequence.

Method 1: Using WHILE loop

There is usually a set based alternative that will perform much better.

Method 2: Using Recursive CTE

Method 3: Using Cross Join and Top

The above methods produce the following result:

Let me know if you know any other methods in the comments section :).

Enjoy!!