Use of Index and View in Sql Sever

Question

Use of Index and View in Sql Sever. I am confusing both. So any one help what is the main purpose of both. Explain me with example.

Daniel Jones · Answer

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

Bikesh Srivastava · Answer

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 .

Manish Kumar · Answer

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 Index3. 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 addressphone number card number etc. There is another typed of view known as Indexed view or Materialized view and Partition Views. Thanks