Sqlserver Interview questions

Total available count: 45
Subject - Databases
Subsubject - Sqlserver

Explain SQL Server Index Optimization tips?

1. Every index increases the time in takes to perform INSERT, UPDATE and DELETE. So the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table; then the number of indexes may be increased
2. Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index
3. If you create a composite index, the order of the columns in the key are most selective columns to the leftmost of the key
4. If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns
5. Create surrogate integer primary key if your table will not have many insert operations
6. Clustered indexes are more preferable than non-clustered; if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY

Next 5 interview question(s)

Explain different types of Locks in SQL Server?
What is SQL Server Agent?
What is SQL Profiler?
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
What is OLTP (Online Transaction Processing)?