SQL INDEX - Improve Performance on Query Execution

The clustered index is a form of index that creates a physical sorting order for rows. This indicates that the SQL cluster index will sort the records before storing them on disc. When we construct a table with a primary key constraint, a clustered index based on that primary key is built by default. The SQL Server CREATE CLUSTER INDEX statement, on the other hand, can be used to create it explicitly.

Because the data for the remainder of the columns in the table is stored in the index's leaf nodes, there are no extra structures that need to be consulted when a search is conducted on this sort of index.

A clustered index can be formed on a table in one of two ways: through a primary key constraint or by using the create index statement.

We must delete the existing clustered index before we can construct a new one. In relational databases, an index is an additional data structure connected with a table that aids in the speedier retrieval of records from that table. An index is a key that is created from the table's columns and saved in a B-Tree. The B-Tree is a data structure that allows for faster searches and access to information.

Note that each table can only have one SQL Server Cluster Index.

Syntax and Parameters:

The basic syntax used for creating a clustered index in SQL is as follows:

CREATE CLUSTERED INDEX  index_name
ON schema.table_name(column_name);

Example 1: When we construct a primary key in a database table, a clustered index is created automatically, as seen in this example. Let's make a table called "employees" with a primary key to observe it.

CREATE TABLE employees (
id integer IDENTITY (1,1) PRIMARY KEY,
name varchar (255),
role varchar (255),
department varchar (255),
);

Example 2: The CLUSTERED keyword must be added to either the ALTER TABLE or CREATE INDEX statement in both circumstances, as demonstrated in the SQL examples below.

-- Adding clustered index through constraint

ALTER TABLE Sales.Currency ADD CONSTRAINT PK_Currency_CurrencyCode
PRIMARY KEY CLUSTERED (CurrencyCode ASC);

-- Adding clustered index without constraint

CREATE CLUSTERED INDEX IX_Person_LastNameFirstName ON Person.Person 
(LastName ASC,FirstName ASC);

Example 3: If you have a table Student_info with ROLL_NO as a primary key, a self-created clustered index on that primary key will sort the Student_info database according to ROLL_NO. The clustered index is similar to a dictionary in that the alphabetical sorting order is maintained and there is no separate index page.

CREATE TABLE Student_info
(
ROLL_NO int(10) primary key,
NAME varchar(20),
DEPARTMENT varchar(20),
);

insert into Student_info values(1410110405, 'H Agarwal', 'CSE') 
insert into Student_info values(1410110404, 'S Samadder', 'CSE')
insert into Student_info values(1410110403, 'MD Irfan', 'CSE') 

SELECT * FROM Student_info 

Output:

ROLL_NO		NAME		DEPARTMENT
1410110403	MD Irfan	CSE
1410110404	S Samadder	CSE
1410110405	H Agarwal	CSE

If we wish to establish a Clustered index on a different column, we must first delete the primary key, and then the prior index.

Example 4: We'll teach you how to use the Transact command in SQL to create a Clustered Index in this example. At the time of table creation, we will construct a Primary Key.

CREATE TABLE [CustomerRecord]
(
  [CustomerKey] [int] NOT NULL PRIMARY KEY,
  [FirstName] [varchar](50) NOT NULL ,
  [LastName] [varchar](50) NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [Profession] [nvarchar](100) NULL,
  [Yearly Income] [money] NULL
)

Output:

In SQL, an index is a particular table that is used to accelerate the search of data in database tables. It also regularly retrieves a large amount of data from the tables. The INDEX requires its own hard disc space. An index is simply a reference to data in a table.

While an index speeds up SELECT queries and WHERE clauses, it slows down data input in UPDATE and INSERT statements. There is no influence on the data when indexes are created or eliminated.

The index notion in SQL, for example, is the same as the index concept in a novel or a book. If you want to find all pages in a book that discuss a particular subject, go to the index, which lists all of the topics alphabetically, and then look for one or more particular page numbers.

For each value that appears in the indexed columns, an index creates an entry. In the database, each index name must be unique. In the same way that the UNIQUE constraint prohibits duplicate entries in the column or combination of columns on which the index exists, the index prevents duplicate entries in the column or combination of columns on which the index exists.

Create an Index

The CREATE INDEX statement is used to create an index. It permits you to name the index, provide the table and which column(s) to index, and define whether the index should be in ascending or descending order.

It is the most effective SQL approach for increasing query performance. Indexes have the disadvantage of slowing down the execution time of UPDATE and INSERT statements. They do, however, have one advantage in that they reduce the time it takes for SELECT and WHERE expressions to execute.

An index on the fields of the tables is generated in SQL. On a table, we can easily create one or more indexes. The data in the database is unaffected by the creation and deletion of the Index.

Syntax: Creating an Index:

CREATE INDEX index
ON TABLE column;

where the index is given a name, TABLE is the name of the table on which the index is constructed, and column is the name of the column to which it is applied.

For multiple columns:

CREATE INDEX index
ON TABLE (column1, column2,.....);

Example: Let's look at an example of how to create an index in SQL.

CREATE INDEX websites_idx
  ON websites (site_name);

In this example, we've created an index on the websites table called websites_idx. It consists of only one field - the site_name field.

There are advantages and disadvantages of using Index in our tables.

Advantages of Indexes

• Query optimization: Indexes make search queries and reports much faster.
• Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data.
• Text searching: With full-text indexes in MySQL, users can optimise searching for even vast volumes of text in any column that has been indexed as such.
• Fast Data Connectivity of data query, sorting, and grouping of results will be made possible with the suitable index.
• Reduce I/O and use the lowest I/O.

Disadvantages of MySQL indexes

When a new index is established on a table column, a separate file is generated. Only the field you're interested in sorting on was saved in that file.

The indexes, for starters, consume up disc space. Unless absolutely necessary, employing the Index will increase the extra disc cost. As a result, when we establish an index, it consumes disc space. However, because the index file would expand much faster than the data file due to the creation of indexes on every column in every possible combination, the index file would grow much faster than the data file. When a table has a huge table size, the index file may exceed the operating system's file size limit.

