{"id":107509,"date":"2025-08-05T11:00:00","date_gmt":"2025-08-05T11:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107509"},"modified":"2025-12-11T10:25:08","modified_gmt":"2025-12-11T10:25:08","slug":"mastering-sql-views","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/mastering-sql-views\/","title":{"rendered":"Mastering SQL VIEWs: Syntax, Use Cases, and Best Practices"},"content":{"rendered":"\n

VIEWs<\/a> are an undervalued and underused feature in SQL. They basically consist of a query that has been given a name, and a parameter list, so can be used like an inline macro. Technically, you\u2019re supposed to think of it as a virtual table. <\/p>\n\n\n\n

The idea of an inline macro or inline function goes way back to the first versions of Fortran<\/a> and later BASIC<\/a>. Depending on the product, the syntax might look like this: FN DOUBLE (N) = (N + N)<\/code>. You had to give the name of the Macro, a simple optional parameter list, assignment operator, usually an =, and the text of the computation. It was often important to put parentheses around the body of the macro, or to have a compiler that would do this for you. <\/p>\n\n\n\n

This inline feature was underused in most of the languages that had it. After all, you are already using a text editor to input your code, so why not just put the code for the macro into the program text, using a \u201ccut and paste\u201d in your editor? <\/p>\n\n\n\n

Basic Syntax for a CREATE VIEW Statement in SQL Server<\/h2>\n\n\n\n

The basic syntax for a CREATE VIEW<\/a><\/code> statement is not as straightforward as you might think. There are two options for the VIEW<\/code>:<\/p>\n\n\n

\n
    \n
  1. Views that are recursive,  <\/li>\n<\/ol>\n<\/div>\n\n
    \n
      \n
    1. Views that have a check option. <\/li>\n<\/ol>\n<\/div>\n\n\n

       We\u2019ll get to those in a minute, but the basic syntax is: <\/p>\n\n\n\n

      CREATE VIEW <VIEW name> [(<VIEW column list>)] \n[WITH RECURSIVE <table name (<VIEW column list>)] \nAS \n<Query expression>; <\/pre><\/div>\n\n\n\n
      Ignore the options involved with a recursive VIEW<\/code>. We will discuss that separately, but a recursive view is almost never used in practice, because the recursive queries in Standard SQL are expensive and confusing. They are generally better handled with nested set models in the DDL (Data Definition Language)<\/a>, but if you must be able to take anything that generates a table and use it as the query expression in a VIEW<\/code>.<\/p>\n\n\n\n
      It\u2019s always a good idea to provide a list of column names for the VIEW<\/code>, even if they just repeat the column names from the query. If anything subsequently changes, this is the best place to update the view without needing to fiddle with base tables. The column list will also allow you to change the column names in the view as necessary. For example, given a Personnel table, there might be a logical distinction between a regular, old employee, and employees who belong to a special group:<\/p>\n\n\n\n
      CREATE VIEW NYC_Team (emp_id, emp_name, city_name AS metro_nyc., ..) \nAS \nSELECT emp_id, emp_name, office_city_name, ... \n FROM Personnel \n WHERE location_name IN (\u201cNew York\u201d, \u201cNew Jersey\u201d, \u201cBrooklyn\u201d, \u201cBronx\u201d,\n\u201cManhattan\u201d); <\/pre><\/div>\n\n\n\n
      If you look at the internal schema DDL in most SQL products, you will find that it contains a fair number of relatively simple VIEWs that are assembled from base tables. This is for human readability and to make sure that everybody uses the same definition for reporting purposes. <\/p>\n\n\n\n
      Why are VIEWS Useful?<\/h2>\n\n\n\n
      Views are useful because they let you modify an application from a single location. I wish it had always been like that. Decades ago, pre-SQL, I wrote a scheduling program for a friend who needed to have wait-staff at his restaurant. A problem that had to be resolved was that the age to drink and to serve alcohol had just jumped from 18 to 21 years of age, and this change did not get correctly propagated in the BASIC programs that we were using. <\/p>\n\n\n\n
      Cruel fate arranged that we had underage servers on the night that the Alcoholic Beverage Control people decided to run a surprise inspection… <\/p>\n\n\n\n
      Types of VIEWs<\/h2>\n\n\n\n
      Nested VIEWs <\/h3>\n\n\n\n
      You can put VIEWs inside other VIEWs. How deeply you can nest them depends on the product you\u2019re using. The only rule is that each macro has to exist before it can be used. You might think this is a bit fussy, but in one of the very early SQL products, you could mistakenly write\u2026 <\/p>\n\n\n\n
      -- (wrong!) \nCREATE VIEW Foobar \nAS \nSELECT * FROM Foobar; <\/pre><\/div>\n\n\n\n
      This would hang the compiler in an endless loop. Essentially, it would try to resolve the FROM<\/code> clauses that nested from here to infinity, as \u2018Foobar\u2019<\/strong> Is invoked endlessly. This led to the rule that tables referenced in the VIEW<\/code> must completely <\/em>exist, not just be \u201cin process\u201d, and thereby appear, prematurely, in a symbol table.<\/p>\n\n\n\n
      A related problem is caused by declaring circular references. VIEW A depends on VIEW B, VIEW B references VIEW C and finally VIEW C is defined with VIEW A. This ends badly.  <\/p>\n\n\n\n
      Union VIEWs <\/h3>\n\n\n\n
      This is a typical pattern in VIEWs. The query that defines the VIEW<\/code> is a UNION<\/code>, rather than a simple SELECT<\/code>. The skeleton looks something like: <\/p>\n\n\n\n
      CREATE VIEW European_Sales (country_code, sales_total) \nAS \n(SELECT country_code, country_sales \n  FROM Albania_Sales \nUNION \nSELECT country_code, country_sales \n  FROM Bulgaria_Sales \nUNION \n\u2026 \nSELECT country_code, country_sales \n  FROM Yugoslavia_Sales); <\/pre><\/div>\n\n\n\n
      In the SQL Standards, the result of a UNION<\/code> expression has no column names unless you explicitly create these names with a syntax as follows:<\/p>\n\n\n\n
      \u201c(<UNION expression>) \n AS <table name> (<column name list>)\u201d  <\/pre><\/div>\n\n\n\n
      In practice, some products get the default column names from the first query construct in the UNION<\/code>, while others look at the last UNION<\/code> construct. There is also a rule that the statements in the list being unioned must be \u201cunion compatible\u201d; this concept appears in other places in the language, but the idea is that columns in the results have to be at the same data type and same position. The intention is that you can glue these lists together easily. Today, they don\u2019t have to be an exact match in most products, but are implicitly cast to a single, higher-level data type. <\/p>\n\n\n\n
      Remember that the SELECT<\/code> <\/strong>clause can also rename columns from the base tables. If you\u2019re going to do this with a VIEW, you would assign the names in the CREATE VIEW<\/code> clause. This puts the information you need in one place. Rules for alias names in a query are trickier than you would first think; it might be worth an article on its own, but, for the time being, just remember these rules. <\/p>\n\n\n\n
      Computational VIEWs <\/h3>\n\n\n\n
      How many kgs in 1 lbs? Let me save you the trouble of doing a Google search: 1 pound = 0.45359237 kilograms, to 9 decimal places. I think I can say with some certainty that this number does not roll off your tongue easily. I\u2019m sure there\u2019s a few nerds out there who know pi to at least 10 decimal places because that was the precision on of calculators in the late 1960s. <\/p>\n\n\n\n
      These conversion constants can be hidden in VIEWs, a convenience so that you can get to reports and queries easily in one system of measurements, but how many decimal places do you want to use? As a general rule, I would recommend carrying it out to what looks like a large number of decimal places, say two more than you think you\u2019re going to need. This is because you can always round or truncate the results to get a VIEW with meaningful precision for your specific application, but <\/em>you cannot reliably pad out decimal places. Most of the time, we are happy to use a factor of 2.2 pounds per kilogram because were more apt to work with groceries than with precious gems or physics experiments. I also must mention Celko\u2019s law: \u201ca number impresses people as the square of the decimal places, even if it is meaningless.\u201d <\/p>\n\n\n\n
      Recursive VIEWs <\/h3>\n\n\n\n
      Technically speaking, standard SQL allows for recursive queries. I\u2019ve never seen anyone actually use them in production so I won\u2019t go into any details. A recursive SQL query is a query that repeatedly calls itself to process hierarchical or self-referencing data structures, such as organizational charts, family trees, or parts explosions. Its execution is limited and expensive, so I would recommend that you use instead a nested sets model for such problems. <\/p>\n\n\n\n
      This construct consists of the CTE, which holds the results and is a recursive part. The other part is the anchor. The anchor is a base table or table expression used to define starting point. <\/p>\n\n\n\n
      The Common Table Expression (CTE)<\/a> is a temporary, named result set within an SQL query. The Recursive CTE is a special type of CTE that allows for recursion. It references itself within its definition. <\/p>\n\n\n\n
      You need to inform the compiler that the CTE is going to be recursive in standard SQL. Some commercial products can detect this. Here\u2019s the basic skeleton: <\/p>\n\n\n\n
      WITH RECURSIVE cte_name [(<column_name_ list)] \n -- Anchor \n  SELECT ... \n  FROM <table_name> \n  WHERE <anchor condition> \n  UNION ALL \n -- Recursive CTE expression \n  SELECT ... \n  FROM <cte_name> -- Referencing the CTE itself \n  WHERE <termination_condition> -- beware of infinite loops! \n) \nSELECT * FROM cte_name; <\/pre><\/div>\n\n\n\n
      The WITH CHECK Clause<\/h3>\n\n\n\n
      The <\/strong>WITH CHECK<\/code> <\/strong>clause <\/strong>appears at the end of the CREATE VIEW<\/code> statement. <\/p>\n\n\n\n
      A VIEW that is defined WITH CHECK<\/code> enforces any rows that are modified or inserted against the SELECT<\/code> statement for that VIEW<\/code>. <\/p>\n\n\n\n
      VIEWs with the WITH CHECK <\/code>option are also called symmetric VIEWs. For example, a symmetric VIEW<\/code> that only returns employees in the Sales<\/strong> department will not allow insertion of employees in other departments. The WITH CHECK<\/code> <\/strong>option forces the WHERE<\/code> clause to be checked before actions are done against the base tables that define the VIEW<\/code>. In other words, the WITH CHECK<\/code> option does not affect the result of a SELECT<\/code> <\/strong>statement. <\/p>\n\n\n\n
      Updatable VIEWS <\/h3>\n\n\n\n
      Not all VIEWs can be updated. In fact, this has been a topic in relational theory for a very long time. Chris Date even wrote a whole book on this topic: VIEW Updating and Relational Theory: Solving the VIEW Update Problem<\/a><\/em> (ISBN-13: 978-1449357849). <\/p>\n\n\n\n
      In Standard SQL, a VIEW<\/code> is updatable if a row in the VIEW<\/code> maps exactly to one row in a base table. This means that there can be no computed columns, joins or set operations in the VIEW<\/code>. <\/p>\n\n\n\n
      However, there is a sneaky and complicated kludge to get around this limitation. Let\u2019s say you have a VIEW<\/code> that has a column for the company’s total sales. Total sales are computed by adding up sales made by all of the branch offices. If you update the total sales, there is a business rule that you distribute new sales proportionately over the branches via some formula. You\u2019re going to need to do arithmetic here. <\/p>\n\n\n\n
      The best way to do this is in a procedural language that\u2019s designed to do arithmetic. However, you can do an INSTEAD OF<\/a> trigger using math mixed with CASE expressions<\/a>. You are essentially doing procedural coding with SQL declarative constructs! The advantage is that unlike a procedure call, you can\u2019t avoid the trigger. Happily, none of this is explicitly exposed to the user.<\/p>\n\n\n\n
      Conclusion <\/h2>\n\n\n\n
      A view allows the database to insulate the user from the complexities of accessing the base tables and can provide a convenient way to update a database as required. Although there is likely to be a performance cost, much of this can be avoided. If a VIEW<\/code> is used in many places in the same query, or is used in several queries in the same database, there\u2019s a reasonable chance that a modern SQL engine will materialize it rather than recompute it each time. This can save execution time – just be careful that you don\u2019t overdo nesting or query complexity in your views.<\/p>\n\n\n\n
      \n    
      FAQs: How to Master SQL VIEWs in SQL Server<\/h2>\n\n                        
      1. What is a SQL VIEW?<\/h3>\n            
      \n                
      A SQL VIEW is a virtual table created from a query that simplifies data access and improves readability.<\/div>\n            <\/div>\n                    
      2. Why use VIEWs in SQL Server?<\/h3>\n            
      \n                
      VIEWs help encapsulate complex queries, enforce security by restricting column access, and provide a consistent interface for applications.<\/div>\n            <\/div>\n                    
      3. Can SQL Server VIEWs improve performance?<\/h3>\n            
      \n                
      No, not directly, because SQL Server VIEWs don\u2019t store data. However, indexed views can boost performance for frequently queried datasets.<\/div>\n            <\/div>\n                    
      4. How do VIEWs differ from Tables in SQL Server?<\/h3>\n            
      \n                
      Tables store physical data, while VIEWs are query-based representations of data from one or more tables.<\/span><\/p>\n            <\/div>\n                    
      5. Are SQL Server VIEWs updatable?<\/h3>\n            
      \n                
      Yes, but only if they meet certain conditions (e.g., based on a single table without aggregates). Otherwise, they are read-only.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
      Learn how to use SQL VIEWs effectively with practical examples, syntax breakdowns, and expert tips for recursive, union, and updatable views.…<\/p>\n","protected":false},"author":96214,"featured_media":107513,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,143524],"tags":[4168,4170,4150,4151,4252],"coauthors":[6781],"class_list":["post-107509","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-sql-server","tag-database","tag-database-administration","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107509","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=107509"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107509\/revisions"}],"predecessor-version":[{"id":108029,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107509\/revisions\/108029"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/107513"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=107509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=107509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=107509"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=107509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}