A spatial index is a database object that represents a data structure consisting of keys built on the basis of one or several columns of a table or view and direct to the map that indicates to the storage location of the specified data. Indexes are designed for faster retrieval of rows from a table, in other words, indexes provide fast data retrieval in a table, which significantly improves query and application performance. Indexes can also be used to ensure the uniqueness of rows in a table, thereby ensuring data integrity.
Types of indexes on Clouds:-
Efficient cloud query processing experts from IT Support Nottingham and Outsourced IT Support Nottingham emphasized on the following types of spatial indexes on the cloud.
The Clustered - is an index that stores data in a sorted table, by the value of the index key form. A table can have only one clustered index since the data can only be sorted in one order. If possible, each table should have a clustered index, if the table does not have a clustered index; such a table is called a “heap ". A clustered index is created automatically when the PRIMARY KEY (primary key ) and UNIQUE constraints are created if the clustered index for the table has not yet been defined. If you create a clustered index for a table (heap), in which there are nonclustered indexes, then after creating all of them need to be rebuilt.
Non-clustered - is the index which contains the key value and a pointer to a string of data containing the value of that key. A table may have multiple nonclustered indexes. Nonclustered indexes can be created on tables with or without a clustered index. It is this type of index that is used to improve the performance of frequently used queries since nonclustered indexes provide fast search and access to data by key values.
Filterable - is an optimized non-clustered index that uses a filter predicate for indexing portions of the rows in the table. If it is good to design this type of index, it can improve query performance, as well as reduce the cost of maintaining and storing indexes compared to full-table indexes.
Unique- is an index that ensures the absence of duplicate (identical) index key values, thereby guaranteeing the uniqueness of the strings by this key. Both clustered and nonclustered indexes can be unique. If you create a unique index on several columns, the index guarantees the uniqueness of each combination of values in the key. When creating a PRIMARY KEY or UNIQUE SQL constraint, the server automatically creates a unique index for key columns. A unique index can be created only if the table currently has no duplicate values for the key columns.
Columnstore- is an index based on data storage technology in the form of columns. This type of index can be effectively used for large data warehouses since it can increase the performance of queries to the storage by up to 10 times and also reduce data size by 10 times, as the data in the Columnstore index is compressed. There are both clustered column indexes and non-clustered ones.
Full-text - is a special type of index that provides effective support for complex searches of words in the character string data. The process of creating and maintaining a full-text index is called “copulating.” There are such types of filling as full filling and filling based on change tracking. By default, the SQL server completely fills the new full-text index immediately after it is created, but this may require a significant amount of resources, depending on the size of the table, so it is possible to postpone full population. Track-based padding is used to maintain a full-text index after it is initially full.
Spatial – is an index that allows for the more efficient use of specific operations on spatial objects in columns of type geometry or geography data. This type of index can only be created for a spatial column, and the table for which the spatial index is defined must contain a primary key.
XML - is another special type of index that is designed for columns of data type XML. Thanks to the XML-index improved query performance for XML columns. There are two types of XML index: primary and secondary. Primary XML Index-indexes all tags, values, and paths, stored in an XML column. It can be created only if the table has a clustered index on the primary key. A secondary XML index can be created only if the table has a primary XML index and is used to improve the performance of queries on a particular type of reference to an XML column. In this regard, there are several types of secondary indexes: PATH, VALUE, and PROPERTY.
According to the experts of IT Support Derby and IT Support Nottingham, Spatial indexes also exist for the tables that are optimized for memory (with In the Memory-the OLTP ) such as hash ( Hash ) indexes and non-clustered indexes, optimized for memory that is created to scan a range of scanning and orderly.