Skip to content

Tips and Tricks #6 – How To Use SQL Views

April 28, 2010

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.

sql-view-how-to

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.

From → SQL

6 Comments
  1. Jeanne permalink

    This was very helpful as a start.
    I followed your directions, but every script I copied had an error message similar to this one that I got when verifying the [tspvActiveChartofAccounts] script.

    Msg 102, Level 15, State 1, Procedure tspvActiveChartofAccounts, Line 5
    Incorrect syntax near ‘‘’.

  2. Jeanne,

    Can you paste the exact script you copied on here so I can take a look? There is a ‘ out of place somewhere. I test all these scripts before I post them (I actually use everyone that I post), so I am not sure where the ‘ came from. Reply with the script and let me take a look.

    Ron

  3. Michael permalink

    Any suggestions as how to be able to have access to SQL Management Studio to use your views, but not breach SOX compliance?

    I have been given a super-user role in GP, but not an sa role. We have a third-party handling our SQL databases, and a partner handling GP. I work as Accounting Manager for the client. Both parties have taken a stand that if they give me access to the Studio, I will have the ‘keys to the castle’, and that copying the view scripts by me in to the Studio would breach SOX compliance.

    Isn’t there a way to lock down the Studio to allow only scripts placed by me in to the Studio to be view data only?

    Thank you for any suggestions you might have.

    Cheers,
    Michael

    • Ron permalink

      Michael,

      While I am familiar with Sarbanes Oxley I am not sure the rule on this. I would ask them exactly what you can and cannot do and then try to find a way to live within those parameters. What I mean is that if you can’t have access to create the views yourself then ask them to create them for you. Locking down SSMS, while not a pain, is quite a chore and I can see why they would be hesitant to try and do that for you.

      Can you ask them what your options are, let me know, and let’s go from there?

      Ron

      • Michael permalink

        Thanks, Ron.

        Our GP partner has agreed to create the views for me.

        A special thanks to you for the quick response.

        Cheers,
        Michael

Trackbacks & Pingbacks

  1. How to Use SQL Views - DynamicAccounting.net

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: