tailieunhanh - The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P57

The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P57: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 262 Chapter 7 Maintaining Your Database The use of sparse columns is especially appropriate with filtered indexes. Filtered indexes are indexes that are optimized for querying data based on certain criteria. A filtered index on a sparse column can index only the rows that have non-null values. Filtered indexes created on sparse columns consume less disk space and improve performance. Maintaining Indexes Index maintenance is an important part of looking after databases. As you have learned from earlier chapters indexes are structures that speed up data retrieval signif-cantly. Indexes are stored on disk as physical structures. Indexes are automatically updated when underlying table data is modified. However with time and data usage analysis you may want to create new or drop existing indexes. You may also need to defragment indexes on frequently modified tables as they can be highly fragmented. Test Day Tip Sometimes very large indexes become difficult to maintain. As an alternative consider partial indexes created with a filter WHERE clause. For partial indexes the index structure is created only for a subset of data that is frequently searched for. For example if you store a decade s worth of data in a table and only ever query the last year s worth of data frequently there is no reason to maintain the index for the portion of the data that is accessed infrequently. An index with a WHERE clause helps overcome the situation by creating a small and efficient index structure that covers the current data only. Dynamic Management Views DMVs is a feature introduced in SQL Server 2005 that allows you to view useful information about indexes. The following DMVs are available for index maintenance Lists missing indexes Shows index usage statistics Shows fragmentation information on each index As shown in Example when the includes .