Views in TSQL - T-SQL

A view is one of the simplest database objects. On the surface, a view is nothing more than a SELECT query that is saved with a name in a database. Ask any modern-day programmer what they believe to be the most important and fundamental concept of programming. They will likely tell you that it is code reuse. Writing every line of code, every object, every script, and every query represents a cost or risk. One risk is that there could be a mistake (a bug) in the code. The cost of a bug is that it must be fixed (debugged) and tested. Buggy applications must be redeployed, shipped, installed, and supported. Undiscovered bugs pose a risk to productivity, business viability, and perhaps even legal exposure. One of the few constants in the software universe is change. Business rules will change, program logic will change, and the structure of your databases will also change. For all of these and other reasons, it just makes sense to reduce the number of objects that you create and use in your solutions. If you can create one object and reuse it in several places rather than duplicating the same effort, this limits your exposure to risk. Views promote this concept of code reuse by enabling you to save common queries into a uniform object. Rather than rewriting queries, complex queries can be created and tested and then reused without the added risk of starting over the next time you need to add functionality to an application.

Virtual Tables

One of the great challenges facing users is dealing with the complexity of large business databases. Many tools are available for use by casual database consumers for browsing data and building reports. Applications such as Microsoft Excel and Access are often used by information workers, rather than programmers, to obtain critical business management and operational information. A typical mid-scale database can contain scores of tables that contain supporting or special-purpose data. To reassemble the information stored in a large database, several tables must be joined in queries that take even skilled database professionals time and effort to create effectively. As you've seen in many examples, this is often not a trivial task. From the user's perspective, views are tables. They show up in most applications connecting to a SQL Server, along with the tables. A view is addressed in a SELECT statement and exposed columns, just like a table.

From the developer or database designer's perspective, a view can be a complex query that is exposed as if it were a simple table. This gives you an enormous amount of flexibility and the ability to hide all of the query logic, exposing a simple object. Users simply see a table-like object from which they can select data.

Creating a View

Defining a view is quite simple. First of all, a database user must be granted permission to create a view. This is a task that you may want to have performed only by a database administrator or a select number of trusted users. Because creating a view isn't particularly complicated, you may want certain users to be granted this ability. Several simplified tools are available that you can use to create views. Microsoft Access, SQL Server Management Studio, and Visual Studio all leverage the T-SQL Query Designer interface to create and manage views. The process is just about the same in all of these tools because they all actually expose the same components. The following section steps through creating a view using Management Studio. I will not demonstrate each tool because the process is nearly identical.

Creating a View in Management Studio

Creating a view with Management Studio is very easy using the graphical query designer. It is basically the same designer used by Visual Studio and Microsoft Access. In Management Studio's Object Explorer, navigate to the AdventureWorks2008 database and then expand the database to expose the Views folder. Right-click the Views folder and choose New View, as shown in Figure Below.

Creating a View in Management Studio

After clicking New View, the Add Table dialog appears, as shown in Figure Below. Add the Product,
Product Category, and Product Subcategory tables. Then click the Close button.

Add table

After you close the Add Table dialog, the graphical query designer is displayed. It shows the three tables connected by relation links in the top Diagram pane. Because of the relationships that exist between these tables, inner joins are automatically defined in the query. Beneath the Diagram pane is the Criteria pane, SQL pane, and then the Show Results pane, as shown in Figure Below.

Views-Figure

Each one of the panes can be displayed or hidden by clicking on the associated button on the View Designer toolbar (see Figure Below).

View Designer toolbar

Select the Name column from the Product Category table and the Name column from the ProductSubcategory table. Then select the ProductID, Name, ProductNumber and ListPrice columns from the Product table (using the checkboxes in the table windows). Using the Alias column in the columns grid, define aliases for the following three columns:

aliases for the three columns

Also, designate these three columns for sorting in the order listed by dropping down and selecting the word Ascending in the Sort Type column. Check your results against Figure (Show In Below) and make any adjustments necessary.

results against Figure

Notice that something interesting happens to the SQL that is being written for you when you choose sort criteria: the query designer adds the TOP 100 PERCENT statement to your code.

Back when the original ANSI SQL specification was written, its authors wanted to ensure that database designers wouldn't create SQL queries that would waste server resources. Keep in mind that this was at a time when production servers had 32MB of memory. One common memory-intensive operation is reordering a large result set. So, in their infinite wisdom, the authors imposed a rule that views cannot support the ORDER BY clause unless the results are restricted using a TOP statement.

