Advanced SQL interview questions for experienced .NET Developers – 2

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.

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.

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.