Views



About Views in SQL Server


Views are virtual tables that represent the result set of a select statement from one or more tables or other views. In most cases, that is unless a view is indexed, a view is basically a predefined query that is stored and executed whenever the view is referenced in a query.

Views are of 3 types:

  • partition views

  • updateable views

  • Indexed views

    1.Partition Views

    Distributed partitioned views are those that reference data across multiple servers and combine the data to the user as a single result set.

    Syntax:
    
    CREATE VIEW vDistributedSample
    AS
    SELECT col1, col2, col3 FROM Server1.DBName.dbo.TableName
    UNION ALL
    SELECT col1, col2, col3 FROM Server2. DBName.dbo.TableName
    UNION ALL
    SELECT col1, col2, col3 FROM Server3.DBName.dbo.TableName
    
    
    

    2.Updateable Views

    You can use a view to insert, update, and delete data in the underlying tables as long as certain conditions are met:

    All the columns being modified must be in the same base table.

    The columns must also directly reference the base table; you cannot modify computed columns or columns that are derived from or affected by aggregate functions.

    If the WITH CHECK option is specified, as shown in Listing 5, the view cannot be updated in any way that would cause the updated record to disappear from the result set.

    Syntax:
    
    USE AdventureWorks2008
    GO
    
    --Drop the view if it currently exists
    IF OBJECT_ID('dbo.vMarketingEmployees', 'V') IS NOT NULL
    DROP VIEW dbo.vMarketingEmployees;
    
    GO
    
    --Create a view using the WITH CHECK option
    CREATE VIEW vMarketingEmployees
    AS
    SELECT dbo.Employee.EmpFirstName AS FirstName,
    dbo.Employee.EmpLastName AS LastName,
    dbo.Department.DepartmentID,
    dbo.Department.DepartmentDscr AS Department
    FROM dbo.Department INNER JOIN
    dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID
    WHERE dbo.Department.DepartmentID = 1
    WITH CHECK OPTION
    
    
    

    3. Indexed Views

    If you have a process-intensive view that is run often, you can improve performance and reduce the processing time needed to execute the view by creating an index on the view.

    The first index you create on a view must be a unique clustered index, which causes the result set of the view to be stored in the database. Thereafter, you can create nonclustered indexes.

    Syntax:
    
    USE AdventureWorks2008
    GO
    
    CREATE VIEW vEmployees
    WITH SCHEMABINDING
    AS
    SELECT dbo.Employee.EmpFirstName AS FirstName,
    dbo.Employee.EmpLastName AS LastName,
    dbo.Department.DepartmentID,
    dbo.Department.DepartmentDscr AS Department
    FROM dbo.Department INNER JOIN
    dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID
    GO
    --Create an index on the view
    CREATE UNIQUE CLUSTERED INDEX IDX_vEmployee_Dept
    ON vEmployees (DepartmentID);
    GO
    
    

    Connection String

    SQL Server Connection String Formats

    posted on 2019-08-27 05:53:53 - SQL Server Tutorials


    UNION

    union in SQL server

    posted on 2019-08-09 23:05:33 - SQL Server Tutorials


    System Functions

    System Functions in SQL Server

    posted on 2019-08-09 06:14:33 - SQL Server Tutorials


    Prompt Examples

    ChatGPT Prompt Examples

    posted on 2023-06-21 22:37:19 - ChatGPT Tutorials


    Use Cases

    Chat GPT Key Use Cases

    posted on 2023-06-21 21:03:17 - ChatGPT Tutorials


    Prompt Frameworks

    Prompt Frameworks

    posted on 2023-06-21 19:33:06 - ChatGPT Tutorials