Jainendra Verma is Sharing SQL Server Hint's & Tips on SQL Integrity: 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

--Set the options to support indexed views.

SET NUMERIC_ROUNDABORT OFF

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

--Create view.

CREATE VIEW TestView

WITH SCHEMABINDING

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

--Create index on the view.

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

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.

12. How to create Non-cluster index?

To create a nonclustered index by using the Table Designer

1. In Object Explorer

2. Expand the Tables folder.

3. Right-click the table and select Design.

4. On the Table Designer menu, click Indexes/Keys.

5. In the Indexes/Keys dialog box, click Add.

6. Select the new index in the Selected Primary/Unique Key or Index text box.

7. In the grid, select Create as Clustered, and choose No from the drop-down list to the right of the property.

8. Click Close.

9. On the File menu, click Save table_name.

To create a nonclustered index by using Object Explorer

1. In Object Explorer

2. Expand the Tables folder.

3. Expand the table.

4. Right-click the Indexes folder, point to New Index, and select Non-Clustered Index.

5. In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

6. Under Index key columns, click Add.

7. In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the nonclustered index.

8. Click OK.

9. In the New Index dialog box, click OK.

To create a nonclustered index on a table

USE AdventureWorks;

-- Find an existing index named IX_ProductVendor_VendorID and delete it if found.

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'IX_ProductVendor_VendorID')

DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;

-- Create a nonclustered index called IX_ProductVendor_VendorID

-- on the Purchasing.ProductVendor table using the BusinessEntityID column.

CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID

ON Purchasing.ProductVendor (ProductId);

13. Nonclustered indexes are implemented in the following ways.

· UNIQUE constraints

When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist

· Index independent of a constraint

The maximum number of nonclustered indexes that can be created per table is 999. This includes any indexes created by PRIMARY KEY or UNIQUE constraints, but does not include XML indexes.

· Nonclustered index on an indexed view

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

14. What is Unique Index?

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique.

15. What is the restriction on unique index?

We cannot create a unique index on a single column if that column contains NULL in more than one row.

We cannot create a unique index on multiple columns if any of columns contains NULL in more than one row because it’s treated as duplicate values for indexing purposes.

A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

A unique Non-clustered index can contain included Non-key columns.

16. How to create Unique index?

To create a unique index by using the Table Designer

1. In Object Explorer.

2. Right-click the table and select Design.

3. On the Table Designer menu, select Indexes/Keys.

4. In the Indexes/Keys dialog box, click Add.

5. Select the new index in the Selected Primary/Unique Key or Index text box.

6. In the main grid, under (General), select Type and then choose Index from the list.

7. Select Columns, and then click the ellipsis (…).

8. In the Index Columns dialog box, under Column Name, select the columns you want to index. You can select up to 16 columns. For optimal performance, select only one or two columns per index. For each column you select, indicate whether the index arranges values of this column in ascending or descending order.

9. When all columns for the index are selected, click OK.

10. In the grid, under (General), select Is Unique and then choose Yes from the list.

11. Optional: In the main grid, under Table Designer, select Ignore Duplicate Keys and then choose Yes from the list. Do this if you want to ignore attempts to add data that would create a duplicate key in the unique index.

12. Click Close.

13. On the File menu, click Save table_name.

Create a unique index by using Object Explorer

1. Expand the table on which you want to create a unique index.

2. Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….

3. In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

4. Select the Unique check box.

5. Under Index key columns, click Add….

6. In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index.

7. Click OK.

8. In the New Index dialog box, click OK.

To create a unique index on a table

USE AdventureWorks;

-- Find an existing index named AK_UnitMeasure_Name and delete it if found

IF EXISTS (SELECT name from sys.indexes

WHERE name = N'AK_UnitMeasure_Name')

DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;

-- Create a unique index called AK_UnitMeasure_Name

-- on the Production.UnitMeasure table using the Name column.

CREATE UNIQUE INDEX AK_UnitMeasure_Name

ON Production.UnitMeasure (Name);

17. What is filtered Index?

. A filtered index is based on Non-clustered index especially suited to cover queries that select from a conditional data

18. What are the benefits of Filtered Index?

1. Improved query performance and plan quality

2. Reduced index maintenance costs

3. Reduced index storage costs

19. How to create Filtered Index?

To create a unique index by using the Table Designer( Same as above)

Create a unique index by using Object Explorer( Same as above)

To create a unique index on a table

USE AdventureWorks;

-- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"

-- and deletes it from the table Production.BillOfMaterials if found.

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'FIBillOfMaterialsWithEndDate'

AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))

DROP INDEX FIBillOfMaterialsWithEndDate

ON Production.BillOfMaterials

-- Creates a filtered index "FIBillOfMaterialsWithEndDate"

-- on the table Production.BillOfMaterials

-- using the columms ComponentID and StartDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate

ON Production.BillOfMaterials (ComponentID, StartDate)

WHERE EndDate IS NOT NULL ;

20. Can we create multiple Cluster index in one object?

21. How to get Index in the table?

Syntax: SP_HELPINDEX <Tablename>

Example: SP_HELPINDEX 'Purchasing.ProductVendor'

--- Or –

---Get the all index in the instance

select OBJECT_NAME(object_id ), name from sys.indexes

22. When we should Rebuild and when you should Organize the index?

