Oracle Vs MS-SQL: INDEX
In continuation with the topic 'Oracle Vs MS-SQL : Views', I would like to say about indexes before taking you to the materialized views, because materilazied views use the Index. Index is not a new concept or strictly database concept. It is similar as book index which helps to find out a paricular topic instead of searching all the pages to find out the topic. Index in database is used to improve the performance of the query. Index concept is there in both oralce as well as MS-SQL. Indexes are most useful on larger tables, on columns that are likely to appear in 'where ' clauses. We can create index on multiple columns and it is better to create less number of index with multiple column instead of creating index on each columns. If we create more index on the table then the DML operation will take longer time to execute.
There are two types of Index, first one is Btree and the other is Bitmap, but MS-SQL does not support Bitmap seraching.
A B-tree consist of a root node that contains a single page of data, zero or more intermediate levels containing additional pages and leaf level.
MS-SQL have two types of Btree index one is Clustered and the other is Non-Clustered Index.
Columns you defined for a clustered index are called clustering key. Only one cluster index is possible per table because it sorts the data physically. Actually a clustered index causes the rows in a table as well as the data pages in the doubly linked list that stores all the table data to be ordered according to the clustering key.
In contrast with clustered index, a non clustered index does not force a sort order on the table. We can create multiple non clustered index per table. Unlike a clustered index, in a non clustered index the leaf level of the index contains a pointer to the data instead of actual data. If the table has clustered index, the pointer points to the clustering key and it does not have pointer points to a relative identifier (RID), which is a reference to the physical location of the data within a data page.
SQL Server 2008 introduces new Index which is known as filtered Index.Filtered indexes basically allow you to create a filter on an index – think WHERE clause. The index B-Tree will only contain the rows of data that meet the filtering criteria. This allows you to reduce the amount of data contained in an index which means that you are also reducing the data affected in an index by data modifications.
CREATE NONCLUSTERED INDEX indx_Name ON Table(ClnDate) INCLUDE (clnSession)
WHERE colDate > DateFilter