SQL Views

8 Key excerpts on "SQL Views"

• 

  eBook - ePub

  PROC SQL

  Beyond the Basics Using SAS, Third Edition

  • Kirk Paul Lafler(Author)
  • 2019(Publication Date)
  • SAS Institute

    (Publisher)

  Views are one of the more powerful features available in the SQL procedure. They are commonly referred to as “virtual tables” to distinguish them from base tables. The simple difference is that views are not tables, but instead are files that consist of executable instructions. As a query, a view appears to behave as a table with one striking difference—it does not store any data. When referenced, a view always produces up-to-date results just like a table does. So how does a view get its data? Views access data from one or more underlying tables (base tables) or other views, provide you with your own personal access to data, and can be used in DATA steps as well as by SAS procedures.

  Views can be made to extend the security capabilities in dynamic environments where data duplication or data redundancy, logic complexities, and data security are an issue. When properly designed, views can be made to adhere to row-level and column-level security requirements by allowing access to only those columns and/or rows of data with relevance to the information needs of the application. Any columns and/or rows of data that are deemed “off limits,” or are classified as “restricted” can be eliminated from the view’s selection list. Consequently, views can be constructed to allow access to only those portions of an underlying table (or tables) that each user is permitted to access.

  Another important feature of views is that they ensure consistently derived data by creating calculated (computed) columns that are based on some arithmetic formula (or algorithm). Database purists often design tables to be free of calculated columns, thereby relying on the view to create computed columns. By allowing views to perform data aggregation, instead of the tables themselves, processing costs can be postponed until needed or requested.

  As a means of shielding users from complex logic constructs, views can be designed to look as though a database were designed specifically for a single user as well as for a group of users, each having different needs. Data references are coded once and, only when fully tested and ready for production, can be conveniently stored in common shareable libraries for all to access. Views ensure that the most current input data is used without the need for replicating partial or complete copies of the input data. They also require very little storage space because a view contains only its definition, and does not contain a copy of the data that it presents.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL Pocket Primer

  • Oswald Campesato(Author)
  • 2022(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  The preceding syntax is useful when you invoke a SQL script that modifies the definition of an existing view. Incidentally, you can also include such statements in a SQL file.

  Advantages of Views in SQL Statements

  Now that you understand what views are and how to create them, here is a list of some advantages of a view over SQL statements that directly access data from one or more tables:

  • restricted access to data (i.e., security)
  • simpler queries
  • abstraction of business logic

  Earlier, we briefly mentioned restricted access. For example, you might want to prevent users from accessing sensitive information, such as a table attribute that contains social security numbers.

  A view can be a replacement for a SQL statement that involves a multi-table join or a subquery. In fact, a view definition can consist of a combination of tables and other view definitions. Finally, a view can “abstract” away complex business logic in transactions, thereby reducing the likelihood of creating and executing incorrect SQL queries to retrieve the desired data. In a sense, SQL Views can function as an access layer between users and database tables.

  Views Involving a Single Table

  The following SQL statement defines a view over the customers table:

  MySQL [mytools]> CREATE VIEW V1 AS (SELECT * FROM customers); 1 row in set (0.002 sec)

  Note that the data that is visible via view V1 is identical to the data that is visible from the customers table, so view V1 does not provide any significant advantages. If you select the data rows from view V1 , you will see the same set of rows that are selected from the customers table.

  The interesting aspect of V1 is that it’s possible to insert data rows into V1 as well. However, this is not true in general; specifically, you cannot insert a data row into a view that is defined as a join of two or more tables if there is any

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  CHAPTER 18 VIEWs, Derived Tables, Materialized Tables, and Temporary Tables

  V IEWS, DERIVED TABLES, MATERIALIZED tables and temporary tables are ways of putting a query into a named schema object. By that, I mean they hold the query, rather than the results of the query.

  A VIEW is also called a virtual table, to distinguish it from temporary and base tables. The definition of a VIEW requires that it act as if an actual physical table is created when its name is invoked. Whether or not the database system actually materializes the results or uses other mechanisms to get the same effect is implementation-defined. The definition of a VIEW is kept in the schema tables to be invoked by name wherever a table could be used. If the VIEW is updatable, then additional rules apply.

  The SQL Standard separates administrative (DBA) privileges from user privileges. Table creation is administrative and query execution is a user privilege, so users cannot create their own VIEWs or TEMPORARY TABLEs without having Administrative privileges granted to them.

  In the Standard SQL model, a temporary table acts very much like a base table. It is persistent in the schema, but it “cleans itself up” automatically so users do not have to bother, and it can be shared among several users. The temporary table has the same user privileges model as a base table.

  However, a user can build a derived table inside a query. This is like building a VIEW on the fly. With the AS operator, we can give names to the results of subquery expressions and use them. The syntax is very simple, but the scoping rules often confuse new users.

  You can think of a VIEW’s name being replaced by a derived table expression when it is invoked in a query.

  18.1 VlEWs in Queries

  The Standard SQL syntax for the VIEW definition is

  The <levels clause> option in the WITH CHECK OPTION did not exist in SQL-89, and it is still not widely implemented. Section 18.5

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  26 Virtual Tables

  VIEWs, Derived Tables, CTEs, and MQTs

  VIEW s, derived tables, and CTEs (Common Table Expression) are ways of putting a query into a named schema object. By that, I mean these things hold the query text rather than the results of the query. They are executed as needed and then we see the results.

  A VIEW is also called a virtual table, to distinguish it from temporary and base tables. The definition of a VIEW in Standard SQL requires that it act as if an actual physical table is created when its name is invoked. Whether or not the database system actually materializes the results or uses other mechanisms to get the same effect is implementation defined. The definition of a VIEW is kept in the schema tables to be invoked by name wherever a table could be used. If the VIEW is updatable, then additional rules apply.

  The SQL Standard separates administrative (ADMIN ) privileges from user (USER ) privileges. Table creation is administrative and query execution is a user privilege, so users cannot create their own VIEW s or TEMPORARY TABLE s without having Administrative privileges granted to them. However, a user can create a CTE, which is a local, temporary virtual table.

  26.1 VIEW s in Queries

  The Standard SQL syntax for the VIEW definition is:

  CREATE VIEW <table name> [(<view column list>)] AS <query expression> [WITH [<levels clause>] CHECK OPTION] <levels clause>::= CASCADED | LOCAL

  The <levels clause> option in the WITH CHECK OPTION did not exist in SQL-89 and it is still not widely implemented. Section 26.5 will discuss this clause in detail. This clause has no effect on queries, but only on UPDATE, INSERT INTO , and DELETE FROM statements.

  A VIEW is different from a TEMPORARY TABLE , derived table, and base table. You cannot put constraints on a VIEW , as you can with base and TEMPORARY tables. A VIEW has no existence in the database until it is invoked, whereas a TEMPORARY TABLE

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

  • Joe Celko(Author)
  • 2008(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Views

  A VIEW IS a virtual table defined by a query that does not exist until it is invoked by name in an SQL statement. I will get into details about what the invocation can mean in physical terms shortly The Standard SQL syntax for the VIEW definition is

  CREATE VIEW <table name> [(<view column list>)]AS <query expression>[WITH [<levels clause>] CHECK OPTION]<levels clause> ::= CASCADED | LOCAL

  The <levels clause> option in the WITH CHECK OPTION did not exist in SQL-89 and it is still not widely implemented. This clause has no effect on queries, but only on UPDATE , INSERT , and DELETE statements. You cannot put constraints on a VIEW , as you can with base and TEMPORARY tables. A VIEW has no existence in the database until it is invoked, while a TEMPORARY table is persistent. A derived table exists only in the query in which it is created.

  The name of the VIEW must be unique within the entire database schema, like a table name. The VIEW definition cannot reference itself, since it does not exist yet. Nor can the definition reference only other VIEWs; the nesting of VIEWS must eventually resolve to underlying base tables. This only makes sense; if no base tables were involved, what would you be viewing?

  6.1 Mullins VIEW Usage Rules

  Craig Mullins gave the following rule to ensure that VIEWS are created in a responsible and useful manner. Simply stated, the VIEW creation strategy should be goal-oriented, VIEWs should be created only when they achieve a specific, reasonable goal. Each VIEW should have a specific application or business requirement that it fulfills before it is created. That requirement should be documented somewhere, preferably in a data dictionary

  Although this rule seems obvious, VIEWS are implemented at some shops without much thought as to how they will be used. This can cause the number of VIEWS that must be supported and maintained to continually expand until so many VIEWS

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Professional Microsoft SQL Server 2008 Programming

  • Robert Vieira(Author)
  • 2010(Publication Date)
  • Wrox

    (Publisher)

  8 Views

  Since we're assuming, in this book, that you already know something about SQL Server, I am going to minimize the discussion of the basics and focus primarily on the more meaty uses of views. That said, we'll touch ever so briefly on view basics before moving on.

  Views have a tendency to be used either too much, or not enough—rarely just right. When we're done with this chapter, you should be able to use views to:

  • Be more comfortable with view basics
  • Add additional indexing to your database to speed query performance—even when you're not using the view the index is based on
  • Understand and utilize the notion of partitioned views and federated servers

  A view is, at its core, really nothing more than a stored query. You can create a simple query that selects from only one table and leaves some columns out, or you can create a complex query that joins several tables and makes them appear as one.

  Reviewing View Syntax The most basic syntax for a view looks something like this:

  CREATE VIEW <view name> AS <SELECT statement>

  It utilizes that basic CREATE <object type> <object name>

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Handbook of Data Management

  1999 Edition

  • Sanjiv Purba(Author)
  • 2019(Publication Date)
  • Auerbach Publications

    (Publisher)

  Creating a view is similar to creating a user table in that a view object is created for the default database. This means that the view is actually a physical object. Running views causes a new user table to be built as a result of filtering table columns through the restrictions of the view. Views are identified uniquely with object names within a database. A view must be dropped before a view with the same name can be updated or recreated in that database. Once a view is created, it can be used as a user table name with the SELECT command. Views do not support triggers.

  A simplified syntax for the CREAT VIEW command is the following: Syntax: CREATE VIEW owner.viewname [column_list]   AS select_clause A simplified syntax for the command to drop a view from a database is as follows: Syntax: DROP VIEW owner. viewname A simplified syntax to execute a view after it is created is as follows: Syntax: SELECT [*/column_names] from view_name where search_clause Unlike many other SQL Server commands, view objects are created in the default database only. Examples:

  1. Create a view for a user who only requires a subset of fields in a table. Use a DROP VIEW command to drop the view before trying to create it to support iterative execution of the CREATE VIEW command. This code can be entered interactively or through an executed script file.
     DROP VIEW customer_contactsgoCREATE VIEW customer_contactsAS    SELECT first_name, last_name, home_phone,business_phone       FROM customergoSELECT * FROM customer_contacts                 /* retrieves all the columns in the view*/goSELECT first_name from customer_contactsgo
  2. In this example, a view is created to join two tables. This example demonstrates how views can be used to simplify database access by users. Instead of learning to write joins or other complex queries, users use a view name as a virtual table to retrieve their data. However, using views is not free. Someone in the data organization needs to be charged with the responsibility for building and maintaining views for the user community. This often turns into a full-time job. The caretaker of the views must understand the business application.
     DROP VIEW customer_typegoCREATE VIEW customer_typeAS    SELECT a.first_name, a.last_name, b.description       FROM customer a, member_type b          WHERE a.member_type = b.member_typegoSELECT * FROM customer_type             /* retrieves all the columns in the view */go

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Microsoft SQL Server 2012 Programming

  • Paul Atkinson, Robert Vieira(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  10 Views

  WHAT YOU WILL LEARN IN THIS CHAPTER:

  • The nature of views, and how they can be used
  • How to create, alter, and drop views using T-SQL
  • View management using SSMS
  • How to use views for abstraction and security
  • An introduction to indexed (or materialized) views

  Up to this point, you’ve been dealing with base objects — objects that have some level of substance of their own. In this chapter, you’re going to go virtual (well, mostly anyway), and take a look at views.

  Views have a tendency to be used either too much or not enough — rarely just right. When you’re done with this chapter, you should be able to use views to:

  • Reduce apparent database complexity for end users
  • Prevent sensitive columns from being selected, while still affording access to other important data
  • Add additional indexing to your database to speed query performance — even when you’re not using the view the index is based on

  A view is, at its core, nothing more than a stored query. What’s great is that you can mix and match your data from base tables (or other views) to create what will, in most respects, function just like an ordinary base table. You can create a simple query that selects from only one table and leaves some rows or columns out, or you can create a complex query that joins several tables and makes them appear as one.

  CREATING SIMPLE VIEWS

  The syntax for a view, in its most basic form, is a combination of a couple of things you’ve already seen in the book — the basic CREATE statement that you saw back in Chapter 5 , plus a SELECT statement like you’ve used over and over again:

  CREATE VIEW <view name> AS <SELECT statement>

  The preceding syntax just represents the minimum, of course, but it’s still all you need in a large percentage of the situations. The more extended syntax looks like this:

  Sign up to read

  Learn more about book