{"id":1343,"date":"2012-05-28T00:00:00","date_gmt":"2012-05-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-view-beyond-the-basics\/"},"modified":"2021-09-29T16:21:48","modified_gmt":"2021-09-29T16:21:48","slug":"sql-view-beyond-the-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-view-beyond-the-basics\/","title":{"rendered":"SQL View: Beyond the Basics"},"content":{"rendered":"
\n

In my previous article, ‘SQL View Basics<\/a>‘, I discussed a  VIEW<\/code> as being a virtual table that does not exist until it is invoked by name in SQL statements, and which disappears at the end of that statement. The strong advantage of a VIEW<\/code> is that it will produce the correct results when it is invoked, based on the current data. Trying to do the same sort of things with temporary tables or computed columns within a table can be subject to errors and is likely to be slower to read from disk. Let’s keep going and look at some code. <\/p>\n

Types of VIEWs <\/h2>\n

We can classify a view as being either read-only or updatable. This is handy, but we can also use the type of SELECT<\/code> statement in the VIEW<\/code>s to classify them. These two methods can overlap, but let’s start with the SELECT<\/code> statements method. <\/p>\n

Single-Table Projection and Restriction<\/h2>\n

In practice, many VIEW<\/code>s are projections or restrictions on a single base table. This is a common method for implementing security-control by removing rows or columns that a particular group of users is not allowed to see. These VIEW<\/code>s are usually updatable and represent the most common VIEW<\/code> pattern. <\/p>\n

Translated Columns <\/h2>\n

Another common use of a VIEW<\/code> is to translate codes into text or other codes by doing table look ups. This is a special case of a joined VIEW<\/code> based on a FOREIGN KEY<\/code> relationship between two tables. For example, an order table might use a part number that we wish to display with a part name on an order entry screen. This is done with a JOIN<\/code> between the Orders <\/code>table and the Inventory<\/code> table, thus:<\/p>\n

CREATE VIEW Screen (part_nbr, part_name, ...)\nAS SELECT Orders.part_nbr, Inventory.part_name, ...\n     FROM Inventory, Orders\n    WHERE Inventory.part_nbr = Orders.part_nbr;\n<\/pre>\n
The idea of JOIN VIEW<\/code>s to translate codes can be expanded to show more than just one translated column. The result is often a “star” query with one table in the center, joined by FOREIGN KEY<\/code> relations to many other tables to produce a result that is more readable than the original central table. <\/p>\n
Missing values are a problem. If there is no translation for a given encoding, no row appears in the VIEW<\/code>, or if an OUTER<\/code> JOIN<\/code> was used, a NULL will appear. The programmer should establish a referential integrity constraint to CASCADE <\/code>changes between the tables to prevent loss of data. <\/p>\n
Grouped VIEWs<\/h2>\n
A grouped VIEW<\/code> is based on a query with a GROUP BY <\/code>clause. Since each of the groups may have more than one row in the base from which it was built, these are necessarily read-only VIEW<\/code>s. Such VIEW<\/code>s usually have one or more aggregate functions and they are used for reporting purposes. They are also handy for working around weaknesses in SQL. Consider a VIEW<\/code> that shows the largest sale in each state. The query is straightforward:<\/p>\n
CREATE VIEW BigSales (state_code, sales_amt_total)\nAS SELECT state_code, MAX(sales_amt)\n     FROM Sales\n    GROUP BY state_code;\n<\/pre>\n
SQL does not require that the grouping column(s) appear in the select clause, but it is a good idea in this case. <\/p>\n
These VIEW<\/code>s are also useful for “flattening out” one-to-many relationships. For example, consider a Personnel table, keyed on the employee number (emp_nbr<\/code>), and a table of dependents, keyed on a combination of the employee number for each dependent’s parent (emp_nbr<\/code>) and the dependent’s own serial number (dep_id<\/code>). The goal is to produce a report of the employees by name with the number of dependents each has.<\/p>\n
CREATE VIEW DepTally1 (emp_nbr, dependent_cnt)\nAS SELECT emp_nbr, COUNT(*)\n     FROM Dependents\n    GROUP BY emp_nbr;\n<\/pre>\n
The report is simply an OUTER JOIN<\/code> between this VIEW<\/code> and the Personnel table. <\/p>\n
The OUTER<\/code> JOIN<\/code> is needed to account for employees without dependents with a NULL <\/code>value, like this.<\/p>\n
SELECT emp_name, dependent_cnt\n  FROM Personnel AS P1\n       LEFT OUTER JOIN \n       DepTally1 AS D1 \n       ON P1.emp_nbr = D1.emp_nbr;\n<\/pre>\n
UNION-ed VIEWs<\/h2>\n
VIEW<\/code>s based on a UNION<\/code> or UNION ALL<\/code> operation are read-only because there is no single way to map a change onto just one row in one of the base tables. The UNION<\/code> operator will remove duplicate rows from the results. Both the UNION<\/code> and UNION ALL<\/code> operators hide which table the rows came from. Such VIEW<\/code>s must use a <view column list>,<\/code> because the columns in a UNION [ALL] <\/code>have no names of their own. In theory, a UNION<\/code> of two disjoint tables, neither of which has duplicate rows in itself should be updatable. <\/p>\n
The problem given in the section on grouped VIEW<\/code>s, could also be done with a UNION<\/code> query that would assign a count of zero to employees without dependents, thus:<\/p>\n
CREATE VIEW DepTally2 (emp_nbr, dependent_cnt)\nAS (SELECT emp_nbr, COUNT(*)\n      FROM Dependents\n     GROUP BY emp_nbr)\n   UNION\n   (SELECT emp_nbr, 0\n      FROM Personnel AS P2\n     WHERE NOT EXISTS \n          (SELECT *\n             FROM Dependents AS D2\n            WHERE D2.emp_nbr = P2.emp_nbr));\n<\/pre>\n
The report is now a simple INNER JOIN<\/code> between this VIEW<\/code> and the Personnel table. The zero value, instead of a NULL value, will account for employees without dependents.<\/p>\n
Calculated Columns <\/h2>\n
The main reason for hiding computations in a VIEW<\/code> is so that the computation is done one way, one place, one time and so it gets a unique data element name. One common use for a VIEW<\/code> is to provide summary data across a row. For example, given a table with measurements in metric units, we can construct a VIEW<\/code> that hides the calculations to convert them into English units. <\/p>\n
It is important to be sure that you have no problems with NULL<\/code> values when constructing a calculated column. For example, given a Personnel table with columns for both salary and commission, you might construct this VIEW<\/code>:<\/p>\n
CREATE VIEW Payroll (emp_nbr, paycheck_amt)\nAS \nSELECT emp_nbr, (salary + COALESCE(commission), 0.00)\n FROM Personnel;\n<\/pre>\n
Office workers do not get commissions, so the value of their commission column will be NULL<\/code>, so we use the COALESCE()<\/code> function to change the NULLs<\/code> to zeros. <\/p>\n
SQL Server introduced a computed column construct in their table declaration syntax, <expression> AS <column name><\/code>. This is proprietary and has some limitations in that it can only reference columns in the same row. This can be a way to avoid a VIEW<\/code> and is a good idea to reduce the number of objects in a schema.<\/p>\n
Computed VIEWs not just for fancy math; strings and temporal data also have computations. For example a VIEW<\/code> can hide all but the last four digits of a 16-digit credit card number:  <\/p>\n
masked_creditcard_nbr \n  AS '****-****-****-' + SUBSTRING (creditcard_nbr, 13, 16)<\/pre>\n
likewise, the following Monday from a weekend date is easy to put into a computation business_date<\/p>\n
  AS CASE WHEN DATEPART(DW, sale_date) = 7\n         THEN DATEADD (DD, 1, sale_date)\n         WHEN DATEPART(DW, sale_date) = 6\n         THEN DATEADD (DD, 2, sale_date)\n         ELSE sale_date END<\/pre>\n
Updatable and Read-Only VIEWs<\/h2>\n
Unlike base tables, VIEW<\/code>s are either updatable or read-only, but not both. INSERT<\/code>, UPDATE<\/code>, and DELETE<\/code> operations are allowed on updatable VIEW<\/code>s and base tables, subject to any other constraints. INSERT<\/code>, UPDATE<\/code>, and DELETE<\/code> are not allowed on read-only VIEW<\/code>s, but you can change their base tables, as you would expect.<\/p>\n
For the computer science majors, this is an NP-complete problem. Since we have no general solution, we need a way to handle each VIEW<\/code> with particular code. We have a way to do that! <\/p>\n
The INSTEAD OF Trigger<\/h2>\n
The INSTEAD OF<\/code> trigger is how to resolve the VIEW<\/code> updating problem. It is possible for a user to write INSTEAD OF<\/code> triggers on VIEW<\/code>s, which catch the changes and route them to the base tables that make up the VIEW<\/code>. The database designer has complete control over the way VIEW<\/code>s are handled.<\/p>\n
These triggers are added to a VIEW<\/code> and are executed on base tables instead of making changes directly to the VIEW<\/code> it self. If you think about it, how would a program change the VIEW<\/code> anyway? It does not exist. <\/p>\n
The basic syntax is fairly simple; T-SQL has a lot of other options but we will not be concerned with them. <\/p>\n
CREATE TRIGGER <trigger_name>\nON <view_name>\nINSTEAD OF } \n{[INSERT] [,] [UPDATE] [,] [DELETE]} \nAS <SQL statement>; \n<\/pre>\n
Triggers have a global scope for obvious reasons. The options INSERT<\/code>, UPDATE<\/code> or DELETE<\/code> are called “database events” and they cause the trigger to fire. As a general heuristic, you will want to have your SQL statement fire on all three events. <\/p>\n
The trigger’s SQL statement is executed instead of the triggering SQL statement. The attempted INSERT<\/code>, UPDATE<\/code> or DELETE<\/code> has no effect at all. If you feel fancy, you can define views on views where each view has its own INSTEAD OF<\/code> trigger.<\/p>\n
INSTEAD OF<\/code> triggers are not allowed on updatable views that use WITH CHECK OPTION<\/code>. SQL Server raises an error when an INSTEAD OF<\/code> trigger is added to an updatable view WITH CHECK OPTION<\/code> specified. The user must remove that option by using ALTER VIEW<\/code> before defining the INSTEAD OF<\/code> trigger.<\/p>\n
For INSTEAD OF<\/code> triggers, the DELETE<\/code> option is not allowed on tables that have an ON DELETE CASCADE<\/code> referential action. Similarly, the UPDATE<\/code> option is not allowed on tables that have ON UPDATE CASCADE<\/code> referential action. <\/p>\n
This can be complicated, so let us start with a very example of a read-only VIEW<\/code> and its base tables. <\/p>\n
CREATE TABLE Alpha\n(alpha_key VARCHAR(10) NOT NULL PRIMARY KEY,\n alpha_amt INTEGER NOT NULL);\n \nCREATE TABLE Beta\n(beta_key VARCHAR(10)  NOT NULL PRIMARY KEY,\n beta_amt INTEGER NOT NULL);\n<\/pre>\n
Now let’s declare a VIEW<\/code> that can’t be updated. <\/p>\n
CREATE VIEW  Combined_Shares (ab_key, ab_tot)\nAS \nSELECT ab_key, SUM(ab_tot)\n  FROM (SELECT * FROM Alpha \n        UNION ALL \n          SELECT * FROM Beta) \n         AS X (ab_key, ab_tot)\n GROUP BY ab_key;\n<\/pre>\n
As a simple example, when you do an insertion, put half of an amount into each of the two hidden tables, Alpha and Beta. If the amount is an odd number, favor one of the two hidden tables. <\/p>\n
CREATE TRIGGER Split_Loot\nON Combined_Shares\nINSTEAD OF INSERT  \nAS \nBEGIN \nINSERT INTO Alpha -- favor alpha \nSELECT I.ab_key, CEILING (I.ab_tot\/2.0)\n  FROM INSERTED AS I;\n \nINSERT INTO Beta \nSELECT I.ab_key, FLOOR(I.ab_tot\/2.0)\n  FROM INSERTED  AS I;\nEND; \n<\/pre>\n
Put in one row of data via the view and not the base tables. <\/p>\n
INSERT INTO Combined_Shares (ab_key, ab_tot)\nVALUES ('Jerry', 51); \n<\/pre>\n
Now look at what happened by looking at the VIEW<\/code> and the hidden tables in it. <\/p>\n
SELECT * FROM Combined_Shares;\n<\/pre>\n\n\n\n
\n
Jerry<\/p>\n<\/td>\n
\n
51<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 SELECT * FROM Alpha;\n<\/pre>\n\n\n\n
\n
Jerry<\/p>\n<\/td>\n
\n
25<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SELECT * FROM Beta;\n<\/pre>\n\n\n\n
\n
Jerry<\/p>\n<\/td>\n
\n
26<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
That was nice. Now let’s do a delete trigger <\/p>\n
CREATE TRIGGER Delete_Loot\nON Combined_Shares\nINSTEAD OF DELETE \nAS \nBEGIN \nMERGE INTO Alpha\nUSING DELETED AS D\nON D.ab_key = Alpha.ab_key\nWHEN MATCHED\nTHEN DELETE;\n \nMERGE INTO Beta\nUSING DELETED AS D\nON D.ab_key = Beta.ab_key\nWHEN MATCHED\nTHEN DELETE;\nEND; \n<\/pre>\n
And fire the trigger:<\/p>\n
DELETE FROM Combined_Shares\nWHERE ab_key = 'Jerry';\n<\/pre>\n
Please notice the use of the MERGE<\/code> in this trigger. You need to learn how to write standard SQL.  Also, when this fires you will get three messages, one for the VIEW<\/code> and one for each table. <\/p>\n
CREATE TRIGGER Update_Loot\nON Combined_Shares\nINSTEAD OF UPDATE\nAS \nBEGIN\nMERGE INTO Alpha\nUSING INSERTED AS I\nON I.ab_key = Alpha.alpha_key\nWHEN MATCHED\nTHEN UPDATE\n     SET alpha_amt = CEILING (I.ab_tot\/2.0);\nMERGE INTO Beta\nUSING INSERTED AS I\nON I.ab_key = Beta.beta_key\nWHEN MATCHED\nTHEN UPDATE\n     SET beta_amt = FLOOR (I.ab_tot\/2.0);\nEND; \n<\/pre>\n
WITH CHECK OPTION Example <\/h2>\n
In my previous article, ‘SQL View Basics<\/a>‘,, I mentioned the use of the WITH CHECK OPTION<\/code> but did not give an example. Let us define a simple Organizational Chart table in a nested sets model. I am going to assume that you know this SQL idiom and so I will not explain it. <\/p>\n
 \nCREATE TABLE OrgChart \n(dept_name CHAR(10) NOT NULL PRIMARY KEY, \n lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), \n rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),\n  CONSTRAINT order_okay CHECK (lft < rgt));\n \n INSERT INTO OrgChart \nVALUES ('Corporate', 1, 120), \n('Acct', 20, 30), \n('Sales', 40, 110), \n('Retail', 50, 60), \n('Wholesale', 70, 80), \n('Internal', 90, 100);\n<\/pre>\n
 Let us define a simple Organizational Chart table in a nested sets model. I am going to assume that you know this SQL idiom and I will not explain it in detail. <\/p>\n
