What is an index and a view in DATA Base Theory

Introduction to index

Index:- is created for query optimization,on the column of a table so that the split record of the table is clustered with an index, having a key and values concept.

Extents: SQL server stores the row of table in data pages. 8 physical adjacent data pages are called extent.

Clustered Index:  rearrange the data in sequence manner.

Non clustered index: the data is not physically rearranged in sequential manner.

Heap: a table that does not have a clustered index.

IAM (Index Allocation Map): pages are used to keep track of what an extent is being used for.

Allocation units: is used to view information about table index.

Views

Definition of View

View is a dynamic, virtual table computed or collated from data in the database.

Or

 View is a virtual table.It doesn't occupy the memory in physical memory. So if we make any changes

it wont be reflect on original table.Views are mainly for providing security

Partitioned view: joins the data of tables from 1 or more servers (computer). :OR A partitioned view merges the data from the member tables, such that resultant data appear as it taken from a single table.

<!Partition view is created using union operator.

<!Create view employees
AS 

 Select * from employees_Lahore

Union ALL

Select * from employees_islamabad

Union ALL

Select * from employees_Rawalpindi

Distributed Partitioned View: Can be updated only if the user has Control, Alter or view definition permissions on each of the member tables.

Guideline to Create a View:

a)   A View is created only in the current database.

b)  View must have a unique name not same as the table names in the schema (DB Definition).

c)   A view cannot have a full-text index.

d)  A view definition cannot contain compute, compute by and into keywords.

<!Object_Definition : function can be used to display the view definition by providing the object id of the view as input parameter . For example      Select  OBJECT_DEFINITION(Object_ID(‘vEmp’))