This article discusses one of the basic but greatly misunderstood objects in SQL server – “Views”. Views could be looked as an additional layer on the table which enables us to protect intricate or sensitive data based upon our needs. It’s like a window exposed to the flocks and they can see very selective items inside the room through the window. Since views are an additional layer sure they do add an overhead but there is a tradeoff for situations when they are of great help.
We begin with creating 3 tables , & . These are fictitious tables for our demo. The stores data for a retail shop with a flag column based on whose value we treat the products as Salable.
Next, we have a table which stores and details for customers.
Lastly, I have created a table which houses all the bookings from different customers.
Next, insert a few records into these tables:
Our table’s contents look like this. I know the tables are not completely normalized, for now please ignore them, these are simple demo tables.
A purchases/books a and the same gets recorded into the table now to generate the bill on his name we can uses a VIEW which would help us do away with a physical table. Instead it would enable us to generate the bill based on the information from these 3 tables itself. Let’s see how it’s possible.
We have been able to generate the bill based on the 3 tables hence we have not only optimized the bill generation also we have saved ourselves from hosting a physical table in the database with this information.
This is the most credible use of a VIEW; it can not only reduce apparent complexity but also prevent redundant hosting of data in the DB.
Next say there is some API which enables the care executives to view the information details. Now exposing the Password might be risky, it’s strictly confidential info.
We create a View which can be exposed to the API:
We have a created a View which can be used by the API to fetch details –(Minus) the Password Column.
Views can be used to prevent sensitive information from being selected, while still allowing other important data.
Views do not have a physical existence, but still they do return a set of record set as a table does, the differences is it is simply an additional layer which calls the underlying code which finally returns the record set.
When I execute the code...
...I get recordsets as I would get in a table with the only difference that the data returned is as per the below query:
But arguably, we still get a set of records, isn’t it? So say if there are 1 million customers in my database, wouldn’t it be cool if I have clustered/non clustered index on my view for optimized queries. But is it possible as the view doesn’t host data physically, the answer is yes. It is possible to have indexes on views. But before we find ourselves capable of creating index on views, we have to our s.
What is SCHEMABINDING a VIEW
Schema binding binds your views to the dependent physical columns of the accessed tables specified in the contents of the view, i.e. if is schema bind no one will be able to alter the table unless they drop the table.
Why would we need that?
The answer is, it prevents your views from being orphaned. Just think that someone drops/alters the table without paying any heed to our view. Now that would leave our view nowhere. Hence schema bind it, this will prevent any such accidents from happening.
Also to be able to create an index on the view you need it essentially schema bound. Let’s make the change:
Cool now we have an index on the view; remember you need to have a to be able to create a . By which I mean I can create the below index mandatorily post the creation of the .
So next use of the View is to be able to create an additional index upon the db to speed up your query performance.
Are views only meant for reading data in a customized mode? Not really views also facilitate DML (//). But there is a set of rules which needs to be adhered to enable DMLs:
1) If you are using a view to insert data, then your view should have a single select and also all the mandatory columns of the “being edited” table must be included in the view unless the table has a default values for all columns of the table.
2) Secondly don’t forget, for views with “” options enabled, it’s important to keep in mind that the data begin inserted qualifies in the clause of the view and is certain to be selected by the view. Simply put the data you insert is picked up while you select from your view.
3) If the view is having s with more than one table, then most cases chances of modifying capabilities are negligible unless Triggers are in place to handle the request.
Keeping these in mind, let’s turn to an example and perform s/s/s. I am altering the below view as:
The happened because though the columns and are mandatory but is and has a . All the other mandatory data was supplied in the query.
Alternatively what I do is in my SSMS.
Tools > Options > Environment >Keyboard > Ctrl-F1 ==
From next time, to see the contents of a /, simply select it and hit Ctrl+F1?
Just in case we are working with a non-schema bound view and there is some change in the underlying table, to prevent the view from producing unexpected results, we have an option to refresh the view with:
This updates the metadata of a non-schema bound view.
Encrypting your Views
The “” option encrypts the views by which I mean it will not be visible via so in case of strict requirements where the contents of the view don’t need to be exposed this option freezes the view. It’s important to save the contents script in some archive to be able to retrieve the code for any change.
So we have encrypted the view, now if we try to ''. The result would be:
It is not advised to encrypt your views unless you have a very strong reason behind it.