SQL Views

10 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 - PDF

  Database Development and Management

  • Lee Chao(Author)
  • 2006(Publication Date)
  • Auerbach Publications

    (Publisher)

  222 Database Development and Management For some database applications, you may need to provide calculated values. For example, the price of a product on sale is calculated by subtracting the deduction and adding the sales tax based on the reduced price. In this chapter, views and indexes are introduced to overcome the above-mentioned problems. A view is a virtual table. It is created by selecting data from one or multiple data sources, such as tables from one or more databases, other views, calculated values, or even data from Excel, but looks like a single table and can be used as a single table. The use of views will simplify a front-end user’s task in collecting data from multiple tables. A SQL statement that selects data from a view is just like a SQL statement that selects data from a table. A user may have the privilege to read and update data in a view but may not be given the privilege to read or update the associated table(s). In this way, we can avoid accidentally erasing or updating data in database tables. By using a view, we can also restrict users from accessing certain rows or columns in a table. For example, if a table contains employee information such as name, address, and salary, a secretary may only be allowed to access information about an employee’s name and address, but not salary. You can create a view with employees’ names and addresses and let the secretary access the view only. Views are much safer to be used by front-end users. Also, views are dynamically created in memory. Updating data in memory instead of in database tables also speeds up performance. For many database applications, you should create views first and then bind the views to applications. The applications will use these views as tables. The advantages of using views are summarized below. Views can be used to limit the actions such as inserting, deleting, and updating tables directly by front-end users.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Cloud Database Development and Management

  • Lee Chao(Author)
  • 2013(Publication Date)
  • Auerbach Publications

    (Publisher)

  A view can be created by selecting data from one or multiple data sources, such as tables from one or more databases, other views, calculated values, or even data from spreadsheets. You may consider a view as a virtual table. It looks like a single table and can be used as a single table. An SQL statement that selects data from a view is just like an SQL statement that selects data from a table. The advantages of using views are summarized below: ◾ Views are more secure. Views limit the database management activities such as inserting, deleting, and updating tables directly by front-end users. A view only provides data that are necessary for an application. ◾ Views improve the performance. Data stored in a view are ready for an application to use. There is no need for the application to search various data sources to obtain the data. Usually, tables are not used to store calculated values, which may hold back performance. A view can have all the columns needed for calculation and a calculated column that stores calculation results. ◾ Views simplify data manipulation tasks. When a table name is changed, one can redefine the view to use the new table name; there is no need to modify the code in every database application that is tied to the view. Owing to these advantages, views are widely used with database applications. Next, you will learn how to create and manage the views. 8.2.1 Create Views Like creating other database objects, you can use the CREATE VIEW statement to create a view. The syntax for creating a view is given below: CREATE VIEW [ db_name .] [ owner .] view_name [( column [,... n ])] [WITH < view_attribute > [,...n]]

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  A Guide to SQL

  • Mark Shellman, Hassan Afyouni, Philip Pratt, Mary Last, Mark Shellman(Authors)
  • 2020(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  A view is a derived table because the data in it comes from one or more base tables. To the user, a view appears to be an actual table, but it is not. In many cases, a user can examine table data using a view. Because a view usually includes less informa- tion than the full database, its use can represent a great simplification. Views also provide a measure of security, because omitting sensitive tables or columns from a view renders them unavailable to anyone accessing the database through the view. Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Module 7 202 To help you understand the concept of a view, suppose that Martina is interested in the item ID, description, units on hand, and unit price of items in category DOG. She is not interested in any other columns in the ITEM table, nor is she interested in any rows that correspond to items in other categories. Viewing this data would be simpler for Martina if the other rows and columns were not even present. Although you cannot change the structure of the ITEM table and omit some of its rows just for Martina, you can do the next best thing. You can provide her with a view that consists of only the rows and columns that she needs to access. A view is defined by creating a defining query, which indicates the rows and columns to include in the view. The SQL command (or the defining query) to create the view for Martina is illustrated in Example 1. EXAMPLE 1: Create a view named DOGS that consists of the item ID, description, units on hand, and unit price of each item in category DOG.

  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 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

  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

  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
• 

  eBook - PDF

  Beginning SQL

  • Paul Wilton, John Colby(Authors)
  • 2005(Publication Date)
  • Wrox

    (Publisher)

  Using this strategy makes it possible to determine exactly what information a specific user is allowed to view. Vertical and Horizontal Views Vertical views represent select columns of one or more tables such that the user can see only portions of a table or tables. This allows you to hide sensitive data columns from certain users while allowing access to those columns to other users. For example, suppose you had a business rule that states that employees in the Personnel department should be able to see the names and addresses of all employees, but not the SSN or salary. You might implement this view with a SQL statement similar to the following: CREATE VIEW vEmployeeNonSensitive AS SELECT Name, Address, City, State, ZIP, Phone FROM tblEmployees 337 SQL Security You could then give SELECT privileges to the usrPrsnl user ID. That user can now view any of the non- sensitive fields from tblEmployees, as shown in Figure 12-5. In order to give Payroll access to the SSN and Salary fields, you might implement a view with a SQL statement similar to the following: CREATE VIEW vEmployeeSensitive AS SELECT Name, SSN, Salary FROM tblEmployees Figure 12-5 Name Addr City TBL Employee View Non Sensative Usr Personnel Can Access Cannot Access Sensitive Fields State Zip Phone SSN Salary Age Name Addr City State Zip Phone 338 Chapter 12 You could then give SELECT privileges to vEmployeeSensitive to the usrPayroll user ID. Figure 12-6 illustrates that the user usrPayroll can now view only the person’s name, SSN, and salary fields while hiding address and phone numbers. Horizontal views, on the other hand, allow the user to view all the fields in the table but only for selected sets of records. For example, suppose you had a business rule that states that a department manager should be able to view all personnel information for only the employees in their department.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Automated Physical Database Design and Tuning

  • Nicolas Bruno(Author)
  • 2011(Publication Date)
  • CRC Press

    (Publisher)

  Chapter 8 Handling Materialized Views Similar to indexes, materialized views are redundant data structures that can be used to speed up query processing. A view is a virtual table whose content is derived from base tables by using a subset of Standard Query Language ( SQL ) (we discuss the language used to define views in Section 8.1). A view is materialized by defining an appropriate clustered index. Then, the content of the view is persisted and maintained as if it were a regular table, which can be indexed and leveraged to answer queries efficiently. Consider again the query from Chapter 2 that returns the names of employees working in departments with budgets larger than $10 million: Q1 = SELECT Emp.name FROM Emp, Dept WHERE Emp.DId = Dept.DId AND Dept.Budget>10M Suppose that we create a materialized view MV1 that precomputes the join be-tween employees and departments. This view, which we denote MV1 , is defined as follows: MV1 = SELECT Emp.EId, Dept.DId, Emp.name, Dept.Budget FROM Emp, Dept WHERE Emp.DId = Dept.DId Using MV1 , we can rewrite the original Q1 into an equivalent form as follows: Q2 = SELECT MV1.name FROM MV1 WHERE MV1.Budget>10M In general, a view must subsume a query subexpression to be useful in an-swering the full query (i.e., the view definition must contain at least all the tuples required in the query subexpression as well as means to filter out the tuples that are not relevant). MV1 subsumes the whole Q1 , since the results of MV1 are a superset of those of Q1 . For Q1 and Q2 to be equivalent, we add the filter condition MV1.Budget > 10M in Q2 , generally referred to as a compensating action . Just by syntactically looking at Q1 and Q2 , it cannot be determined which alternative would lead to the most efficient execution 139 140 Automated Physical Database Design and Tuning plan.

  Sign up to read

  Learn more about book