Use of Index and View in Sql Sever

Index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

 

View is really nothing more than a logical representation of one or more tables in a database. Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.

 

To know about the different type of views, visit: http://sqltechtips.blogspot.com/2015/12/views-in-database.html

View;-We can contains infromation from multiple table.and store in virtual table.

index:- Its machanism to get data very fastly from table. on the behalf of index . 

Index is basically a mark (sql server assigning internal numbers) in a table to identify a record in quick manner. Like you have seen index in a book let's say a page number. If you have to reach at certain page number you will note the page number from index-page (2nd or 3rd page in every book) and you will reach quickly.

 

Same mechanism is applied in sql server. Internal number (say row number) is assigned by sql server and for any searching value this will go to look/seek a value.

 

Algorithm behind index is B-Tree structure (Balanced tree) i.e one root node (say Parent node)  then their intermediate node (child node) then leaf node (sub-child node).

 

Further Index is divided into 2 types :Clustered and Non-Clustered index.

Clustered Index : We can apply only one clustered index on a table as clustered index sort data internally and logically we can only sort data  with respect to one column not the multiple column. Data is stored at leaf node of B-Tree.

Non-Clustered Index: Depends on sql server version we can apply different number of index. For 2005 we can have 249 non clustered index, for 2008 and above 999 non clustered index can be appy.In Non clustered index it has pointer at leaf node that pointing to clustered index address. Further we have different non clustered index.

1. Covering Index ,2 Filtered Index,3. Spatial index  (for longitude .latitude, area ,polygon purpose).

 

When ever we are doing any DML operation on table (Insert, update or delete) sql server is maintaining index position as well. Mean to say lets say you have removed some pages from a book, so for a symmetric serial number on page  you have to modify the page number. 

Also there is new index is available from sql server 2012 Column stored index. 

Index is a very lengthy topic so cannot discuss all points here so gradually study more it.

 

Now come to view.

A view some times called as virtual table where you can apply  select  statement (some times you may update view as well). In view you can restrict user to not show important columns. For example, you can not show a column with email address,phone number, card number etc. There is another typed of view known as Indexed view or Materialized view and Partition Views.

 

Thanks