Tuesday 26 July 2022

01 What is index in SQL server and advantages of creating indexes in table 01 -Telugu - VLR Training

Index is used to find the data quickly when a query is processed.

SQL Server can retrieve the data in two ways

1. Using Table Scan

2. Using Index

Table Scan:

 When a SQL Server has no index to use for searching, the result is similar to the reader who looks at

every page in a book to find a word, the SQL engine needs to visit every row in a table.

Using Index:

 Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within

a database fast, much like they do in libraries.

 Indexes speed up the querying process by providing swift access to rows in the data tables.



Generally every database server will perform 2 types of searching mechanism for retrieving the data from the Tables

• Table Scan

• Index Scan 

Table Scan:

If there is no index to help the query then the query engine,

checks every row in the table from the beginning to end. This is called as Table scan. Table scan is bad for performance.

It is a difficult mechanism of every database. In this mechanism

the database server is searching on entire structure of the table

for required data. 

So that it will take time consuming and application performance will slow. 

So to overcome the above drawbacks we used Index Scan


Index Scan:

Indexes are special lookup tables that the database search engine can use to speed up data retrievalwithout reading the whole table. Simply put, an index is a pointer to data in a table. An index in a database is

very similar to an index in the back of a book. 

For example, if you want to reference all pages in a book that discuss a

certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers. If you don't have an Index, to locate a specific chapter in the

book, you will have to look at every page of the book. 

In a similar way Table & View Indexes can help the query to find the

data quickly. 

An index helps speed up SELECT queries and WHERE clauses, but it

slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data. 

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

 select * into vlrperson

 from [AdventureWorks2019].[Person].[Person]

 select * from vlrperson where firstname  = 'kelvin'

 create clustered index first_idx

on vlrperson(firstname)

id  int

name char(8)


1000 12


2 ram

5 sures

1 kiran


No comments:

Post a Comment

Note: only a member of this blog may post a comment.