Skip to main content

6. VIEW

Image
By


§ A view is a virtual table.
§ Database views are created using the CREATE VIEW statement.
§ Views can be created from a single table, multiple tables, or another view.
§ When we update a view it will update underline base table.
CREATEVIEWview_nameAS
SELECT column1, column2.....
FROMtable_name
WHERE [condition]

Example:

CREATEVIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS
SELECT*FROM CUSTOMERS_VIEW

                      

Advantages

§ Reduce complexity of database schema.
§ Mechanism to implement row and column level security.
§ Represent aggregate data and hide detailed data.

Limitations

§ Cannot pass parameter to view.
§ Rules and default cannot be associated with the view.
§ Order by clause is invalid in a view unless TOP or XML is also specified.
§ Cannot be created on the bases of temporary tables.

Indexed View

§ Create unique clustered index on the view.
§ As we create index on a view it will become capable of storing data.
§ A view that is to be indexed has to be created with schema binding. This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped.
CREATEVIEWMyViewWITHSCHEMABINDINGAS
SELECT ID, Duration
FROMdbo.OAGTable
WHEREDurationinMin> 300
GO

CREATEUNIQUECLUSTEREDINDEXidx_MyViewONMyView(DurationinMin)
















<<Prev                                                                                         Next>>
Labels:

Comments

Post a Comment

Popular posts from this blog

10. SQL Aliases

By
§ SQL aliases are used to temporarily rename a table or a column heading. § Basically aliases are created to make column names more readable. Syntax for Columns SELECT column_name AS alias_name FROM table_name Syntax for Tables SELECT column_name ( s ) FROM table_name AS alias_name ; Here is a SQL statement with alias § Example SELECT o . OrderID , o . OrderDate , c . CustomerName FROM Customers AS c , Orders AS o WHERE c . CustomerName = ’Around the Horn’ AND c . CustomerID = o . CustomerID ; The same SQL statement without aliases: § Example SELECT Orders . OrderID , Orders . OrderDate , Customers . CustomerName FROM Customers , Orders WHERE Customers . CustomerName = "Around the Horn" AND Customers . CustomerID = Orders . CustomerID ; <<Prev                                                                           Next>>
Post a Comment
Read more

5. INDEX

By
§ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. § An index can be created in a table to find data more quickly and efficiently. § An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. § Indexes can be created or dropped with no effect on the data. § Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). Clustered indexes § Uses physical memory. § Easy to retrieve data. § Data is in stored form. Non-clustered indexes § No use of physical memory is needed. § More complicated, data retrieval is not that easy. § We can too createuniqueindex . Duplicate values are not allowed . CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX Pindex Difference between clustered index and no
Post a Comment
Read more