03 how to create clustered and non clustered indexes in sql server and delete indexes

 https://youtu.be/Xil6BKgdbUA

There are two basic types of indexes:

Clustered Index:

 Data is physically stored in the order of index key.

 There can be only one clustered index on table.

 Leaf level of a clustered index is the actual data.

 It can include multiple columns.

 Creating primary key on particular column creates clustered index on that column.

 A table that has a clustered index is referred to as a clustered table. A table that has no clustered

index is referred to as a heap

Non Clustered Index:

 It does not affect the order of data being stored physically.

 Leaf nodes of a non clustered index contain only the values from the indexed columns and reference

to actual row ( row locators)

 If there is clustered index on table then the row locator points to the clustered index key otherwise

points to actual data row

Composite index:

 It is an index that contains more than one column.

 Both clustered and non-clustered indexes can be composite indexes.

Unique Index:

 It is an index that ensures the uniqueness of each value in the indexed column.

 If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the

individual columns.

 For example, if you were to create an index on the FirstName and LastName columns in a table, the

names together must be unique, but the individual names can be duplicated. 

-------------------

Types of Indexes:

1. Clustered Index 2. Non-Clustered Index

Syntax to Create an Index 

Create index <Index_Name> on

<table_Name> (Column_Name (ASC | DESC]);

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. 

However the index can contain multiple columns(a composite index) 

Whenever we apply Primary key constraint on a column in a table, then automatically clustered index will applied on primary key column and

clustered Index will arrange the data in Ascending order. 

To verify the Index:

Execute sp_helpindex Tbl_Employee;

With the above query it will display the all Index's information which are assigned on that particular Table. 

Creating Clustered Index Explicitly: - Syntax:

Create clusteredindex<Index_Name>on <Table_Name> (Colum_Name ASC | Desc, Column-name ASC| Desc);

 Non- Clustered Index:

A Non clustered index is analogous to an index in a textbook. The data

is stored in one place, the index in another place. The index will have

pointers to the storage location of data. 

Whenever we are applying Unique key constraint on a column in a table

then it automatically creates Non clustered index will be applied on

Unique column. 

We can apply more than one non clustered index on a single table. 

Non clustered index will not arrange the data in Ascending order.

Syntax:

Create Non Clustered Index <Index_Name> on <Table_Name> (Column-Name); 

Create Non Clustered Index IX_Tbl_Employee_Name on Tbl_Employee(Name);

---------------------

NOTE:

 Only one clustered index per table, where as you can have more than

one Non clustered index. 

Clustered index is faster than a non-clustered index , because the

clustered index has to refer back to the table , if the selected column is

not present in the index.

Clustered index determines the storage order of rows in the table, and

hence doesn't require additional disk space, but whereas a nonclustered index is stored separately from the table, additional storage space is required.

Unique Index:

A unique index does not allow any duplicate values to be inserted into the table.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

Unique Index is not an separate Index type itself. It's a property.

Syntax:

Create Unique Index <Index_Name> on <Table_Name> (<Column-Name>);

Drop Index:

Drop index<Tble Name> <Index_Name>;

-----------------

Go to Object Explorer (Left side) select the database Name expand the Table folder and Expand the Index Folder click on Index Name and select the Particular Index Name Right click on it select Delete.

Useful Point: - 

 By default Primary constraints creates a unique clustered index where

as a Unique constraint creates a unique non clustered index. These

defaults can be changed if you wish to. 

2. A unique index can't be created on an existing table, if the table

contains duplicate values. To solve this remove the key columns from

the index definition or delete or update the duplicate values. 

3. In non-clustered indexes it will occupy more disk space.

When should indexes be avoided? 

Although indexes are intended to enhance a database's performance, there are times when they should be avoided.

• Indexes should not be used on small tables.

• Tables that have frequent, large batch update or insert operations,

• Indexes should not be used on columns that contain a high number of

nULL values.

· Columns that are frequently manipulated should not be indexed.

interview qustions and ansers

https://dotnettutorials.net/lesson/sql-server-indexes-interview-questions-answers/

Indexes

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

https://youtu.be/yvs-zVayRj8

Notes

https://www.vlrtrain.com/2022/07/01-what-is-index-in-sql-server-and.html

02 index and heap how data stored in sql server database in telugu

https://youtu.be/OO6s8oSHsUk

https://www.vlrtrain.com/2022/07/02-index-and-heap-how-data-stored-in.html

03 how to create clustered and non clustered indexes in sql server and delete indexes

https://youtu.be/Xil6BKgdbUA

How to Import or restore database in ms SQL server  in Telugu - VLR Training

https://youtu.be/5spJcvUnWho

hai all

Please watch below videos and ask if you have doubts

I will arrange one online session

02 index and heap how data stored in sql server database in telugu

 https://youtu.be/OO6s8oSHsUk

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

 https://youtu.be/yvs-zVayRj8

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.

---------------

Indexes

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

Mechanism.

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)

10

1000 12

12000

2 ram

5 sures

1 kiran

30

How to Import or restore database in ms sql server in telugu