In this article , the author created the following query to pull together the relevant data from both tables:. While this query isn't super complicated, it's a mouthful. Imagine if you had several reports that were analyzing number of comments per user's blog posts, forum traffic, etc. That's an awful lot of SQL you have to write or copy and paste over and over.
By turning that SQL into a view, you can create a kind of query-ready virtual table. The best part? All you have to do to create a view is add the first and last lines below to your SQL query:. That's an error-prone process that can lead to lots of discrepancies across your reports and dashboards. The alternative is to take the above query and dump the results into a new table. And every time you wanted up-to-date data in the table?
You'd have to delete all of the existing data in the table, rerun the query, and dump the new results into the table. But if you use a view, all that work goes away. You create the view using a SQL query, and from now on you get clean, up-to-the-minute data in this virtual table.
Best of all, you can always redefine your view to and it will apply to all the downstream queries consistently without any need to update the queries themselves! Once you get comfortable with views, they will end up saving you an enormous amount of work. Here's why:. Suppose you need to create a bunch of reports that use data from your company's SaaS platform. To do just about any analysis of this data, you need grab columns from 6 related tables. So for every report, you're going to have to join those 6 tables.
That eats up your time and focus. It creates more opportunities to make mistakes. And it makes it more risky for you to allow end-users to create reports. Views are also a great way of hiding calculations, business logic, and other complexities so creating reports is much easier and cleaner.
Suppose your business has two types of customers that everyone is obsessed with: whales, who use your service at least 1, times a month, and minnows, who use it times a month. Your founder went to a talk, and ever since it's been nonstop whales and minnows. You could bake the business logic for whales and minnows into every report that needs these categories.
Or you could take care of it once, in a view. If you build the categories in a view, you'll also be very happy two months from now, when your company decides it makes more sense to expand the whales category so it includes everyone who uses your service at least times a month.
You don't have to rewrite a bunch of reports or a bunch of SQL. Change the view and you're set. And in six months, when your company switches to using sophisticated machine learning models to categorize whales and minnows as well as adding a new predictive category called "potential whales"? Multiple view facility — Different views can be created on the same table for different users. This article is contributed by Harsh Agarwal. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.
See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
Skip to content. Change Language. Related Articles. Table of Contents. Save Article. Improve Article. Like Article. Next SQL Comments. Recommended Articles. Article Contributed By :. Overview of Secure Views. Interacting with Secure Views. Viewing the Definition for Secure Views. Determining if a View is Secure.
Best Practices for Using Secure Views. Sequence-generated Columns. Secure Views and Data Sharing. Some of the internal optimizations for views require access to the underlying data in the base tables for the view. This access might allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions, or other programmatic methods. Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.
In addition, by default, the query expression used to create a standard view, also known as the view definition or text, is visible to users in various commands and interfaces. For details, see Interacting with Secure Views in this topic. For security or privacy reasons, you might not wish to expose the underlying tables or internal structural details for a view.
With secure views, the view definition and details are only visible to authorized users i.
0コメント