8. Resultados
8.1. Resultados Asociados al Objetivo 1 – Contextualización del Área
8.1.5. Identificación de Interacciones Socio-Ecosistémicas
A view is a single SELECT statement that is compiled into a reusable object. Views can be used in a variety of situations, for a variety of purposes. To the user, views more or less appear the same as tables, and have the same security characteristics as tables. They are objects just like tables (and procedure, sequences, etc.) and as such cannot have the same name as any other object. The query can be very complex, or very simple. Just like a table, it does not have parameters. Also like a table, there is no guaranteed order of the data in a view, even if you have an ORDER BY clause in the view to support the TOP phrase on the SELECT clause.
The basic form of a view is very straightforward:
Click here to view code image
CREATE VIEW SchemaName.ViewName [WITH OPTIONS]
AS SELECT statement [WITH CHECK OPTION]
The SELECT statement can be as complex as you desire, and can use CTEs, set operators like UNION and EXCEPT, and any other constructs you can use in a single statement.
The options you can specify are:
SCHEMABINDING Protects the view from changes to the objects used in the
SELECT statement. For example, if you reference Table1.Column1, the properties of that Column1 cannot be changed, nor can Table1 be dropped. Columns, not
references can be removed, or new columns added.
VIEW_METADATA Alters how an application that accesses the VIEW sees the metadata. Typically, the metadata is based on the base tables, but
VIEW_METADATA returns the definition from the VIEW object. This can be useful when trying to use a view like a table in an application.
ENCRYPTION Encrypts the entry in sys.syscomments that contains the text of the VIEW create statement. Has the side effect of preventing the view from being published as part of replication.
THE WITH CHECK OPTION will be covered in more detail later in this skill, but it basically limits what can be modified in the VIEW to what could be returned by the VIEW object.
This section covers how to:
Design a view structure to select data based on user or business requirements
Identify the steps necessary to design an updateable view Implement partitioned views
Implement indexed views
Design a view structure to select data based on user or business requirements
There are a variety of reasons for using a view to meet user requirements, though some reasons have changed in SQL Server 2016 with the new Row-Level Security feature (such as hiding data from a user, which is better done using the Row-Level Security feature, which is not discussed in this book as it is not part of the objectives of the exam.)
For the most part, views are used for one specific need: to query simplification to encapsulate some query, or part of query, into a reusable structure. As long as they are not layered too deeply and complexly, this is a great usage. The following are a few specific scenarios to consider views for:
Hiding data for a particular purpose A view can be used to present a projection of the data in a table that limits the rows that can be seen with a WHERE clause, or by only returning certain columns from a table (or both).
Reformatting data In some cases, there is data in the source system that is used frequently, but doesn’t match the need as it stands. Instead of dealing with this situation in every usage, a view can provide an object that looks like the customer needs.
Reporting Often to encapsulate a complex query that needs to perform occasionally, and even some queries that aren’t exactly complicated, but they are performed
repeatedly. This can be for use with a reporting tool, or simply for ad-hoc usage.
Providing a table-like interface for an application that can only use tables Sometimes a stored procedure makes more sense, but views are a lot more general purpose than are stored procedures. Almost any tool that can ingest and work with tables can use a view.
Let’s examine these scenarios for using a VIEW object, except the last one. That particular utilization is shown in the next section on updatable views. Of course, all of these scenarios can sometimes be implemented in the very same VIEW, as you might just want to see sales for the current year, with backordered and not shipped products grouped together for a report, and you might even want to be able to edit some of the data using that view. While this might be possible, we look at them as individual examples in the
following sections.
Using views to hide data for a particular purpose
One use for views is to provide access to certain data stored in a table (or multiple tables).
For example, say you have a customer requirement that states: “We need to be able to provide access to orders made in the last 12 months (to the day), where there were more than one-line items in that order. They only need to see the Line items, Customer,
SalesPerson, Date of Order, and when it was likely to be delivered by.”
A view might be created as shown in Listing 1-2.
LISTING 1-2 Creating a view that meets the user requirements Click here to view code image
CREATE VIEW Sales.Orders12MonthsMultipleItems AS
SELECT OrderId, CustomerID, SalespersonPersonID, OrderDate, ExpectedDeliveryDate
FROM Sales.Orders
WHERE OrderDate >= DATEADD(Month,-12,SYSDATETIME()) AND (SELECT COUNT(*)
FROM Sales.OrderLines
WHERE OrderLines.OrderID = Orders.OrderID) > 1;
Now the user can simply query the data using this view, just like a table:
Click here to view code image SELECT TOP 5 *
FROM Sales.Orders12MonthsMultipleItems ORDER BY ExpectedDeliveryDate desc;
Using TOP this returns 5 rows from the table:
Click here to view code image
OrderId CustomerID SalespersonPersonID OrderDate ExpectedDeliveryDate
--- --- --- -- ---
---73550 967 15 2016-05-31 2016-06-01
73549 856 16 2016-05-31 2016-06-01
73548 840 3 2016-05-31 2016-06-01
73547 6 14 2016-05-31 2016-06-01
73546 810 3 2016-05-31 2016-06-01
Note that this particular usage of views is not limited to security like using row-level
security might be. A user who has access to all of the rows in the table can still have a perfectly valid reason to see a specific type of data for a purpose.
Using a view to reformatting data in the output
Database designers are an interesting bunch. They often try to store data in the best
possible format for space and some forms of internal performance that can be gotten away with. Consider this subsection of the Application.People table in WideWorldImporters database.
Click here to view code image
SELECT PersonId, IsPermittedToLogon, IsEmployee, IsSalesPerson FROM Application.People;
What you see is 1111 rows of kind of cryptic data to look at (showing the first four rows):
Click here to view code image
PersonId IsPermittedToLogon IsEmployee IsSalesPerson --- --- --- ---1 0 0 0
2 1 1 1 3 1 1 1 4 1 1 0
A common request from a user that needs to look at this data using Transact-SQL could be: “I would like to see the data in the People table in a more user friendly manner. If the user can logon to the system, have a textual value that says ‘Can Logon’, or ‘Can’t Logon’
otherwise. I would like to see employees typed as ‘SalesPerson’ if they are, then as
‘Regular’ if they are an employee, or ‘Not Employee’ if they are not an employee.”
In Listing 1-3 is a VIEW object that meets these requirements.
LISTING 1-3 Creating the view reformat some columns in the Application.People table Click here to view code image
CREATE VIEW Application.PeopleEmployeeStatus AS
SELECT PersonId, FullName,
IsPermittedToLogon, IsEmployee, IsSalesPerson, CASE WHEN IsPermittedToLogon = 1 THEN 'Can Logon' ELSE 'Can''t Logon' END AS LogonRights, CASE WHEN IsEmployee = 1 and IsSalesPerson = 1 THEN 'Sales Person'
WHEN IsEmployee = 1
THEN 'Regular'
ELSE 'Not Employee' END AS EmployeeType FROM Application.People;
Now, querying the data in the same manner (leaving off names), you see something more pleasant to work with:
Click here to view code image
SELECT PersonId, LogonRights, EmployeeType FROM Application.PeopleEmployeeStatus;
Which returns:
Click here to view code image
PersonId LogonRights EmployeeType --- --- ---1 Can't Logon Not Employee 2 Can Logon Sales Person 3 Can Logon Sales Person 4 Can Logon Regular
There is one serious downside to this method of reformatting. While this looks better, and is easier to see, queries that use the reformatted values to filter on the new columns never use an index since the data does not match what is in the index. For a smaller table, this isn’t an issue, but it is a concern. We included the columns in the view that had the original data for that reason.
A final concern is not to use views as layers of encapsulation in your application code for that same reason. The more layers of views you have, the less likely you get a great plan of execution. Views are definitely useful to have for morphing a set for many reasons, particularly when a user is repeatedly doing the same kinds of transformations in their code.
Using a view to provide a reporting interface
A very useful pattern to apply with a view is building a reporting interface, to format some data for a reporting tool.
Requirements might be given to “Build a simple reporting interface that allows us to see sales profit or net income broken down by city, state, or territory customer category for the current week, up to the most current data”. If the system is normalized, there are quite a few tables involved in the query. Note that an important part of these requirements is that it be up to the most current data. If it did not include the most recent data, a data warehousing solution with a separate database would likely be more efficient.
In Listing 1-4, the code for the view that gives you a structure that can easily be used for providing these answers is included. The object is in a new schema named Reports to
segregate it from other bits of code, and the view is suffixed “Basis” because this view could be the basis of several reports.
There are not any locking or isolation hints, and it is generally not a good practice to do so in your code unless using the SNAPSHOT isolation level in your database. Chapter 3 covers concurrency, isolation levels, in more detail.
LISTING 1-4 Creating the view that is the basis of an Invoice Summary report Click here to view code image
CREATE SCHEMA Reports;
GO
CREATE VIEW Reports.InvoiceSummaryBasis AS
SELECT Invoices.InvoiceId,
CustomerCategories.CustomerCategoryName,
Cities.CityName, StateProvinces.StateProvinceName, StateProvinces.SalesTerritory,
Invoices.InvoiceDate,
--the grain of the report is at the invoice, so total --the amounts for invoice
SUM(InvoiceLines.LineProfit) as InvoiceProfit,
SUM(InvoiceLines.ExtendedPrice) as InvoiceExtendedPrice FROM Sales.Invoices
JOIN Sales.InvoiceLines
ON Invoices.InvoiceID = InvoiceLines.InvoiceID JOIN Sales.Customers
ON Customers.CustomerID = Invoices.CustomerID JOIN Sales.CustomerCategories
ON Customers.CustomerCategoryID =
CustomerCategories.CustomerCategoryID JOIN Application.Cities
ON Customers.DeliveryCityID = Cities.CityID JOIN Application.StateProvinces
ON StateProvinces.StateProvinceID = Cities.StateProvinceID
GROUP BY Invoices.InvoiceId,
CustomerCategories.CustomerCategoryName,
Cities.CityName, StateProvinces.StateProvinceName, StateProvinces.SalesTerritory,
Invoices.InvoiceDate;
Now you can create a report of the top 5 Sales by SalesTerritory pretty simply:
Click here to view code image
SELECT TOP 5 SalesTerritory, SUM(InvoiceProfit) AS InvoiceProfitTotal
FROM Reports.InvoiceSummaryBasis WHERE InvoiceDate > '2016-05-01' GROUP BY SalesTerritory
ORDER BY InvoiceProfitTotal DESC;
This returns:
Click here to view code image
SalesTerritory InvoiceProfitTotal --- ---Southeast 536367.60
Great Lakes 366182.65 Mideast 344703.00 Southwest 344386.95 Plains 288766.20
Or, using the same structure, the top five sales by state and customer category:
Click here to view code image
SELECT TOP 5 StateProvinceName, CustomerCategoryName,
SUM(InvoiceExtendedPrice) AS InvoiceExtendedPriceTotal FROM Reports.InvoiceSummaryBasis
WHERE InvoiceDate > '2016-05-01'
GROUP BY StateProvinceName, CustomerCategoryName ORDER BY InvoiceExtendedPriceTotal DESC;
This returns:
Click here to view code image
StateProvinceName CustomerCategoryName InvoiceExtendedPriceTotal - ---
---
---Texas Novelty Shop 229966.31 Pennsylvania Novelty Shop 210254.62 Ohio Novelty Shop 201242.59 New York Novelty Shop 197664.32 California Novelty Shop 178698.48
Clearly, novelty shops are a big business for WideWorldImporters. Looking at the plans of both queries, you should notice a couple of things. The plans are pretty large, not
surprising since we joined a lot of tables. Nothing stands out as too terrible, and both plans suggest the same index:
Click here to view code image
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Sales].[Invoices] ([InvoiceDate]) INCLUDE ([InvoiceID],[CustomerID]);
There is no need to actually apply the index, but as we have discussed frequently, having a covering index for almost every query is a very common desire of the optimizer, and is not always a bad idea when you are doing ranges queries on the table (which was one of the strengths of the clustered index, because it had all of the data sorted for the range.
Covering the data needs of queries make a non-clustered index with included columns behave like a clustered index for queries such as this.)
Identify the steps necessary to design an updateable view
In the previous section, we identified four scenarios where views are frequently useful (not an exhaustive list). The fourth scenario (providing a table-like interface for an application that can only use tables) was put off until this section because for the most part, the goal is to make objects that behave exactly like tables in regards to SELECT, INSERT, UPDATE, and DELETE statements with no special modifications.
To provide interfaces with views there are some special configurations that you need to know. We look at the following scenarios for using views to modify tables.
Modifying views that reference one table
Limiting what data can be added to a table through a view through DDL Modifying data in views with more than one table
Modifying views that reference one table
Generally speaking, any view that references a single table is going to be editable. For example, create a VIEW on the HumanResources.Employee table. To keep it simple, say the requirements are: “The user needs a view to allow the interface to only be able to modify rows where the type of gadget is ‘Electronic’, but not any other value.” The table is shown in Listing 1-5.
LISTING 1-5 Creating the table and some data that is the basis of the updatable view example
Click here to view code image
CREATE TABLE Examples.Gadget (
GadgetId int NOT NULL CONSTRAINT PKGadget PRIMARY KEY, GadgetNumber char(8) NOT NULL CONSTRAINT AKGadget UNIQUE, GadgetType varchar(10) NOT NULL
);
INSERT INTO Examples.Gadget(GadgetId, GadgetNumber, GadgetType) VALUES (1,'00000001','Electronic'),
(2,'00000002','Manual'), (3,'00000003','Manual');
When building a view to be editable, the simpler the view, the easier it is when you’re working with it. In the code that you see in Listing 1-6, there is a column that is the
uppercase version of the gadget type to show how a non-executable column behaves.
LISTING 1-6 Creating the view that is the basis of an Invoice Summary report Click here to view code image
CREATE VIEW Examples.ElectronicGadget AS
SELECT GadgetId, GadgetNumber, GadgetType, UPPER(GadgetType) AS UpperGadgedType FROM Examples.Gadget
WHERE GadgetType = 'Electronic';
Note Using VIEW_METADATA
When using views to provide an interface for an application, you can use the VIEW_METADATA to alter how an application that accesses the view sees the metadata. Typically, the metadata is based on the base tables, but
VIEW_METADATA returns the definition from the VIEW object. This can be useful when trying to use a view like a table in an application.
Now, any user who is granted access to this view can only see rows that meet the WHERE clause of GadgetType = ‘Electronic’.
Exam Tip
It‘s very useful to know all of the comparison operators that are used in SQL, such as LIKE, IN, =, <,>,<> (or !=), etc. While there aren’t necessarily
questions on how to use LIKE, base knowledge of logical and comparison operators is necessary to get a question correct.
Next, perform the following query that shows you the data in the table, seeing what data the user sees:
Click here to view code image
SELECT ElectronicGadget.GadgetNumber AS FromView, Gadget.GadgetNumber AS FromTable,
Gadget.GadgetType, ElectronicGadget.UpperGadgetType FROM Examples.ElectronicGadget
FULL OUTER JOIN Examples.Gadget
ON ElectronicGadget.GadgetId = Gadget.GadgetId;
You can see that for rows where the GadgetType <> ‘Electronic’:
Click here to view code image
FromView FromTable GadgetType UpperGadgetType --- --- --- ---00000001 ---00000001 Electronic ELECTRONIC
NULL 00000002 Manual NULL NULL 00000003 Manual NULL
Now we run three statements to create some new rows, delete two rows, and update two rows in the table. In the comments on the code, include details of what you’re doing. First, try creating two new rows, referencing the derived column:
Click here to view code image
INSERT INTO Examples.ElectronicGadget(GadgetId, GadgetNumber, GadgetType,
UpperGadgetType)
VALUES (4,'00000004','Electronic','XXXXXXXXXX'), --row we can see in view
(5,'00000005','Manual','YYYYYYYYYY'); --row we cannot see in view
This fails, as you would expect:
Click here to view code image
Msg 4406, Level 16, State 1, Line 433 Update or insert of view or function
'Examples.ElectronicGadget' failed because it contains a derived or constant field.
Exam Tip
As you are studying for the exam, it is a great idea to try different syntaxes and scenarios everything to see what happens. While the question writers are not writing tricky questions, they can test you on scenarios that are atypical based on your experience. By pushing the boundaries of what you might normally try, you learn new concepts.
Now, try again, not referencing the calculated column:
Click here to view code image
INSERT INTO Examples.ElectronicGadget(GadgetId, GadgetNumber, GadgetType)
VALUES (4,'00000004','Electronic'), (5,'00000005','Manual');
This succeeds, so now use the query with the FULL OUTER JOIN from before, but limit it to the rows you created.
Click here to view code image
SELECT ElectronicGadget.GadgetNumber as FromView, Gadget.GadgetNumber as FromTable,
Gadget.GadgetType, ElectronicGadget.UpperGadgetType FROM Examples.ElectronicGadget
FULL OUTER JOIN Examples.Gadget
ON ElectronicGadget.GadgetId = Gadget.GadgetId;
WHERE Gadget.GadgetId in (4,5);
Both rows were created, even though you cannot see the row in the view after the
operation:
FromView FromTable GadgetType UpperGadgetType --- --- --- ---00000004 ---00000004 Electronic ELECTRONIC
NULL 00000005 Manual NULL Next, update two rows:
Click here to view code image
--Update the row we could see to values that could not be seen UPDATE Examples.ElectronicGadget
SET GadgetType = 'Manual' WHERE GadgetNumber = '00000004';
--Update the row we could see to values that could actually see UPDATE Examples.ElectronicGadget
SET GadgetType = 'Electronic' WHERE GadgetNumber = '00000005';
When looking at the data (using the same query as before,) see that the row you could see has change to be not visible from the view, but the row we could not see was not updated:
Click here to view code image
FromView FromTable GadgetType UpperGadgetType --- --- ---
---NULL 00000004 Manual ---NULL NULL 00000005 Manual NULL
Since you cannot see the row in the results of a query of the view, you cannot update the row either. Hence, the same would be true of the DELETE operation. The FROM clause of the SELECT, DELETE, and UPDATE all work the same, and only give us access to the rows that are visible through the view. What is interesting though is that you are able to update a row from a name you could see, to a name you could not. The following section demonstrates how to make that not the case using DDL. Change the E1111111 row back to the original value:
Click here to view code image
Click here to view code image