SQL Index Tutorial | SQL Optimisation Part 1

SQL index is a distinct structure in the database that is built using the CREATE INDEX statement. Mostly , DBAs love & hate SQL indexes at the same time due to the tricky nature they posses. They can either speed up your databases or slow it down , depending on how correctly you have implemented them. People familiar with programming languages understand the behaviour of pointers very well. An index is the pointer in database.


As said , SQL indexes are a distinct data structure such as B+tree or balanced tree where reference to the original data is stored with the keys.  It requires its own disk space. Creating an index does not change the table data; it just creates a new data structure that refers to the table. You can also put it as SQL Index are special lookup tables that the database search engine can use to speed up data retrieval.


Syntax to create SQL Index is as follows :

 CREATE INDEX index_name ON table_name;

You define index on columns and you can do it two ways

*Single-Column Indexes

Declare index on one column only :

 CREATE INDEX index_name ON table_name (column1) ;

*Composite Indexes ( Multiple Columns)

Declare index on more than one column

 CREATE INDEX index_name ON table_name (column1) ;

Implicit Indexes

Implicit indexes are indexes that server creates automatically  upon creation of the object . Indexes are automatically created for primary key constraints and unique constraints.


You can Drop index with the below code

 DROP INDEX index_name;

4 Scenarios WHEN SQL Index is Not An Option

  1. When tables are too small
  2. Tables have frequent, large batch updates or insert operations.
  3. When columns contain a high number of NULL values.
  4. Manipulation frequency of Column(s) is high.

SQL indexes are very important when it comes to the optimisation of database and enhancing query performance. The selection criteria for index and the columns which should be selected for index , needs a special consideration. This is the reason we have decided to go deep with this topic and write separate posts :

  1. How Does an SQL Index work.
  2. Clustered & Non Clustered Indexes.
  3. Fragmentation of Indexes

We will see how an SQL index sort data and how it helps to optimise the performance of the database. How data is organised in data structures and what will happen upon each read /write operation.

At the end we will discuss types if indexes with practical examples because then you will be able to understand why DBAs love & hate SQL .

Stay Tuned for next articles!

Since you are here. Support BI Talks BI in efforts to meet the Maintenance costs so that we can keep serving you up with the latest articles.


Table vs View – All You Need To Know

Table vs View is a topic of debate among DBAs and beginners because the similarities they both share . We will discuss various aspects of both tables and views . Such as:

  1. Difference between Table vs View.
  2. Table vs View Performance.
  3. Advantages of Views in SQL

In SQL Beginner’s Guide we have developed basic understanding about views. This article will discuss advance aspects of the topic. In case you need some background reading

Read : What is View in SQL ?

Difference between Table vs View

A table is where you store your data. The table actually occupies space on disk and is created by defining columns , data types & Constraints . They are the entities and responsible to store data . From our sample university database . Below is the Course Table

Table Vs Views

This table is created by a following DDL statement :

