I have recently changed my team and was working on a project and my business analyst asked me to CREATE A TABLE for some business logic. However when I converted the business logic into SQL I did not see it as a requirement for a new TABLE. In my opinion it should be a VIEW.
When I asked why did he specifically mention about a new TABLE and not VIEW , I learned that the team has been creating tables for every business requirement. Considering he is a business guy and not Data guy I discussed with other members in the team and could not figure out any specific reason for that.
In this post, we will answer the question – WHEN TO CREATE TABLE & WHEN TO CREATE VIEW FOR ANY BUSINESS REQUIREMENT ?
Before we proceed with answering the question let us see the key differences between Tables & Views.
Difference between Tables and Views
5 key difference between tables and views are:
- Tables are persistent objects which stores the data in the local disk where Views does not require any storage space.
- Tables metadata and data both are stored where as for Views only metadata is persisted.
- Tables have pre-calculated data so you can directly fetch it while Views data is calculated at run-time dynamically.
- Tables are independent entity and can be queried directly however Views are built on top of one or more tables hence are dependent on underlying base tables
- Tables require mechanism to refresh data like INSERT command while views data is refreshed automatically as data in base tables is changed.
What is Materialised View ?
When we talk about Tables & Views , we should also consider Materialised Views. Materialised views bring the best of both Tables & Views together and overcomes the existing challenges with Tables & Views.
Materialised Views are database objects which are like views but persistent and stores pre-calculated data just like tables. However the maintenance of Materialised views is done by the system only. The data refresh is done automatically as and when the underlying base table data changes. So it is faster than views but take storage space.
Materialised Views does not require run-time data calculation hence it is quicker to retrieve results via Materialised views. However the optimiser may or may not use materialised views in the query.
The figure explains the PROS & CONS of Tables , Views and Materialised Views
Frequently Asked Questions (FAQ)
Is View better than Table ?
- If the business requirement is to encapsulate complex logic into simple query then Views can be better than Table.
- If you don't want to give access to base table then creating a view on top of table is better option.
- If you want to restrict user access to some rows or specific columns then VIEW is better than Table.
- If you are just joining few tables and getting the output without calculating any metrics for specific intervals creating VIEW is better option than Table.
Why do we use VIEWS instead of TABLES ?
- Views are generally used for reporting purpose.
- Views let you hide complex SQL logic from the end user.
- Views let you have more meaningful column names to the output data.
- Views can be used as read-only data layer that enhances data security.
- Views require minimum maintenance hence no additional script is required for data refresh.
In some databases, you have to recreate views if underlying base table structure is changed. This is the minimum maintenance required for views.
What is the difference between TEMP table and VIEW ?
- Temp (Temporary) Tables are special type of tables which are session specific. When the session ends , the temp tables are gone i.e. data and ddl both are lost for temp tables. However views are session independent and the metadata is persisted.
- Some databases allows you to collect stats on Temp tables while you cannot collect stats on Views.
- Some databases allows you to create Temp tables with keys however you cannot have keys assigned in Views.
Which is faster Table or View ?
In most of the scenarios, Tables are faster than Views.
However if you consider a scenario where you join multiple tables, materialised view may be faster than directly querying multiple tables.
Are views slower than Table ?
Views data is not persisted and is calculated at run time hence it takes more time than the tables. If it is 1-1 view i.e. View built on top a single table then the performance difference should be negligible.
If view is a complex view with multiple tables joining in the definition then it may be slower than tables directly joining. One reason could be that in some database, efficient query plan is not generated as information about filter, keys, stats cannot be systematically utilised.
If view is a materialised views for complex joins then it may actually be faster than directly querying underlying tables. However the optimiser may or may not pick materialised view for query execution.
Does views improve performance ?
- Views generally are created for enhanced security and better readability.
- Views gives flexibility to rename columns to more meaningful names.
- Improving the performance is not the first selection criteria while building views. However materialised views definitely improve performance of the query when created and used efficiently.
When to create View or Table for any business requirment?
Now coming to the original question of this post, how did I determine if view should be created or table ?
- I checked the SQL generated by converting business logic. It was a complex SQL with joins on multiple table.
- There was no filter on time specific data. Intention of checking this logic was to confirm if creating output of this SQL as table then what will be the approach to maintain that table.
- I could see there was no filter on date and it was basically looking for entire data in the base tables with filters on some other business columns. There was no specific functional identifiers in the output rows.
- This means if I would have created a table then it would have been TRUNCATE/LOAD table.
- There was no metrics calculated in the query and also no sourcing of keys from base tables. Hence not a candidate for FACT table.
- Creating a table just to store output of join conditions would have added maintenance overhead – creating a table, creating a loading script, scheduling the script to run after base tables are loaded.
Considering all the above mentioned options I went for creation of VIEW.
Did I create materialised view ? No. The reason was view was resulting query output within 10 seconds. All the base tables had less than 100k rows and join was efficient. If the view output would have taken time then I would have gone ahead with materialised view. Because storage space generally takes lower priority than query performance.
The output was used in intermediate step so I took decision to went ahead with view.
Let me know your approach how do you determine if view or table should be created for any given business requirement.