Some SQL tips/(interview) questions

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.

Read more: Tech Samosa
QR:

Posted via email from Jasper-net