In general, indexes speed up Select queries while slowing down DML operations like INSERT, UPDATE, and DELETE. Indexes should not be used, especially in tables with a lot of DML operations.

AS Because MySQL must internally keep the pointers to the inserted rows in the real data file, there is a performance penalty in the case of the above-mentioned writing queries, because the indexes must be updated every time a record is altered.

If there are many non-clustered indexes, any updates to the clustered index will affect all non-clustered indexes.

The number of combinations the optimizer must assess during the optimization process will rise if there are too many indexes.

Each index contains statistical data, and the higher the index, the more statistical data there is.

Overhead update When data changes and there are a lot of new columns, numerous indexes may need to be updated.

When we utilize an index, the database is subjected to additional strain since the database's maintenance (index maintenance) load increases.

The ALTER INDEX command modifies an existing index's definition.

Note: Because the ALTER INDEX command isn't part of the ANSI SQL standard, its syntax differs from vendor to vendor.

Even though some databases provide ALTER and MODIFY INDEX instructions, it's quicker to drop and recreate an index. Oracle, Microsoft SQL Server, and PostgreSQL all allow ALTER INDEX. As a result, the ALTER INDEX command may have limits when it comes to deleting and re-creating indexes.

Syntax:

ALTER [UNIQUE] INDEX < index name> ON < table name> (< column(s)>);

To improve performance one might want to add indexes to columns:

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`column_name`)

Altering to add composite (multiple column) indexes:

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`col1`,`col2`)

You can access information from the database more quickly with indexes and restrictions, and you can be sure that the information is referentially accurate.

Indexes

Indexes are used to quickly locate records with certain column values. An index is a collection of ordered pointers to a table's rows. It can hold data from one or more table columns. By minimising the number of physical pages that the database needs access in order to read a row in the database, an index enhances data retrieval performance. If the appropriate column is shown in an index, there is no need to generate temporary storage for the ORDER BY clause because indexes keep data in order.

Without an Index, SQL would have to start search from first row and then read the entire table. This would take lot of time, of the data or table is huge. With Index, SQL could start the search from that point and save time. We can say Index is a table of pointers. Each row in Index table points to a row in the data table.

Consider an example of library, and you have to search for a book say “Chicken Soup for Soul”. If the library is not organized then just imagine what would you do, how would you start your search, where to first start. But if the data is organized like for example categorized may be on Starting letter or Author or Type of book, atleast you know from where to search.

Every index has a header, which contains the following information:

• The depth of the index
• number of leaf pages
• The selectivity factor

The PointBase RDBMS automatically creates and maintains indexes and delivers up-to-date information to the query optimizer.

Note: PointBase automatically constructs a unique index whenever you give a unique constraint.

The Build INDEX and DROP INDEX statements can also be used to create and drop indexes. Refer to "Optimizer Usage in PointBase" in this tutorial for further information on the query optimizer.

Constraints

The rules that define what values the table attributes can have are known as constraints. You may prohibit users from inserting invalid data into a column by using constraints. Consider the case of Employee and Department: if the organisation only has ten departments, you can limit the system or database to just accepting those ten department values. The user will not be able to proceed until the relevant department has been entered.

You can indicate whether an index should be produced in ascending or descending order when constructing it. This can be accomplished easily by defining an index with the key words ASC or DESC, as seen below.

You must explicitly set the option to sort columns in decreasing order when constructing the index. (The default is ascending order.)

It's worth noting that the order in which a column appears in the index affects the order in which the index keys appear, as well as the performance of queries with an ORDER BY clause.

Syntax:

primarily matters when used with composite indexes:

CREATE INDEX ix_index ON mytable (col1, col2 DESC);

can be used for either:

SELECT  *
FROM    mytable
ORDER BY
col1, col2 DESC

Example 1:

CREATE INDEX ON users (city DESC, name);

Example 2: Here is sample code that shows how to create indexes in descending or ascending order :

Create index in descending order:

CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] DESC )

Create index in ascending order:

CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC ) 

An index is a lookup table associated with a table or view that the database uses to improve the speed of data retrieval. In an index, keys are kept in a structure (B-tree) that allows SQL Server to fast and effectively retrieve the row or rows associated with the key values. If the table has a primary key and a unique constraint, an index is produced automatically.

There are two types of index:

• Clustered Index - When a table with primary key constraints is generated, the database engine creates a clustered index automatically. Sort or store this data in a table or view based on its key and values.
• Non-Clustered Index - When a table is created with UNIQUE constraints, the database engine creates a non-clustered index automatically. The nonclustered index key values are stored in a nonclustered index, and each key value entry contains a pointer to the data row containing the key value.

The data for the rows is kept in a clustered index, which is a table. If a table column contains a primary key, MySQL creates a clustered index named PRIMARY in a relational database.

Non-clustered indexes are those that are not PRIMARY indexes (clustered indexes). Secondary indexes are non-clustered indexes that are not clustered.

Difference between Clustered and Non-clustered index :

A single-column index is a table index that is based on the values in only one column. A multicolumn index is a table index that is depending on the values of many columns.

Single-column indexes and multicolumn indexes are the two types of indexes.

(1) Creating a single-column index

When retrieval will be performed with only one column as the key, a single-column index should be built.

(2) Creating a multicolumn index

In the circumstances listed below, a multicolumn index should be generated.

Syntax:

For single columns:

CREATE INDEX index_name
ON table_name (column_name);

For multiple columns:

CREATE INDEX index
ON TABLE (column1, column2,.....);

Example: Creating an index on one column example

Suppose, you often want to look up members by the last name and you find that the query is quite slow. To speed up the lookup, you create an index for the last_name column:

CREATE INDEX members_last_name_i 
ON members(last_name);

Creating an index on multiple columns example

The following example creates an index on both last name and first name columns:

CREATE INDEX members_name_i
ON members(last_name,first_name);

To remove rows from a nonclustered index, use the Index Delete operation. Rows returned by the operator's child subtree identify the rows to be destroyed; these rows must include at least all columns indexed by the target index. Index Delete operators are normally only used in wide update plans; if the optimizer decides to employ a narrow update plan, it will use the Clustered Index Delete or Table Delete operator to change all nonclustered indexes.