CREATE TABLE [dbo].[Course](
[CourseID] [int] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,

View  is a created by DML statement (SELECT Statement) which doesn’t occupy space on disk . View is literally a query . Nothing more nothing less . What it does is it executes the query defined in it’s structure and display the result which looks a lot like table.

Run this query in our Sample database

FROM [School].[dbo].[Course]

FROM [School].[dbo].[CourseView] 

This query will produce the same result

SQL Subquery

Difference is in the behaviour these two behave and the purpose they serve.

We create views to avoid writing same piece of code multiple times on multiple places . For instance : You want to see the departments name against the Department ID with  courses from above table . The query will be as follows :

,D.[DepartmentID] , D.Name
FROM [School].[dbo].[Course] C

LEFT JOIN [School].[dbo].Department D
ON C.DepartmentID=D.DepartmentID

When to use Views?

Now you will write this query once , execute it and then what will you do if you need this one more time? will you write this piece of code again? What if your information is scattered over 10 different tables  & you need to execute this in 20 different places? This Scenario is common in Enterprise Applications. You may say , this is not a problem because we can save the files and call them every time. Problem is that when a query  executes , it goes through various stages in Database engine .

Two main stages are

  1. Query Parsing
  2. Query Plan

If you run this query 20 times. Your database engine will parse the query & make the query plan 20 times ( I know smart engines may cache frequent queries -for the sake of argument , let’s think they won’t) . Therefore , your query will acquire & release locks approx. 20 times . Therefore ,This will create bottlenecks for other operations as well.

Instead of all this just create a VIEW . This way for the same query , database engine will create query plan and parse the query only once. Then whenever you call view it will simply execute the query to return results.

,D.[DepartmentID] , D.Name
FROM [School].[dbo].[Course] C

LEFT JOIN [School].[dbo].Department D
ON C.DepartmentID=D.DepartmentID

Next time if we have to execute it , we will simply call CourseView to display result.

Views can be updated , deleted & inserted though with a lot of restrictions. The base table for these operations should be same . More complex the view more difficult it is to do DDL operations.

CAN we have insert or update statement in view?

No we cannot do this . For such scenarios , use Stored Procedures 


Yes , index views are called materialised view. Very useful for reporting purposes & highly functional .
We will soon write on materialised views as they are related to query optimisation section . The series is in the queue .

Table vs View Performance

This question is frequently asked in interviews & comes up frequently in our daily work. Answer to the question is as dynamic as it can be. This is one of those questions that doesn’t have one right answer because –It Depends

If the table and the view have the same structure and data in it, a view is slower than a normal query, because the view has to be generated from the table every time you query it. This is a step which is not necessary in a normal query. Therefore, view has normally only a piece of data from a table, which in turn makes  view faster than a normal query.

To further make it easy for you remember this

  1. It depends on the query- How well written it is.
  2. Do not use like operator in SQL Views because It will take millions of years if you’re data is big
  3. It depends on how many Joins are use to fetch data. Normally , if there are more tables . DBAs usually use multiple views to optimise speed. For example if the information is coming from 15 different tables. DBA may use 8 tables in one View lets call it View A and then later use the View A to form View B with remaining 7 tables.
  4. It depends on how well indexed your base table is. If the base table has poor index . View can’t do much.
  5. Advantages of Views in SQL
  6. Simplify the complex queries .For instance , reporting team wants to access the data. Usually , well managed databases can be large & complex . Sometimes , DBAs create views and make the data available hiding the complex underlying structure for other users. Though this practice is not recommended but still in the wake of meeting deadlines . Developers can be rough on naming columns and tables conventions.
  7. Computed Columns /Derived Columns can be created in Views. Great way to display reports. Write queries on multiple tables and create bulky reports with simple Views
  8. A database view provides extra security layer. Security is a vital part of any relational database management system. The database view offers additional protection for a database management system. The database view allows you to create the read-only view to expose read-only data to specific users. Users can only retrieve data in read-only view but cannot update it because he wont’t have access to data.
  9. Limits the access of data to certain users. For instance , your client may not want developers to access finance data or sensitive information.Because , this may create data breach. Hence , they need to have limited access.  This can be easily manage with Views.

Table vs View is one of the favourite topics among DBAs. Hopefully , you have had enough content here to understand the difference. In case , you want to add anything . Feel free to comment below!

How Grouping Works in SQL

One of our subscribers reached us and asked How Grouping works in SQL ?  So today , this is what we are going to do . We will discuss in detail about grouping in SQL and how it actually works behind the scenes. Also , this is our first post in Questions category and we will be doing more as there are bundle of requests in the queue. So if you want to contribute and like us to write something for you , please do not hesitate.

How Grouping works in SQL?

Grouping summarises the whole data set into smaller chunks of data where each row represents a group . Group is when we combine a group of things, elements under some rule. While working with SQL reports , a lot of times we will come across scenarios where we want to summarise the data. For this purpose we use MS SQL Group By Clause

Also see : MS SQL Group By Clause

because this clause summarise the data into groups . Time to dig in now.

Prepare the data

Examples and queries that we will see here will run on our University Management System database . If you want to use these SQL queries for practice , create the same database , you need to just copy paste the script from here .

Case Study : Grade Reports from University Management System

We are asked to get the top 5 students with top grades . One best query writing practice is to try and visualise the end result and then build the query around it. To do this , you need first to see the underlying data. If you run the below SQL query , you will have a good look at the data we have.

SELECT P.FirstName,C.Title,Grade
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID

How Grouping Works in SQL


Remember our objective. We need first five students with top marks out of these forty rows. Give a brief and a long look at the table. You are seeing three columns here. Student names , course names and respective grades. Objective is to find the top 5 students .

Let’s break up the table and visualise what is we want. We want two things

  1. Student Names
  2. Student Grades

Now it is clear that we want top achievers which are actually the students who have obtained highest marks. How can you find that? By adding marks in all courses of each student.

Aggregate functions in SQL will help us to add the grades . See the Grades columns , its a column of numbers.

How Grouping Works in SQL examples

Can you guess of which aggregate functions in SQL we will use? SQL Count () or SQL Sum() ?

See : Difference Between SUM() & Count ()

It has to be SQL Group By SUM  which will do the job for us. Now , we have already determined that when Grouping clause is in effect each row represents
a group . So to achieve the objective remove Course column from the query

Read : MS SQL Group By Clause 

SELECT P.FirstName,GradeFROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID

How Grouping Works in SQL example 2

See the table again. Let’s find total marks of each student. Run this query

SELECT P.FirstName,SUM(GRADE) AS TotalStudentsinCourse
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID

It will give us out this

How Grouping Works in SQL example 2

We have applied the aggregate function but haven’t told our SQL Server on what basis it should be grouped. We want to group the grades against each student that is why we are writing FirstName in query . To apply SQL aggregate function of SUM on grades column you need to put First Name in Group By Clause. When you do that it will create groups for each student like this

SELECT P.FirstName,SUM(GRADE) AS TotalStudentsinCourse
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID
GROUP BY P.FirstName

How Grouping Works in SQL example 4

All students are appearing only once because all same elements are grouped together . Each student was taking up more than one course so SQL Server first combines the elements into group. In our case , unique student names.  Now it will apply SQL Sum for each group. Combine both steps and SQL Server handles bot these steps like this

How Grouping Works in SQL example 5

This is the final output you will see

How Grouping Works in SQL example 6

See how SQL Server reduces the table into groups and then by the use of aggregate functions in SQL it gives us results for those groups. We will use TOP clause with Order By Clause to find the results


SELECT TOP (5) P.FirstName,SUM(GRADE) AS TotalStudentsinCourse
FROM [School].[dbo].[StudentGrade] SG
LEFT JOIN [School].[dbo].Course C
ON C.CourseID=SG.CourseID
LEFT JOIN [dbo].Person P
ON P.PersonID=SG.StudentID
GROUP BY P.FirstName

How Grouping Works in SQL example 7

If you use TOP clause always use Order By clause with it to get correct data. Because , results can be random , so if you apply Order By then the order will be consistent. Topic for another day!


  1. Grouping is applied on all columns as combination in the same order they appear in query.
  2. Each row represents a unique row.
  3. Filtering on grouping is done by using HAVING Clause
  4. Use ORDER BY clause to arrange the data set in a order.

if you can find more points about grouping , do comment and we will add this in our list.

I hope now you understand about How Grouping Works in SQL because this has been a brief articleNow if you like this article , do share with your friends and colleagues and recommend us . Oh! and if the sponsored content is bothering you , apologies for that too because it’s the only source of revenue for this blog.

See you with next post very soon!