- Posted On 15 July 2014
- In Interview Questions
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.
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.