Hi everyone! We are going to start this week off by looking at database views and how useful they are when retrieving data. I have used them a lot during my working experience as they are incredibly useful for bringing back data from multiple related tables and joining them together.

So what is a View? 

A view is a SELECT statement that has been named and stored within the database. The purpose of this is so that it can be recalled and manipulated at a later point in future SELECT statements, just like any other table would. 

The syntax for a sample view is as follows: 

What Are The Pros and Cons of Views? 

Pros

• Views can join multiple tables together and make what could be a complicated database schema simpler.
• When you don't fully trust the security of the person accessing the database, it shows exactly what you want it to show and restricts access to the underlying base tables.
• Views contain one or more SELECT statements so don't take much room to store as only the statement is stored and not the underlying data.
• Database object names can be modified using aliases in the view so that when they are output to the user, they are more user friendly.

Cons

• Views are difficult to update.
• Views have constraints so cannot be created in certain instances. 

How Can a View Be Created?

The view in the example above is incredibly simple and is rather pointless for use as the straight table can be used instead. The snippet of code below is an example of a statement that I wrote for my portfolio of work which formatted the display of a Google API Map marker pop-up: 

As you can tell, there's a lot of room for error when you type the code in - which is why I actually used the view generator as you can see exactly what you are modifying (for full size, click the image):

Now I Have Created My View, What Can I Use It For?

As you can see, the SELECT statement can be quite complicated and perform various different tasks. However it is limited and cannot do the following:

• Create a new table (permanent or temporary) by using a SELECT ... INTO statement. 
• Reference a temporary table.
• Reference any type of variable.
• Have a total number of columns greater than 1024.
• Contain a COMPUTE or COMPUTE BY clause.
• Contain an OPTION clause.
• Contain an ORDER BY clause unless the TOP operator is also used. 

Something which you will need to consider when creating views is that despite the fact that they can be updated, there are quite a number of constraints and conditions that need to be met in order for this to take place:

• The update must reference one table only.
• The column requiring the update in the view must reference the same column in a table directly.
• The update cannot be performed on a computed column from a UNION/UNION ALL, CROSS JOIN, EXCEPT or INTERSECT.
• The update cannot be performed if the column is impacted by a DISTINCT, GROUP BY or HAVING clause.
• The update cannot be performed on an aggregate column.
• The update cannot be performed if the TOP operator is used. 

As a result of these constraints, many try to avoid updating through a database view and use a trigger instead.

Views can also be partitioned or indexed in order to optimise query performance and make them faster, however you cannot create an index on a partitioned view and vice versa.

I am going to tackle triggers and indexes in later blog posts so please check back at a later date!

As an after note, if you want to remove a database view from a schema, you can simple use the DROP clause:

In this blog post, I will show how to create simple queries on the database in order to filter data. This may be required for a number of reasons, for example if you wish to produce a report showing the number of items bought by a particular customer and the details of those items.

For the purpose of this tutorial - I have used a Red Gate Tool called SQL Data Generator in order to pre-populate the database I created in the last tutorial with 1000 rows of data (who really wants to spend time typing all that in manually?!) For more information on Red Gate and for a downloadable link, please click here: 

http://www.red-gate.com/products/sql-development/sql-data-generator/

Ok, so now your database is full of data. You may decide you wish to bring the entire data set of a table back using the following: 

To run a query in SQL Server Management Studio, make sure that you click the "Execute" button:

You should then see results similar to the following (obviously it won't be identical as the data contents will vary):

However as you can probably guess although the query runs quickly as it is only bringing back 1000 

records of data, imagine how the time will increase when bringing back 100,000 records with every single column, with lots of joins into other tables and bringing back all their data too. Some databases may have millions of records! Plus is it really necessary to bring back every single column of data? In almost all cases, the answer will be no. Therefore we need to think about adding clauses and conditions.

It'll be very rarely that you will want to bring back absolutely everything from a database table without having some form of condition on it; there's just no need for it, especially not if you're creating well formatted specific reports. 

So, let's reduce the number of columns. I have decided that I only want to bring back the CustomerId, FirstName, LastName and ContactNo. The query would read: 

Don't forget to execute the query! 

You will see that the number of columns brought back will be decreased. You can bring back as many or as few results as you need. Now you have decided that you wish to filter by all the Customers who have a last name beginning with the letter S, as this will reduce the number of records brought back. The query would read: 

Again, don't forget to execute the query!

The LIKE operator allows you to match a character string found within any column in the table specified to a specific pattern using a WHERE clause. The LIKE operator has the following wildcard characters:

This can be particularly useful when searching for similar or like data, particularly for items that have similar names or for similar last names. There are different types of operator that impact on what the result set will look like, for example, IN, BETWEEN and AS. These will be discussed and used in later tutorials.

Now, we can also add an ORDER BY expression that will allow us to specify which column we wish to display in either ascending or descending order. An example of this would read:

By default, a straight ORDER BY expression will order a column in ascending order (a-z). However if you wish to reverse this sort, use DESC after declaring the column name:

And that's all there really is to it! You've just created your first database query. However, what if you need to take data related to a user found from another table? The next lesson will focus on joining information from other tables to further customise a result set.