We use Views to remove complexity for an end user by saving a query for future use. Writing a statement where the select is from a table or from a view looks identical, so database architects will often begin the name of a view with a prefix v_ or vw. This is not required, but helps assist a developer or a Database Administrator to know what type of object they are working with.
The syntax for creating a view uses the keywords CREATE VIEW and then we name the view and use the keyword AS. We encapsulate our query with open and closed parentheses.
CREATE VIEW [ViewName] AS ( SELECT FROM TABLE );
We can create a view for our database to store the logic needed to find all the cats named Garfield.
CREATE VIEW v_SearchForGarfield AS ( Select * FROM Cat WHERE Cat.NameOfCat = 'Garfield' );
Command(s) completed successfully.
After we create this view, we can now use it in a select statement to retrieve the results we are looking for. Notice we no longer need the where logic to get the results we are looking for—they are already included in the view.
SELECT * FROM v_SearchForGarfield;
(1 row(s) affected)
All versions of RDBMS systems allow for security in the database. This allows us to control who sees what objects. Depending on your RDBMS, others may not be able to see the objects you create until you give them permission.
While we can use views to hide the complexity of a query, don’t make a habit of creating views from views. This process, called nesting, can make queries very difficult to troubleshoot and can lead to performance problems.