Views are like virtual tables that are built from a T-SQL query statement. The fields are dynamically produced based on the query used to build the table, and the values can be pulled from multiple tables. Views can be used in several ways to add functionality to a database. Using a view can allow users to access data that may be produced from several joins using a simpler query. Views could also be used as a security mechanism; users can access a view without having access to the entire source table, this can allow admins to grant visibility to a segment of fields instead of all of them. Views can also be used to reference tables using an alias; this technique can be useful for integrations or supporting older code that may reference outdated table names.
(Views, 2017)Typically, a view is not stored as a dedicated dataset; it just references the data stored in other tables. Some views need to be indexed in some cases to improve the performance. When indexing is applied the query used to create the view is computed, and the data is stored just like a normal table. These types of view are called indexed views. (Views, 2017)The following example is a simplified version of creating a view using a T-SQL statement based on the example found at (Create Views, 2017).
CREATE VIEW EmployeeHireDate AS SELECT p.FirstName, p.LastName, e.HireDate FROM Employee AS e JOIN Person AS p ON e.
BusinessEntityID = p.BusinessEntityID;In this example, the CREATE VIEW statement is used to the create view using a query that joins fields from two different tables. The SQL statement can now be used to view data from both tables using the view and therefore not requiring the join clause.SELECT FirstName, LastName, HireDate FROM EmployeeHireDate;Stored ProceduresStored procedures are like views; they can be used to simplify the access to data generated by more complicated queries. However, they do have some additional functionality.
One important feature is the ability to pass parameters to and from the procedure. (Robidoux, n.d.) The parameters that are passed to a procedure can be referenced during the execution of the procedure to filter data pulled by queries or be used as part of programming statements.
A procedure can also return values to a procedure that called it; these values are stored in output parameters. Multiple values can be returned using output parameters. (Gould, 2013) This functionality can be useful for pulling information from multiple tables without using a shared database key and then returning that data in in a single result set. The following is the T-SQL statement to create a procedure with the equivalent functionality of the previous view example.
CREATE PROCEDURE EmployeeHireDate AS SELECT p.FirstName, p.LastName, e.HireDate FROM Employee AS e JOIN Person AS p ON e.BusinessEntityID = p.
BusinessEntityID;To retrieve the results from the procedure, you would use the following SQL statement. EXEC EmployeeHireDate;