SQL Data Interview Series: Views in SQL
Hey there everyone. Welcome to Day 2 of the SQL Data Analyst Interview Questions series. You can read up the topic discussed yesterday here. Today, we’ll be discussing views, their advantages and limitations, and how they differ from stored procedures.
What is a View in SQL?
A view can be considered as a saved SQL query. It can also be represented as a virtual table, in that it contains rows and columns like an actual table, but does not contain any of the actual data. For instance, from our tables containing patients’ information (this is from the SQL for Healthcare project), we the analysts could output the patients’ IDs, their conditions, the states and counties, and the healthcare expenses for these conditions.
This is somewhat easy for us to create, but now we want the health specialists to also be able to access this, and they may not be able to fathom these queries. As such, we need to create a visual, where they’ll be able to access this information without having to input all these queries. This is where views come in handy. Now, instead of all these we’ll use the queries to create a view.
CREATE VIEW PatCon
AS
SELECT P.Id, C.Description, P.State, P.Healthcare_Expenses
FROM PersonalTutorial.dbo.Patients P
JOIN PersonalTutorial.dbo.Conditions C
ON P.Id = C.Patient
SELECT * FROM PatCon
This view has now been created from the two tables ‘Patients’ and ‘Conditions’. So, the specialist just has to select everything (SELECT *) from this view (FROM PatCon) to view (hehe 😅) the necessary information. Easy, right?
You can also update a view
ALTER VIEW PatCon
AS
SELECT P.Id, C.Description, P.State, P.County, P.Healthcare_Expenses
FROM PersonalTutorial.dbo.Patients P
JOIN PersonalTutorial.dbo.Conditions C
ON P.Id = C.Patient
SELECT * FROM PatCon
and delete a view.
DROP VIEW PatCon
Advantages of Views in SQL
Views are important as they can be used:
- to reduce the complexity of the database schema
- as a mechanism to implement row and column level security
- to present aggregated data and hide detailed data
Limitations of Views
• Parameters cannot be passed to a view, like in a stored procedure. Instead, the WHERE clause could be used. Table valued functions, also, are an excellent replacement for parameterized views.
• Rules and Defaults cannot be associated with views. This is because views are virtual tables which do not really store data.
• The ORDER BY clause is invalid in views unless TOP is also specified. This limitation also goes for inline functions, derived tables, subqueries and CTEs.
And lastly,
• Views cannot be based on temporary tables.
Differences between views and stored procedures
As I said earlier, a view is a virtual table which is made up of a query or a complicated set of queries. Views can be used to reduce the complexity of the database schema, especially for non-IT users, in that it eliminates the need for them to work their way through joins and/or unions. Views can be used as a mechanism to implement row and column level security. Also, they are useful in presenting aggregated data and hiding detailed data.
Stored procedures, on the other hand, are a group of SQL statements that are stored together in a database. By grouping SQL statements, a stored procedure allows them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time.
The major difference between these two constructs is that views are used to store commonly used JOIN queries and specific columns to build virtual tables of an exact set of data we want to see, while stored procedures hold the more complex logic, such as INSERT, DELETE, and UPDATE statements to automate large SQL workflows.
Other differences include:
• Views do not accept parameters, while stored procedures do. Instead, the WHERE clause is used in views.
• A view can contain only one single SELECT statement, stored procedures can contain several statements.
• A view cannot call stored procedures but can call functions, while stored procedures can call functions and views.
• Views only allow SELECT statements while stored procedures allow INSERT, UPDATE, DELETE and/or SELECT statements.
• Views can be used in a SELECT query, whereas stored procedures cannot.
• Views should return a value, while stored procedures are not really required to return a value.
In summary, views are great for customizing and restricting access to what users can see from database tables, while stored procedures should be used when one has a long running or complex task.
That’s it for today on views. I hope you learnt a thing or two about views and their functions. If you did, you could — should — leave lots of claps and an encouraging comment. Tomorrow, we’ll be discussing the RANK, DENSE_RANK and ROW_NUMBER window functions. 🤗🤗