In the execution plan XML, a columnstore index-targeting Index Delete operator is represented as a standard Index Delete operator with the Storage subproperty of the Object property set to 'ColumnStore.' However, in the graphical execution plan, SSMS (and most other tools) display this operator as a Columnstore Index Delete operator. When the Storage property is set to 'RowStore,' Index Delete is activated, and the essential differences are highlighted on a separate Columnstore Index Delete page.

To the best of our knowledge, an Index Delete operation cannot be used to delete a memory-optimized index. For deleting data from a memory-optimized table, all execution plans utilize a narrow plan, with only one Table Delete operator per row to modify the data for all indexes.

Example: To delete an index:

In Object Explorer, connect to an instance of Database Engine.

USE AdventureWorks2012;  
GO  
-- delete the IX_ProductVendor_BusinessEntityID index  
-- from the Purchasing.ProductVendor table  
DROP INDEX IX_ProductVendor_BusinessEntityID   
    ON Purchasing.ProductVendor;  
GO  

The ALTER INDEX command is used to disable an index. One index can be deleted at a time, or all the indexes associated with a table or view can be deleted in one command. The index's status will be changed from valid to useless.

The ALTER INDEX statement is used to disable all indexes of a table in the following format:

While you disable an index, the query optimizer won't take it into account when generating query execution plans.

SQL Server preserves the index definition in the metadata and the index statistics in nonclustered indexes when you disable an index on a table. If you disable a nonclustered or clustered index on a view, SQL Server will destroy all of the indexes.

If you disable a table's clustered index, you can't use data manipulation language like SELECT, INSERT, UPDATE, or DELETE to access the table's data until you rebuild or drop the index.

The ALTER INDEX command does not have an ENABLE option. When you disable an index, SQL stops updating it. That is to say, the data there is absolutely false (or more importantly SQL has no idea what parts of it are or are not valid). So rebuilding it is the only way to get it operating again.

Syntax:

To disable an index, you use the ALTER INDEX statement as follows:

ALTER INDEX index_name
ON table_name
DISABLE;

Example 1: Disabling an index example:

Let’s take some examples of disabling indexes to have a better understanding.

This example uses the ALTER INDEX to disable the ix_cust_city index on the sales.customers table:

ALTER INDEX ix_cust_city 
ON sales.customers 
DISABLE;

Example 2: To disable an index in SQL Server use the following command:

USE Database_Name
GO
ALTER INDEX Index_Name ON Table_Name DISABLE;
GO

Example 3: Rebuild the disable an index:

ALTER INDEX ix_DisableIndexes_Col1 ON DisableIndexes REBUILD;
GO

SELECT name, is_disabled 
FROM sys.indexes 
WHERE object_id = object_id('DisableIndexes');
GO

Example 4: The clustered index associated with the students table will be disabled in this first example using the command below. The command disables the index on the table as well as the indexes on the view students_view for the table, as we can see from the output.

ALTER INDEX PK_students
ON students
DISABLE;

Warning: Clustered index 'ix_students_view' on view 'students_view' referencing table 'students' was disabled as a result of disabling the index 'PK_students'.

Warning: As a result of disabling the clustered index on the view, index 'ix_nc_students_ view' on view'students view' was disabled.

We'll run a SELECT query on the table after disabling the index to select all rows from the table. The query fails because the query processor is unable to plan how to execute the query, as seen below.

Example 5: Disable Index using UNUSABLE

ALTER INDEX STU_LN_IDX UNUSABLE;

Output:

index STU_LN_IDX altered.

DROP INDEX deletes one or more indexes from the current database. To run this command, you must be the index's owner.

Use the DROP INDEX command to remove a non-primary key index.

Syntax:

Here is the syntax of the DROP INDEX statement:

DROP INDEX [IF EXISTS] index_name
ON table_name;

or

drop index [if exists] table_name.index_name;

index_name Name of the index for the table.

In this syntax:

• After the DROP INDEX clause, enter the name of the index you want to remove.
• Second, type the name of the table that the index belongs to.
• If you use the if exists keyword, you can delete an index only if it already exists.

Example 1: if you want to drop index order_id for table orders(id, order_date, amount), then here is the SQL query for it.

drop index orders.order_id;

Example 2: The statement bellow will create an INDEX TABLE 'test_index':

CREATE INDEX test_index
ON agents (agent_code ASC);

If we want to remove the index table 'test_index' of the base table 'agents', the following SQL statement can be used :

DROP INDEX test_index;

Example 3: This statement uses the DROP INDEX statement to remove the ix_cust_email index from the sales.customers table:

DROP INDEX IF EXISTS ix_cust_email
ON sales.customers;

If you check the indexes of the sales.customers table, you will see that the ix_cust_email index was deleted.

Example 4: To drop a primary key index, use the ALTER TABLE .. DROP CONSTRAINT command :

alter table products
drop constraint pk_products;

index fragment

The task of database maintenance includes identifying index fragmentation and maintaining the indexes. Your tables and indexes become fragmented over time when records are inserted, updated, and deleted. This fragmentation can cause your SELECT queries, as well as your INSERT, UPDATE, and DELETE actions, to perform poorly.

The index fragmentation value is a percentage value that may be retrieved using SQL Server DMV. Users can maintain indexes by adjusting the fragmentation percentage using the Rebuild or Reorganize operations, based on the index performance value.

Internal data file fragmentation is known as index fragmentation. Database Architecture, Database Design, and Query Writing are the three most important factors in ensuring that your database runs quickly. The database engine's query performance is always boosted by good index design and maintenance.

The goal of building SQL indexes on database tables is to improve query performance by speeding up data retrieval procedures for queries that read data from the base table. These transactional tables, on the other hand, are dynamic and change often over time. Adding new records, changing existing records, and deleting existing records are all examples of database table updates. The table index will become fragmented over time when these modifications are copied to the relevant table indexes.

