Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 137
Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 137 The basis for the index usually is determined by the order in which the majority of applications and queries want their output. The clustered index values are also present in other indexes and the size of the defined index should be kept as small as possible. When you select a clustering key, try to utilize a numeric data type because character types cause index storage to occupy much more space. Always define a clustered index first before you define any of the nonclustered indexes. If you do these tasks in reverse order, then all nonclustered indexes rebuild themselves upon creation of the clustered index. Nonclustered Indexing Nonclustered indexes provide a means of retrieving the data from the database in an order other than that in which the data is physically stored. The only alternative to the use of these indexes would be provisions for a sort operation that would place undue overhead on the client system and might not produce the desired response times. A data sort implementation is usually performed only for one-time operations or for applications that will have very limited usage. Although the creation of indexes saves time and resources in a lot of cases, avoid the creation of indexes that will rarely be utilized. Each time a record is added to a table, all indexes in the table must be updated, and this might also cause undue system overhead. For that reason, careful planning of index usage is necessary. Unique Indexing At times when indexes are created, it is important to guarantee that each value is distinctive. This is particularly important for a primary key. SQL Server automatically applies a unique index to a primary key to ensure that each key value uniquely defines a row in the table. You might want to create additional unique indexes for columns that are not going to be defined as the primary key. Leaving Space for Inserts Fill factor is the percent at which SQL Server fills leaf-level pages upon creation of indexes. Provision for empty pages enables the server to insert additional rows without performing a page-split operation. A
If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.