Tech Junkie Blog - Real World Tutorials, Happy Coding!: SQL: Views

Thursday, June 10, 2021

SQL: Views

Views are virtual tables that you can create that others can use without knowing the complexity of the query, but can be used like a table. Also they can provided an added security by giving developers access to a view instead of the underlying table. Views does not contain data in itself the data stays at the tables that the views are created from. Complex views can degrade performance since they contain no data the query must be processed every time. Let's say a junior developer just came on board and he doesn't really know SQL that well. You can create a view of the more complex views to work with until he gets better with his SQL.

CREATE VIEW EmployeeTerritoriesDescriptions AS
SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e
INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID
INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID

The view above queries the employees territories using joins, by creating a view the person using the view does not have to know the underlying table structures that is in the database they can just use the view.



If you check in SQL Server you will see that a new view call EmployeeTerritoriesDescriptions has been created



To select a view you just select it like any other table, by using the SELECT statement. Here is an example of how you would select the EmployeeTerritoriesDescriptions view after it has been created.
SELECT Name,TerritoryDescription,TerritoryID
FROM EmployeeTerritoriesDescriptions

Here are the results:



As you can see a view is a great way to hide the complexity of a query. All you need to do is query the columns in the view and don't have to worry about the complex query any longer.


5 comments:

  1. Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts.
    Business Submission & Search:

    ReplyDelete
  2. https://www.luxuryhotelholidays.com/blog/item/2015-greece-and-the-%E2%82%ACuro-grexit#comment1464045

    ReplyDelete
  3. Nice blogs. thanks for sharing these information with all of us. Kinemaster Lite

    ReplyDelete
  4. Predicting such kind of material in the years to come as it is absolutely helpful and educational.
    url opener
    online filmek
    uwatchfree

    ReplyDelete

Search This Blog