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.
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.
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%.
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 :