SQL Optimization Tips/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.

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.

kick it on DotNetKicks.com

Shout it

pimp it

Query to get a particular row in Sql Server 2005

In this article we will look into how to write a sql query to get a particular row in sql.

Lets write the code to find 2nd row in a table named mcc_measures.

So first we declare a temporary table

declare @temp table (id int) 

No in this temp table we insertrow numbers of the table

insert into @temp(id)

SELECT 
ROW_NUMBER() over(ORDER BY Measure_Id DESC)
FROM 
mcc_measures

Now from the temp table we select the row where rowid=2 as required

SELECT
*
FROM
@temp
WHERE
id = 2

kick it on DotNetKicks.com

Shout it

pimp it

Save Asp.NET Session state in SQL Server

dotnetlogo
Session states of the asp.net application can be easily saved into SQL Server using the following steps:

  1. Change the settings in the web.config file.
     <sessionState mode=   allowCustomSqlDatabase="true"  
    cookieless="false" timeout="20" sqlConnectionString=
    "database=Test_globalBms ;
    user id=abc;password=abc123$;server=172.27.68.1 " />
    
  2. In the database create the appropriate tables.It is done automatically wen we execute the exe aspnet_regsql.exe located at C:\WINDOWS\Microsoft.NET\Framework\v2.0. location

kick it on DotNetKicks.com