How many type of index in SQL Server 2008?

There are   2 Types of  Index

Clustered Index: Which comes with PK, can have only on Clustered Index.

Non-Clustered Index: Can create any user  999  cluster and stored as B-tree structures

In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

In a Non-Clustered index, the leaf level page does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

Heaps (Tables without Clustered Indexes)

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap.

Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently.

Index Types

• Composite index: An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes

• Unique Index: 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

                A unique index is automatically created when you define a primary key or unique constraint.

• • Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, nonclustered index on the primary key.

  • Unique: When you define a unique constraint, SQL Server automatically creates a unique, nonclustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.

  • Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns

                                                   What is a Filtered index in sql server 2008?

Filtered Index is a new feature in SQL SERVER 2008. 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.

A filtered index will be suited for this scenario since it will have the data in a well defined subset.. Create index for only subset rows in a table though some value is needed to query

Filtered indexes will help to sort the data.

Points to remember when creating Filtered Index:

They can be created only as Non clustered Index. They can be used on Views only if they are persisted views.  They cannot be created on full-text Indexes

A Filtered Index, which is an optimized non-clustered index, allows us to define a filter predicate, a WHERE clause, while creating the index.

Rebuilding an Index

Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data

Index Rebuild : This process drops the existing Index and Recreates the index.

Fill Factor-->Fill factor is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the rest as free space for future growth

 a default Fill Factor of 100 (or 0) is ideal.

• • If your system static and just read only, a default Fill Factor of 100 (or 0) is ideal. As there is no insert, update or delete, having all the pages filled up makes sense.

  •  If your system has lots of OLTP transactions, then a lowering Fill Factor (between 70 to 90) provides better result

                    Defragmenting Indexes in SQL Server 2005 and 2008

Indexes must be periodically reorganized or rebuilt (defragmented) so the physical order of the leaf-level pages matches the logical order of the leaf nodes. This means that you should analyze your indexes periodically to determine whether they’ve become fragmented and the extent of that fragmentation from there; you can either reorganize or rebuild the affected indexes, depending on the results of your analysis

 There are two types of fragmentation:

Internal Fragmentation indicates that there is too much free space on the index page.

External Fragmentation indicates that the logical ordering and physical ordering do not match

 How to Analyzing Fragmentation and method:

The system function sys.dm_db_index_physical_stats to determine which indexes are fragmented and the extent of that fragmentation. Below values are important for analyzing point of view .avg_fragmentation_in_percent:  The percent of logical fragmentation (out-of-order pages in the index)

fragment_count: Number of fragments in the leaf level.

avg_fragment_size_in_pages: Average number of pages in a leaf-level fragment.

page_count: Number of index or data pages

Recommendation: Based on your index analysis, you can determine what action to take. Microsoft recommends that you reorganize your index if the avg_fragmentation_in_percent value is less than or equal to 30% and rebuild the index if the value is greater than 30%.

avg_fragmentation_in_percent value                                      Corrective statement 

5% and < = 30 %( less than or equal to 30%)                        ALTER INDEX REORGANIZE 

 > 30% (value is greater than 30 %.)                                      ALTER INDEX REBUILD WITH (ONLINE = ON)* 

Column                                                                      Description

avg_fragmentation_in_percent                         The percent of logical fragmentation (out-of-order pages in the index)

fragment_count                                                  The number of fragments (physically consecutive leaf pages) in the index

avg_fragment_size_in_pages                           Average number of pages in one fragment in an index

Index Rebuild: This process drops the existing Index and Recreates the index.

Index Reorganize: This process physically reorganizes the leaf nodes of the index.

 The following example queries the sys.dm_db_index_physical_stats dynamic management function to return the average fragmentation.

 SELECT  --schema_name(t.schema_id)         AS [Schema],

  ps.object_id,

  ps.index_id AS IndexID,

  object_name(ps.object_id)                          AS [Table],

  i.name                                             AS [Index],

  ps.Index_type_desc                                 AS IndexType,

  convert(TINYINT,ps.avg_fragmentation_in_percent)   AS [AvgFrag%],

  convert(TINYINT,ps.avg_page_space_used_in_percent) AS [AvgSpaceUsed%],

  ps.avg_fragment_size_in_pages   AS [PagesPerFrag],

  ps.record_count                                    AS RecordCnt,

  ps.fragment_count                                  AS FragmentCnt

FROM     sys.dm_db_index_physical_stats(db_id(db_name()),

  NULL,NULL,NULL,'DETAILED') ps -- Faster option: SAMPLED

  INNER JOIN sys.indexes i

     ON ps.object_id = i.object_id

     AND ps.index_id = i.index_id

  INNER JOIN sys.tables t

      ON ps.object_id = t.object_id

WHERE    t.is_ms_shipped = 0

and ps.avg_fragmentation_in_percent > 0

ORDER BY [Index], [Table]--,  [Schema]

Table 1: Partial List of Index Statistics for the AdventureWorksDW Database.

Table 1 shows only a partial list of indexes, but the number of indexes can grow quite large. As a result, you might want to narrow down the result set. In the following example, I specify the FactResellerSales in the AdventureWorksDW database so that information related only to that table’s indexes is returned:

SELECT b.name AS IndexName,

  a.avg_fragmentation_in_percent AS PercentFragment,

  a.fragment_count AS TotalFrags,

  a.avg_fragment_size_in_pages AS PagesPerFrag,

  a.page_count AS NumPages

FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorkDW'),

  OBJECT_ID('FactResellerSales'), NULL, NULL , 'DETAILED') AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id

  AND a.index_id = b.index_id

WHERE a.avg_fragmentation_in_percent > 0

ORDER BY IndexName

Table 2: List of Index Statistics for the FactResellerSales Table

As Table 2 indicates, the FactResellerSales table contains four indexes whose avg_fragmentation_in_percent value is greater than zero. Based on Microsoft’s guidelines, I might choose to reorganize the  IX_FactResellerSales_ResellerKey index and rebuild the others.

DECLARE @IndexName VARCHAR(100)

SET @IndexName = 'IX_FactResellerSales_ResellerKey'

DECLARE @IndexID SMALLINT

SET @IndexID =

  (SELECT index_id FROM sys.indexes

    WHERE name = @IndexName)

SELECT @IndexName AS IndexName,

  avg_fragmentation_in_percent AS PercentFragment,

  fragment_count AS TotalFrags,

  avg_fragment_size_in_pages AS PagesPerFrag,

  page_count AS NumPages

FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorkDW'),

  OBJECT_ID('FactResellerSales'),

  @IndexID, NULL , 'DETAILED')

WHERE avg_fragmentation_in_percent > 0

To reorganize an index, run an ALTER INDEX statement and include the keyword REORGANIZE, as shown in the following example:

ALTER INDEX IX_FactResellerSales_ResellerKey

  ON FactResellerSales

REORGANIZE

  Attached Related Document  Below :