Clustered And Non Clustered Index Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Database indexes help speed up retrieval of data. The great benefit of indexes is that the server doesnt have to work as hard to get the data. They are much the same as book indexes, providing the database with quick jump points on where to find the full reference.

An index is a data structure that organizes data records on disk to optimize certain kinds of retrieval operations. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records.

In a relational database, an index is a copy of one part of a table. Some databases extend the power of indexing by allowing indexes to be created on functions or expressions. Indexes may be defined as unique or non-unique. A unique index acts as a constraint on the table by preventing duplicate entries in the index. Microsoft SQL Server allows a unique index to contain a single NULL value.

There are both advantages and disadvantages to using indexes. One disadvantage is they can take up quite a bit of space, check a textbook or reference guide. By using too many indexes the database will slow down is another disadvantage. Indexes speed up finding data, but slow down inserting, updating or deleting data.

Some fields are automatically indexed. A primary key or a field marked as 'unique' for example an email address, a userid or a social security number are automatically indexed so the database can quickly check to make sure that you're not going to introduce bad data.

There are many types of Indexes in database and there are the following:

Clustered index

Unclustered index

Bitmap index

Dense index

Sparse index

Covering index

A bitmap index is a special kind of database index that uses bitmaps. An index record appears for every search key value in the file is dense index. An index is created only for a few values is called sparse index. If all the columns in table on which there is an index are used in the join clause in the same order as it is in the index definition then it is called as index covered.

Clustered and Non Clustered Index

Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. A clustered index defines the order in which the data should be stored in the database.  This means that there can only be one clustered index per table.

What is the difference between clustered and nonclustered indexes?

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Consider a library, where books are arranged by the names of Author in the shelves. So, if we need a book written by 'Arundhati Roy' , we can go directly to the shelf which has the books written by she, instead of going sequentially through all the books.

This is example for clustered index in database.

Now suppose, you realized that some books are rather requested by titles. But as we have already arranged books by the names of Author, we can not rearrange it with titles in sequence, so you created a list of title and it's shelf number, so you ask me a book title, and i can get it for you from the shelf noted with it. This is example for non-clustered index.

Each table can have only one clustered index as the key values in the data rows are unique and the index is built on the unique key column. When a table has a clustered index, it is known as a clustered table. Non-Clustered indexes have structures that are different from the data rows. A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. A clustered index is the most important index you can apply to a table. If the database engine can use a clustered index during a query, the database does not need to follow references back to the rest of the data, as happens with a nonclustered index. The result is less work for the database, and consequently, better performance for a query using a clustered index.

As a general rule of thumb, every table should have a clustered index. If you create only one index for a table, use a clustered index. Not only is a clustered index more efficient than other indexes for retrieval operations, a clustered index also helps the database efficiently manage the space required to store the table. In SQL Server, creating a primary key constraint will automatically create a clustered index using the primary key column as the index key.

Sometimes it is better to use a unique nonclustered index on the primary key column, and place the clustered index on a column used by more queries. For example, if the majority of searches are for the price of a product instead of the primary key of a product, the clustered index could be more effective if used on the price field. A clustered index can also be a UNIQUE index.

A Disadvantage to Clustered Indexes

If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance. A table's clustered index can often be found on the primary key or a foreign key column, because key values generally do not change once a record is inserted into the database.

Examples for Clustered Index in SQL

First Example

To select productid from table products which the productid will be list from 14 values to 25 values.

Index

The difference between clustered index and index is that in clustered index the cost is 100% but in the index the cost is 0, 00%.

Examples for Non-Clustered Index in SQL

First Example

To select productName, Unitprice, QuantityPerUnit and Discontinued from table products , where the unitprice will be listed from 10 to 15.

Index

The difference between non clustered index and index is that in non clustered index the cost is 100% but in the index the cost is 0, 00%.

Conclusion

In this topic I explained how to create, manage, and select clustered and non clustered indexes for SQL Server tables. Indexes can be created using one or more columns of a database table. Most of what we covered is true for any relational database engine. Indexes may be defined as unique or non-unique. Proper indexes are crucial for good performance in large databases. Sometimes you can make up for a poorly written query with a good index, but it can be hard to make up for poor indexing with even the best queries. Sometimes it is better to use a unique nonclustered index on the primary key column, and place the clustered index on a column used by more queries. The great benefit of indexes is that the server doesn't have to work as hard to get the data.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.