Below are some of the tips for SQL query optimizations in the form of question/answers.
1. Which of the following query is the most optimized?
a. SELECT column_name FROM table_name WHERE LOWER(column_name) = ‘name’.
b. SELECT column_name FROM table_name WHERE column_name = ‘NAME’ or column_name = ‘name’.
c. SELECT * FROM table_name WHERE LOWER(column_name) = ‘name’
d. SELECT * FROM table_name WHERE column_name = ‘NAME’ or column_name = ‘name’.
Answer: B.
Reason: We should specify the columns in the Select queries and avoid functions like UPPER, LOWER etc as far as possible.
2. Which of the following query generally prevents (but not always) the query optimizer from using an index to perform a search?
a. SELECT member_number, first_name, last_name FROM members WHERE firstname like ‘m%’
b. SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
c. SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
d. All of these
Answer: C
Reason: Column name is mixed within a function. Hence index cannot be used by optimizer.
3. When we use “NOT IN” our SQL queries, the query optimizer uses which technique to perform the activity?
a. Indexing
b. Clustered Indexed scan
c. Nested table scan
d. None of these
Answer: C
4. Which of the following is the best way of inserting a binary image into database?
a. Use Insert statement
b. Use Stored procedure
c. Both give same performance
d. None of these
Answer: B
Reason: The reason for this is because the application must first convert the binary value into a character string (which doubles its size, thus increasing network traffic and taking more time) before it can be sent to the server. And when the server receives the character string, it then has to convert it back to the binary format (taking even more time).
5. SELECT INTO option locks the system tables. True or false?
Answer: True
6. Using which among “Derived table” and “Temporary table” can we reduce I/O and boost our application’s performance?
Answer: Derived table
Reason: A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, we can reduce I/O and boost our application’s performance.
7. Which of the following query is the best one in performance to verify the existence of a record in a table:
a. SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’
b. IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)
c. Both give same performance
Answer: B
Reason: don’t use SELECT COUNT(*) in your Transact-SQL code to identify it, which is very inefficient and wastes server resources.
8. Which of the following is NOT recommended for UPDATE queries in order to reduce the amount of resources required to perform the query:
a. Try not to change the value of a column that is also the primary key.
b. Try to update a column that has a reference in the WHERE clause to the column being updated whenever possible
c. Try to avoid updating heavily indexed columns.
d. When updating VARCHAR columns, try to replace the contents with contents of the same length.
Answer: B
Reason: All points except “B” are recommended points for UPDATE queries. Instead we should try not to update a column that has a reference in the WHERE clause to the column being updated.
9. Omitting of which clause (if possible) can decrease the possibility that a sort operation will occur:
a. DISTINCT
b. ORDER BY
c. LIKE
d. Both A and B
Answer: D
10. Which of the following query will be better in performance?
a. SELECT * FROM Orders WHERE OrderID*3 = 33000
b. SELECT * FROM Orders WHERE OrderID = 33000/3
c. Both are same in terms of performance as well.
Answer: B
Reason: We should avoid computation on columns as far as possible and hence we will get an index scan instead of a seek.
