INDEXES

In order to retrieve data from tables SQL Server Database engine will have two methods
a)Table Scanning
b)Using Indexes

Table Scanning:
In this method table will be scanned and result is retrieved, it is indeed time consuming as it has to scan whole table row by row and vice-versa, so obviously it would lead to less performance.

Using Indexes:
In this method indexes are used to prepare result set.

B-Tree (or) Balanced Tree:
It is a special kind of binary tree which starts at root node and where all leaf pages are at same level.

Heap:
A table without a clustered index is called as heap, we can create a non-clustered index on heap as well.

Clustered Table:
A table with clustered index is called clustered table, one can create non-clustered indexes on clustered table as well.

INDEX: Index is a ordered list of values taken from one or more columns and organized into B-Tree structure, a index can be simple index or composite index.

SQL Server Supports two types of indexes
a)Clustered Index b)Non-Clustered Index

A table is stored in one or more pages, each page contains a header and data sections.

Types of pages:

1. Data pages: It stores actual rows.
2. Index Pages :It stores index column values.
3. Blob pages: It stores text documents and images (BLOB-Binary large objects)

Difference Between Clustered and Non-Clustered Index

Clustered Index
1)A table can have only one clustered index
2)Leaf pages in B-Tree structure contains actual rows.
3)Actual rows arranged in same order as Index.
4)Data pages merged with Index pages, that is called as clustered.

Non-Clustered Index
1)A table can have 249 non-clustered Indexes.
2)Leaf pages in B- Tree structure contains addresses of rows.
3)Actual rows may not be in the same order as Index.
4)Data Pages not merged with index pages is called as non-clustered Index.

PS: Clustered Indexes give better performance than non-clustered Indexes.

Advantages of Indexes

1. Fast retrieval of rows from tables(select statement performance is improved drastically in large tables.)
2. Indexes improve performance while searching for the data in a table or column.

Creating Indexes:
Once we define a column with primary key constraint, SQL server automatically creates clustered index.

If we define a column with a unique constraint SQL server automatically creates Non-Clustered index on that particular column.

Creating Index:
Syntax:
Create [Unique] index on table name(col names).

PS:While creating indexes one can opt for Unique option, so that the column should not have duplicates and Default is non-clustered, if it is not mentioned specifically in the create statements.

In order to list out all Index information of a table the syntax is : sp_helpindex (table name)

Syntax:

Altering Index:
Alter index indexname on tablename [Rebuild (or) Re-organize]
Re-Organized - Only leaf pages are re-arranged.
Re-Build - Total B-Tree structure will be re-arranged.

Dropping a Index:
Drop Index tablename.indexname

Drawbacks of Indexes
Indexes do degrade the performance while performing insertions, deletions and updations in the table.

Indexed views.
Once we create a Index on a view columns then it is called as a Indexed view. Indexed view improves the performance while retrieving data through view.

Materialized views
To Prepare materialized views we should apply following rules.

1. A View should be created with "schema binding" option.
2. The first index on the view should be unique clustered index.
3. The view must be in the same database as all objects referenced by the view.
4. Any functions referenced by the view and all underlying tables were created.
5. The view must not reference any other views - just tables and UDFs.
   

Schema Binding:
Once we create a view or user defined function with schema binding, then the dependent objects(create/alter) structure cannot be modified as long as view or function is present.
This option provides security for views structure as well as table structure.

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