When indexes have pages where the logical ordering based on the key value does not match the physical ordering inside the data file, this is known as fragmentation. An index's leaf pages all have links to the index's next and prior pages. This creates a list of all index/data pages that is doubly linked. The physical order of the pages in the data file should, ideally, correspond to the logical order. When the physical ordering of the data matches the logical ordering of the data, overall disc throughput increases dramatically. For certain sorts of queries, this results in significantly improved performance. Because the disc head must go back and forth to gather the index pages instead of scanning forward in one direction when the physical ordering does not match the logical ordering, disc throughput can suffer. This is how I/O performance is affected by fragmentation.

The first step in fixing the fragmented index performance problem is to determine which indexes are fragmented. This is when the SQL Index Fragmentation test comes in handy. This test looks for high and extremely high levels of fragmentation in a Microsoft SQL server's indexes and provides the number of fragmented indexes. You can rapidly drill down to the precise indexes that have been fragmented using the test's thorough diagnosis capability. As a result, you can proceed to defragment/rebuild the impacted indexes in order to enhance disc throughput and overall SQL performance.

SQL Server Index Fragmentation Overview

There are two main types of SQL index fragmentation; Internal and External Fragmentation.

Internal Fragmentation is caused by entering a new record or changing an existing record with values that do not fit in the currently available free space on the data page, causing the page to be divided into two pages to accommodate the new value. In this situation, to maintain the SQL index tree balanced, the SQL Server Engine will shift around half of the data from the current page to the new data page formed by the split operation. Random delete operations that result in empty space on the data pages can also create internal fragmentation. Due to the need to read more data pages to retrieve the necessary data, this type of action leaves the data pages blank, resulting in a higher SQL Server index size and poorer performance.

Because the logical order of the SQL Server index does not match the physical order on the underlying disc drive, External Fragmentation occurs when the data pages of the SQL Server index are distributed over the database physical file. Random delete operations that leave a number of extent pages empty while the extent itself is reserved can create external fragmentation. This sort of fragmentation degrades performance since jumping between the scattered data pages to retrieve the desired data from the underlying disc drive takes longer and consumes more resources.

Example: Find Index Fragmentation status using the T-SQL statement:

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

SQL Index Defragmentation

Index defragmentation is a SQL Server maintenance procedure that optimises the indexes of the database engine's insert, update, and delete operations to reduce fragmentation. Reorganizing or rebuilding an index on a full index or a particular division does this.

All insert, update, and delete operations done on databases are indexed by SQL Server. These indexes on the database server's discs can get fragmented or stored out of order over time. This consumes disc space and can cause database access activities to slow down. SQL Server, fortunately, includes tools for detecting and repairing fragmentation.

By rearranging or rebuilding an index, the indexes are defragmented. Specific tables in a database can also have their indexes rebuilt. Instead of being spread out across numerous drives and partitions, defragmentation ensures that all of the entries in an index are contiguous for faster and more efficient access. Defragmenting files on a hard drive is similar.

Defragmentation is the process of resolving index fragmentation that is considered severely fragmented and causes slowness. We basically have two options for removing fragmentation.

1) Rebuild Index

The database indexes are entirely dropped and recreated when you rebuild indexes. Internally, rebuilding entails copying, renaming, and deleting the previous version. Since you're rebuilding and realigning the physical and logical order of the index rows in continuous pages from scratch, this is one of the activities you'll execute to defragment your indexes. Fragmentation is removed, pages are compressed, and disc space is freed up.

2) Reorganize Index

The index rearrangement method reorganises the index's leaf nodes physically. The system resources required to rearrange an index are modest. It physically reorders the leaf-level pages to match the logical, left to right, order of the leaf nodes in clustered and nonclustered indexes on tables and views to defragment the leaf level of clustered and nonclustered indexes on tables and views. The index pages are also compacted as a result of the reorganisation.

Reorganizing indexes, unlike rebuilding indexes, does not necessitate additional disc space but does necessitate log space. Because rebuilding indexes is a fully logged activity, the log might get quite large. The amount of log usage for a rearrange may exceed the index's size, depending on how much rearranging and compaction is done, as well as the extent of fragmentation scattered throughout the index.

The efficiency of the query in SQL Server can be considerably improved by creating an index on the column that is utilised in the GROUP BY clause.

Two separate group by algorithms are used in SQL databases.

The hash algorithm, for example, groups the input records into a temporary hash table. The hash table is returned as the result once all input entries have been processed.

The sort/group method sorts the input data by the grouping key first, so that the rows of each group follow each other in a sequential order. After that, all the database has to do is aggregate them. Both algorithms, in general, need the materialisation of an intermediate state, hence they are not pipelined. Nonetheless, the sort/group method can use an index to skip the sorting step, allowing for a pipelined group by.

Example 1: Consider the following query. It delivers yesterday’s revenue grouped by PRODUCT_ID:

SELECT product_id, sum(eur_value)
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 GROUP BY product_id

Example 2: Let’s understand this with an example.

Click the Estimated Query Execution plan option to display the query execution plan, as shown below. In a later article, we'll look at SQL Server's Query Execution Plan. For now, all we need to know is how to view the query execution plan in SQL Server.

SELECT PS.ProductID, 
  SUM(PS.QunatitSold) AS TotalQuantitySold
FROM  ProductSales AS PS  
GROUP  BY PS.ProductID

The Generate INDEX statement in PROC SQL or the MODIFY and INDEX CREATE statements in the DATASETS procedure are used to create indexes. Indexes have the SAS member type INDEX and are kept in specialised members of an SAS library. If you make a modification to the underlying data, the values saved in an index are automatically updated.

You can run a set of queries on your database, record the time it takes for those queries to finish, and then start constructing indexes and rerunning your tests to see whether indexes will start to reduce query times.

In MySQL, database indexes allow you to speed up the execution of SELECT query queries. An index isn't very useful for short tables. Indexes, on the other hand, can drastically enhance performance if you have tables with a lot of data.

The following behaviours all point to the fact that indexes may be beneficial to tables:

• Your website takes a long time to load or does not load at all.
• A database connection is not possible for an application.
• The queries in the database are backed up.
• Indexes can help some types of retrievals work better.

When an indexed column is compared to a constant value in a WHERE statement, for example, the index may help the query perform better. Indexing the column given in a correlated reference to an outer table enhances the performance of a subquery (and thus the query).

