Subscribe - It's FREE!!

Stay Connected Here

Stay Updated With Us Here



Google

Advanced SQL interview questions for experienced .NET Developers – 2


Share with WhatsApp


With continuity to my previous post "Advanced SQL Interview Questions And Answers for experienced developers" here in this post I am sharing some more questions with their answers.

Here are the questions.

Can you add clustered index on a table having primary key column? OR Can you add primary key without clustered index?

This question can be very tricky so always listen carefully and then think one more time before you start your answer. About this question, In SQL there can only be one clustered index per table, because the data rows themselves can only be sorted in one order. but when you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint. So yes you can add primary key without clustered index.

I will recommend to go through this MSDN post to understand more about Clustered Index.

There are 2 databases on single server having identical schema and there is one table named "employee". What will be the query which will update "employee" table of target database with newly added or updated records of source database "employee" table?

This scenario can be handled by many ways but simple way is using MERGE Statement. MERGE statement performs insert, update, or delete operations on a target table based on the results of a join with a source table.

Following SQL lines will insert/update records of target "employee" table.

SET IDENTITY_INSERT [destdb].[dbo].[employee]  on

MERGE [destdb].[dbo].[employee] AS  T

USING [sourcedb].[dbo].[employee] AS  S

ON (T.EmpId = S.EmpId)

WHEN NOT MATCHED BY TARGET

THEN  INSERT(EmpId,EmpName,EmpSalary) VALUES(S.EmpId,S.EmpName,S.EmpSalary) WHEN MATCHED

THEN UPDATE SET T.EmpName=S.EmpName,T.EmpSalary = S.EmpSalary;  

SET IDENTITY_INSERT [destdb].[dbo].[employee]  OFF

 

In above code SET IDENTITY code is not requried if EmpId column is not IDENTITY column.

Read this post to know more about dynamic insert update from source database to target database.

 

What is "ON DELETE CASCADE" in SQL?

This question can be asked by several ways so make your base clear which will help you to not get confused even if the question comes in another way. Coming back to the answer, ON DELETE CASCADE is a type of Cascading referential integrity constraint by which once set you can enforce delete of all related records of child table if its main record of parent table gets deleted.

Suppose you have two tables like below.

ON CASCADE DELETE in SQL SERVER

In above table if you had implement ON DELETE CASCADE like below while adding foreign key

ALTER TABLE States

ADD FOREIGN KEY (CountryId)

REFERENCES Country(CountryId)

ON DELETE CASCADE;

Then if you delete aby record of "Country" table, all related records of "States" table will gets deleted automatically.

NOTE: If you delete any record of "States" table then there will not be any effect on "Country" table.

Suppose there is one table named "LuckyDraw" having more than 10000 rows and I want any random five rows as a winner. What will be the query?

To select random rows from table you can use "ORDER BY NEWID()" in SQL server so to satisfy above situation a query will be like below.

select top  5 * from LuckyDraw order by NEWID()

There is one table having 55 columns and I am writing one stored procedure in which I want one temporary table with same schema of existing table to hold some temporary data which I will add one by one. How will you create a temporary table having 55 columns schema with same data type as of existing table without data?

You can create a temporary table of having schema as like of existing table by follwing query.

select * into #tempTable from yourtable where 1 = 2

In above query, condition 1= 2 never gets satisfy therefore it creates a temporary table with no data in it.


Hope you have benefited from above interview questions. If you want to add something positive to above post do share it in comment section below. Thanks.



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

Share with WhatsApp

Posts To Read Next

Advanced SQL Interview Questions And Answers for experienced developers

SQL Server advanced interview questions for experienced developers is what I am sharing in this post.


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 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 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!
Vu 32'' TV Vu 24'' TV
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!

x