SQL - VIEWS & MATERIALIAZED VIEWS

VIEWS | MATERIALIZED VIEWS

VIEWS:

 

A View is a Virtual Table that uses a query to pull data from the underlying tables.

The following are the characteristics of VIEW:

  • A View is a virtual relation that acts as an actual relation.

  • View is a virtual table, created using Create View command. This virtual table contains the data retrieved from a query expression, in Create View command.

  • View can be created from one or more than one base tables or views.

  • View is never stored it is only displayed.

  • View is never stored it is only displayed.

  • View is updated each time the virtual table (View) is used.

  • View has slow processing.

  • View do not require memory space.

SYNTAX

186.png

MATERIALIZED VIEWS:​

​

When the results of a view expression are stored in a database system, they are defined as materialized views.

The following are the characteristics of Materialized Views:

  • Materialized View is the copy of the original base tables in the database.

  • The following are the characteristics of Materialized Views:

  • The Materialized View are stored on a disk like an object, and they do not get updated every time they are used.

  • The materialized view must be updated manually or with the help of triggers. The process of updating the Materialized View is called Materialized View Maintenance.

  • Materialized Views has fast processing.

  • Materialized View requires memory space.

SYNTAX

187.png
 

Want More SQL Deep Dives

  1. SQL Introduction

  2. Database Overview

  3. Creating a Database

  4. Dropping a Database

  5. Table Fundamentals

  6. Table Types

  7. Datatypes in SQL

  8. DDL, DML, TCL & DCL

  9. Null & Not Null Values

  10. Creating Tables

  11. SQL - Constraints

  12. SQL - Index 

  13. SQL Inserting Data

  14. SQL Identity Columns

  15. Selecting Data

  16. Using Distinct

  17. SQL in NOT IN

  18. Using Top Clause

  19. Where Clause

  20. Group By

  21. Order By

  22. Having

  23. Exists

  24. Offset

  25. SQL - Operators

  26. Between

  27. Update

  28. Delete

  29. SQL - Aggregate Functions

  30. SQL - Introductions to Joins

  31. Table Alias

  32. SQL - Inner Join

  33. SQL - Left Join

  34. SQL - Right Join

  35. Full Join

  36. Self Join

  37. SQL Union

  38. Pivot

  39. Alter Command

  40. Limit Clause

  41. Truncate

  42. Drop

  43. Table Backup

  44. SQL - Normalization

  45. Subquery

  46. SQL - Views & Materialized Views

  47. Case Statement

  48. SQL Transactions

  49. SQL - Scaler Functions

  50. Working with Dates

  51. Grant revoke

  52. SQL - Performance Tunning

  53. Stored Procedure Basics

  54. Stuff function

  55. Keyword

  56. SQL - Miscellaneous