VIEWS Understanding by chirag

DEFINITION

  • A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables. It does not hold any data and does not exist physically in the database. Similar to a SQL table, the view name should be unique in a database. 
  • It contains a set of predefined SQL queries to fetch data from the database. It can contain database tables from single or multiple databases as well.
  • A VIEW does not require any storage in a database because it does not exist physically

How Does an SQL View Work?

A table (concrete table) stores its data in columns and rows in the database. A view (virtual table) is built on top of the concrete table(s) it fetches data from and does not store any data of its own in the database. A view only contains the SQL query that is used to fetch the data.

To summarize, the result set of a view is not materialized on the disk, and the query stored by the view is run every time we call the view. Let’s look at the diagram below to understand the difference between concrete and virtual tables.



Uses of a View 

  1. Restricting data access – Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table.
  2. Hiding data complexity – A view can hide the complexity that exists in multiple tables join.
  3. Simplify commands for the user – Views allow the user to select information from multiple tables without requiring the users to actually know how to perform a join.
  4. Store complex queries – Views can be used to store complex queries.
  5. Rename Columns – Views can also be used to rename the columns without affecting the base tables provided the number of columns in view must match the number of columns specified in select statement. Thus, renaming helps to hide the names of the columns of the base tables.
  6. Multiple view facility – Different views can be created on the same table for different users.

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Query a view
Once a view is created, it can be queried like a regular table.
SELECT * FROM VW_employee_data
Views with conditions
SELECT * FROM VW_employee_data WHERE gender = 'female'
Using the where clause, we filter the views table for employees who are female.
Insert data into a view
In general, it isn’t recommended to directly insert data into a views table as views are virtual tables and don’t store data on their own. However, there are advanced cases where you can perform data insertion through the view. This is beyond the scope of this article.
It is recommended, to insert the data directly through the original table.
Insert data through the original table
If you add data to one of the tables from which the view is derived, the data will automatically be added and updated in the view.
Updating a View
In most cases, you can’t update a view directly. However, you can update the original table and the changes will be reflected in the view.
SQL VIEW for DML (Update, Delete and Insert) queries

We can use SQL VIEW to insert, update and delete data in a single SQL table. We need to note the following things regarding this.

  1. We can use DML operation on a single table only
  2. VIEW should not contain Group By, Having, Distinct clauses
  3. We cannot use a subquery in a VIEW in SQL Server
  4. We cannot use Set operators in a SQL VIEW

Use the following queries to perform DML operation using VIEW in SQL Server.

  • Insert DML
  • Delete DML
  • Update DML

    Getting Information about a view


    Sp_Helptext View

    A view is deleted with the DROP VIEW statement.
    DROP VIEW view_name;

    Renaming the View in SQL Server


    Sp_Rename OldViewName , NewViewName

    SQL VIEW and Check Option

    We can use WITH CHECK option to check the conditions in VIEW are inline with the DML statements.

    • It prevents to insert rows in the table where the condition in the Where clause is not satisfied
    • If the condition does not satisfy, we get an error message in the insert or update statement

    In the following query, we use the CHECK option, and we want only values starting with letter F in the [Codeone] column.

    CREATE VIEW DemoView
    AS
         SELECT *
         FROM [dbo].[MyTable]
         WHERE [Codeone] LIKE 'F%'
    WITH CHECK OPTION;

    If we try to insert a value that does not match the condition, we get the following error message.


    Summary
    SQL views are a powerful tool that allows you to simplify complex queries, enforce security, and simplify the structure of tables.
    -------------------------------------------------------------------------
    QUESTIONS & ANSWERS
    Q1-Can we drop a table that has dependent views on it?

    Yes, we can drop a table even if any dependent views are associated with it, but the views that are associated with it will not be dropped. They will still execute in the database only with the status as inactive object and all those views become active and start functioning provided the table is recreated.

    Q2-Can you create a view based on other views?

    Yes, you can create a view based on other views. Usually we create views based on tables, but it also possible to create views based on views.

    Q3-Can you update views?

    Yes, views can be updated. However, updating a view that is based on multiple tables, may not update the underlying tables correctly. To correctly update a view that is based on multiple tables you can make use INSTEAD OF triggers in SQL Server.





    Comments

    Post a Comment

    Popular posts from this blog

    Overfitting , Underfitting Bias & Variance Understanding by CHIRAG

    Image
      What is Overfitting? Overfitting means model has High accuracy score on training data but low accuracy score on test data. (That means our model best fit line satisfied all the training data points . ie. Low Training Error & High Testing Error & High Training Accuracy & Low Test Accuracy ) Reasons for Overfitting:   High variance(High Test Error) .  Low Bias(Low Training Error). The model is too complex. The size of the training data. What is Bias? Bias is the difference between the average prediction of our model and the correct value which we are trying to predict. A model with high bias pays very little attention to the training data and oversimplifies the model. Let’s assume we have trained the model and are trying to predict values with input ‘x_train’. The predicted values are y_predicted.  Bias is the error rate of y_predicted and y_train. In simple terms.   think of bias as the error rate of the training data. When the error rate is high,...
    Read more

    Linear Regression Understanding by CHIRAG

    Image
      Linear Regression 1 .)Linear regression is a supervised learning algorithm used when target / dependent variable continues real number. 2.)Linear regression is a type of statistical analysis used to predict the relationship between dependent and independent Variables using BEST FIT LINE. 3.) It work on the principle of ordinary least square (OLS)/ Mean square errror (MSE) Simple Linear Regression In a simple linear regression, there is one independent variable and one dependent variable. The model estimates the slope and intercept of the line of best fit, which represents the relationship between the variables. The slope represents the change in the dependent variable for each unit change in the independent variable, while the intercept represents the predicted value of the dependent variable when the independent variable is zero. Mathematically, we can represent a linear regression as: y= a0+a1x+ ε Y= Dependent Variable (Target Variable) X= Independent Variable (predictor Variab...
    Read more

    Understanding Confusion Matrix by CHIRAG

    Image
    What Is a Confusion Matrix? A confusion matrix is a performance evaluation tool in machine learning, representing the accuracy of a classification model.  It displays the number of true positives, true negatives, false positives, and false negatives. A Confusion matrix is an N x N matrix used for evaluating the performance of a classification model, where N is the total number of target classes.     For a binary classification problem, we would have a 2 x 2 matrix, as shown below, with 4 values: Let’s understanding this matrix: The target variable has two values: 0 & 1 The  columns  represent the  Predicted values  of the target variable The  rows  represent the  Actual values  of the target variable. But wait – what’s TP, FP, FN, and TN here? That’s the crucial part of a confusion matrix. Let’s understand each term below. Important Terms in a Confusion Matrix True Positive (TP)  The predicted value matches the actual value...
    Read more