7 Tips for Query Optimization in SQL Servers
Tags: sql, database, programming, productivity
Published: 2023-01-31 17:29:57

When optimizing performance, developers and architects often overlook tuning their SQL queries. Understanding how databases work and writing better SQL queries play a huge role in **boosting performance.** Efficient SQL queries mean quality, scalable applications. 

In this tutorial, we will look at 7 essential SQL tips for optimizing your SQL server. 

**This guide at a glance:**
* [Tip 1: Choose the proper data type for the column](#1)
* [Tip 2: Table Variables and Joins](#2)
* [Tip 3: Use conditional `WHERE` clause](#3)
* [Tip 4: Use `SET NOCOUNT ON`](#4)
* [Tip 5: Avoid `ORDER BY`, `GROUP BY`, and `DISTINCT`](#5)
* [Tip 6: Fully qualify database object names](#6)
* [Tip 7: Learn how to fully secure your code](#7)
* [What to learn next](#next)

<a name="1"></a>

## Tip 1: Choose the proper data type for the column

Every table column in SQL has an associated data type. You can choose from integers, dates, varchars, Boolean, text, etc. When developing, it is important that you **choose the proper data type**. Numbers should be of numeric type, and dates should be dates, etc. This is extremely important for indexing. 


Let’s look at the example below.

```sql
SELECT employeeID, employeeName
FROM employee
WHERE employeeID = 13412;
```

The above [query](https://www.educative.io/blog/what-is-database-query-sql-nosql?eid=5082902844932096) fetches the employee ID and name for the employee with ID `13412`. *What if the data type for employeeID is string?* You may run into trouble there when using indexing, as it will take forever when it should be a simple scan. 


<a name="2"></a>

## Tip 2: Table Variables and Joins

When you have complex queries like fetching the orders for customers, along with their names and order dates, you need something more than a simple select statement. In this case, we’re fetching data from the customer and order tables. That’s where [joins](https://www.educative.io/blog/what-are-sql-joins?eid=5082902844932096) come in. 

Let’s look at the example of a join:

```sql
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
```
> SQL offers `inner`, `full`, `left outer`, and `right outer` types of joins.

Table variables are local variables that store data temporarily and have all the properties of local variables. Do not use table variables in joins, as SQL sees them as a single row. Even though they are fast, table variables do not perform well in joins.

<br> 

<a name="3"></a>

## Tip 3: Use conditional `WHERE` clause
Conditional `WHERE` clauses are used for **subsetting**. Let’s say you have a situation like this:

```sql
-if SEQ_VAR in (1, 2, 3) & diff(DATE_VAR2, DATE_VAR1)≥ 0
— elif SEQ_VAR in (4, 5, 6) & diff(DATE_VAR2, DATE_VAR1) ≥1
— else diff(DATE_VAR2, DATE_VAR1) ≥2
```

With the conditional `WHERE` clause, it would look like this:

```sql
SELECT 
  DAT.ID_VAR,
  DAT.SEQ_VAR,
  DAT.NUM_VAR,
  DATE_VAR1,
  DATE_VAR2,
  TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1) AS LAG_IN_DATES

FROM 
  CURRENT_TABLE      DAT 
WHERE
  (TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1)) >= CASE WHEN SEQ_VAR IN (1,2,3) THEN 0 WHEN SEQ_VAR IN (4,5,6) THEN 1 ELSE 2 END 
ORDER BY ID_VAR, SEQ_VAR
```

<br>

<a name="4"></a>

## Tip 4: Use `SET NOCOUNT ON`

When performing `INSERT`, `SELECT`, `DELETE`, and `UPDATE` operations, use `SET NOCOUNT ON`. SQL always returns the affected number of rows for such operations, so when you have complex queries with a lot of joins, it can affect performance.

With `SET NOCOUNT ON`, SQL will **not count the affected rows** and improve performance. 

In the following example, we are preventing the message about the number of rows affected from displaying.

```sql
USE AdventureWorks2012;  
GO  
SET NOCOUNT OFF;  
GO  
-- Display the count message.  
SELECT TOP(5)LastName  
FROM Person.Person  
WHERE LastName LIKE 'A%';  
GO  
-- SET NOCOUNT to ON to no longer display the count message.  
SET NOCOUNT ON;  
GO  
SELECT TOP(5) LastName  
FROM Person.Person  
WHERE LastName LIKE 'A%';  
GO  
-- Reset SET NOCOUNT to OFF  
SET NOCOUNT OFF;  
GO  
```

<br>

<a name="5"></a>

## Tip 5: Avoid `ORDER BY`, `GROUP BY`, and `DISTINCT` 


Use `ORDER BY`, `GROUP BY`, and `DISTINCT` only when necessary. SQL creates work tables and puts the data there. It then organizes the data in the worktable based on the query and then returns the results.

<br> 

<a name="6"></a>

## Tip 6: Fully qualify database object names

The goal of using fully qualified database object names is to remove ambiguity. A fully qualified object name looks like this:

```sql
DATABASE.SCHEMA.OBJECTNAME.
```

When you have access to multiple databases, schemas, and tables, it becomes important that you specify what you want to access. You don’t need to do this unless you’re working with large databases with multiple users and schemas, but it is a good practice. 

So instead of using a statement like:

```sql
SELECT * FROM TableName
```
You should use:

```sql
SELECT * FROM dbo.TableName
```

<br>

<Br>

<a name="7"></a>

## Tip 7: Learn how to fully secure your code

Databases store all sorts of information, making them prime attack targets. Common attacks include [SQL injections](https://www.educative.io/blog/cyber-security-guide?eid=5082902844932096), where the user enters a SQL statement instead of a username and retrieves or modifies your database. Examples of SQL injections include:

```sql
textuserID = getRequestString("userID");
textSQL = "SELECT * FROM Users WHERE userID = " + textuserID;
```

Let’s say you have this, `textuserID` will fetch the input from the user. Here is how it can go wrong:

```sql
SELECT * FROM Users WHERE userID = 890 OR 1=1;
```

Since `1=1` is always true, it will fetch all the data from the Users table. 

You can guard your database against SQL injections using parameterized statements, input validations, sanitizing input, etc. How you secure your database depends on the DBMS. You will need to understand your DBMS and its security issues so you can write code that is secure. 

<br>

<a name="next"></a>

## What to learn next

In this article, we covered some essential SQL tips but, there’s always more to learn. Some good next steps are:
* Optimizing views
* Nested queries 
* `INSERT` triggers
* [Foreign keys](https://www.educative.io/blog/what-is-foreign-key-database?eid=5082902844932096)

If you’re interested in learning more about these concepts, check out Educative's **[Introductory Guide to SQL.](https://www.educative.io/courses/introductory-guide-to-sql?eid=5082902844932096)** You will learn the basics of everything from creating databases to queries, to common SQL interview questions. It’s a perfect refresher for any SQL developer. 

*Happy learning!*

<br>

### Continue reading about SQL on Educative
* [What is a Database Foreign Key? A beginner's tutorial](https://www.educative.io/blog/what-is-foreign-key-database?eid=5082902844932096)
* [What are SQL Joins? Types of SQL joins explained](https://www.educative.io/blog/what-are-sql-joins?eid=5082902844932096)
* [What are database schemas? 5-minute guide with examples](https://www.educative.io/blog/what-are-database-schemas-examples?eid=5082902844932096)

### Start a discussion 
Are there any helpful tips that we missed? Was this article helpful? Let us know in the comments below!