Composite indexes can help queries that compare the columns mentioned in the composite index with constant values connected using the AND operator run better.

For example, if you have a compound index in the columns CITY and STATE, and the WHERE expression is specified as WHERE CITY='xxx' AND STATE='yy', then the index can be used to select that subset of rows more efficiently. Indexes can also benefit queries that have a WHERE clause in this form:

where var1 in (select item1 from table1) ...

The values of VAR1 from the outer query are found in the inner query by using the index. An index can improve the processing of a table join, if the columns that participate in the join are indexed in one of the tables. This optimization can be done for equijoin queries only—that is, when the WHERE expression specifies that table1.X=table2.Y.

Example : To do this, try using the EXPLAIN ANALYZE clause in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM friends WHERE name = 'Blake';

Because not all indexes will save query time, just construct one at a time.

• Because PostgreSQL's query planning is fairly efficient, adding a new index may have no effect on query performance.
• Adding an index will always result in additional data being stored.
• Adding an index to your database will lengthen the time it takes for it to fully update following a write transaction.
• You can easily remove an index from the database if adding it does not reduce query time.

Non-key columns, which are specified in the along with statement, are not counted in the total number of columns. By removing the need to consult numerous indexes or the primary table, the inclusion of non-key columns speeds up query processing. It accomplishes this by include all relevant column data in the index. Normally, SQL Server gets part of the needed column values from the index, but it must perform a key lookup on the main table or the default clustered index for the remaining column values, which is resource and time intensive. That extra work is eliminated with an index that includes the relevant non-key columns.

Non-key columns can include columns that could not previously be used in queries, such as nvarchar(max). Text, picture, and ntext data types, on the other hand, cannot be used in included columns. You can also utilise computed columns. While the column cannot be removed while it is an Included Column, it can be replaced with up to 1023 non-key columns.

The biggest benefit is that it eliminates key lookups, which improves efficiency by covering all of the fields in the query.

Syntax:

The syntax for creating an index with included column is as follows:

CREATE INDEX Indexname on table
(Key column)
INCLUDE (column1, column2)

Example 1: We will use the sales.customers table from the sample database for the demonstration.

CREATE UNIQUE INDEX ix_cust_email 
ON sales.customers(email);

This statement finds the customer whose email is 'aide.franco@msn.com':

SELECT customer_id, 
    email
FROM    
    sales.customers
WHERE 
    email = 'aide.franco@msn.com';

Example 2: SQL Server Index with Included Columns Simple:

Consider the customer table, which provides basic customer information. Below is a representation of the table. The primary key in the table is the custid column, which has a default clustered index attached to it. The email column, which is often requested and on which we will build our instance, includes customer email ids.

custid	fname	lname		city		contact		email
101	Brian	Posey		New York	NULL		bposey@gmail.com
102	Sarah	Parker		New York	16616165325	sparker@gmail.com
103	Stephen	George		Washington	NULL	s	g_wash@msn.com
104	James	Mare		New York	16277212992	jjameson19@gmail.com
105	Angela	Crawford	Washington	16633775159	angela2angel@msn.com
106	Selena	Spears		Detroit		NULL		spearss01@gmail.com
107	Patty	Campbell	Detroit		16107575525	camped_patty@msn.com
108	Brandon	Powell		Seattle		NULL		powellbb@gmail.com
109	Kim	Fox		Washington	16529929936	kimkimmy@gmail.com
110	Edith	Poe		Seattle		16767335231	epepp@msn.com

Table: Customer

Suppose we generate the estimated query execution plan for the below query to fetch a row of information for a particular customer with the email id ‘angela2angel@msn.com’.

SELECT fname, lname, emailFROM customerWHERE email='angela2angel@msn.com';

The query execution plan will be as below. We can see from it that it does an index scan of the default primary index which is the only index available.

In a nonclustered index, the Index Insert operator is used to insert rows. The operator's child subtree generates the rows to be inserted and returns them to its parent. Index Insert operators are normally only used in wide update plans; if the optimizer decides to employ a narrow update plan, it will use the Table Insert or Clustered Index Insert operator to edit all nonclustered indexes.

In the execution plan XML, a columnstore index-targeting Index Insert operator is represented as a standard Index Insert operator with the Storage subproperty of the Object property set to 'ColumnStore.' However, in the graphical execution plan, SSMS (and most other tools) display this operator as a Columnstore Index Insert operator.

I'll focus the discussions below on how Index Insert works when the Storage attribute is 'RowStore,' and call out key differences on the separate Columnstore Index Insert page, to match the representation of the execution plan that most people use.

As far as we know, an Index Insert operator cannot target a memory-optimized index. All entering data into a memory-optimized table execution plans employ a narrow plan, with only one Table Insert operator per row to edit the data for all indexes.

SQL query to insert entries into a table with a clustered index on TRACKING_NUMBER field.

Example:

INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) 
SELECT TRACKING_NUMBER, COL_B, COL_C 
FROM STAGING_TABLE

A materialised view is JOIN INDEX. An index join is a technique that uses the intersection of two or more indexes to entirely satisfy a query.

Its definition is saved indefinitely, and the data is updated anytime the base tables to which the join index refers are updated. One or more tables may be included in the JOIN INDEX, as well as pre-aggregated data. These are mostly used to boost performance.

If a table has numerous small indexes rather than a huge covering index, the optimizer can satisfy a query by using index intersection on these small indexes. It must conduct logical reads on all indexes utilised to serve the query during index intersection.

Index intersection can be utilized by sql server to fetch the whole data to be returned if the querying table is huge and has numerous narrow indexes instead of one broad index.

A nested loops join operates by:

• For each row in an outer table
• Find matching rows in an inner table

An index on the inner table is useful for quickly finding matched rows and actually having to scan the entire table for each row from the outer table.

A merge join requires two inputs that are ordered by the join predicate's column(s). This order can be provided via an index without the need to sort first.

There are different types of join indexes available.

• Single Table Join Index (STJI)
• Multi Table Join Index (MTJI)
• Aggregated Join Index (AJI)

Syntax:

Following is the syntax of a JOIN INDEX.

