Fill Factor: The fill factor is “how much percentage of space is filled by data on a page”. By default fill factor value is –>0 which is equal to–>100% The fill-factor option 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 […]
Fragmentation: The empty spaces was found in a page, then that empty page is called “fragmentation”. The empty spaces were formed when huge modifications/deletions happen on a index (internally in a table). Fragmentation will impact performance of queries, because those empty spaces occupy variant space for no reason and are not utilized.
Filtered indexes were introduced in SQL Server 2008. Filtered indexes are non clustered indexes that have the addition of a WHERE clause. Creating filter index: CREATE NONCLUSTERED INDEX filter_index1 ON mydb.dbo.emp(sal) WHERE sal > 5000
B-Tree is a data structure which holds the organized data in a organized tree manner. B-Tree contains the Root node Intermediate Nodes Leaf Nodes(where actual data might be linked/present). Index Key: Index key is a key that contains the location specific of the data selected in the table. Index keys present at the leaf node […]
What is Covering Index? A covering index is also a composite index i.e., it indexes more than one column in its base table.
What is Bookmark/Key Lookup? If the table has clustered index, it is called as bookmark lookup (or key lookup).
What is RID Lookup? If the table does not have clustered index, but having a non-clustered index, it is called RID lookup.