CREATE TABLE OrgChart \n(dept_name CHAR(10) NOT NULL PRIMARY KEY, \n lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), \n rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),\n  CONSTRAINT order_okay CHECK (lft < rgt));\n \n INSERT INTO OrgChart \nVALUES ('Corporate', 1, 120), \n('Acct', 20, 30), \n('Sales', 40, 110), \n('Retail', 50, 60), \n('Wholesale', 70, 80), \n('Internal', 90, 100);\n<\/pre>\n
The (lft<\/code>, rgt<\/code>) pairs are like tags in a mark-up language, or parentheses in algebra, BEGIN-END<\/code> blocks in Algol-family programming languages, etc. — they bracket a sub-set. This is a set-oriented approach to trees in a set-oriented language. <\/p>\n
 Notice all of the constraints on the columns at the column and table level. But we are missing two constraints we need. The first is that we have a single root. The second is that the (lft<\/code>, rgt<\/code>) pairs do not overlap .. that is, we really have nesting. <\/p>\n
 CREATE VIEW  OrgChart_2\nAS\nSELECT O1.dept_name, O1.lft, O1.rgt \n  FROM OrgChart AS O1\n WHERE NOT EXISTS \n      (SELECT *\n         FROM OrgChart AS O2\n        WHERE O1.lft BETWEEN O2.lft AND O2.rgt\n          AND O1.rgt NOT BETWEEN O2.lft AND O2.rgt)\n   AND O1.lft \n       BETWEEN 1\n       AND (SELECT rgt \n              FROM OrgChart \n             WHERE lft = 1)\n   AND O1.rgt \n       BETWEEN 1\n       AND (SELECT rgt \n              FROM OrgChart \n             WHERE lft = 1)\nWITH CHECK OPTION; \n<\/pre>\n
