SQL-ASK For DBAs: April 2014

Most important 60 Question and Answer on SQL Server Index-

Note:- I have mentioned ready made indexing Script for all types of indexes. To use just copy and run in UAT or Production 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)

6. Spatial Index

7. XML 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 indexes out of 256 indexes. 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 | NON-CLUSTERED ] 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 sub query, 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 query on index view it not allowed any sub queries 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?

(a) To create a non-clustered 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.

(b) To create a non-clustered 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 non-clustered index.

8. Click OK.

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

(c) To create a non-clustered 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 non-clustered index called IX_ProductVendor_VendorID

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

CREATE NON-CLUSTERED INDEX IX_ProductVendor_VendorID

ON Purchasing.ProductVendor (ProductId);

13. Non-clustered indexes are implemented in the following ways.

1. UNIQUE constraints

When you create a UNIQUE constraint, a unique non-clustered 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

2. Index independent of a constraint

The maximum number of non-clustered 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.

3. Non-clustered index on an indexed view

After a unique clustered index has been created on a view, non-clustered 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?

(d) 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.

(e) 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.

(f) 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

Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. When we see an Index created with some where clause then that is actually a FILTERED INDEX.

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?

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

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

(i) 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 NON-CLUSTERED 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;

Above Script 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 stored the Indexing information?

In the Fill factor stored the indexing information. Important Creating an 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. Limitation of index?

· A 900-byte size limit on indexes

· We can only a maximum of 16 columns can be named in the index

· The following columns with data types like nvarchar(max), text, and ntext cannot be used in indexes

38. What are the difference between clustered and a non-clustered index?

Cluster Index	Non-Cluster Index
A clustered index is a special type of index that it physically sorts the rows in the table.	A non-clustered index is a special type of index in It cannot sort the rows physically.
Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.	The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
If table consists of clustered index, then index pages consist of index key columns and clusteredindexkeycolumns.	Always we have to place first clustered index then non-clustered .
Both data and indexes are merged and sorted at 3^rd level (Leaf level).	Both data and Index pages are sorted separately.
Queries that return large dataset and Queries that return Ranges.	Queries that don’t return large dataset and Queries that return exact matches.
Create clustered index pid_indx on prods (pid)	These indexes are rebuilded when Cluster index created/dropped/modified.
We can have up to 249 non clustered indexes in a table.	You can have up to 999 non clustered indexes from SQL Server 2008 onwards.

39. What is the diff between re-building and re-organize? When your use.

Re-building	Re-Organize
1. Complete index is dropped and recreated. Here you can specify FillFactor.	1. Just index pages are placed in order of index key column volume. Here you can not specify Fill Factor
2.Fragmentaion >30% or if scan density < 75%	2. .Fragmentaion >5% &<30% or if scan density
2.Fragmentaion >30% or if scan density < 75%	> 75% &<95%
3.Statistics are updated	3.Not Updated
4.Take Long time	4.Take less time
5.Table is block if the option “online processing of DML…” is not enabled	5.Allows
6.Use Tempdb space	6.Min.space
7.Alter index indexname/All on <tname> rebuild	7.Alter index indexname/All on <tname> reorganize
8. It is offline operation	8. It is online operation
9.Option is available in all SQL Server 2005 edition	9.Option is available in SS 2005 EE and DE

40. DMV - sys.dm_db_index_usage_stats:

Ø The seeks refer to how many times an index seek occurred for that index. A seek is the fastest way to access the data, so this is good.

Ø The scan refers to how many times an index scan occurred for that index. A scan is when multiple rows of data had to be searched to find the data. Scans are something you want to try to avoid.

Ø The lookups refer to how many times the query required data to be pulled from the clustered index or the heap(does not have a clustered index). Lookups are also something you want to try to avoid.

Ø The updates refer to how many times the index was updated due to data changes which should correspond to the first query above.

41. What are “Table Scan’s” and “Index Scan’s”?

A table scan is where the table is processed row by row from beginning to end.

An index scan is where the index is processed row by row from beginning to end.

If the index is a clustered index then an index scan is really a table scan.

42. Which is faster a Table Scan, or a Clustered Index Scan?

Same speed in case a table has a clustered index that it’s always show index scan instead of table scan.

43. Readymade indexing Script or Readymade 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

)