How to Show Results of sp_spaceused in a Single Result? – Interview Question of the Week #135

Question: How to Show Results of sp_spaceused in a Single Result?

Answer: When I hear this question from my co-interviewer, I objected. I think it is not a good interview question. I do not expect any of the candidates to remember any of the syntax. Remembering basic syntax should be a good idea, but it should not be the sole requirement of the hiring a good candidate. I believe I am a good DBA but trust me personally I do not remember all the syntax.

However, my co-interviewer was a Sr. DBA and I was an external member. Even though, I objected to the question, I know I could not influence the Sr. DBA. He continued with the question.

Let us see the problem in detail. First run sp_spacedused command in SQL Server and you will notice there are two result sets.

For many years developers and DBA wonder how to move combines both of the result set in a single result. Many different tricks were used across the year to bring the results in a single result set so the DBA can use it to insert into a single table.

EXEC sp_spaceused

Well, this was the question from Sr. DBA. He wanted the candidate to answer how will he bring above two resulted in a single row.

While, I was sitting there unhappy as I do not approve of this question, the candidate asked a very interesting question – “What is the version of SQL Server?”

Sr. DBA answered – “Let us assume that it is of the latest version – say SQL Server 2016 or SQL Server 2017.”

Instantly I noticed a spark in the candidate’s eye. He said enthusiastically – “I will use parameter @oneresultset = 1”.

When I hear this answered, I almost jumped from the joy. Very apt answer to arrogant Sr. DBA who was sitting next to me.

In SQL Server 2016, there is a new parameter @OneResultSet, when it is set to 1, it gives us the entire result set in a single row. Here is the example of it.

EXEC sp_spaceused @oneresultset = 1

Sometimes, I love my job very much when I encounter such a fun situation.

Note: This works in only SQL Server 2016 and later version of SQL Server.

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