Morgan Stanley Interview Question
Software Engineer / DevelopersDiff in Having and Where:
Having is used with Group by clause and without group by clause it works similar to where.
Where : It is conditional join between table and it filter the data from tables.
Query Optimization : The best way is to figure out the execution plan and then analysis how the data is fetched. If there are full table scans in large data tables then we need to consider the index. See if table joins are necessary otherwise we need to remove few tables if it is possible.
More details
javadecodedquestions.blogspot.sg/2013/01/database-interview-questions-for-java.html
1. Clustred vs Non-Clustred
- Sadineni.Venkat March 07, 2008A clustered index forces the data to be stored in the index column sequence. Clustered index is helpful to search range of data values.
Eg: Assume there is an Employee(EmpId, EmpName, Sal, Dept) with clustered index on EmpId column
SELECT Empname
FROM Employee
WHERE EmpId between 8 AND 16.
As the employee records are stored in the sequence of EmpId, we can easily locate EmpIds 9 to 16 once we find the location of record for EmpID 8.
A good example of clustered index is: Dictionary where each page header contains the starting letters of words that are present in that page. If we go to a page, we can easily find all the words adjacent to a particular word.
There can be only one clustered index per table but there can be approximately 249 non-clustred indexes.
Non-Clustred index: A non-clustered index stores location of the data page and an offset to the data record in that page.
A good example is: index on the back of a book
You can refer to the below links for more information
1. sql-server-
performance.com/articles/per/index_data_structures_p2.aspx
2. msdn2.microsoft.com/en-us/library/ms189280.aspx