Sqlserver Interview questions

Total available count: 45
Subject - Databases
Subsubject - Sqlserver

Explain Transact-SQL optimization tips?

1. Use views and stored procedures instead of heavy-duty queries

2. Try to use constraints instead of triggers, whenever possible

3. Use table variables instead of temporary tables

4. Try to use UNION ALL statement instead of UNION, whenever possible

5. Try to avoid using the DISTINCT clause, whenever possible

6. Try to avoid using SQL Server cursors, whenever possible

7. Try to avoid the HAVING clause, whenever possible

8. If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement

9. Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement

10. Try to restrict the queries result set by using the WHERE clause

11. Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows

12. Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns

Next 5 interview question(s)

What is Foreign key?
How can a programmer enforce to use particular Index?
Explain the joins in SQL Server?
What is Index Tuning in SQL Server?
What are the differences between UNION and JOINS in SQL Server?