CREATE JOIN INDEX < index name> 
AS 
< SELECT Query> 
< Index Definition>;

Example 1: Creates a Join index named Employee_JI on Employee table.

CREATE JOIN INDEX Employee_JI 
AS 
SELECT EmployeeNo,FirstName,LastName, 
BirthDate,JoinedDate,DepartmentNo 
FROM Employee 
PRIMARY INDEX(FirstName);

Example 2: Demonstrate the index join, i am going to create two narrow nonclustered indexes on two different columns of this table as below:

CREATE NONCLUSTERED INDEX noncidx_DemoTable_EmpName ON DemoTable(EmpName)
 
CREATE NONCLUSTERED INDEX noncidx_DemoTable_EmpAddress ON DemoTable(EmpAddress)

A table's index can be established on more than one column. Composite INDEX is created using many columns from a database table.

Multicolumn indexes (sometimes called composite indexes) are similar to regular indexes in that they have many columns. A sorted array with values produced by concatenating the values of the indexed columns can be thought of as a multiple-column index. Due to their capacity to go through data more quickly, multi-column indexes can accomplish even greater reductions in query time.

They both keep a "table" of sorted references to the main table. Multicolumn indexes, on the other hand, can store sorted links to other columns.

Multiple-column indexes in MySQL can be used for queries that test all of the index's columns, or queries that only test the first column, the first two columns, the first three columns, and so on. A single composite index can speed up various types of queries on the same table if the columns are specified in the appropriate order in the index description.

Note: You can use a column that is "hashed" based on data from other columns as an alternative to a composite index. This column may be faster than a "broad" index on multiple columns if it is small, relatively unique, and indexed.

Syntax:

CREATE INDEX [index name]
ON [Table name]([column1, column2, column3,...]);

Example 1:

CREATE INDEX index_userinfo
ON userinfo (no, name);

Example 2: For example, if you have a table of this form:

CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

(say, you keep your /dev directory in a database...) and you frequently issue queries like:

SELECT name FROM test2 WHERE major = constant AND minor = constant;

Then it might be appropriate to define an index on the columns major and minor together, e.g:

CREATE INDEX test2_mm_idx ON test2 (major, minor);

Example 3: Multicolumn indexes work similarly to traditional indexes. You can see in the gifs below how using a multicolumn index compares to using both a sequential table scan and a traditional index scan for the following query:

SELECT * FROM myTable
WHERE year=2017
AND make='ACURA'
AND model='TL';

Example 4: In MySQL, it is very easy to use this extra column:

SELECT * FROM tbl_name
  WHERE hash_col=MD5(CONCAT(val1,val2))
  AND col1=val1 AND col2=val2;

