Scribd Logo
Upload

Welcome to Scribd!

  • 43 views

    View Dsil

    Uploaded by

    neepolion
    A view is a virtual table that is defined by a query. Views allow users to see data from one or more tables in a restricted or combined way. Views can be used for data security, simplifying complex queries, and presenting different perspectives of the same data. Views are created using the CREATE VIEW statement and can be queried, updated, and deleted like tables depending on how they are defined. Indexed views store the results of their query on disk like a table to improve performance.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd

    View Dsil

    Uploaded by

    neepolion
    43 views16 pages
    A view is a virtual table that is defined by a query. Views allow users to see data from one or more tables in a restricted or combined way. Views can be used for data security, simplifying complex queries, and presenting different perspectives of the same data. Views are created using the CREATE VIEW statement and can be queried, updated, and deleted like tables depending on how they are defined. Indexed views store the results of their query on disk like a table to improve performance.

    Original Title

    View-Dsil

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    A view is a virtual table that is defined by a query. Views allow users to see data from one or more tables in a restricted or combined way. Views can be used for data security, simplifying complex queries, and presenting different perspectives of the same data. Views are created using the CREATE VIEW statement and can be queried, updated, and deleted like tables depending on how they are defined. Indexed views store the results of their query on disk like a table to improve performance.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    43 views16 pages

    View Dsil

    Uploaded by

    neepolion
    A view is a virtual table that is defined by a query. Views allow users to see data from one or more tables in a restricted or combined way. Views can be used for data security, simplifying complex queries, and presenting different perspectives of the same data. Views are created using the CREATE VIEW statement and can be queried, updated, and deleted like tables depending on how they are defined. Indexed views store the results of their query on disk like a table to improve performance.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 16

    What is VIEW

    Logically represents subsets of data from one or more tables A view is a DBO that consists of columns from one or more tables. It is a query stored as an object.

    View can be referred like any other table in a database.


    A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them

    Advantages of VIEW
    To restrict data access To make complex queries easy

    To provide data independence

    To present different views of the same data

    User Defined Views


    User Defined Views
    Simple View Complex View Indexed View

    User Defined View


    These Views are created by a user as per requirements. Simple Views and Complex Views

    Creating a View

    CREATE VIEW View_Name [(Column List)] [with {ENCRYPTION |SCHEMABINDING | VIEW METADAT}] AS Select [WITH CHECK OPTION]

    Example : Simple View


    Create the EMPVDEPT10 view, which contains details of employees in department 10

    CREATE VIEW EMPVDEPT10 AS SELECT Eno,EmpName,Salary,DeptNo FROM employees WHERE DeptNo = 10

    Example: Complex View


    Create a complex view that contains group functions to display values from two tables

    CREATE VIEW EMPV (Ename, Minsal, Maxsal, Avgsal) AS SELECT d.DeptName,MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e JOIN Departments d ON (e.DeptNo = d.DeptId) GROUP BY d.DeptName

    Rules for Performing Insert Operations on a View


    You cannot add data through a view if the view includes:
    Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

    Querying a View

    Rules for Performing Update Operations on a View


    You cannot modify data in a view if it contains:
    Group functions A GROUP BY clause The DISTINCT keyword The pseudo column ROWNUM keyword Columns defined by expressions

    Rules for Performing Delete Operations on a View


    You can usually perform DML operations on simple views. You cannot remove a row if the view contains the following:
    Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword

    Using the WITH CHECK OPTION Clause


    CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck
    You can ensure that DML operations performed on the view stay in the domain of the view by using the WITH CHECK OPTION clause:

    Any attempt to change the department number for any row in the view fails because it violates the WITH CHECK OPTION constraint.

    WITH Encryption
    The WITH ENCRYPTION option encrypts the SELECT statement, thus enhancing the security of the database system. Software vendors who use SQL Server in the back end often encrypt their views or stored procedures in order to prevent tampering or reverse-engineering from clients or competitors. If you use encryption, be sure to save the original, unencrypted definition.

    WITH SCHEMABINDING
    The SCHEMABINDING clause binds the view to the schema of the underlying table. Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.

    Drop View
    You can remove a view without losing data because a view is based on underlying tables in the database. DROP VIEW view; DROP VIEW empvu80; ALTER VIEW "Alphabetical list of products" AS SELECT Statement

    Indexed View
    A view created with a unique clustered index is known as an Indexed View or Materialized View. Unlike views, an Indexed View exists on the disk like a table in which the clustered index is created. Once a clustered index is created you may create non-clustered indexes on the view CREATE VIEW vwOrderDetails WITH SCHEMABINDING AS SELECT OD.OrderID, OD.ProductID, P.ProductName , OD.UnitPrice , OD.Quantity, OD.Discount FROM dbo.Products P INNER JOIN dbo.[Order Details] OD ON P.ProductID = OD.ProductID GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE UNIQUE CLUSTERED INDEX [IDX_vwOrderDetails]

    You might also like