Advanced SQL Interview Questions And Answers for experienced developers

Hello friend, after sharing interview questions on ASP.NET and C# here are some interview questions with their answers on SQL Server. 

What is Scope Identity in SQL Server?

If you are working with SQL Server more no. of years then it is expected that you know IDENTITY. To answer this question SCOPE_IDENTITY is the inbuilt function in SQL Server which returns the last identity value inserted into an identity column in the same scope. A scope is a module which can be a stored procedure, trigger, function, or batch i.e. when two SQL statements are in same stored procedure, function, or batch then they are in same module.

If you answered this question then most of times interviewer stretches it like what is IDENT_CURRENT or @@IDENTITY and what is the difference between each of them as compare to SCOPE_IDENTITY(). Therefore I am mentioning all these together below.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

To summarize I will put above information in table like below.

What is FMT ONLY in SQL Server?

Before answering this I want to share that when this question was asked to me I was totally unaware about this as I haven't used it in my career and now after 6 years in this field still I haven't used this anyways coming to the answer, SET FMTONLY is sql statement by which you can return only meta to the client i.e. when you SET FMTONLY ON then no rows are processed or sent to the client as a result of the request.
When sometimes interviwer asks by how many ways you can get only schema of the table in sql then you can add this to the answer along with other options.

Suppose Table A has 5 rows and table B has 7 rows, with same schema. How many rows does "Select * from A, B" statement will return?

Well this question can be the tricky question as it will genuinely test your understanding of SQL queries. Coming back to the answer, above statement will return 35 rows because when you write query like above it internally applies cross join which returns the cartensian product of the tables involved in join.

Write a query to display all anyway related records to "1" in following table.

Here expected output is "3,4,5,6" as these are the numbers which are anyway related to the "1".

So Answer is, to display the anyway related records you need apply union on two alias of same table as you need to consider both columns therefore query will be like below.

SELECT  tb1.col2 as result  FROM ids tb1 where tb1.col1 = 1  
 UNION
SELECT  tb2.col1 as result FROM  ids tb2 where tb2.col2 = 1

Suppose you have 3 databases on different networked computers and you want to insert data on all databases sequntially but if any of the insertion fails then you must rollback all inserts of all databases. How can you achieve this in SQL?

This question is very interesting and that time I was aware how to handle such kind of situation. Coming back to the answers, this can be achieved by implemented Ditribution Transaction. Yes, you can implement transaction in above scenario.

A distributed transaction is a transaction that updates data on two or more networked computer systems. Distributed transactions extend the benefits of transactions to applications that must update distributed data. Implementing robust distributed applications is difficult because these applications are subject to multiple failures, including failure of the client, the server, and the network connection between the client and server. In the absence of distributed transactions, the application program itself must detect and recover from these failures.

Hope you have liked this post. Do share your opinion in comment section below. Thanks for reading.