{"id":1332,"date":"2012-05-10T00:00:00","date_gmt":"2012-05-10T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-view-basics\/"},"modified":"2021-08-16T15:02:01","modified_gmt":"2021-08-16T15:02:01","slug":"sql-view-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/sql-view-basics\/","title":{"rendered":"SQL VIEW Basics"},"content":{"rendered":"
\n

A VIEW<\/code> is a virtual table, defined by a query, that does not exist until it is invoked by name in an SQL statement. This may sound simple enough, but\u00a0 some developers have difficulties with the\u00a0 concept.\u00a0 Because of this, they tend to leave out VIEW<\/code>s because they do not appreciate their value. It is easy to write a query and not bother to put it into a VIEW<\/code>, because there is no performance boost. ‘If I am going to save code,’ they reason, ‘I want it in a stored procedure that can take a parameter list instead.’<\/p>\n

In fact, a VIEW<\/code> definition can be copied as in-line text, just like a CTE. But with a good optimizer, the SQL engine can decide that enough sessions are using the same VIEW<\/code> and materialize it as a shared table. The CTE, in contrast, is strictly local to the statement in which it is declared.<\/p>\n

VIEW<\/code>s are often named incorrectly. A VIEW<\/code> is a table, so it is named just like any other table. The name tells us what set of things it represents in the data model. The most common offender is the “Volkswagen” coder who prefixes or suffixes the VIEW<\/code> name with “vw_” or “VIEW<\/code>_” in violation of ISO-11179 rules. We do not mix data and meta data in a name. This is as silly as prefixing every noun in a novel with “n_” so the reader will know that\u00a0 the word is a noun in English grammar.<\/p>\n

The ANSI Standard SQL syntax for the VIEW<\/code> definition is<\/p>\n

CREATE VIEW <table name> [(<VIEW column list>)]\r\nAS <query expression> \r\n[WITH [<levels clause>] CHECK OPTION]\r\n<levels clause> ::= CASCADED | LOCAL\r\n<\/pre>\n
the WITH CHECK OPTION<\/code> is a little known and less used feature that has been around since the SQL-86 Standards. The <levels clause><\/code> option in the WITH CHECK OPTION<\/code> did not exist in Standards before SQL-92. It is not implemented in T-SQL dialect, so I will skip it. If you see it, just remember T-SQL defaults to CASCADED<\/code> behavior.<\/p>\n
A VIEW<\/code> has no physical existence in the database until it is invoked. You cannot put constraints on a VIEW<\/code> for that reason. The name of the VIEW<\/code> must be unique within the entire database schema, like a base table name. The VIEW<\/code> definition cannot reference itself, since it does not exist yet. Nor can the definition reference only other VIEW<\/code>s; the nesting of VIEW<\/code>s must eventually resolve to underlying base tables. This only makes sense; if no base tables were involved, what would you be VIEW<\/code>ing?<\/p>\n
You can either build a column name list in the VIEW<\/code> header or inherit the column names from the SELECT<\/code> statement. Building this list is usually just one quick “cut & paste” and well worth it. This is why we do not ever use “SELECT<\/code> *” in a VIEW<\/code> definition in production code. When the columns of a base tables change, the definition of the “star” will also change. If you are lucky, you will get an error when the VIEW<\/code> has too many or too few columns when it is invoked. If you are not so lucky, the VIEW<\/code> will run and give you unexpected results. If you are unlucky, the VIEW<\/code> will run and give you wrong answers that you use.<\/p>\n
Every few months, someone will post to a SQL forum asking how to use a parameter in a VIEW<\/code>. They would never ask how to use a parameter in a base table. The sight of a SELECT<\/code> statement instead of a list of column declarations throws their mindset in the wrong direction.<\/p>\n
Mullins Heuristics for VIEWS<\/h2>\n
Programmers have rules and standards for creating base tables. The data element names should follow the ISO-11179 rules. We have to have a key. We can have all kinds of constraints. We can have Declarative Referential Integrity actions among other base tables. But how do you design a VIEW<\/code>?<\/p>\n
Craig Mullins, a DB2 expert and author, gave the following rule to ensure that VIEW<\/code>s are created in a responsible and useful manner. Simply stated, the VIEW<\/code> creation strategy should be goal-oriented. VIEW<\/code>s should be created only when they achieve a specific, reasonable goal. Each VIEW<\/code> should have a specific application or business requirement that it fulfills before it is created. That requirement should be documented somewhere, preferably in a data dictionary.<\/p>\n
Although this rule seems obvious, VIEW<\/code>s are implemented at some shops without much thought as to how they will be used. This can cause the number of VIEW<\/code>s that must be supported and maintained to increase until so many VIEW<\/code>s exist that it is impossible to categorize their uses. Nobody wants to take a chance and drop a VIEW<\/code>, so they just write a new one. Whenever a base table used by a VIEW<\/code> definition is changed, then all those VIEW<\/code>s have to be re-compiled and checked. Since VIEW<\/code>s can be built on top of VIEW<\/code>s, this can be tricky.<\/p>\n
Unlike other virtual tables, a VIEW<\/code> is defined in the schema information tables and its definition (not its content!) is persisted. This implies some privileges are needed to use, create, alter and drop VIEW<\/code>s. The first question is do you need to have privileges on the base tables that build a VIEW<\/code>? Yes, but not full privileges. The minimal privileges would be to use the base tables, so you can build the VIEW<\/code>. But that does not mean that the user needs to be able to directly query or modify the base tables.<\/p>\n
The ideal design should give each user a set of VIEW<\/code>s that make it look as if the schema was designed for just his or her use, without regard to the rest of the enterprise.<\/p>\n
This is most often done for security and privacy. The payroll clerk can see the salaries of other personnel and change them. But he cannot give himself a pay raise and try to get out of town before the police find out. He can see the minimum, maximum and average salary in each department, but not who is making which salary.<\/p>\n
The Data Control Language (DCL) is the third sub-language in SQL after DDL and DML. This is where the DBA can GRANT<\/code>, REVOKE<\/code> or DENY<\/code> all kinds of schema object privileges. We spend almost no time on it in training classes, and failure to do it right can destroy your enterprise. As a generalization, the DBA ought to start with a list of roles users can play in the enterprise and create a script for the privileges each role needs. A new user can then be assigned a role and you do not have to repeat the script over and over.<\/p>\n
Do not grant VIEW<\/code> creation privileges to everyone. The “nearly the same” VIEW<\/code>s are a special problem. One user might have read the spec “Employees must be over 21 years of age to serve alcohol” to mean strictly over 21 as of today or can they pour a drink on their 21-st birthday? If VIEW<\/code> creation had been left to just one data modeler, only one of these VIEW<\/code>s would exist and it would have the correct business rule.<\/p>\n
Tricky Queries and Computations<\/h2>\n
Not all programers are equal, so you can make sure that the VIEW<\/code>s preserve the best work in your shop. The other advantage is that if someone finds a better query for the current state of the database, you keep the VIEW<\/code> header, drop the SELECT<\/code> statement in the body, replace it and then re-compile your code. The programmer needs no knowledge of how the VIEW<\/code> works. This technique becomes more useful as the SQL becomes more complex.<\/p>\n
In T-SQL, we used to write complicated code to get sequence numbers and pure dates without time. This code was often hidden in VIEW<\/code>s. The numbering can now be done with ROW_NUMBER() and we have a DATE data type since SQL Server 2008. In many cases, procedures and functions that used loops and fancy string manipulations can be replaced with VIEW<\/code>s.<\/p>\n
Updatable and Read-Only VIEW<\/code>s<\/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 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
An updatable VIEW<\/code> is one that can have each of its rows associated with exactly one row in an underlying base table. When the VIEW<\/code> is changed, the changes pass through the VIEW<\/code> to that underlying base table unambiguously. Updatable VIEW<\/code>s in Standard SQL are defined only for queries that meet these criteria<\/p>\n
    \n
  1. They are built on only one table<\/li>\n
  2. No GROUP BY<\/code> clause<\/li>\n
  3. No HAVING<\/code> clause<\/li>\n
  4. No aggregate functions<\/li>\n
  5. No calculated columns<\/li>\n
  6. No UNION<\/code>, INTERSECT<\/code> or EXCEPT<\/code><\/li>\n
  7. No SELECT DISTINCT<\/code> clause<\/li>\n
  8. Any columns excluded from the VIEW<\/code> must be NULL<\/code>-able or have a DEFAULT<\/code> clause in the base table, so that a whole row can be constructed for insertion.<\/li>\n<\/ol>\n
    By implication, the VIEW<\/code> must also contain a key of the table. In short, we are absolutely sure that each row in the VIEW<\/code> maps back to one and only one row in the base table. The major advantage of this limited definition is that it is based on syntax and not semantics. For example, these VIEW<\/code>s are logically identical:<\/p>\n
    CREATE VIEW Foo1 (a, b, ..) -- updatable, has a key!\r\nAS SELECT (a, b, ..)\r\n\u00a0\u00a0\u00a0FROM Foobar\r\n\u00a0\u00a0WHERE x IN (1,2);\r\n<\/pre>\n
     <\/p>\n
    CREATE VIEW Foo2 (a, b, ..)-- not updateable!\r\nAS SELECT (a, b, ..)\r\n\u00a0\u00a0\u00a0FROM Foobar\r\n\u00a0\u00a0WHERE x = 1\r\n\u00a0\u00a0UNION ALL\r\n\u00a0\u00a0SELECT (a, b, ..)\r\n\u00a0\u00a0\u00a0FROM Foobar\r\n\u00a0\u00a0WHERE x = 2;\r\n<\/pre>\n
    But Foo1 is updateable and Foo2 is not. While I know of no formal proof, I suspect that determining if a complex query resolves to an updatable query for allowed sets of data values possible in the table is an NP-complete problem.<\/p>\n
    The INSTEAD OF<\/code> trigger was the ANSI Standards Committee letting the Data Modeler decide on how to resolve the VIEW<\/code> updating problem. These triggers are added to a VIEW<\/code> and are executed on base tables that make up the VIEW<\/code>. The user never sees them fire and work their magic.<\/p>\n
    \u00a0As an example, consider a VIEW<\/code> that builds the total compensation for each employee by joining the personnel, employee stock holdings, bonuses and salary_amt<\/code> tables in one VIEW<\/code>. An INSTEAD OF<\/code> trigger can update the total compensation using a hidden formula and complex business rules that the user never sees.<\/p>\n
    The use of INSTEAD OF<\/code> triggers gives the user the effect of a single table, but there can still be surprises. Think about three tables; A, B and C. Table C is disjoint from the other two. Tables A and B overlap. So I can always insert into C and may or may not be able to insert into A and B if I hit overlapping rows.<\/p>\n
    Going back to my Y2K consulting days, I ran into a version of such a partition by calendar periods. Their Table C was set up on Fiscal quarters and got leap year wrong because one of the fiscal quarters ended on the last day of February.<\/p>\n
    Nested VIEWs<\/h2>\n
    A point that is often missed, even by experienced SQL programmers, is that a VIEW<\/code> can be built on other VIEW<\/code>s. The only restrictions are that circular references within the query expressions of the VIEW<\/code>s are illegal and that a VIEW<\/code> must ultimately be built on base tables. One problem with nested VIEW<\/code>s is that different updatable VIEW<\/code>s can reference the same base table at the same time. If these VIEW<\/code>s then appear in another VIEW<\/code>, it becomes hard to determine what has happened when the highest-level VIEW<\/code> is changed. As an example, consider a table with two keys:<\/p>\n
    CREATE TABLE CanadianDictionary\r\n(english_id INTEGER UNIQUE,\r\n\u00a0french_id INTEGER UNIQUE,\r\n\u00a0eng_word CHAR(30),\r\n\u00a0french_word CHAR(30),\r\n\u00a0CHECK (COALESCE (english_id, french_id) IS NOT NULL);\r\n<\/pre>\n
    The table declaration is a bit strange. It allows an English-only or French-only word to appear in the table. But the CHECK()<\/code> constraint requires that a word must fall into one or both type codes.<\/p>\n
    INSERT INTO CanadianDictionary\r\nVALUES (1, 2, 'muffins', 'croissants'),\r\n\u00a0\u00a0\u00a0\u00a0(2, 1, 'fish bait', 'escargots');\r\nCREATE VIEW EnglishWords\r\nAS SELECT english_id, eng_word\r\n\u00a0\u00a0\u00a0FROM CanadianDictionary\r\n\u00a0\u00a0WHERE eng_word IS NOT NULL;\r\nCREATE VIEW FrenchWords\r\nAS SELECT french_id, french_word\r\n\u00a0\u00a0\u00a0FROM CanadianDictionary\r\n\u00a0\u00a0WHERE french_word IS NOT NULL);\r\n<\/pre>\n
    We have now tried the escargots and decided that we wish to change our opinion of them:<\/p>\n
    UPDATE EnglishWords\r\n\u00a0\u00a0SET eng_word = 'appetizer'\r\n\u00a0WHERE english_id = 2;\r\n<\/pre>\n
    Our French user has just tried Haggis and decided to insert a new row for his experience:<\/p>\n
    UPDATE FrenchWords\r\n\u00a0\u00a0SET french_word = 'tripoux'\r\n\u00a0WHERE french_id = 3;\r\n<\/pre>\n
    The row that is created is (NULL, 3, NULL, 'tripoux')<\/code>, since there is no way for the VIEW<\/code> FrenchWords<\/code> to get to the VIEW<\/code> EnglishWords<\/code> columns. Likewise, the English VIEW<\/code> user can construct a row to insert his translation, (3, NULL, 'Haggis', NULL),<\/code> but neither of them can consolidate the two rows into a meaningful piece of data.<\/p>\n
    To delete a row is also to destroy data; the French-speaker who drops ‘croissants’ from the table also drops ‘muffins’ from VIEW<\/code> EnglishWords.<\/code><\/p>\n
    WITH CHECK OPTION Clause<\/h3>\n
    If WITH CHECK OPTION<\/code> is specified, the VIEW<\/code>ed table has to be updatable. This is actually a fast way to check how your particular SQL implementation handles updatable VIEW<\/code>s. Try to create a version of the VIEW<\/code> in question using the WITH CHECK OPTION<\/code> and see if your product will allow you to create it. The WITH CHECK OPTION<\/code> was part of the SQL-89 Standard, but nobody seems to know about it! Consider this skeleton:<\/p>\n
    CREATE VIEW V1\r\nAS SELECT key_col, col1, col2\r\n\u00a0\u00a0\u00a0FROM Foobar\r\n\u00a0\u00a0WHERE col1 = 'A';\r\n<\/pre>\n
    and now UPDATE<\/code> it with<\/p>\n
    UPDATE V1 SET col1 = 'B';\r\n<\/pre>\n
    The UPDATE<\/code> will take place without any trouble, but the rows that were previously seen now disappear when we use V1 again. They no longer meet the WHERE<\/code> clause condition! Likewise, an INSERT INTO<\/code> statement with VALUES (col1 = 'B')<\/code> would insert just fine, but its rows would never be seen again in this VIEW<\/code>. This might be the desired behavior. For example, you can set up a VIEW<\/code> of rows in a jobs table with a status code of ‘to be done’, work on them, and change a status code to ‘finished’, and the rows will disappear from your VIEW<\/code>. The important point is that the WHERE<\/code> clause condition was checked only at the time when the VIEW<\/code> was invoked.<\/p>\n
    The WITH CHECK OPTION<\/code> makes the system check the WHERE<\/code> clause condition upon INSERT<\/code> and UPDATE<\/code>. If the new or changed row fails the test, the change is rejected and the VIEW<\/code> remains the same. The WITH CHECK OPTION<\/code> clause does not work like a CHECK constraint.<\/p>\n
    CREATE TABLE Foobar (col_a INTEGER);\r\nCREATE VIEW TestView (col_a)\r\nAS\r\nSELECT col_a FROM Foobar WHERE col_a > 0\r\nWITH CHECK OPTION;\r\nINSERT INTO TestView VALUES (NULL); -- This fails!\r\nCREATE TABLE Foobar_2 (col_a INTEGER CHECK (col_a > 0));\r\nINSERT INTO Foobar_2(col_a)\r\nVALUES (NULL); -- This succeeds!\r\n<\/pre>\n
    The WITH CHECK OPTION<\/code> must be TRUE<\/code> while the CHECK constraint can be either TRUE<\/code> or UNKNOWN<\/code>. This is an example of the differences in DDL and DML in SQL. Once more, you need to watch out for NULL<\/code>s.<\/p>\n
    T-SQL checks all the underlying levels that built the VIEW<\/code>, as well as the WHERE<\/code> clause condition in the VIEW<\/code> itself. If anything causes a row to disappear from the VIEW<\/code>, the UPDATE<\/code> is rejected. Consider two VIEW<\/code>s built on each other from the Personnel table:<\/p>\n
    CREATE VIEW Low_Paid_Personnel (emp_id, salary_amt)\r\nAS SELECT emp_id, salary_amt \r\n\u00a0\u00a0\u00a0FROM Personnel\r\n\u00a0\u00a0WHERE salary_amt <= 250.00;\r\nCREATE VIEW Medium_Paid_Personnel (emp_id, salary_amt)\r\nAS SELECT emp_id, salary_amt \r\n\u00a0\u00a0\u00a0FROM Low_Paid_Personnel\r\n\u00a0\u00a0WHERE salary_amt >= 100.00;\r\n<\/pre>\n
    If neither VIEW<\/code> has a WITH CHECK OPTION<\/code>, the effect of updating Medium_Paid_Personnel <\/code>by increasing every salary_amt<\/code> by $1,000 will be passed without any check to Low_Paid_Personnel <\/code>. Low_Paid_Personnel <\/code>will pass the changes to the underlying Personnel table. The next time Medium_Paid_Personnel <\/code>is used, Low_Paid_Personnel <\/code>will be rebuilt in its own right and Medium_Paid_Personnel <\/code>rebuilt from it, and all the employees will disappear from Medium_Paid_Personnel <\/code>.<\/p>\n
    If only Medium_Paid_Personnel <\/code>has a WITH CHECK OPTION<\/code> on it, the UPDATE<\/code> will fail. Medium_Paid_Personnel <\/code>has no problem with such a large salary_amt<\/code>, but it would cause a row in Low_Paid_Personnel <\/code>to disappear, so Medium_Paid_Personnel <\/code>will reject it. However, if only Medium_Paid_Personnel <\/code>has a WITH LOCAL CHECK OPTION<\/code> on it, the UPDATE<\/code> will succeed. Medium_Paid_Personnel <\/code>has no problem with such a large salary_amt<\/code>, so it passes the change along to Low_Paid_Personnel <\/code>. Low_Paid_Personnel <\/code>, in turn, passes the change to the Personnel table and the UPDATE<\/code> occurs. If both VIEW<\/code>s have a WITH CHECK OPTION<\/code>, the effect is a set of conditions, all of which have to be met. The Personnel table can accept UPDATE<\/code>s or INSERT<\/code>s only where the salary_amt<\/code> is between $100 and $250.<\/p>\n
    WITH CHECK OPTION as Constraints<\/h2>\n
    Lothar Flatz, an instructor for Oracle Software Switzerland made the observation that while Oracle cannot put subqueries into CHECK()<\/code>() constraints and triggers would not be possible because of the mutating table problem, you can use a VIEW<\/code> that has a WITH CHECK OPTION<\/code> to enforce subquery constraints.<\/p>\n
    For example, consider a hotel registry that needs to have a rule that you cannot add a guest to a room that another is or will be occupying. Instead of writing the constraint directly, like this:<\/p>\n
    CREATE TABLE Hotel\r\n(room_nbr INTEGER NOT NULL,\r\n\u00a0arrival_date DATE NOT NULL,\r\n\u00a0departure_date DATE NOT NULL,\r\n\u00a0guest_name CHAR(30) NOT NULL,\r\n\u00a0CONSTRAINT valid_stay_dates\r\n\u00a0CHECK (H1.arrival_date <= H1.departure_date),\r\n\u00a0CONSTRAINT no_overlaps\r\n\u00a0CHECK (NOT EXISTS\r\n\u00a0\u00a0\u00a0\u00a0(SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Hotel AS H1, Hotel AS H2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0WHERE H1.room_nbr = H2.room_nbr\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND H2.arrival_date < H1.arrival_date \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND H1.arrival_date < H2.departure_date)));\r\n<\/pre>\n
    The valid_stay_dates<\/code> constraint is fine, since it has no subquery, but will choke on the no_overlaps<\/code> constraint. Leaving the no_overlaps<\/code> constraint off the table, we can construct a VIEW<\/code> on all the rows and columns of the Hotel base table and add a WHERE<\/code> clause which will be enforced by the WITH CHECK OPTION<\/code>.<\/p>\n
    CREATE VIEW Valid_Hotel (room_nbr, arrival_date, departure_date, guest_name)\r\nAS SELECT H1.room_nbr, H1.arrival_date, H1.departure_date, H1.guest_name\r\n\u00a0\u00a0\u00a0FROM Hotel AS H1\r\n\u00a0\u00a0WHERE NOT EXISTS \r\n\u00a0\u00a0\u00a0\u00a0\u00a0(SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Hotel AS H2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE H1.room_nbr = H2.room_nbr\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND H2.arrival_date < H1.arrival_date \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND H1.arrival_date < H2.departure_date)\r\n\u00a0\u00a0\u00a0AND H1.arrival_date <= H1.departure_date \r\n\u00a0\u00a0WITH CHECK OPTION;\r\n<\/pre>\n
    For example,<\/p>\n
    INSERT INTO Valid_Hotel\r\nVALUES (1, '2012-06-01', '2012-06-03', 'Ron Coe'); \r\nGO\r\nINSERT INTO Valid_Hotel\r\nVALUES (1, '2012-06-03', '2012-06-05', 'John Doe');\r\n<\/pre>\n
    will give a WITH CHECK OPTION<\/code> clause violation on the second INSERT INTO<\/code> statement, as we wanted.<\/p>\n
    Dropping VIEW<\/code>s<\/h2>\n
    VIEW<\/code>s, like tables, can be dropped from the schema. The T-SQL syntax for the statement is:<\/p>\n
    DROP VIEW <table name list>; <\/pre>\n
    The use of the <table name list> is dialect and it gives you a shorthand for repeating drop statements. The drop behavior depends on your vendor. The usual way of storing VIEW<\/code>s was in a schema information table is to keep the VIEW<\/code> name, the text of the VIEW<\/code>, but dependencies. When you drop a VIEW<\/code>, the engine usually removes the appropriate row from the schema information tables. You find out about dependencies when you try to use something that wants the dropped VIEW<\/code>s. Dropping a base table could cause the same problem when the VIEW<\/code> was accessed. But the primary key\/foreign key dependencies among base tables will prevent dropping some base tables.<\/p>\n
    Table Expression VIEW<\/code>s<\/h2>\n
    An old usage for VIEW<\/code>s was to do the work of CTEs when there were no CTEs. The programmers created VIEW<\/code>s, and then used them. Of course they wasted space, caused disk reads and were only used in one statement. It might be worth looking at old code for VIEW<\/code>s that are not shared.<\/p>\n
    Today the reverse is true. Programmers create the same CTE code over and over in different queries and give it local names for each appearance. Those local names are seldom the same and are often “place markers” like “X” or “CTE_1” and give no hint as to what the table expression means in the data model.<\/p>\n
    It can be hard to factor out common table expressions across multiple queries. One query uses an infixed JOIN operators and another uses a <span class=”mono”>FROM<\/span> list, the predicates are equivalent but written slightly different and so forth.<\/p>\n
    I recommend that you sit down and think of useful VIEW<\/code>s, write them and then see if you can find places where they would make the code easier to read and maintain. As an example, our hotel application will probably need to find vacant rooms by calendar date, compute an occupancy ratio by calendar date and other basic facts.<\/p>\n
    Another bad use is the one VIEW<\/code> per Base Table myth that was poplar with DB2 programmers years ago. The reasoning behind this myth was the applaudable desire to insulate application programs from database changes. All programs were to be written against VIEW<\/code>s instead of base tables. When a change is made to the base table, the programs would not need to be modified because they access a VIEW<\/code>, not the base table.<\/p>\n
    This does not work in the long run. All you do is accumulate weird orphaned VIEW<\/code>s. Consider the simplest type of database change – adding a column to a table. If you do not add the column to the VIEW<\/code>, no programs can access that column unless another VIEW<\/code> is created that contains the new column. But if you create a new VIEW<\/code> every time you add a new column it will not take long for your schema to be swamped with VIEW<\/code>s. Even more troublesome is the question of which VIEW<\/code> should be used by which program. Similar arguments can be made for any structural change to the tables.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    SQL Views are essential for the database developer. However, it is common to see them misused, or neglected.  Joe Celko tackles an introduction to the subject, but there is something about the topic that makes it likely that even the experienced developer will find  out something new from reading it.…<\/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":[143525],"tags":[4242,4168,4149,4150,5134],"coauthors":[6781],"class_list":["post-1332","post","type-post","status-publish","format-standard","hentry","category-learn","tag-basics","tag-database","tag-learn-sql-server","tag-sql","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1332","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=1332"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1332\/revisions"}],"predecessor-version":[{"id":83520,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1332\/revisions\/83520"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1332"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1332"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1332"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1332"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}