Introducing views

- Introducing Views. SQL Views are considered either a virtual table or you can look at it as a stored query. A stored T-SQL query. Which, frankly, is the same thing as a virtual table. Views are security enabled. As a matter of fact, the original idea of a View was to enhance security in relational databases so that we could become more granular. If you think of a database with 30, 40 tables, etcetera, and we have a certain group of users that we want to limit to certain tables, and maybe even within those tables certain columns, then we could create a View and apply security to that View, and then all that user can see is the information in the base table that the View exposes.

A View can and often does contain a Join from multiple tables, and it also can have aggregations. The standard sum average, etcetera. Now, Views since SQL 2000 are updatable, meaning, through the View we can do all of the CRUD statements. The R's read, obviously we can read it. But, that is, we could do UPDATE, INSERT, and DELETE and to do that we must reference a single base table.

So, we have a View that references into a table and say it shows customers, and that customer has a phone number, and through that View, the customer service reps can access it and update the information on the base table. We need direct reference of the columns. Meaning, we can't do an update if there's an aggregation involved in the column. And the column cannot be participating in a GROUP BY, a HAVING clause, or DISTINCT.

Here's the Syntax. This is just our standard stuff. USE the AdventureWorks2014 database. GO, which breaks it into a batch. The CREATE VIEW command, CREATE VIEW named in the HumanResources schema, .EmployeeHireDate; that's the name of the View. AS, now you see this AS, that's where the code's going to be. SELECT, and then you see we're selecting some columns FROM HumanResources.Employee AS e JOIN Person.Person AS p ON, and there's my PK/FK.

BusinessEntityID on p BusinessEntityID, GO. And that will create the View. Okay, I switched over to SQL Management Studio so you can see some more interesting stuff. I'm in AdventureWorks and if we expand Views you see all of the Views we looked at. Let's look at HumanResources.vEmployee. You don't have to name it that. This is a hangover from a thing called Hungarian notation. So we know this Object here is a View and it's handy for people to know that.

It's no longer that big of a deal because it's in the Views folder; you know it's a View. But anyway, here we go. HumanResources.Employee, and if I right click that and go into Design, here's my designer. So I can use a designer to come through and create that and create my View, which, some people use them. Another way is to actually look at the code, Script View as, CREATE To, New Query Editor Window. All right, so here's my View, and this is just my housekeeping. Bench work GO, SET ANSI_NULLS ON, QUOTED_IDENTIFIER ON, and now I'm dropping down, and here's all of the columns in the table HumanResources.ViewEmployee.

The error is, this already exists, the View. So, they're coming down and they're creating the columns, and here's FROM [HumanResources] and then a GO statement, the very bottom, breaking it into a batch. By the way, when you do a View it has to be the very first item in a batch and I would always separate them out. How do you know a batch? The GO breaks apart your batches. So, there's the CREATE that VIEW, now let's close it, right click, and we'll Select Top 1000. Bang, from the View, and you see the virtual table or the T-SQL that was contained within it.

As a matter of fact if we dump out this TOP Business and just re-execute the code, there we go. So, it's got 290 rows, so the Top 1000 obviously covered it for us. And we can look at another one. VendorWithContacts, let's look at that. CREATE To, New Query Editor. Again, CREATE a VIEW, a SELECT statement, FROM, and it's dropping that down. Now you see all the INNER JOINs? Actually, here I've got INNER JOINs and then I've got LEFT OUTER JOINs.

So let's look to see what that looks like. And, again, we can drop this on down. It's not going to change or anything, you've already got 156 rows, but just to show it, F5. Now, this may be interesting. Let's pick that, although we don't have to pick it. Let's look at the Estimated Execution Plan. This is a little more interesting Execution Plan. The reason this is a little more interesting is I had all those JOINs in there. So you see, here's my Scans. If you look at the size of the arrows, that is representative of how much data actually is going to flow across it.

So here's 3%, 1%, 1%, does those JOINs and it's moving on over. Here's a Hash Match. And now down here I have a Clustered Index Scan, Person PK, and that's 66% of the cost of this entire query. I move on across, again I do another JOIN. Remember, we have multiple JOINs. On over, and let me move down. See, I've got other Index Scans that I'm coming across for all these JOINs, and then I dump the information into my SELECT.

Again, here's the actual View. And, you see these JOINs coming up, and that's what you saw in that Execution Plan. When you saw the multiple Index Scans and the multiple JOINs, to make this work. By the way, an Execution Plan, once a Execution Plan happens, the estimation goes in, the Optimizer does not consider all of the possible Execution Plans, but just picks what it thinks is going to be the best one based on statistics.

Which means, you know it has to have some kind of a history. But once we have statistics, then the information is stored in what's known as the Execution Plan Cache. So, on second execution the Optimizer skips the Execution Plan and just goes and reuses it again. If the Optimizer detects that the table has had a more than a 20% change then it redoes the plan. But the best thing you can do is to keep your statistics up to date on SQL, and that's a fairly easy thing to do.

But you keep them up to date and then SQL will do an excellent job of optimizing your queries.