Tips and Tricks #6 – How To Use SQL Views

So it occurred to me the other day that I am posting all of these SQL views and assuming that people know what to do with them.  That may or may not be the case, so I wanted to write up a little How To on SQL views.

What is a view

- As always, I like to go to w3schools.com to learn about different SQL syntax and they have a great tutorial on SQL views.  From the tutorial, a SQL view is:

“In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.”

I would like to expand on that by saying, think of a SQL view as a saved SQL SELECT statement on the server.  Instead of have to write all the joins and where clauses every time, you just SELECT * FROM <SQL VIEW> just as if it were any other table in the database, and it will execute the “saved” query that you made into a view.

How To Use

- So, I post quite a view SQL views on this blog.  How in the world are you supposed to use them?  It is really simple…first you need to create the view on your database:

1. Copy the view from the post

2. Fire up SQL Server Management Studio, and login

3. Click New Query

4. Paste the contents of the view that you copied in to the window

5. Change the USE [TSP] to USE [xxx] (replace xxx with the database name you want to create the view in

6. Change the name of the view to whatever you want it to be called

7. Click Execute

Your view will now be created and ready to use.

After you have created the view, then all you need to do is include the view in your select statement, just as if it were a table in your database.

In the above example we created the view tspvItemMaster.  To select that view in a query you would use the following statement:

SELECT * FROM tspvItemMaster

I hope this helps you understand how to use the views that I post on the blog.  Let me know in the comments if you have any questions.