When the avg. fragmentation percent is greater than 30% than we should Rebuild the index and if the Avg. fragmentation is below 30% than we should Organize to index.

Using this system DMV view sys.dm_db_index_physical_stats we can get the Avg. fragmentation percent of the table

23. How to Rebuild or Organize Existing Index?

ALTER INDEX ALL ON PERSON.ADDRESS REBUILD

ALTER INDEX ALL ON PERSON.ADDRESS REBUILD WITH (ONLINE=ON)

24. How to enable or disable Index in Table?

--- Get the Index in a table

USE AdventureWorks;

EXEC sp_helpindex 'person.contact'

25. Q14. How to enable or disable Index in an Instance?

Some time it our need to Enable and Disable the Index such as Bulk Operation (Import & Export), BCP Operation, DTS Package etc.

The above following situation you can use the Enable/Disable on the Table.

---Get the all index in the instance

select OBJECT_NAME(object_id ), name from sys.indexes

Example –

USE AdventureWorks;

----Diable Index

ALTER INDEX [IX_Contact_EmailAddress] ON person.contact DISABLE

----Enable Index

ALTER INDEX [IX_Contact_EmailAddress] ON person.contact REBUILD

26. How to modify Index?

Using Alter index command you can modify or alter the index

27. How to Drop Index?

USE AdventureWorks;

-- Looks for an existing index named "FIBillOfMaterialsWithEndDate"

-- and deletes it from the table Production.BillOfMaterials if found.

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'FIBillOfMaterialsWithEndDate'

AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))

DROP INDEX FIBillOfMaterialsWithEndDate

ON Production.BillOfMaterials

28. How to get script to which index should REBUILD or ORGANIZE?

Just Run the below T-SQL Script and get all auto build query for Reorganize and Rebuild to all table in a single database.

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN;

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

PRINT (@command);

END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

Result:

ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE

ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD

After that select result and Run to Reorganize and Rebiuld all Tables of a single Database.

ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REORGANIZE

ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product] REBUILD

Result:

Command(s) completed successfully.

29. Can we create multiple Cluster index in one table?

No, Only One in One Table

30. Can we use cluster and Non-Cluster index on same Column on single table?

Yes

31. Where we should not use indexing?

A table are frequently inserting records, updating records and deleting records of a table on that table we should not use indexing.

32. What are the Disadvantages of the index?

Some time uses of indexes slow down Data modification operations (such as INSERT, UPDATE, and DELETE).
Every time data changes in the table, all the indexes need to be updated.
Indexes need disk space, the more indexes you have, and more disk space is used.

33. Where stoered the Indexing information?

In the Fill factor stored the indexing information. Important Creating a index with a FILLFACTOR affects the amount of storage space the data occupies because SQL Server redistributes the data when it creates the index.

34. What are the benefits of Indexing?

35. What is the restriction on filtered index?

36. What is the difference between Indexing, Identity and Primary Key?

37. Ready made indexing Script or Ready made indexing example for you use with all type of index. Just copy and run in AdventureWorks Database.

-- Readymade indexing Examples

--A. Use a simple index

--This example creates an index on the AddressID column of the Address table.

SET NOCOUNT OFF

USE AdventureWorks

IF EXISTS (SELECT name FROM sysindexes

WHERE name = 'IX_VendorAddress_AddressID')

DROP INDEX Address.IX_VendorAddress_AddressID

USE AdventureWorks

CREATE INDEX IX_VendorAddress_AddressID

ON Person.Address(AddressID)

--clear data

DROP INDEX Address.IX_VendorAddress_AddressID

--B. Use a unique clustered index

--This example creates an index on the AddressID column of the Person.Address table that enforces uniqueness. This index physically orders the data on disk because the CLUSTERED clause is specified.

SET NOCOUNT ON

USE AdventureWorks

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'Address')

DROP TABLE Address

USE AdventureWorks

IF EXISTS (SELECT name FROM sysindexes

WHERE name = 'IX_VendorAddress_AddressID')

DROP INDEX Person.Address.AddressID

USE AdventureWorks

CREATE TABLE Person.Address

(

employeeID int NOT NULL,

base_pay money NOT NULL,

commission decimal(2, 2) NOT NULL

)

INSERT Person.Address

VALUES (1, 500, .11)

INSERT Person.Address

VALUES (2, 1050, .15)

INSERT Person.Address

VALUES (3, 8500, .07)

INSERT Person.Address

VALUES (4, 1510, .03)

INSERT Person.Address

VALUES (7, 7550, .02)

SET NOCOUNT OFF

CREATE UNIQUE CLUSTERED INDEX IX_VendorAddress_AddressID

ON Person.Address (AddressID)

--C. Use a simple composite index

--This example creates an index on the orderID and employeeID columns of the order_emp table.

SET NOCOUNT ON

USE AdventureWorks

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'SalesOrderDetail')

DROP TABLE SalesOrderDetail

USE AdventureWorks

IF EXISTS (SELECT name FROM sysindexes

WHERE name = 'IX_sales_order')

DROP INDEX SalesOrderDetail.emp_order_ind

USE AdventureWorks

CREATE TABLE SalesOrderDetail

(

orderID int IDENTITY(1000, 1),

employeeID int NOT NULL,

orderdate datetime NOT NULL DEFAULT GETDATE(),

orderamount money NOT NULL

)