If you close the window, using the Close button in the top-right corner, Management Studio prompts you to save the view. Click Yes to save the view and enter a name for the new view in the Choose Name dialog, as shown in Figure (Show In Figure Below). I've always made it a point to prefix view names with v, vw, or vw_ and to use Pascal case (no spaces, with the first letter of each word capitalized). This ensures that when you retrieve objects with older data access methods, the drivers rarely differentiated between tables and views. When creating a data application, it is generally pretty important to know if the object you're referencing is a physical table or a view.

Views-Figure

After you save the view, it appears in the list of views in Object Explorer and can now be queried as you would query a table, as shown in Figure (Show In Below).

query a table

Creating a View Using SQL Script

Regardless of the tool or product used to create a view, as you saw in the previous example, SQL script runs in the background and the result will be as varied as handwriting without the use of an automated tool. The syntax for creating a new view is quite simple. The pattern is the same whether the query is very simple or extremely complex. I'll start with a simple view on a single table:

CREATE VIEW vProductCosts AS SELECT ProductID, Name, StandardCost FROM Production.Product

To continue working with this view and extend its capabilities, I can either use the ALTER command to make modifications to the existing view or drop and create it. Using the ALTER statement rather than dropping and re-creating a view has the advantage of keeping any existing properties and security permissions intact.
Here are examples of these two statements. The ALTER statement is issued with the revised view definition:

ALTER VIEW vProductCosts AS SELECT ProductID, ProductSubcategoryID, Name, ProductNumber, StandardCost FROM Production.Product

Using the DROP statement will wipe the slate clean, so to speak, reinitializing properties and security permissions. But for the sake of the following example, don't run this code just yet.

DROP VIEW vProductCosts

What happens if there are dependencies on a view? I'll conduct a simple experiment by creating another view that selects data from the view previously created:

CREATE VIEW vProductCosts2 AS SELECT Name, Standard Cost FROM vProductCosts

For this view to work the first view has to exist and it must support the columns it references. Now, what happens if I try to drop the first view? I'll execute the previous DROP command. Here's what SQL Server returns:

Command(s) completed successfully.

The view is gone? What happens if I execute a query using the second view?

SELECT * FROM vProductCosts2

SQL Server returns this information:

Msg 208, Level 16, State 1, Procedure vProductCosts2, Line 3 Invalid object name 'vProductCosts' Msg 4413, Level 16, State 1, Line 1 Could not use view or function 'vProductCosts2' because of binding errors.

Why would SQL Server allow something so silly? I may not be able to answer this question to your satisfaction because I can't answer the question to my own satisfaction. This capability to drop an object and break something else is actually documented as a feature call delayed resolution. It's a holdover from the early days of SQL Server, but to a degree it makes sense. The perk of this feature is that if you needed to write script to drop all of the objects in the database and then create them again, this would be difficult to pull off with a lot of complex dependencies. If you're uncomfortable with this explanation, there is good news. An optional directive on the CREATE VIEW statement called SCHEMA BINDING tells SQL Server to check for dependencies and disallow any modifications that would violate them. To demonstrate, the first thing I'll do is drop both of these views and then re-create them:

Some unique requirements are apparent in the example script. First of all, for a view to be schema-bound, any objects it depends on must also be schema-bound. Tables inherently support schema binding, but views must be explicitly schema-bound.

Any dependent objects must exist in the database before they can be referenced. For this reason, it's necessary to use batch delineation statements between dependent CREATE object statements. This example used the GO statement to finalize creating the first view.

When referring to a dependent view, you must use a two-part name. This means that you must use the schema name (which, as you can see in this example, is dbo). A schema-bound view also cannot use the SELECT * syntax. All columns must be explicitly referenced.

Ordering Rows

As mentioned earlier when working with the View designer, ordering rows in a view is not allowed without the TOP statement.

I run into this restriction all of the time. I'll spend some time creating some big, multi-table join or sub query with ordered results. After it's working, I think, "Hey, I ought to make this into a view.” So I slap a CREATE VIEW vMyBigGnarlyQuery AS statement on the front of the script and execute the script with this result:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Then I remember I have to use a TOP statement. This is a no-brainer and is easily rectified using the following workaround:

Now that most database servers have 500 times the horsepower and 100 times the memory of those 10 to 15 years ago, ordering a large result set is of much lesser concern.

Partitioned Views

Every system has its limits. Performance-tuning and capacity planning is the science of identifying these gaps and formulating appropriate plans to alleviate them. To partition data is to place tables or other objects in different files and on different disk drives to improve performance and organize data storage on a server. One of the most common methods to increase the performance and fault-tolerance of a database server is to implement RAID storage devices. Although this isn't a book on server configuration, I bring this up for a good reason. In teaching classes on database design and talking about partitioning data across multiple hard disks, I've often heard experienced students ask, "Why don't you just use a RAID device? Doesn't it accomplish the same thing?” Yes, to a point. Disk arrays using RAID 5 or RAID 10 simply spread data across an array of physical disks, improving performance and providing fault-tolerance. However, data partitioning techniques and using RAID are not necessarily mutually exclusive. Categorically, there may be three scenarios for server size and scale:

  • Small-scale servers
  • Medium-scale servers
  • Large-scale servers

