I’ve been a little busy for the past week which made me fall behind most of the things l wanted to write on the Learning Blog. There are more features added to the site because I’m trying to do something bigger and impact the world a little better.
In this tutorial, I’m going to discuss another important database object, that is a View. What is a view? It is simply a virtual table defined by a select statement. The code for the view is stored in the database and any time the view is accessed, this select statement is run to generate the result sets for the user. There are 3 types of views that I’m going to discuss, these are Regular View, Schemabinding view and Indexed View. The general syntax for creating a view looks like this,
CREATE VIEW <viewname> AS
<select statements go here>
Let’s now take each of the views separately and see how they differ.
Regular Views: They are type of views that have no physical link between the underlying tables and the view. That means, anything can be done to the underlying table without getting any error but the view might become useless if a selected column or table in the view gets changed or dropped. Data Definition Language is allowed on the tables and columns but it might lead to making the view useless. For example, Let’s look at table for employee, customer and contact. I will be referring to this table to explain all the other types of views. Lets see the script to create the three tables.
CREATE TABLE Contact(ContactID int Primary Key, Firstname Varchar(50),LastName Varchar(50), Email Varchar(50))
CREATE TABLE Employee(EmpID int Primary Key, Birthdate date NOT NULL, ContactID int Foreign Key References Contact(ContactID))
CREATE TABLE Customer(CustID int Primary key, ConactID int Foregin key References Contact(ContactID))
Ok, now let’s say we want to create a regular view that will give us all employees and their contact information. We do it like this,
CREATE VIEW v_EmployeeInfo AS
SELECT e.EmpID, e.Birthdate, c.Firstname, c.Lastname, c.Email
FROM Employee e
INNER JOIN Contact c
ON e.ContactID = c.ContactID
The view gives information required in a virtual table but creating the view doesn’t automatically output any result. The idea for the view is, the select statement is a logical definition of a table, but it doesn’t exist in the database, to see the virtual table or the result set of the view, we have to select from this virtual table, just like we do to regular tables. For example, l want to see the result set of the view, l’ll do it like this,
SELECT * FROM v_EmployeeInfo
That will give me all information returned by the view. l can also do restrictions on the result set. For example, if l want to see information for employees with ids 2, 4 and 6, l can use the view like this,
SELECT * FROM V_EmployeeInfo
WHERE EmpID IN (2,4,6)
This will display the information for only those EmpIDs. Remember the view is just like a table so you can do almost every selection with the view. You can also select only some columns from the view to show, let see an example.
SELECT firstname, lastname
FROM v_EmployeeInfo
WHERE EmployeeID = 3
I hope you know what you are getting with this result. You will get just first name and last name of the employee with ID = 3.
This examples about using the view is the same for all types of views. The main reason behind the regular view is that, the tables can be dropped, their columns dropped or changed without any error, but the view will become useless because it doesn’t have the source for its data to use in creating the virtual table. That means, the regular view allows any type of Data Definition Language without complain but it will become useless when those changes affect it. Let’s see the second type of view.
Schemabinding view: It is a type of view in which the columns and tables reference in the select statement are physically bound to the view. That means, any column referred to by the view can not be changed or dropped if the view still exists in the database. In other to drop those tables or columns referred to in the schemabinding view, the view has to be dropped first. An attempt to drop any of those objects will result in an error. The syntax is the same as regular view with the schemabinding option added. Lets create a schemanbind view that gives us customer information.
CREATE VIEW v_customerInfo WITH SCHEMABINDING AS
SELECT cu.CuID, c.Firstname, c.Lastname, c.Email
FROM Customer cu
INNER JOIN Contact c
ON e.ContactID = c.ContactID
Now, we can do all the operations on the view as we did in the regular view. The problem with this view is that, if the view is still in the database and you try to run statements like
DROP TABLE Customer
It will give you error because the Customer table is bound to the view. If you run
DROP TABLE Employee
The Employee table will be dropped because its not referred to by the schema binding view. It was referred to by the regular view which has no physical link to any object referenced. The operation will be successful but when you try to get information from the regular view, it will not give you any result, it will yell at you that the table Employee doesn’t exist. IF for example, you want to drop a column from one of the tables referred to in the schema bind view and that column is also referenced in the view, that will generate error but if the column is not referenced in the view, there will be no error. In simple term, you can’t mess with columns in tables that are referenced in a view that is schema bound, no Data Definition Languages permitted on the underlying columns or tables referenced by this type of view. The next type of view is the Indexed View.
Indexed View: It is a schemabind view which has a unique clustered index created on it causing the result set of the underlying select query of the view to be stored in the Balance Tree structure of the unique clustered index. I’ll be discussing indexes in the next tutorial but for now, just understand a unique clustered index is created on a schemabind view to form an indexed view. This is the only view that has the result set stored separately from the underlying tables from which the view is created. The indexed view has a mechanism where any updates, insertions, deletes in the base or underlying tables from which the view is created are replicated immediately in the stored data at the nodes of the Balance Tree structure. I hope you smell something bad here, from this information about indexed view given above, will you create many indexed views in a highly transactional environment? The answer is no, if you do that, there is going to be so much stress on your database so much that, it will be crying like a baby, insulting you, calling you names and just starring at you most of the time. Remember, a copy of the data from the underlying table from which the view is created is stored on the Balance tree so we are also wasting space in addition to the overhead of refreshing the Balance tree after every Data Manipulation on any table and columns used in the select query of the Indexed view. Let see the process for creating an indexed view. Lets say this view only show employees who are 18 years or older and its an indexed view.
—–First create a schemabind view
CREATE VIEW v_18yrsEmpInfo WITH SCHEMABIND AS
SELECT e.EmpID, e.Birthdate, c.Firstname, c.Lastname, c.Email
FROM Employee e
INNER JOIN Contact c
ON e.ContactID = c.ContactID
WHERE DATEDIFF(YY,e.Birthdate,GETDATE()) >= 18
— Now create a unique clustered index on this view
CREATE UNIQUE CLUSTERED INDEX idx_18yrs ON v_18yrsEmpInfo (EmpID)
The syntax for the index creation might look frustrating to you at this time but it will all be clear in next tutorial when l discuss indexes. Now that you have the indexed view, you can run all the operations we did for the other views but the indexed view will give you higher performance because the result set is stored at the Index nodes and will be quickly retrieved.Both copies of data (Indexed view and Base table) are always in sync at any given point. select statements result is precomputed and stored in the memory so it’s faster than generating resultset every time.–Drawback: In a heavy transactional system with lots of DML operations, the index on the view will need to refresh itself a lot, that will be an overhead and affect the performance of these DML operations as l said earlier.Also too many indexes will also need a lot of storage space, just repeating myself huh.Until we discuss indexes, this might just be sounding crazy to you so wait to understand indexes in the next tutorial, then come back and try to picture how the indexed view works.
The last thing I’m going to talk about is how to affect underlying tables through the view. First let me ask a question, Can we insert, update and delete record from the view? Somebody said yes, the answer is no but we can do the above mentioned DML on the base tables of the view. What this means is that, we can insert a new value into the table that the select query for the view is written on. So when you are inserting into the view, you are not actually inserting into the virtual table of the view, you are inserting the values into the table at the background of the view. The same applies to deletion and updates on the view. To wrap it up, let’s go through some general information about views;
1. They are created on top of a select statement
2. They provide security because access is given on the column levels in the view
3. Can’t use a compute by clause in a view because that clause returns multiple result sets but views can hold only a single result set.
4. Views have no execution plan stored in the database, the select statement of the view is run every single time the view is accessed.
5. DML changes on the view are directly for the base tables.
6. If a view has more than one table in its select statement such as joining tables, then doing DML changes will involve the use of triggers(will be talking about triggers later)
7. A view is a database object, stored in the database, and it reduces network traffic because the entire select statement in the view is stored in the database and only a short amount of code is sent over the network to access the view and query the database.
8. Views do not take paramters and they do not return values.
9. You can view the underlying code that created a view by using the system stored procedure SP_helptext viewname
10. To prevent people from seeing the underlying code of the view, use the encryption option, like this
CREATE VIEW <viewname> WITH ENCRYPTION AS
<any select statement>
11. You can alter views and define new select statements or change the statements using the old alter statement as
ALTER VIEW <viewname> AS
<any select statement goes here>
12. You can add multiple options to views when creating or altering the view. For example
CREATE VIEWWITH SCHEMABINDING, ENCRYPTION AS
<any select statement>
or
ALTER VIEWWITH SCHEMABINDING, ENCRYPTION AS
<any select statement>
13. To remove the encryption on the view and make the code available, just alter the view and leave out the with encryption option, same for schema binding.
14. DML operations do not affect views themselves, it goes to affect the underlying tables.
15. In schema binding view, all objects in the select query must be stated in two name convention form. That is schemaname.objectname. For example dbo.employee, dbo.contact, dbo.customer. If the objects are in default schema, dbo. Replace with the schema in which your objects are created.
16. Examples of DML statements on underlying table through views. Lets say l have the view that show all employees info in the employee table.
CREATE VIEW empinfo AS
SELECT * FROM Employee
Now l want to use the view to insert records into the Employee table
INSERT INTO empinfo VALUES (4,2,’1990-2-10′)
4 represent the EmpID, 2 for contactD, 19-2-10 represent the birthdate. I’m referring to the employee table l’ve created in the beginning of the tutorial.
I can delete an employee record like this,
DELETE FROM empinfo
WHERE Empid = 3
I can update a record in the employee table like this,
UPDATE Empinfo
SET Birthdate =’1900-04-04′
WHERE Empid = 4
17. How can we handle DML for views that refer to two or more tables in the underlying select statement. This is a little complex if you don’t understand triggers but I’m going to write the code and try to explain. If you don’t get it, please relax till l discuss triggers, then you can come back and feel happy for yourself. For now, just do your best to get along, don’t hate me lol. Let us look at this view.
CREATE VIEW EmpContact AS
SELECT e.*, c.*
FROM Employee e
INNER JOIN Contact c
ON e.ContactID = c.ContactID
This is the view that will give you all information about employees. So now, l want to insert a new record into both tables through the view. I don’t know any other way to do this without triggers at the time of writing this post, maybe there is a simpler way. Lets do it using triggers and try to understand when I discuss triggers
First, l have to create a trigger on the view for the type of DML operation, in this case, l want a trigger for insertion. So l create it like this, Syntax for trigger is
CREATE TRIGGER <triggername> ON <objectname>
<dml or ddl statement> AS
BEGIN
<perform action>
END
Now lets create our trigger on the view
CREATE TRIGGER tg_insertView ON EmpContact
INSTEAD OFF INSERT AS
BEGIN
INSERT INTO Contact
SELECT ContactID, Firstname, Lastname, Email
FROM inserted
INSERT INTO Employee
SELECT EmpID, Birthdate
FROM inserted
END
At this time, you are probably wondering what the heck is this guy doing. Well, with the instead off trigger l’ve created, when you try to insert record into the underlying tables through the view like this,
INSERT INTO EmpContact
VALUES(5,6,’1960-04-10′,6,’Vince’,’Cosby’,’info@2smart4school.com’)
What will happen is, the insert statement will not run, the trigger will be fired and it will take care of the insert operation. In this situation, when the trigger is fired, it goes to what is called the inserted table which is a magic table that holds records to be inserted in a table. Even though the insert operation was stopped by the trigger, the values to be inserted will still be residing in the inserted table, so the trigger can go in there and continue the operation in the proper way because there is no physical table matching all the information to be inserted through the view. The trigger goes in the magic table, first of all, select the columns needed for the contact table and insert it into the contact table, then select the columns for the employee table and insert them into the employee table. The order of insertion is very important because the employee table has a foreign key (contactid) that references the contactid column in the contact table. Therefore, any contactid to be inserted into the Employee table must already exist in the Contact table. That’s what is called referential integrity and it’s provided by foreign key constraint.
I know this thingy is a little tricky but hopefully, things will get clearer when l write on triggers.
18. Tip 18, really? Are we done yet? Common men, stop killing me, l can’t remember all the damn thing you are talking about, I’m about to pass out.
That was you talking lol, Ok, l get it, this is the last one. And it’s a question. If l create a view in which l use a WHERE statement to restrict the result set, can l insert a record that does not obey the WHERE restriction of the view into the base table? For example, l created a view that shows information about employees who are 18 years or older, Can l insert an employee who is less than 18 years old into the view so that the underlying table will be affected?
In the next tutorial, I’m going to discuss index and index maintenance. Suggestions, questions and feedback are welcome. Keep in touch, Peace.
