Creating SQL Views

4 Key excerpts on "Creating SQL Views"

• 

  eBook - ePub

  PROC SQL

  Beyond the Basics Using SAS, Third Edition

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

    (Publisher)

  A joined view is based on the joining of two or more base tables. This type of view is often used in table-lookup operations to expand (or translate) coded data into text. Nested view A nested view is based on one view being dependent on another view such as with subqueries. Hybrid view An integration of one or more view types for the purpose of handling more complex tasks.

  Creating Views

  You use the CREATE VIEW statement in the SQL procedure to create a view. When the SQL processor reads the words CREATE VIEW, it expects to find a name assigned to the newly created view. The SELECT statement defines the names assigned to the view’s columns as well as their order.

  Views are often constructed so that the order of the columns is different from the base table. In the next example, a view is created with the columns appearing in a different order from the original MANUFACTURERS base table. The view’s SELECT statement does not execute during this step because its only purpose is to define the view in the CREATE VIEW statement.

  SQL Code

  PROC SQL;   CREATE VIEW MANUFACTURERS_VIEW AS     SELECT manuname, manunum, manucity, manustat       FROM MANUFACTURERS; QUIT;

  SAS Log Results

         PROC SQL;          CREATE VIEW MANUFACTURERS_VIEW AS            SELECT manuname, manunum, manucity, manustat              FROM MANUFACTURERS;   NOTE: SQL view WORK.MANUFACTURERS_VIEW has been defined.        QUIT;     NOTE: PROCEDURE SQL used:         real time           0.44 seconds

  When you create a view, you can create columns that are not present in the base table from which you built your view. That is, you can create columns that are the result of an operation (addition, subtraction, multiplication, etc.) on one or more columns in the base tables. You can also build a view using one or more unmodified columns of one or more base tables. Columns created this way are referred to as derived columns or calculated columns

  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