Small-scale servers will have system files and data on physical disks. You can implement data partitioning by placing objects in different database files residing on different disks, as depicted in Figure Below.

Partitioned Views

Moderate-scale servers may implement a RAID device where an array of identical physical disk drives is treated by the operating system as a single, logical volume. From the database designer's standpoint, the server has one disk, as illustrated in Figure (Show In Figure Below). The fact that what we perceive to be a single hard disk drive is actually a bank of parallel disks is completely transparent and may have little impact on how we design our database. You could argue that there is no need to be concerned with partitioning because the RAID device does this — as long as we have ample disk space.

server has one disk, as illustrated

In a large-scale server environment, we generally take RAID technology for granted and may have several RAID devices, each acting as if it were an individual disk drive. This brings us back to the same scenario as the first example given where the server has a number of physical disks. In this case, we can partition our data across multiple disks, only each "disk” is actually a RAID device, as shown in Figure (Show In Figure Below).

For this discussion, I'd like to put the RAID option aside and treat disks as if they are all physical disks, when in fact, each may be a RAID device. Keep in mind that the following SQL examples are given just to describe the methodology behind partitioned views. The code will not actually run on the AdventureWorks2008 database.

What does all this have to do with views? You'll remember that one of the main reasons for views is to treat complex data as if it were a simple table. Partitioning takes this concept to the next level. Here's an example: Suppose that your product marketing business has been gathering sales order data for five years. Business has been good and, on average, you're storing 500,000 sales detail rows each year. At the end of each month your sales managers and executives would like to run comparative sales reports on all of this data but you certainly don't want to keep nearly 3 million rows of data in your active table. When database performance began to slow down a couple of years ago, you decided to archive older records by moving them to a different table. This improved transactional performance because the active sales detail table stored less data. As long as you only accessed the most current records for reporting, performance was fine. However, when you combined the archive tables with the current detail, you were back to where you started. Once again, the server ground to a snail's pace because all of these tables resided on the same physical disk.

Here's a quick computer trivia question: What's the slowest component of almost any computer system? The user? Okay, besides that. The memory? How about the CPU? No, it's the hard disk. Aside from the cooling fans, the hard disk is the only critical component that is still mechanical. The industry hasn't yet found a cost-effective replacement without moving parts. The platter can only spin so fast and the read/ write heads can only move back and forth so fast. In earlier chapters you learned that the greatest cost- affecting query performance is disk I/O — the time it takes for the system to position the heads and read data from a physical disk. If the system has one disk, it must find a page of data, reposition the heads, read the next page, and so on until it reads all of the data to return a complete result set. Because one disk has one set of heads, this happens in a linear fashion, one page at a time. If you were able to spread data across multiple disks, SQL Server could retrieve data from each disk simultaneously. The query execution plan makes this possible as it maps out those operations that are dependent and those that can be performed in parallel. This is depicted in Figure (Show In Figure Below).

Views-Figure(11)

The view that makes all this possible is actually quite simple. Using your successful marketing business as an example, the view definition might look like this:

Because these tables are all addressable within the database, they can be referenced in joins, subqueries, or any type of SQL expression. It may not make sense to put every table on its own disk, but if you did, each of these SELECT statements could be processed in parallel. Assuming that each drive had its own independent controller, the data on each of these disks could be read simultaneously.

As you can see, many factors can contribute to the effectiveness of a partitioned view. You would likely choose this route when system performance became an issue. The best indicator that an approach solves a performance or resource problem would be to use performance-tuning tools such as analyzing query execution plans, using the Windows system monitor and SQL Server Profiler.

Federated Views

Federated views are close cousins of partitioned views. The term federated means working together, so a federated server solution consists of more than one independent database server working together to solve a business problem. This is not to be confused with a server cluster, where multiple servers appear as a single server on the network. Federated servers may be in close proximity or could be a great distance apart. In fact, one of the significant advantages to a federated server solution is that the database servers are geographically located in close proximity to the users and applications that will use them. With database servers in regional or satellite business locations, the majority of the region's supporting data is typically stored on the local server. Federated views may be used to access data stored on remote servers and in exceptional cases, to connect over the Internet or corporate wide-area network (see Figure Show In Below).

Federated Views

