From the perspective of the database developer, unclustered database indexes do not seem at their surface to be very different from a clustered index. The real difference is that a clustered index will reorder the records on disk, whereas an unclustered index will not. Indeed, the same grouping ideas apply just as much to unclustered indexes as they do to clustered indexes, so we will not go over them again.
The real difference [between clustered and unclustered indexes] is that a clustered index will reorder the records on disk, whereas an unclustered index will not.
Unlike the clustered index, there is no theoretical limit on the number of unclustered indexes that can exist for a specific database table. There is, however, two important caveats. The first is that indexes must be stored on disk, and generally, the larger the table, the larger the database index. Disk space consumption can be of concern in many production environments, so the creation of indexes must be balanced against resource scarcity.
The second—and more important—caveat is that indexes must be updated whenever the tables are modified, either through column updates, insertion, or deletion. For large indexes (i.e., for large tables) these database index updates are an expensive and time consuming operation.
The database developer must then enter into a sort of balancing act for adequate database optimization: indexes speed up access time enormously, but they slow down data modification. If your software application rarely performs UPDATE, INSERT, and DELETE operations, creating indexes is not going to incur significant data modification performance penalties beyond their initial creation, but for applications that perform frequent updates, it might be a better idea to carefully tailor the indexes and create as few of them as possible. The database developer can use the reporting utilities in the DBMS to determine what queries are performed the most frequently and their average CPU times to help make this determination.
Ayoka’s custom software development culture combines the entrepreneurial feel of a start-up company with the confidence and stability of a successful professional services firm. Our vision is to become the ONLY choice for affordable enterprise software development and custom software development in AMERICA. We are proud of our consistent track record of delivering successful projects on time and on budget. We strive to provide custom software development projects that make our clients money. Get in touch today to see how Ayoka’s services can benefit your company.
