SQL SERVER – Everything about Indexing with all details

SQL SERVER – Everything about Indexing with all details

List of Question:-

• What is indexing?
• What types are indexing in SQL Server?
• What is difference between Cluster and Non-Cluster Indexing?
• What is the syntax of creation of index?
• Can we use multiple indexes in one table?
• Can we use index on View?
• How to create index on View?
• What types of permission are required to create or delete index?
• What is Cluster Index?
• What is Non-Cluster index?
• How to create Non-cluster index?
• Non-clustered indexes are implemented in the following ways.
• What is Unique Index?
• What is the restriction on unique index?
• How to create unique index?
•  What is filtered Index?
• What are the benefits of Filtered Index?
• How to create Filtered Index?
• Can we create multiple Cluster index in one object?
•  How to get Index in the table?
• When we should rebuild and when you should organize the index?
• How to Rebuild or Organize Existing Index?
• How to enable or disable Index in Table?
• Q14. How to enable or disable Index in an Instance?
• How to modify Index?
• How to Drop Index?
• How to get script to which index should REBUILD or ORGANIZE?
• Can we create multiple Cluster index in one table?
• Can we use cluster and Non-Cluster index on same Column on single table?
• Where we should not use indexing?
• What are the Disadvantages of the index?
• What are the benefits of Indexing?
• What is the restriction on filtered index?
• What is the difference between Indexing, Identity and Primary Key?
• Readymade indexing Script or Readymade indexing example for you use  with all type of index. Just copy and run in AdventureWorks Database.

Question & Answer:-

1.      What is indexing?

Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. There are two type of Indexing in SQL server. 

2.      What types are indexing in SQL Server?

1.       Cluster index

2.       Non-Cluster Index

3.       Unique Clustered index

4.       Unique Non-Clustered Index

5.       Filtered index (based on Non-clustered index)

 3.      What is difference between Cluster and Non-Cluster Indexing?

Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table. We can apply on only one column in the table.

Non-clustered is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table. In 2005 we can create 255 index out of 256 index. And in 2008 we can create 999 indexes out of 1000 index.

4.      What is the syntax of creation of index?

Syntax:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

[ WITH < index_option > [ ,...n] ]

[ ON filegroup ]

< index_option > :: =

{   PAD_INDEX |

1.       FILLFACTOR = fillfactor |

2.       IGNORE_DUP_KEY |

3.       DROP_EXISTING |

4.       STATISTICS_NORECOMPUTE |

5.       SORT_IN_TEMPDB

}

5.      Can we use multiple indexes in one table?

Yes, we can use multiple indexes on one table but Cluster index can only in one table.

 6.      Can we use index on View?

 Yes, we can create index on the view. Index created view is called ‘indexed view’.

A view with a clustered index is called an indexed view.

 7.      How to create index on View?

--Run This script to create index on View

USE AdventureWorks

GO

--Set the options to support indexed views.

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON

GO

--Create view.

CREATE   VIEW TestView

WITH   SCHEMABINDING

AS

   SELECT SUM(UnitPrice*OrderQty) AS Revenue, oh.ModifiedDate, ProductID, COUNT_BIG(*) AS COUNT

   FROM   Sales.SalesOrderDetail sod, Sales.SalesOrderHeader  oh

   WHERE   sod.SalesOrderID=oh.SalesOrderID

   GROUP BY   oh.ModifiedDate, ProductID

GO

--Create index on the view.

CREATE UNIQUE CLUSTERED INDEX IX_testView ON TestView (ModifiedDate, ProductID)

GO

8.      What are the restrictions on indexed views?

As we can create index on table same as we can create index on the View.

The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords AVG function. It cannot have a subquery, asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(<expression>), computed columns from the base tables, and scalar aggregates, views, rowset functions, inline functions, or derived tables, float data type or uses float expressions,
it must contain COUNT_BIG(*). Other aggregate functions  MIN, MAX, STDEV, etc are not allowed.

We can use any joined tables but not allowed on OUTER JOIN operations .

When we run the quesy on index view it not allowed any subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.

CREATE UNIQUE CLUSTERED INDEX clause can accept COUNT_BIG(*) in select statement when we use Group by clause.

9.      What types of permission are required to create or delete index?

When you Create and Alter the index you should have permissions default to the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles and the table owner, and are not transferable.

10.  What is Cluster Index?

It’s creates an object where the physical order of rows is the same as the indexed order of the rows, and the leaf node contains the actual data rows.

A unique clustered index must be created on a view before any other indexes can be defined on the same view.

It for you good practice first creates the clustered index and than create the Non-clustered index on the table otherwise existing Non-clustered indexes on tables are rebuilt when a clustered index is created.

If CLUSTERED is not specified in the create statement of index than a Non-clustered index is created.

11.  What is Non-Cluster index?

After a unique clustered index has been created on a view, Non-clustered indexes can be created.

A Non-clustered index is an index structure separate from the data stored in a table that reorders one or more selected columns.

       Non-clustered index is not change the physical order of table. It’s based on the Logical arrangement of index and non-clustered index contain the non leaf node of the B-Tree.