You could take a few different approaches to make data accessible from one server to another. One of the most common choices is to configure a linked server connection. A linked server maintains a connection to a database on another server as if the remote database were local. Once a linked server connection is established, tables are referenced using a four-part name, as follows:

LinkedServer. SalesDatabase.Sales.SalesDetail

What does this accomplish? Suppose I have designed the database infrastructure for a banking system. Let's say that credit card transactions may be processed in one of two data centers: one in Atlanta for East Coast accounts, and one in San Francisco for West Coast accounts. All U.S. customers have their account records managed in one of these two data centers. If I live in Seattle and make a purchase anywhere in the western United States, the merchant system sends my transaction to the San Francisco data center where it locates my account record and processes the transaction. However, the system must also be prepared to locate East Coast account records stored in the Atlanta data center. The view used to locate all accounts (from the West Coast server) may be defined like this:

The TOP 1 statement tells the query-processing engine to stop looking after it finds one record. If the record is located in the first table (on the local server), no request is made on the remote server. Otherwise, the connection is used to pass the request to the other server, which processes the query until it locates the account record. Figure (Show In Figure Below) demonstrates this scenario.

Federated Views

Now suppose that I travel to New York and buy a stuffed animal for my daughter's birthday. I find a great deal on a teddy bear wearing a Yankees baseball cap and pay with my credit card, which sends a request to the Atlanta data center to Select Top 1 from a view defined as follows:

In this example, the East Coast server doesn't find my account record in the local Accounts table so it moves to the remote server, which begins searching in its Accounts table. This part of the query is actually processed on the West Coast server so data isn't unnecessarily transferred across the network connection. After finding one record (my account), it stops looking and terminates the query execution. This scenario is depicted in Figure Below.

Federated Views

Securing Data

Another useful offering of views is to provide a layer for user data access without giving users access to sensitive data or other database objects. A common security practice is for the database administrator to lock down all of the tables, denying access to all regular users. Views are then created to explicitly expose selected tables, columns, and/or rows for all or selected users. When the select permission is granted on a view, users gain access to the view's underlying data even if the same user is explicitly denied the select permission on the underlying table(s).

Hiding Complexity

One of the most common, and arguably one of the most important, reasons to use views is to simplify data access. In a normalized database, even the most basic queries can involve many different tables. Views make it possible for programmers, report writers, and users to gain access to data at a reasonably low level without having to contend with the complexities of relationships and database schema.

A practical transactional database is broken down into many tables and related information is spread out across these tables to maintain data integrity and to reduce unnecessary redundancy. Reassembling all of these elements can be a headache for someone who doesn't fully understand the data or who may not be versed in relational database design. Even for the experienced developer or DBA, using a view can save time and minimize errors. The following is an example to demonstrate this point. To do something as fundamental as return product inventory information can be a relatively complex proposition.

In this example, I want to return the category, subcategory, product, model, shelf location, inventory quantity, inventory cost, and inventory date for all products. Because this information could be common to more than one product, the category, subcategory, location cost, and inventory data is stored in separate tables. For operational reasons, I have decided to exclude the price information as well as other descriptive data for the products. Using a view, users are not even aware that the columns containing the omitted information exist.

Here's one more example of a lengthy view. This view will be used a little later in the discussion on processing business logic in stored procedures. I like this view because it contains several columns that can easily be used for reporting purposes, and to sort, group, or filter the resulting data.

Modifying Data Through Views

Can data be modified through a view? Perhaps a better question is should data be modified through a view? The definitive answer is maybe. Yes, you can modify some data through views. Because a view can expose the results of a variety of query techniques, some results may be updatable, some may not, and others may allow some columns to be updated. This all depends on various join types, record-locking conditions, and permissions on the underlying tables.

As a rule, I don't think views are for updating records — that's my opinion. After all, doesn't the word view suggest that its purpose is to provide a read-only view of data? I think so, but I've also worked on enough corporate production databases where this was the only option.

The fact of the matter is that over time, databases evolve. Over the years, people come and go, policies are implemented with little evidence of their purpose, and political culture dictates the methods we use. If I ruled the world, no one would have access to data directly through tables; views would provide read-only data access and support all related application features, and stored procedures would be used to perform all transactional operations and filtered data retrieval. These are the guidelines I follow when designing a system from the ground up. However, I acknowledge that this is not always possible in the typical circumstance where one database designer isn't given free license.

In simple terms, these are the most common rules governing the conditions for updating data through views:

  • In an inner join, columns from one table at a time may be modified. This is due to the record- locking restrictions on related tables. Updates generally cannot be performed on two related tables within the same transaction.
  • In an outer join, generally columns only for the inner table are updatable.
  • Updates can't be performed through a view containing a UNION query.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

T-SQL Topics