Dec 27, 2019 
13.9k
27
DecChristmas Special : Upto 40% OFF! + 2 free courses - SCHEDULE CALL
Try to imagine this. Suddenly some very special friends turn up to your house for lunch or dinner. You want to show them your cooking skills. You do not need to worry as you have your mother’s big fat recipe book with you. All you need to do is,find out your favorite recipes from the notebook and cook them. But there is a little problem. The notebook is big and you do not know on which page your favorite recipes are. You have to browse through every page of the notebook to find those recipes, which would take a lot of time.
Now reframe the same situation. But this time, your recipe book has a table of content that contains the exact page number of different recipes. Having the table of contents will make the searching for the recipes much easier. Moreover, if you manage to have an appendix at the end of the notebook, where locations of different topics in the notebook are arranged based on certain keywords inside the subject, that would make the search for that recipe topic much easier.
Read: How To Differentiate SQL Server JOIN, IN And EXISTS Clause?
Just like an index in the book, SQL also has an index that speeds up the data retrieval process from the database. Want to learn about types of indexes in SQL server? In the following, we will learn about all the SQL server index types and their application. If you have SQL server index interview questions to prepare, this article will be a big help.
Before planning to learn about how to create sql index, make sure to learn types of indexes in SQL server first. In the next few paragraphs, we will learn about different SQL server index types and their usage. Let’s begin with the SQL server index tutorial.
Check out the updated SQL self learning module now!
Learn SQL Server in the Easiest Way
Imagine a table called Employee in a Payroll database. You have names starting with different characters. Some of the names start with A, some with B and so on. There are hundreds of employees on the table. There is always a possibility that the names are stored in an unsorted manner, i.e. a name starting with D comes before a name starting with A and then comes a name starting with C and so on. If somebody needs to search a particular employee name that starts with A from the list of employee names, wouldn't it be better if the data is physically arranged in such a manner? The clustered SQL index does just that to a set of data. It arranges the data physically every time somebody enters updates or deletes a particular record from the table.
There is another way of achieving this. Instead of physically arranging the data, we can have an option to record the exact location of each record in a separate place. We can refer to that record, every time we need to search for a name in the original table. This is how non clustered SQL index works.
Read More: Clustered vs Non-Clustered Index
| Clustered Index | Non-Clustered Index |
| Physically rearranges the record each time somebody deletes, inserts or updates a record | The data is not rearranged. Instead, location of each record is maintained in a separate location which is referred each time a record is searched. |
| Performance is slow | Performance is fast |
| A particular table can have one clustered index | A particular table can have more than one non clustered index |
| Defined in a ordered data file | No ordered data file required |
| Dense Index | Sparse Index |
| In the dense index, there is an index record for every search key value in the database | In the sparse index, index records are not created for every search key |
| Index records contain search key value and a pointer to the actual record on the disk. | index record here contains a search key and an actual pointer to the data on the disk |
CREATE CLUSTERED INDEX test_index1 ON [dbo].[tbl_students] (Firstname)
The above query creates a clustered index name test_index1 on Firstname in tbl_students.
Read: What is SQL Subquery? Types of Subqueries in SQL
If you are using SQL Server Management Studio you can see the index created like below:
CREATE INDEX test_index ON [dbo].[tbl_students1] (Firstname);
The above query creates a non-clustured index named test_index in tbl_students on column firstname.
The output looks like below
In the case of a unique index, the column based on which the index is created should be unique. This means no two rows in the indexed column can have the same value.
Read: Power BI - Getting Started with Query Editor in Power BI
The Primary key is by default a uniquely indexed column.
Now let us see how we can create a unique index.
The query looks somewhat like below
CREATE UNIQUE INDEX test_index2
ON [dbo].[tbl_students1] (Firstname)
Here we are creating a unique index called test_index2 on column Firstname of table tbl_students1.
The output of the SQL Statement looks like below.
Free demo classes live now for SQL server training, check the date!
SQL Server Training & Certification
| Clustured | Non-Clustured | Unique |
| These are stored physically on the table | Stored in a separate place | Type of clustered index where there is no duplicate value eg Primary key |
Read: Difference Between SQLite and MySQL
INSERT, UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field. INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.| Clustered Column Store Index | Non Clustered Column Store Index |
| It is the physical storage for the entire table. | Secondary index that is created on a row store table |
Read: All You Need to Know About SQL: SQL Tutorial for Beginners & Experienced
| Clustered Column Store Index | Non Clustered Column Store Index |
| It is the physical storage for the entire table. | Secondary index that is created on a row store table |
CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON [dbo].[tbl_students1]
This creates a clustered column store index named cci_T1 on tbl_students1
Read: How to Create Database in Microsoft SQL Server?
The output is
CREATE nonCLUSTERED COLUMNSTORE INDEX cci_T2 ON [dbo].[tbl_students1] (firstname)
This creates a non-clustered columnstore index cci_T2 on column firstname in tbl_students1.
The output is
| Columnstore Index | Rowstore Index |
| Stored in column-wise data format | Stored in row-oriented data pages |
| Applicable mostly for read-only queries with large data sets, like data warehousing workloads | Applicable for transactional workloads since these workloads tend to require table seeks instead of large-range table scans |
| Memory usage is lower | Memory usage is higher |
| Transaction process is slow | Transaction process is fast |
| Fast is analytics queries | Slow in analytics queries |
Signup for the SQL server corporate now, new batches available now!
SQL Server Training & Certification
The above few paragraphs give readers a compressed idea on what is an SQL index, types of indexes in SQL server and their application. It is not a detailed discussion, but at least gives the reader a way forward on what is there and how to use SQL index when they are learning about SQL Server indexes, how to create SQL index or are preparing for SQL server index interview questions.
If you want to learn about how to create sql index, SQL server index types and other SQL concepts in detail and are constantly looking for the best SQL server index tutorial, JanBask Training offers SQL server certification training, covering all the basic to advanced SQL concepts with quality instructor-led classes.
Read: What is the Difference Between Control Flow & Data Flow in SSIS?
FaceBook
Twitter
LinkedIn
Pinterest
Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Feb 14, 2024
217.6k
217.6k