INTORDUCTION TO VIEWS
View
- Views are virtual tables defined by a query and generated when called.
Types of Views
- Standard Views
- Combine & filtering data from one or more tables
- Simplifies data manipulations & results
- Indeed Views
- view within a clustered index
- indeed views are computed & physically stored in the database
- Partitioned Views
- Combines horizontally partitioned data from member tables across one or more servers.
- Local portioned views combine data from tables On the same server
- Distributed partitioned views combine data from tables across servers.
View Usage
Standard views
- Simplify results by focusing on specific data
- Simplify security by controlling access through views rather than tables
- Provide backward compatibility during schema changes
Indexed Views
- Great for complex queries which return large results and require heavy processing
- Other queries can benefit from the index on a view
- Updates are reflected in the stored data.
Partitioned Views
- Scale our processing by portioning tables into multiple member tables.
CREATING & MODIFYING VIEWS
WORKING WITH VIEWS
PRACTICE
1)
Create view named as SQL Query ( standard view )
2)
Create view Named with SchemaBinding as SQLQuery ( INDEX VIEW )
Go
Create Unique clustered Index IX_AllTime
On Sales.vixAllTime(name)
3) Distributed Partitioned View
1. Create New Table1 on Server 1 , check constraint on portioning column
2. insert data from original table to Table1
3. Create New Table1 on Server2, check constraint on portioning colum
4. import data from Table1 from its original table1
5. Create Link Server on both server ( be made using login’s current security context )
6. Create view Sales on Server 1 as select query from Server 1 Union All select query from Server2
7. Create same view on server 2
Modifying Data Via View
—creating a Standard Views
Create View vABC
As
Select Statement
//access
Select * from vABC
—Creating Indexed Views
- It binds this view with the table, so you cant change based table
- Count_big(*) required
- Create unique clustered index
- If using group by then make index on this field
Create view vABC
With schemabinding
As
Select name, sum(qty),
Tags: Views