There are two main types of indexes that we can have on a Microsoft SQL Server backend Database. These are normal and clustered indexes. Normal index can be viewed as the index in the back of a book. We first look in the index for a topic. After finding the topic in alphabetical order we also find the page number or address of the topic in the book. We then use the address to easily find the topic. If we did not have this index we would have to search sequentially through the book and the larger the book the greater the effort and time to either find a topic or realize that it does not exist.
This is similar to a normal index in the database where we have the data stored in one file and the index stored in another file in the order specified. The database engine using the index will first look in the index file then retrieve the address of the record that is used to locate the record in the data file.
For clustered index the records in the data file are sorted in the order of the index. We therefore do not have a separate index file from the data file as we have for normal index. You should also note that it is only possible to have one clustered index per table, since the records may only be sorted in one order at any time. You can have several normal indexes and one clustered index for each table. Clustered indexes are more efficient since the index is the actual data records and hence you do not need to seek in another file after finding the key.
The Primary Index of a table is the main index that is used to uniquely identify records in it. No duplicated values are allowed in this index and all fields in the index are required. You can designate a clustered or normal index as the primary index. Since the primary index is the main index you should gain optimum performance by designating a clustered index for this purpose.
Please note that indexes in general improve performance for search but not for inserts or update, since the overhead of organizing and or writing the index is involved.
The fields in the Primary key uniquely identify records in any given table. Each unique combination ofPrimary key fields uniquely identifies one (and only one) record in the table. Primary keys MUST be unique. There can only be ONE primary key per table. Not all unique indexes are primary keys.
This is similar to a normal index in the database where we have the data stored in one file and the index stored in another file in the order specified. The database engine using the index will first look in the index file then retrieve the address of the record that is used to locate the record in the data file.
For clustered index the records in the data file are sorted in the order of the index. We therefore do not have a separate index file from the data file as we have for normal index. You should also note that it is only possible to have one clustered index per table, since the records may only be sorted in one order at any time. You can have several normal indexes and one clustered index for each table. Clustered indexes are more efficient since the index is the actual data records and hence you do not need to seek in another file after finding the key.
The Primary Index of a table is the main index that is used to uniquely identify records in it. No duplicated values are allowed in this index and all fields in the index are required. You can designate a clustered or normal index as the primary index. Since the primary index is the main index you should gain optimum performance by designating a clustered index for this purpose.
Please note that indexes in general improve performance for search but not for inserts or update, since the overhead of organizing and or writing the index is involved.
The fields in the Primary key uniquely identify records in any given table. Each unique combination ofPrimary key fields uniquely identifies one (and only one) record in the table. Primary keys MUST be unique. There can only be ONE primary key per table. Not all unique indexes are primary keys.
The Clustered index is the physical order in which records are stored in the table. Say you have a clustered index on a text field called "Name", and you insert a record with a name that starts with a letter D, the record will physically be inserted between the C and the E record. Clustered indexes do not have to be unique.
No comments:
Post a Comment