You can use letters, numbers, spaces or punctuation in your index name, with the exception of the following characters: /, ~, ,, \, `, &, |, ;, $, and *.

The index name must be less than 256 characters long.

The index name can not end with one the following extensions:

• .bin
• .idx
• .txt

Additionally, it is not a good idea to give your index a name that includes the word ad or advertisement.

• IX_ for non clustered non unique indexes
• UX_ for unique indexes
• _pkey for a Primary Key constraint
• _key for a Unique constraint
• _excl for an Exclusion constraint
• _idx for any other kind of index
• _fkey for a Foreign key
• _check for a Check constraint

Syntax for all of my index name take the form of:

< index or key type>_< table name>_< column 1>_< column 2>_< column n>

Indexes are new and evolving (additional features are being introduced), however they are currently limited to single tables and are not compatible with external tables. A distinct table is created when an index is created. Partitioning indexes is possible (matching the partitions of the base table). Indexes are used to speed up data searches in tables.

At the hdfs level, partitions separate data by creating sub-directories for each partition. Partitioning allows you to limit the number of files read and the amount of data searched in a query. However, partition columns must be provided in your WHERE clauses for this to happen.

Based on the size of your data and your expected results, you can assess the appropriate use of indexes and/or partitions while constructing your data model.

There are two basic types of partitioned index.

• Local - A single table partition will correlate to all index entries in a single partition (equipartitioned). They support partition independence and are generated with the LOCAL keyword. Oracle can be more efficient when creating query plans thanks to equipartioning.
• Global - Multiple table partitions may be represented by a single index in a single partition. The GLOBAL keyword is used to construct them, and they do not support partition independence. Global indexes can only be range partitioned, and they can be partitioned in a way that makes them appear equipartitioned, but Oracle won't use this structure.

Both types of indexes can be subdivided further.

• Prefixed - The partition key is the index's leftmost column(s). It is less expensive to investigate this form of index. If the partition key is specified in the where clause of a query, partition pruning is available, meaning that not all partitions will be searched.
• Non-Prefixed - Doesn't allow partition pruning, although it's good for getting data from numerous partitions. When you want the index to be partitioned on the same key as the underlying table, you can use this method to index a column that isn't the table's partition key.

It does not offer the total size of all indexes on a table, but it does give the total size of all indexes on a table.

Example 1: Uses sys.indexes and sys.dm_db_partition_stats DMF to calculate the size of individual index on a table.

Ensure a USE statement has been executed first.

SELECT i.[name] AS IndexName
    ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
    AND s.[index_id] = i.[index_id]
GROUP BY i.[name]
ORDER BY i.[name]
GO

Example 2: View the size of the indexes by using the following query:

SELECT `database_name`, `table_name`, 
`index_name`, FORMAT(SUM(`MEMORY_USE`) / 1e+6, 0) memoryUseMB
FROM `information_schema`.INDEX_STATISTICS
GROUP BY 1, 2, 3;

Output:

+---------------+------------------+-------------+-------------+
| database_name | table_name       | index_name  | memoryUseMB |
+---------------+------------------+-------------+-------------+
| < database>    | < table>          | PRIMARY     | 1,342       |
| < database>    | < table>          | < indexName> | 839         |
+---------------+------------------+-------------+-------------+
2 rows in set (0.12 sec)

Temporary Table

Temporary tables allow you to add indexes explicitly after they are created, and they can also have implicit indexes that are created as a result of the Primary and Unique Key constraints. Both directly and implicitly, you can add Indexes to the Temporary Table.

The option to add either a clustered or non clustered index to a temp table (#temp) is one of its most significant features. Furthermore, #temp tables allow for the creation of auto-generated statistics against them. When determining cardinality, this can assist the optimizer.

On temporary tables, indexes can be defined. These indexes can increase the performance of tempdb queries in many circumstances. These indexes are used by the optimizer in the same way as indexes on regular user tables are used.

Giving an index for the optimizer can improve speed dramatically, especially in complex procedures that build temporary tables and then execute many actions on them.

The only requirements are:

When the index is built, the table must have data in it. Adaptive Server does not create column statistics such as histograms and densities if the temporary table is created and the index is created on an empty table. The optimizer has incomplete statistics if you insert data rows after building the index.

Example 1: Create Temporary Table

CREATE TABLE #Employee
(
  Id INT PRIMARY KEY CLUSTERED,
  FirstName NVARCHAR(50),
  LastName NVARCHAR(50)
)

--Add Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_#Employee_FirstName 
      ON #Employee(FirstName)
GO

Example 2: Below is an example of creating both a clustered and non-clustered index on a temp table.

CREATE TABLE #Users
(
ID int IDENTITY(1,1),
UserID int,
UserName varchar(50)
)

INSERT INTO #Users
(
UserID,
UserName
)
SELECT
UserID = u.UserID
,UserName = u.UserName
FROM dbo.Users u

CREATE CLUSTERED INDEX IDX_C_Users_UserID ON #Users(UserID)

CREATE INDEX IDX_Users_UserName ON #Users(UserName)

Although you can establish a clustered index on a table variable (@table) by providing a primary key or unique constraint, using a temp table is usually more efficient. Recompilation is a disadvantage, but it can be avoided by using the (Keep Plan) or (Keep Fixed Plan) hints.

SQL indexes are nothing more than a method of lowering query costs. The query's performance degrades as its cost rises. The primary goal of query tuning is to lower the cost of a query through indexing, reducing full table scans, and reducing the time it takes to retrieve records from a query.

There are following types of SQL Indexes:

1. Normal index
2. Unique Index
3. Bit Map Index
4. Composite Index
5. B-Tree Index(Oracle considered Normal indexes as B-Tree Indexes)
6. Function Based Index
7. Clustered Index
8. Non-Clustered Index.

1. Single-Column Indexes

A single-column index is created based on only one table column. The basic syntax is as follows.

CREATE INDEX index_name
ON table_name (column_name);

2. Unique Indexes

Not only are unique indexes utilized for performance, but they are also employed to ensure data integrity. A unique index prevents duplicate values from being stored in the table. The basic syntax is as follows.

CREATE UNIQUE INDEX index_name
on table_name (column_name);

3. Composite Indexes

A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name
on table_name (column1, column2);

Determine the column(s) that you may utilise frequently as filter criteria in a query's WHERE clause when deciding whether to establish a single-column index or a composite index.

A single-column index should be utilized if only one column is used. The composite index is the best option if there are two or more columns that are regularly utilized as filters in the WHERE clause.

4. Implicit Indexes

Implicit indexes are indexes that the database server creates automatically when an object is created. For primary key constraints and unique constraints, indexes are produced automatically.

5. CLUSTERED INDEX

• At the leaf node, a Clustered Index stores the actual data.
• The values of the Clustered Index are sorted ascending or descending. As a result, they do not require additional storage.
• Because it does not require an extra lookup step, a Clustered Index is faster than a Non-Clustered Index.
• The Clustered Index Scan obtains all of the table's rows.
• The data in Clustered Index Seek is real. It pulls certain rows from the table.
• When a table does not have a clustered index, it is referred to as a heap.

6. NON-CLUSTERED INDEX

• Actual data is not included in the Non-Clustered Index. The data is retrieved using a clustered index.
• The value from an indexed column and RowId that point actual data are stored in the leaf node of the non-clustered index.
• If there are no indexes, a RowId points to the heap table. RowId points to the clustered index key and finds the records if we have a clustered index.
• The value of the Non-Clustered Index cannot be sorted. We can, however, establish many non-clustered indexes.

7. COLUMNSTORE INDEX

• In the OLAP system, the Columnstore Index is used. This sort of index used a column-based format to store index data.
• Column store indexes have the advantages of lower storage costs and improved performance.
• If we can create a basic aggregation query, we can assume that reading the column store index fulfilled the entire query.

8. CLUSTERED COLUMN STORE INDEX

• Clustered column store indexes are always the primary storage technique for their entire table and are updateable.
• They can't be used in conjunction with other indexes, and they don't hold columns in any specific sequence.

9. NON-CLUSTERED COLUMN STORE INDEX

• A column store index that isn't clustered is a subset of columns.
• They require more storage to store a copy of a column in the index and must be rebuilt or partitioned to be updated.
• To enhance compression, a non-clustered column store index can be paired with other tables and physically store columns in a specific order.

10. COVERING INDEX

In SQL Server, an indexed view is a view on which a single clustered index is generated. A clustered index is created on a view so that it can be stored in the database like any other table. The unique clustered index is saved and updated like any other clustered index in SQL Server.

When compared to ordinary views, which involve complicated processing of huge numbers of rows, such as aggregating big amounts of data or merging many rows, an indexed view is more important. Even queries that don't use the indexed view directly can benefit from the clustered index created by the view. If we establish an indexed view, every time we edit data in the underlying tables, SQL Server must not only preserve the index entries on those tables, but also the index entries on the view.

The optimizer in SQL Server developer and enterprise versions can use view indexes to optimise queries that don't specify the indexed view. To gain the benefit of the index on the view in other SQL Server editions, the query must contain the indexed view and use the hint NOEXPAND.

Assume you've created a view on numerous tables with a lot of data, a lot of joins, and a lot of aggregate functions. In that situation, the indexed views should be used.

Because the results of an indexed view are physically saved in the database, you don't have to query the underlying tables every time you need to access the data.

The indexed views increase the performance of the database queries if used in appropriate scenarios.

However, indexed views should not be used in situations where the data in the underlying tables needs to be changed often. Because, in this case, when the data in the underlying tables is often changed, the indexed view is also updated. The database's performance will suffer as a result.

When you create a unique clustered index on a view, it becomes an indexed view. The clustered index is stored in SQL Server and updated in the same way as any other clustered index, giving SQL Server another area to look for ways to improve a query that uses the indexed view.

Non-clustered indexes can be constructed on the view if your queries would benefit from having more than one index. This would provide the optimizer with extra options for speeding up queries that reference the view's columns.

Syntax:

The following is the syntax to create an indexed view in SQL Server:

CREATE VIEW < view name>   
WITH SCHEMABINDING   
AS  
SELECT Column1, Column2, Column3   
FROM < table name>
GO  
CREATE UNIQUE CLUSTERED INDEX < index name> ON < view name>(Column1)  
GO

Example 1: The first step, the creation of a typical view that can be indexed to gain performance. (This example assumes that works_on is a very large table.)

USE sample;
GO
CREATE VIEW v_enter_month
	WITH SCHEMABINDING
	AS SELECT emp_no, DATEPART(MONTH, enter_date) AS enter_month
	FROM dbo.works_on;

Example 2: To create an indexed view, first we need to create a view with schema binding option and after this create an index on view.

We will create an indexed view for above table. First we create a view on Employee table,

CREATE VIEW VW_Employee WITH SCHEMABINDING AS    
SELECT e.Emp_Id,e.EmployeeName,e.EmpSalary,e.StateId,e.CityId    
FROM dbo.Employee e    
WHERE e.EmployeeName LIKE '[A-P]%'    

Create a view with the schemabinding option in the query above. It is critical for an indexed view to be constructed with the schemabinding option. The schemabinding option ensures that the underlying tables cannot be changed in any way that would have a meaningful impact on the indexed view unless the view is first changed or discarded. The error "Cannot construct index on view 'VW_Employee' because the view is not schema bound" will be thrown by SQL Server if we try to create an indexed view without the schemabinding option.

In SQL Server, a Primary Key and a Clustered Index are always the same. The behaviour is the same by default, but you can modify it if you like.

Only one Primary Key can be created in SQL, and it builds a Clustered Index on the table by default, with the PK column as the Index key. However, you may also use a PK to construct a Non-Clustered Index.

INDEX

On the other hand, an index isn't a constraint; it just helps you structure the table or selected columns so that you can obtain results faster when using the SELECT statement.

An index is a physical idea that helps you find rows quicker, but it's not meant to create table rules.

Indexes are a specific form of file that includes reference pointers to all records in the data table (the index on the InnoDB data table is an essential component of the table space).

An ordinary index's sole purpose (as stated by the keywords KEY or INDEX) is to speed up data access.

Indexes like INDEX (columnA, columnB), which is a joint index, can encompass several data columns.

Indexes can considerably enhance the speed of data queries, but they will slow down inserting, removing, and updating tables since you must also operate the Index file when conducting these writing operations.

PRIMARY KEY

A logical idea is a primary key. The main key is made up of the column(s) that identify the rows.

A Primary Key is a constraint or rule that ensures that a table's column is uniquely identified and that it contains a value, i.e. it is NOT NULL.

A primary key for a disk-based table is always implemented as an index in SQL Server. The main key might be implemented as an index or a hash in a so-called memory-optimized database.

A primary key is a one-of-a-kind index. In a table, only one primary key index can be specified. The keyword PRIMARY KEY is used to generate the primary key, which is used to uniquely identify a record.

Primary keys are joined together to form a composite primary key.

When your primary key table has more than one field, it's called a composite primary key.

Indexes in SQL Server mainly are:

• Clustered Index
• Non Clustered

you can specify them as unique or non-unique.

Other type of indexes are:

• ColumnStore
• Filtered
• XML
• Spatial
• Full Text

Example: Create the Primary Key constraint on an ever-increasing:

key column

CREATE TABLE Foo2
(
	Col1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
	Col2 UNIQUEIDENTIFIER NOT NULL,
	Col3 INT NOT NULL
)
GO

-- Create the Clustered Index on a random key column

CREATE UNIQUE CLUSTERED INDEX ci_Col2 ON Foo2(Col2)
GO

When you look again at sys.indexes, you can see now that you have created a Clustered and Non-Clustered Index. But only the Non-Clustered Index is used to enforce the Primary Key constraint.

INDEX

An index is a performance-tuning method for retrieving records faster. For each value that appears in the indexed columns, an index creates an entry.

Indexes are pointers that correspond to a data's physical address. As a result, data manipulation gets faster when indexes are used.

An index is a data structure that the database employs to find records in a table more quickly, as the name implies. Each index keeps a list of values within that field that are sorted in ascending or decreasing order. Indexes are created on one or more columns of a table; each index maintains a list of values within that field that are sorted in ascending or descending order. Instead of sorting records by field or fields during query execution, the system can simply access rows in the index order.

Syntax:

Creating an Index:

CREATE INDEX index
ON TABLE column;

where the index is given a name, TABLE is the name of the table on which the index is constructed, and column is the name of the column to which it is applied.

Example: Let's look at an example of how to create an index in SQL.

CREATE INDEX websites_idx
  ON websites (site_name);

In this example, we've created an index on the websites table called websites_idx. It consists of only one field - the site_name field.

VIEW

A logical table is called a view. It is a physical item that logically stores data. The term "view" simply refers to data that has been tored in base tables.

A logical entity is a view. It's a SQL statement that's saved in the system tablespace of the database. The data for a view is stored in the TEMP tablespace in a table produced by the database engine.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

• view_name: Name for the View
• table_name: Name of the table
• condition: Condition to select rows

Example: One of the most important uses of views is in large multi-user systems, where they make it easy to control access to data for different types of users. As a very simple example, suppose that you have a table of employee information on the scheme Employees = {employeeID, empFName, empLName, empPhone, jobTitle, payRate, managerID}. Obviously, you can’t let everyone in the company look at all of this information, let alone make changes to it.

CREATE VIEW phone_view AS
 SELECT empFName, empLName, empPhone FROM Employees;
 GRANT SELECT ON phone_view TO public;