Subscribe - It's FREE!!

Stay Connected Here

Stay Updated With Us Here


Advanced SQL Interview Questions And Answers for experienced developers

Share with WhatsApp

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.

  Table Session Scope
IDENT_CURRENT Specific Table Any Session All Scopes
@@IDENTITY Any Table Current Session All Scopes
SCOPE_IDENTITY() Any Table Current Session Current Scope

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.

Number1 Number2
1 5
2 4
3 1
4 1
1 6
6 8
2 5
5 6

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  
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.

If you enjoyed this post take 5 seconds to share it! Be Socialable. :-)

Share with WhatsApp

Posts To Read Next

Advanced SQL interview questions for experienced .NET Developers – 2

SQL Server advanced interview questions for experienced .NET developers such as clustered index, Merge, on delete cascade etc. is the topic of this post.

How to Prepare for the interviews - Simple but important tips - Part 3 - Prepare for HR Round

In this third post of my interview tips series, I am going to share some tips about how to prepare for HR Round. Also about things to do and things to not to do during the same.

How to Prepare for the interviews - Simple but important tips - Part 1 - Resume Writing

In this post I will share some interview tips from my own personal experience which will help you to prepare for the interview. In part one I will cover how to design a resume which will put your first impression smartly.

How to Prepare for the interviews - Simple but important tips - Part 2 - Things that matter during interview

In this second post of my interview tips series, I am going to share some tips about how to behave during the interview. Also about how to prepare for the interview and maintain self confidence during the interview.

C# Interview questions and answers for freshers and experienced - Part Three

What is InvariantCulture,Difference between string & System.String,Kinds of parameters,int vs uint are some questions which I am sharing in this post.

Your opinion is valuable for us! Comments, suggetions are welcome.

Submit your Email Id to stay updated with us and get notified with our new posts. It's FREE!
We know this popup is disturbing you!
But We would greatly appreciate if you share us with your friends below!

It will not take more than 2 seconds but will motivate us greatly to write more,share more!