Hello, welcome to Appsloveworld Technologies, In the session, we learn about what a view is and the advantages of using views.
what is a view?
We can consider the view as nothing more than a saved SQL query. We can consider a view as a virtual table .
Let’s look at an example that will make this definition clear. In the below image you can see the University’s table. We just got University_Id and University name columns and the second is the Professor table.which has got id, name, salary, gender, and University_Id columns.
Now I want you to write a query that can produce the output that you can see on the below image.
So the Id, name, salary, and gender comes from the TblProfessor’s table and the University name comes from the TblUniversity table.
So obviously to produce this output we will have to join the TblProfessor table with the TblUniversity table. And we have seen how to do that in SQL join series article. We have talked about Joins extensively in the below post.
so here we have a join query which returns Id name salary, gender, and University name from these two tables by joining them.
Select Id, Name, Salary, Gender, UniversityName from TblProfessor join TblUniversity on TblProfessor.University_Id = TblUniversity.University_Id
so now how to create a view,for creating a view we use the “create view” statement. Just like if we want to create a table we use “create table” and along the same lines, we want to create a procedure we use the “create procedure” statement.
Create View vWTblProfessorDetail as Select Id, Name, Salary, Gender, UniversityName from TblProfessor join TblUniversity on TblProfessor.University_Id = TblUniversity.University_Id
In the name of The View, we have prefixes that “VW” standing for view. so just by looking at the name of a SQL server object, you know, if you follow the right naming conventions, you can tell what type of object it is
For example, if it’s a table we prefix that usually with “tbl”, if it’s a stored procedure, we prefix that that “sp”, so along the same lines, if it’s a view prefix that that VW so just by looking at the name of an object, you can tell what type of object it is.
So “create view” view name “as” and then after “as” you specify you are select query.
Now let’s execute the above SQL query
So this view is now created in the SQL server. So once the view is created, how do you see that view?.
To see the view that is just created, go into the database, which is a TestDatabase in my case, because we created this view in the TestDatabase in our database. So expand TestDatabase and look in the view folder
So when I expand that, and let’s refresh that. We can see the view vWTblProfessorDetail.
So now let’s say I want to select data from this view. How do I do that? You can treat this view just like a table.
So if I want to select data from that, I can just say SELECT * from vWTblProfessorDetail.
when you execute the select query, what actually happens is the database engine knows that this view is actually getting its data from the TblProfessor and people TblUniversity.
So for this view, Two tables at the underlying tables or base tables. so when we execute the SELECT * from vWTblProfessorDetail, the SQL Server engine actually executes the select query on these two base tables and returns as the data from those tables.
so a view, in fact, doesn’t store any data. we are treating that view as a table. That’s why a view is called a virtual table. But in fact, when you look at the definition of the view, it doesn’t store any data. It is only a saved select query.
The reason why we say it as a saved select query is that when I say “sp_helptext” and then the name of The View, you can actually look at the definition of that view.
Let’s execute the above query see the definition of view
So as you can see in the above image, what is stored in the database? Only the select query. So a view is nothing more than a stored query. Or you can treat that as a virtual table.
Advantages of using views
Let’s turn our attention to what are the advantages of using views, where do we actually use these views.
Now, views can be used to reduce the complexity of the database schema
Now, we have just done that. let’s say we have some non-technical users within our organization who interact with our database. So for a non-technical user join is a little complex topic.
so writing a Joins query is not easy for non-technical users, but they want to join maybe TblProfessor, TblUniversity, TblCountry, and all the other tables. Maybe they want to join like five to six tables
so in that case, what you can actually do is write a view which joins all those tables and then give access to that user to the view.
And then that user can treat that as a single table. And it is easy for him to query that table and they can simply use the where clause and filter whatever data they want. so obviously views can be used as a mechanism to simplify the database schema for non-technical users.
Views can be used as a mechanism to implement row and column level security
Let’s look at an example of that. So how can views be used to implement row and column level security? if you look at the view that we have, this view is basically retaining all Professors within my institute.
Now, let us say there is a Michigan State University professor and I want to provide him access only to see Michigan State University professor.
In that case, if I grant him access to the table, what’s going to happen? He can issue a select query from those tables and he can see all the rows and all the columns.
So I don’t want to do that. I want to allow him access only to his University professor. So how can I achieve that?
In that case, create a view and within the view that return only Michigan State University professors and give access to that view. Let’s do that.
Create View vWTblProfessorMichigan_State_University_Detail as Select Id, Name, Salary, Gender, UniversityName from TblProfessor join TblUniversity on TblProfessor.University_Id = TblUniversity.University_Id where TblUniversity.UniversityName='Michigan State University' SELECT * from vWTblProfessorMichigan_State_University_Detail
And then once we grant access to that professor, to this view, he can only see Michigan State University professors and nobody else.
We can also implement column-level security, for example, in any organization, salary is confidential information, and we don’t want everybody to see the salaries of other people.
In that case, we can actually create a view that hides the salary column and then retrieves all the other columns.
Create View vWTblProfessor_withoutsalary_Detail as Select Id, Name, Gender, UniversityName from TblProfessor join TblUniversity on TblProfessor.University_Id = TblUniversity.University_Id
So views can be used as a mechanism to implement row-level and column level security.
Views can also be used to present only aggregated data
For example, let’s say you want to prevent the end-users from looking at the detailed data. You want to provide them only with aggregate data. So how can we do that again? Using views.
let’s say, for example, I want the total number of professors by University, for example, at Michigan State University.
How many professors are there? So we want the total number of professors by University to summarize data. And obviously, to do that, we have to use group by clause. And we have seen how to use the group by clause in below post.
Create View vWTblProfessor_by_University as Select UniversityName, COUNT(Id) as TotalProfesssor from TblProfessor join TblUniversity on TblProfessor.University_Id = TblUniversity.University_Id Group By UniversityName
Disadvantages or Limitation of View
You cannot pass parameters to view
Let’s understand what we mean by this with an example. I have a table called UniversityStudent, which has Id, name, Marks, gender, and University_Id columns.
Now, let’s create a view on this table. So here we are creating a view called vWGetStudentDetail, and this view is written to select all the columns from the universitystudent table.
Create View vWGetStudentDetail as Select Id, Name, Marks, Gender from UniversityStudent select * from vWGetStudentDetail
Now let’s try to pass a parameter to this view and see what actually happens.
If you look at the view implementation on below, the difference between these two views is that in the view here, we are actually passing an Id parameter of type int and we are using that parameter to filter the rows from table UniversityStudent
Create View vWGetStudentDetail @Id int as Select Id, Name, Marks, Gender from UniversityStudent where id=@Id
Now, is this possible in view? It’s not possible. You cannot pass parameters to views. what will happen when we try to do that? We will get an error.
So if you want to do that, you can actually use the where clause Just in case I want only Student with id=1, I can use the where clause where Id is equal to 1 and I only get a student with id=1.
select * from vWGetStudentDetail where id=1
You can also use a table-valued function as a replacement for parametrized views. In fact, we have spoken about that when we were discussing about user define -functions.
let us see how to create a function that can act as a replacement for a parametrized view.
Create function fnStundentDetails(@Id int) Returns Table as Return (Select Id, Name, Gender, Marks from UniversityStudent where Id = @Id) Select * from dbo.fnStundentDetails(1)
This is an inline table-valued function which is taking a parameter and the way we invoke the function, look at this, it’s exactly the same way as the Invoke a view.
select * from the function name and we pass in the parameters that it expects in this case Id.
so parametrized views cannot be created in SQL Server, but table-valued functions can be used as a replacement
Rules and defaults cannot be associated with the view
So this is another limitation that we have. And it makes sense because views are virtual tables. They don’t really store any data except for the indexed views .That’s why it makes sense not to have rules and defaults being associated with the view.
- The order by clause is invalid in view unless top or FOR XML is also specified. So in a view definition, you cannot use the order by clause.
Let’s try to use an order by clause in view and see what actually happens.
Create View vWStudentDetail as Select Id, Name, Marks, Gender from UniversityStudent order by id desc
when executing the above query, it will give us an error.
“Procedure vWStudentDetail, The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions unless TOP, OFFSET, or FOR XML is also specified.”
So you cannot use the order by clause unless you use the top or for XML keywords.
You can not create a view on temporary tables
Let’s look at that in action so we have a temporary table called #TblUserDetail, and this is a simple temporary table that has got id, name columns.
And we are inserting some sample data into that temporary table. Again, we have spoken about local temporary tables and global temporary temporary tables in a great in below post.
So if you are new to temporary tables, I strongly recommend reading the below post first.
Create Table #TblUserDetail(Id int,Name varchar(100)) insert into #TblUserDetail values(1,'john') insert into #TblUserDetail values(2,'mike') select *from #TblUserDetail
Now let’s create a view on that temporary table.
Create View vWTblUserDetail as Select Id, Name from #TblUserDetail
Can you create a view on temporary tables? You cannot. If you try to do that, you will get an error message stating.
“Msg 4508, Level 16, State 1, Procedure vWTblUserDetail, Line 4 [Batch Start Line 3]
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.”
So those are the limitations on views,
- You cannot pass parameters to a view.
- Rules and defaults cannot be associated with the views.
- The order by clause is invalid in views unless top or FOR XML, keywords are also specified.
- Views cannot be based on temporary tables.
To modify a view we can use alter view statement
- ALTER VIEW statement
To Drop a view – DROP VIEW vWName
Sql Table Script
/****** Object: Table [dbo].[TblProfessor] Script Date: 09/30/2020 5:35:11 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TblProfessor]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](max) NOT NULL, [Gender] [nvarchar](50) NULL, [Salary] [float] NULL, [University_Id] [int] NULL, CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[TblUniversity] Script Date: 09/30/2020 5:35:11 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TblUniversity]( [University_Id] [int] IDENTITY(1,1) NOT NULL, [UniversityName] [nvarchar](max) NULL, CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED ( [University_Id] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[TblProfessor] ON GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (1, 'Christopher', 'Male', 50000, 2) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (2, 'Mackenzie', 'Female', 60000, 2) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (3, 'Julia', 'Female', 40000, 4) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (4, 'Connor', 'Male', 30000, 3) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (5, 'Madeline', 'Female', 90000, 6) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (6, 'Easton', 'Male', 35000, 5) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (7, 'Gianna', 'Female', 40000, 7) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (8, 'Chase', 'Male', 32000, 3) GO SET IDENTITY_INSERT [dbo].[TblProfessor] OFF GO SET IDENTITY_INSERT [dbo].[TblUniversity] ON GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (1, 'Carnegie Mellon University') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (2, 'Michigan State University') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (3, 'Arizona State University') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (4, 'The Australian National University') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (5, 'The University of Arizona') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (6, 'The University of Melbourne') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (7, 'McGill University') GO SET IDENTITY_INSERT [dbo].[TblUniversity] OFF GO ALTER TABLE [dbo].[TblProfessor] WITH CHECK ADD CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id]) REFERENCES [dbo].[TblUniversity] ([University_Id]) GO ALTER TABLE [dbo].[TblProfessor] CHECK CONSTRAINT [FK_TblStudent_University] GO
The post What is a view? Disadvantages & Advantages of views with an example appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Repeat first element XML SQL Server using for path
- Alter table & Add UNIQUE key results in an error
- when sqlchars and when sqlstring?
- The multi-part identifier "Export_Shipping.site_num" could not be bound
- org.hibernate.AnnotationException: referencedColumnNames(P0, P1, P2)... not mapped to a single property
- Convert from LocalTime to TimeSpan
- Update a local/client Microsoft Access Database from a server (MS SQL Server2005)
- Minimum permissions for a SQL server "heartbeat" check?
- How to get table name, column name, column describe, column value in SQL SERVER 2008
- SQL Server stored procedure concat string as query
- SQL Server - Group by - Additional column
- How does SQL server evaluate the cost of an execution plan which contains a user defined function?
- filling in table from function return sql server
- How to find who deleted a view from DB with SIMPLE recovery model
- i want to remove un-necessary joins from my following query to increse the efficiency.
- Select all rows if @Parameter = NULL
- Using BETWEEN in CASE SQL statement
- Entity Framework Indexing ALL foreign key columns
- Django with database on SQL Server
- Writing a SQL trigger for the first time
- Unable to connect to remote SQL server using SSIS
- @@RowCount inside Try Block not returning correct values --Sql Server
- SSDT Disable renaming - refactor log
- Is it possible to put the XML content in a field of the database?
- Create new table based on values of other
- SQL syntax to join on a column alias
- Query to get count of Rows within a date range stored in records
- SQL Server - How many rows have been inserted into a table while a query is running?
- Good reference for SQL to use with SQL Server 2008
- Incorrect value being passed in to SQL from .NET sqlcommand
- Using Cast As Date vs DateTime for a Between
- How can I find a table with column of maxlength 256 from a SQL DB?
- Identity user object is not mapped to another object - entity framework
- How to copy table data from remote server to Local server in SQL Server 2008
- What is MYSQL equivalent for DATABASENAME..TABLENAME?
- Decrypt MS SQL Server 2008 R2 data in MySQL 5.5 using AES encryption/decryption
- Cannot create assembly because of references to .net assemblies
- Why CAST('20140904 23:59:59.999' AS datetime) equals '2014-09-05 00:00:00.000'?
- Recursive query with CTE - SUM of child columns for a given parent
- Transparent data encryption at column level in sql server
- Select records with associated records of specific type
- Spark-sqlserver connection
- Four columns based on four keys
- How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
- Convert Columns into Rows in SQL Server
- Select operations from tables using JOINS in stored procedures
- What do I need to work with a Visual Studio .dtproj file?
- How to validate content for a forumpost for own PDO database-class ? (to prevent SQL Injections)
- Difference in Selection Order ORACLE and SQL Server without ORDER clause
- SQL reference another table as foreign key
- SQL get chat query
- Query with case statement from SQL Server to Linq query c#
- bulk insert by dynamic file name
- Does SQL Server transform text in any way when performing a BULK INSERT?
- Select 50 previous rows
- SQL Counting Total Time but resetting total if large gap
- SSIS package executes fine from my machine, but failing as a SQL job
- Kill a blocked process in a database
- How to store unicode characters in SQL Server?
- TSQL - running total
- select value into variable and alias in SQL Server
- How to implement this data structure in SQL tables
- PHP call stored procedure with MSSQL_Query from SQL server 2012 return invalid value
- DateTime import using Pandas/SQLAlchemy
- Why PrimaryKey NonClustered Index and Separate Clustered Index on Same PrimaryKey Columns are Designed.?
- SQL Server - Joining two tables directly or, sometimes, through a third table
- Either/Or Relationship in Database design improvement
- Upgrading DTS packages to SSIS Packages
- Deleting non distinct rows
- Deploying SQL Database changes from a SSDT project
- Function-based indexes in SQL Server
- What is the script that is used in SQL Server Management Studio to rollback?
- Update the Lastname of Employee for all version based on current version
- Send notifications to user when row with specific keywords is inserted
- Save XML data to SQL Server table
- Combine two tables in SQL Server
- EF Core keeps complaining about non existent indexes
- Find only those ID's where color are same on all rows
- c# trying to use remote sql server for my login but not work
- How to show record in SQL
- Store result set in memory over multiple queries?
- remove duplicate from result in sql
- PHP query to SQL server database (wordpress)
- Encrypting Windows Authentication connection strings
- Kill all user sessions using dynamic SQL - exec within exec
- Truncate Table C# Cannot Find Table - Does not Exist or no Permission
- How do I add xsi:nil="true" to only one field while generating an xml file from SQL Server 2008 using for 'xml path'
- Trying to INSERT a SEQUENCE number into an INSERT statement but having issue
- Entity Framework 6 Code First - Comments on a Tree Structure
- How to write a DbContext Class.
- DB tables technical info in SAP Data Dictionary
- Talking to SQL Integration Services with C#
- How can I avoid Entity Framework Core OrderBy a list property from doing a Top (1) per list item in the database?
- What is the best way to use SQL Server Analysis Services data in a line of business application?
- Does the stream_id change if I move the file to some other directory within the same filetable?
- How do I get N records before given one?
- Hibernate has returns the zero results but native sql returns different
- Partial Keyword Searching (MS SQL 2005)
- Way to save the other error message in sql server
- Unable to get the right SUM using group by
- using ssis to move tabels and parts of tables from one database to another
- Export stored procedure results into a table
- How to connect to SQL Server provided by AppHarbour
- Entity Framework One To Many Relationship mapping foreignkey causing null on navigation properties
- Get the Defect Type with the maximum Total Defect Qty
- List of DatabaseVersion values from RESTORE HEADERLISTONLY?
- Is having a single column table in SQL Server considered a bad practice?
- Mono equivalent of ClientConnectionId
- SQL Server Enterprise Manager 2005 - stored procedures not showing up
- EF Core 2.1 evaluates locally when subquery and aggregate after Grouping
- ident_current shows wrong last inserted id
- How to bypass "Select Data Source" Prompt For Query Tables w/ VBA
- How to get a break time for the days
- Deciding database need
- in sql server what is the difference between user_type_id and system_type_id in sys.types
- Conditional Update Statement T-SQL
- MS SQL SERVER 2008r2 connection with Visual studio 2012
- Failing installation of msodbcsql17
- Replace in SQL Query
- SQL Server - Deleting rows between a date range using SQL. Date conversion fails
- Identify same amounts over different users
- SQL How to extract all possibles routes of a process
- Add a column containing the id of the previous row in each group
- Deploy SSIS Project using TFS Command Line
- Conversion from varchar to numeric in SQL Server
- How to call SQL Server stored procedure from Android in Xamarin
- How can I convert a JSON date with timezone to a SQL Server datetime?
- SQL Server 2012- Server collation and database collation
- how to parse json into rows instead of columns?
- SSRS call to stored procedure fails, Cannot find user 'dbo'
- How to take the backup of live sql server 2005 express DB
- MSSQL - Create table function, return substring
- How to merge/combine two SqlDataReader Objects
- Loop through a table in a CLR UDF C#
- Get column name of a bit column where value is true
- How to improve query performance
- SQL Server Management Studio 2005 - Change Default Directory for Backup Location
- Using the same table in a join
- order by clause in union statement
- Data Import FROM Postgres SQL_ASCII database TO SQL Sevrer 2008 R2
- Group / User based security. Table / SQL question
- Node Feathers Sequelize API invalid column name
- How to count records by store, day wise and in 2 hours range period with pivot table format?
- SQL: How to get in a single query info from a table and aggregate info from another table?
- Conversion failed when converting varchar value to data type int
- SQL query for evaluating boolean logic from innerjoin on itself
- Intersection of two T-SQL queries
- What would be the fastest way to insert 750 records using Ado.NET?
- Best approach to connect to SQL Server from a non-domain web host