For example, if you need to find a particular word in the whole table. You could either read through all the rows column-by-column or by using an index which is obviously a lot faster. If in a book you got a word and you need to find all the occurrence of that word in that book either you have to go through each and every line of the book or you can go to the index of the book to find out the occurrence (a database index works in the same way for the same reason).
Note that adding the right indexes to the right tables can significantly improve performance. Suppose you are querying a table that gets information which should be ordered or grouped by a certain column, then its good to add an index to the database based on that column.
But there are some of the downsides of using indexes. There will be performance problem if items are frequently added, deleted, or changed. Suppose you are doing any modification, the server at first make that change, then see if it affects any index and then update the index if it affects any of them. That means it takes three operations per modification. Overusing indexes has downsides, since it consumes a fair amount of additional disk space. Choosing a large data column for the index key will increase the resources needed by the database and make it slower to use. The same is true of indexing a column that changes regularly, because every change requires the index to change.
Types of Index :
1. Simple index - A simple index uses only one column as the index key.
2. Composite index - A composite index uses two or more columns in its index key.
3. Nonclustered index - A nonclustered index contains a list of index key columns in the correct order with links to the actual rows in the table.
4. Clustered index - It determines the order in which the rows in a table are stored in the database. You can have only one clustered index in a table.
5. Unique index - A unique index ensures that values in the index key columns are unique.
CREATE INDEX IX_Index1
ON Table1 (ColumnName)
This is how you can create nonclustered index (IX_Index1) on column (ColumnName) of table Table1. If you need to create a clustered or unique index, you can use the query CREATE CLUSTERED INDEX or CREATE UNIQUE INDEX, repectively.
Please Like and Share the Blog, if you find it interesting and helpful.
Related articles
- Definition of Common Type Specification (CTS) and Common Language Specifications (CLS)
- All you want to know about Common Language Run Time (CLR)
- Globals in Nodejs
- What is String.Intern?
- How to improve Asp.Net Application Performance - Part 2
- String Compare methods in C#
- Utilities Module in Nodejs
- All about MongoDB Collections
- How to receive data from the Querystring in Nodejs
- Use of Redis in Nodejs
No comments:
Post a Comment