This assumes that we start with a root node that has (lft<\/code> = 1) and build from that. That could be enforced with another predicate in the  VIEW<\/code>, if you wish. <\/p>\n
 Try these statements and watch the WITH CHECK OPTION<\/code> errors. The first one is a dangling node not under the root node, the second is an overlapping range. <\/p>\n
INSERT INTO OrgChart_2\nVALUES ('dangle', 130, 131);\n \nINSERT INTO OrgChart_2\nVALUES ('overlap', 51, 61);\n<\/pre>\n
giving the error…<\/p>\n
Msg 550, Level 16, State 1, Line 2\nThe attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.\nThe statement has been terminated. \n<\/pre>\n
SUMMARY<\/h2>\n
VIEW<\/code>s are not as simple as most SQL programmers first think they are. But they are worth the effort because they are powerful and, because they are declarative, they can be optimized. <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Following up from his popular article, SQL View Basics, Joe delves into the main uses of views, explains how the WITH CHECK OPTION works,  and demonstrates how the  INSTEAD OF trigger can be used in those cases where views cannot be updatable.…<\/p>\n","protected":false},"author":96214,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4242,4150,4252],"coauthors":[],"class_list":["post-1343","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-basics","tag-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1343","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1343"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1343\/revisions"}],"predecessor-version":[{"id":40689,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1343\/revisions\/40689"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1343"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1343"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}