Welcome to Appsloveworld technologies,In this session, we learn about the different types of indexes that are available in the SQL server, what are clustered and non clustered indexes, and then difference between them.
Before continuing with the session, I strongly recommend reading the previous post this series.
The following are the different types of indexes that are available in SQL server
- Full text
- Index with included columns
- Index on computed columns.
In this post will be talking about clustered in non clustered indexes and the difference between them.
So what’s a clustered index?
A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index. Source
We have a create table statement here, where we are creating a TblUsers table. And if you look at this closely, the Id column is marked as a primary key column.
CREATE TABLE [dbo].[TblUsers]( [Id] [int] Primary Key, [Name] [nvarchar](100) NULL, [Email] [nvarchar](max) NOT NULL, [Gender] [int] NOT NULL, [RewardsPoint] [decimal](18, 2) NOT NULL )
A primary key constraint will automatically create a clustered index on that column.
Here ID is marked as a primary key column. So when I create this table using the “create table” statement, it’s going to create a clustered index automatically on the Id column for this table.
Let’s look at this in action. Let’s execute the above query.
Now, let’s check in the database if it has created a clustered index on this ID automatically
how do we check that? There are two ways.
One way is to basically use sp_helpindex for getting information about indexes. so it shows that we have a clustered index on this ID column.
we did not create this explicitly. It got automatically created because a primary key constraint will automatically create a clustered index on that column if the table doesn’t have any clustered index. if look at this, it says it’s a unique clustered index.
Learn more about unique indexs
Unique index is basically used by SQL Server to enforce the uniqueness of the primary key. we have talk about unique indexes in detail in the what is a unique index post.
What is the other way of checking the indexes on this table?
The other one is basically to use, the object explorer within the object explorer, expand the TblUsers, and expand the index folder. You should see the index that has just been created.
So now we just have created the table TblUsers. Now let’s insert some data into that table. I have some insert statements here. And if you look at the below script, we are inserting values for id, name, email, gender, and RewardsPoint.
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1004, 'Test 2', 'email@example.com', 0, CAST(15.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1005, 'Test 3', 'firstname.lastname@example.org', 1, CAST(31685.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2018, 'aakankshi6', 'email@example.com', 3, CAST(109.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2008, 'Test 8', 'firstname.lastname@example.org', 0, CAST(109.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2010, 'Test 10', 'email@example.com', 0, CAST(5955.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2011, 'Aankashi', 'firstname.lastname@example.org', 1, CAST(15.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2012, 'Aakankshi Gupta', 'email@example.com', 0, CAST(127.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1006, 'Test 4', 'firstname.lastname@example.org', 0, CAST(25.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2004, 'Test 5', 'email@example.com', 0, CAST(30.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2006, 'Hr. Niels Henriksen', 'firstname.lastname@example.org', 1, CAST(30.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2007, 'Neeraj Singh', 'email@example.com', 0, CAST(30.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2, 'rajat Saxena', 'firstname.lastname@example.org', 1, CAST(25.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (3, 'Pankaj Patel', 'Pankaj@gmail.com', 0, CAST(25.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1003, 'Test 2', 'email@example.com', 2, CAST(30.00 AS Decimal(18, 2))) GO
Here, if you will look at the data closely. The values for the ID column are not in sequential order.
I am intentionally, inserting them in a nonsequential order.
we are told that a clustered index determines the physical order of data in a table. And then in table on the ID column, we have a clustered index.
So even if you insert data in nonsequential order, when you say select * from [dbo].[TblUsers], the data should have been automatically arranged in order.
- A table can have only one clustered index but one clustered index, can contain multiple columns and if a clustered index contains multiple columns within that we call that kind of an index as a composite index. Since it’s a cluster index, we can call it a composite cluster index.
Similarly, and non clustered index also can have multiple columns within that. In which case we call it a composite non clustered index
You can consider a telephone directory just like a composite index because, the numbers are organized by the last name first and then if there are similar last names for people, then the data is arranged according to their first names.
so in a similar fashion, we can create a composite cluster index on the TblUsers for name and Rewardspoints columns.
if you look at the way we have the data, it’s arranged on the in the order of the Id column because currently, we have a clustered index on the ID column.
but instead of that, I want to create a clustered index on the name and RewardsPoint columns together, which means I want to sort the data first by name and then by RewardsPoint.
Composite cluster index
So now let’s try to create a clustered index on the name and RewardsPoint. But remember, there is already a clustered index on the ID column.
Create Clustered Index IX_TblUsers_Name_RewardsPoint ON TblUsers(Name ASC,RewardsPoint DESC)
So obviously when we try to create this clustered index on this table, we should get an error stating that you cannot create more than one clustered index on the table TblUsers, drop the existing cluster index before creating another one.
- So obviously we’ll have to drop that index.
- Go to the object explorer, expand the indexes folder.
- Right-click on the index and select delete and click.
that should delete the index.
so we have deleted the cluster index on the ID column. Now let’s try to create the clustered index on the name and RewardsPoint columns.
Basically this is a composite clustered index because your indexed is containing more than one column.
keep in mind you can only have one clustered index on a table. It’s not possible to have more than one clustered index, but it is possible for that one cluster index to have more than one column within that index keys.
Create Clustered Index IX_TblUsers_Name_RewardsPoint ON TblUsers(Name ASC,RewardsPoint DESC)
Now let’s select the data from the TblUsers table. Before we created this index, it was arranged in the ascending order of the Id column. But since now we have created a clustered index on the name and RewardsPoint columns.
Now the data is first arranged, in the table in the order of name and then RewardsPoint within that name in descending order.
Non clustered index
if I have to create a non clustered index. So how do we create that? We say create nonclustered index. The only difference is instead of saying cluster, you specify nonclustered.
So here, if you look at the example, I am creating a nonclustered index on the name column for the TblUsers table.
Create NonClustered Index IX_TblUsers_Name_RewardsPoint ON TblUsers(Name ASC)
Now when we spoke about clustered indexes, we understood that the data in the table is arranged based on the clustered index column.
Now when you create a nonclustered index this is analogous to an index in a textbook.
if you look at the index in a textbook, the index is stored in a separate place and the data is stored in a separate place. For example, at the beginning of the book, you have the chapter index.
Now, if I ask you to go to a specific chapter, you will first check the index, chapter 5 is on page number 400, so you will go to page 400.So the index is stored separately and the data itself is stored separately.
So the index in a book is a little different to an index in a telephone directory or a dictionary.In a telephone directory or a dictionary, the data is arranged in the alphabetical order.
Basically, the data itself is arranged. You don’t have separate index pages and separate data pages, but in a book, you have separate index pages and separate data pages. So similarly non clustered index is analogous to a book index.
The index itself is stored separately , for example, since we have created a Nonclustered, index on the name column, the names are arranged in descending order and then each name has a row address.
For example, if I write a query select * from [dbo].[TblUsers] where name=’Neeraj Singh’,
what is going to happen?
it goes to the table based on the row address and then directly fetch that record.
So since the non clustered index is stored separately from the actual table, a table can actually have more than one non clustered index.
Just like our book, and that can have an index by chapters at the beginning and maybe another index by common terms at the end of the book.
so the indexes that we see in a book, we can have as many indexes as we want of that kind. the same is applicable for non clustered indexes.
I can create one index on the name column, another index on the gender column. So there is no restriction on how many non clustered indexes you can have.
Difference between clustered and non clustered index with example
- The clustered index determines the storage order of rows in the table and hence doesn’t require additional disk space, obviously.But whereas non clustered indexes are stored separately, let’s say, for example, I have a million records here. You will have a million records in the index as well
- Since non clustered indexes are stored separately from the table itself, you require extra disk storage space. But for clustered index, it’s not the case because cluster in the index just determines the order of data in the table itself.You don’t require additional disk storage space.
- Cluster index is obviously faster than the non clustered index because the clustered index has to refer back to the table if the selected column is not present in the index.
what do we mean by that? For example, we are using a non clustered index. Now when I ask select * from [dbo].[TblUsers] where name=’Neeraj Singh’.
it first checks the index of “Neeraj Singh” records. This is the row address. And if I want the Id and Name, Email columns, I don’t have those columns in this index. So I will have to check and I will have to refer the table back.
Obviously there is one extra lookup involved if it’s a non clustered index.
But on the other hand, if it’s a clustered index, then all the columns are present in the table itself and the data itself in the table are arranged based on the clustered index.
So you don’t have that extra lookup. That’s why clustered indexes are slightly faster than non clustered indexes.
The post Clustered and Non clustered index in sql server with real example appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- How to stop the execution of a Stored Procedure using SQL Server?
- SQL Server query result to file
- How do I replicate and validate data from remote systems into SQL Server
- Multiple SQL filter on same column
- Sql issue in calculating formulas
- SQL Server TSQL - How to achieve "NOT IN" statements within a CASE
- Excel VBA - SQL Call - Operation is not allowed when the object is closed
- Parse/Shred Huge Complex XML to SQL Server 2008 Database (30+ tables)
- How to get rows per day (Fill gaps when no records)
- SQL Periodic table updates
- Sql Server Insert query Into multiple tables from temporary tables
- Sql query Postcode search using first 3 characters
- Format function is not working at where clause to filter the format date?
- Evaluate SQL where clause in c# without SQL Server
- SQL Get max date in dataset for each month
- Unable to connect to msSQL database via PHP
- cant appear only the max date for a number of transactions
- Checking if a SQL Server login already exists
- How to get result of two querys in the same result within SQL Server?
- Creating get state code function
- Defining column in SQL Server 2008 so that it substituts NULL with... let's say -1
- Angular 6 service call with a date parameter
- opening a file in asp.net-mvc
- SQL Left-Join - Get value If both values are Not null in TableB or Row missing in TableB
- How to protect against a process run from xp_cmdshell remaining in KILLED/ROLLBACK state forever?
- Sage Manufacturing Line 50 Bill Of Materials, SQL Recursive Two Table Tree iteration
- SQL Query get all values from left table and uncommon values from right table
- Persistent display of updated rows in SQL Server
- SQL time difference between two dates result in hh:mm:ss
- Why might a string not be equal to a stored varchar?
- Dynamically change the where clause in the SQL Query
- query that will duplicate records based on quantity
- SQL Server : "cannot insert the value NULL into column" but column is not null
- Getting the first instance of a zero value in a table of records
- Creating a new table from grouped substring of existing table
- querying SQL Server from Apache Drill and datetimeoffset
- Database design to hold a person's information that changes with time?
- SQL - union two tables, each having a few unique columns
- The best way to update a table on Windows CE device from Server
- Is it possible to point my connection string to the folder of my exe file
- How to Update record values with values from an existing record in same table
- Calling a stored procedure SQL
- SQL Joining different tables structure (MSSQL)
- Set a parameter in select statement
- How to do a bulk update in SQL Server
- Permanent and global variables in SQL Server
- Expressing age between two years
- Pivot function in SQL and sum rows
- Getting quantity between a range of months from 2 date parameters
- Cannot Bulk Load Error Code 3. Procedure working on one machine only
- Update column from inner join
- simple Group By
- C# & SQL Server: how to open a form based on login
- Query results to list in SP
- Declare Variable for a Query String
- SQL Syntax Failure - Experienced SQL Eyes Needed
- Find the last applied seed value on a table in sql server 2012
- Sql server - "Map" function?
- How to convert Time to Decimal in SQL with an integer
- SQL Server query plan differences
- How to automatically truncate string when do bulk insert?
- How to split string and display it in a gridview ?
- Reliable alternative to replication for continous data sync between two databases
- What is the best way to collapse the rows of a SELECT into a string?
- sql max/min query and data transformation
- Format Currency into millions of dollars SQL Server
- SQL Server Sum Distinct Group by
- How To Determine Column Types In A SQL Server Results Set
- Does anyone have a backup strategy for SQL Azure databases?
- Database vs application security design
- In SQL Server, how do I create a reference variable to a table?
- SQL Server Is there a way to select columns from a given list
- Inserting a file in to the SQL database and retrieving it
- How to create SQL Server stored procedure returning an array?
- Can I use full text search a row in a table where the condition is an array of values from a select query?
- Problem with multipart sql identifiers
- Getting two different types of sums with only one row
- Rows to comma separated, vertical to horizontal, values in SQL Server
- How to insert into three tables at once in sql server 2012
- Multiple session-less requests to a page (which interacts with database) results in HTTP Error 500
- LAG function in Alter Table produces windowed function error
- Can SQL Server table have a foreign key to a table that resolves to many records?
- Create different format for a table
- how we delete a row from parent table when child table connected with paren table by forigen key
- "Invalid object name" error on INSERT to SQL Server from pyodbc
- Select values in SQL that have a colum value in one row and not a specific other column value in another
- SQL Delete using select on linked server issue
- Update Table compare to other table
- SQL Server join question
- Optimizing SQL Queries
- SQL - Find all UPPER CASE strings
- How to update a column only once in a SQL Server Agent job that runs every 5 minutes
- Design database based on EAV or XML for objects with variable features in SQL Server?
- Non-SQL API for SQL Server?
- Get next 2 values for sequence in single row
- C# SqlCommand syntax for this SQL command
- Why does FireDAC ignore index name?
- How can I run an SSRS report as the current Windows User?
- Output parameter used to insert a row in same table
- Concatenating Column Values into a Comma-Separated List
- What is the point of "Initial Catalog" in a SQL Server connection string?
- Struggling with faulty date fields in SQL Server
- SQL Query From String
- Find time difference between start status and end status with pause status
- For each category, display its subcategory
- Readable "always false" evaluation in TSQL
- SPID of a SqlConnection (SQL-Server) in ADO.NET
- SQL server: Transpose Rows to Columns (n:m relationship)
- How to rename something in SQL Server that has square brackets in the name?
- Regex expression in MS SQL
- Combine multiple result together in Hibernate
- Assign column name of max value to row based on comparison across columns
- Why ajax error is coming in my Data table in .Net?
- Select first record with aggregate functions
- From where should Microsoft.SqlServer.Management.IntegrationServices.dll be loaded?
- SSIS User Defined Date or Default
- How to update X% of rows to A, Y% of rows to B, Z% of rows to C
- SQL Query to get min and max date per year date ranges
- Error when using phpunit/dbunit with SQL Server
- SqlHierarchyID casting from Datarow
- What is the alternative of SqlConnection to connect data?
- Sort data in a table using a cursor
- Not able to deploy to SQL Server using DacPac
- Getting Top 3 values for each id and status
- C# Assembly Injection Check
- Give System Managed Identity access to classic SQL Server
- Count of files received in the last 30 days split by each day
- Automating DBCC CHECKDB
- SQL Server 2005 query plan optimizer choking on date partitioned tables
- SQL Server: Find out default value of a column with a query
- T-SQL Soundex/Difference to find duplicate rows
- Pivot without aggregation in SQL
- SQL Server 2008 R2: Assign alias dynamically
- T SQL: Inner join in sub select query?
- SQL query: Finding unique number combinations within all records
- SQL query optimization
- RedGate SQL tools v Microsoft Visual Studio Team System Database Edition (Data Dude)
- Calculate time difference (only working hours) in minutes between two dates
- Fill values "down" when pivoting
- SQL Server - group by with row number - Gaps and Islands
- Troubleshooting Master Data Services (MDS) Permission Authorization
- how to use split function in where used LIKE operator
- SQL Inserting data in master table and then reference data in detail table
- Option Recompile makes query fast - good or bad?
- How can I populate a parent and a child table from another table?
- SQL statement's placeholders that is not replaced leads to "Cannot update '@columnName'; field not updateable"
- ASPNETDB.mdf for MySQL
- LIKE vs CONTAINS on SQL Server, CONTAINS can't find a value in DB while LIKE can
- Insert into a row at specific position into SQL server table with PK
- Incorrect syntax near ')' calling stored procedure with GETDATE