Technical Interview Round in Deloitte

1. Tell me about yourself.
2. So how much you rate yourself in your current technologies i.e. Asp.net,Sql server, SSRS and SSIS out of five.

SQL SERVER
3. How can you optimize a complex Stored procedure?
ANS:
1. The first criteria for any efficient stored procedure is its Total Time. The total time can be defined as the time taken for the query to return all records. There is another concept known as, Response time, it is the time taken for a query to return the first record. Now it depends on our requirement, if we want the whole set of records or few records from our database depending on the parameters.
2. In a Stored Procedure, we have to secondly analyze the poor performing SQL Server Queries.
3. Managing Indexes: Applying Indexes to the appropiate table columns can increase the query performance drastically. After adding an index, rerun the query to see if performance is improved. If it is not, remove the index. When we create a primary key for a table, a clustered index is automatically created and all the data pages and all the rows for the data pages are physically sorted according to the primary key values. If the table structure is complicated or the query demands very particular rows in return then we can further add non-clustered index there by taking the combination of particular columns as unique.
But then we have to consider the query type also. For search operations i.e. SELECT queries and parametrized queries(queries with where clauses)run faster with Indexes. But if we have more DML operations like UPDATE/DELETE then index decrease the performance as we have physically sort the rows after each modification.
4. Joins: Join is a very important factor in our stored procedure performance. Sometimes we can replace our IN,EXISTS OR GROUP BY clauses and subqueries with our JOINS to achieve better results. On the other-hand, the same JOIN takes much longer time in certain situations. The OUTER JOIN takes more time than an INNER JOIN so we can restrict them as much as possible.
5. We should practice to write queries to return distinct and definite results. To achieve that we can use more parametrized queries. We have to again check if our stored procedure is calling some other SP or functions, and then their response time also.

4. Can a SP call itself?
Ans: Yes a SP can call itself. Such a SP is called as recursive SP.

5. Can you call one SP inside another SP ?How to do that? How many number of times we can do that?
Yes we can call SP inside another. We can use EXEC(Execute) command to execute one SP inside another.We can nest stored procedures and managed code references up to 32 levels.

6. How can we replace cursor in a query?
We can replace cursor by using table variable or temp table. We can create the same cursor effect of row by row iteration by using a While loop and insert data into the table variable/temp table.

7.Write the syntax of 'FOR Loop' in Sql Server?(Gives me a paper)
There is no For Loop in Sql server.

8. What is a trigger?
A trigger is a special kind of a store procedure that executes in response to certain actions like INSERT,DELETE or UPDATE data in a table. It is a database object which is bound to a table and is executed automatically. we can't explicitly invoke triggers.

9. How many types of Trigger are there?
Basically, triggers are of two main types:
1.After Triggers (For Triggers)
2.Instead Of Triggers

ASP.NET
Mostly he asked about the project description and then few questions he asked for which he handed me papers to write down the codes.
1. Can we use a dropdown to the editable gridview?
2. Write the code how to bind the dropdownlist to the gridview?
3. In which event of the gridview we can write the code to get the selected value of the dropdownlist?
4. Write down the code to update the database with the selected value of the dropdownlist from the gridview?