SQL Query Optimization for Beginners

Kurmapu Hymavathi — Tue, 21 Oct 2025 10:17:27 +0000

Query Optimization is a process of writing a SQL query to improve the database performance. When I started learning SQL, I have noticed that even small improvements reduced the query execution time. Below are the few practical tips I use whenever I write SQL queries:

Use specific columns instead of * to get all records
select EmployeeId,EmpName,city from Employee
Use Group by clause instead of distinct if possible
select distinct Department from Employee

Below query also gives the same result but this has some query improvements.
select Department from Employee Group by Department

3.Use primary key while creating a table
Create table Employee ( id int constraint id_employee primary key, name varchar (50) );

4.Use top/limit to preview query results
select top 3 * from Employee order by 1 desc

5.Indexing the right columns - Adding index is always better for the select operation.

select * from Employee create index idx_department_employee on Employee (Department)

6.When you want to create a new stored procedure, you should always create a one stored procedure started with usp_ not with sp_. if you try to create stored procedure started with sp_ it will first try to look into the master database and then it will go to your database but at the same time when you have created the store procedure started with usp_, it will go directly to your database and it will search for that stored procedure. so, using this usp_(User Stored Procedure) is a bit faster than sp_.

Bad Approach:
CREATE PROCEDURE sp_GetEmployee AS BEGIN SELECT * FROM Employee; END

Good Approach:
CREATE PROCEDURE usp_GetEmployee AS BEGIN SELECT * FROM Employee; END

7.We should always use nolock when we are querying something

we are querying this employee table and assume this table has a lot of records and the query is bit complex then in that case if your transaction got a stuck then other people who wants to query this table on the same server will not be able to do so because this table has been locked for that same reason we should always use nolock

Bad approach:
SELECT EmpName FROM EMPLOYEE WHERE EmpId=7

Good approach:
SELECT EmpName FROM EMPLOYEE WITH (NOLOCK) WHERE EmpId=7 OR SELECT EmpName FROM EMPLOYEE (NOLOCK) WHERE EmpId=7

8.If possible, use wildcards only at the end of the phrase.
SELECT EmpName FROM EMPLOYEE WHERE DEPARTMENT='H%'

Forem: Kurmapu Hymavathi

SQL Query Optimization for Beginners