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.