SQL 201 – Views

Previously I showed you how to create tables.  The normal goal for designing a table is to store data in such a way that each table contains one group of facts that are highly related to each other.  For example, if you had built a contact database, you’d have one table for people, one for addresses, one for phone numbers.  Each table would relate to each other through a series of primary to foreign keys.

But you will regularly get requests to report on data in such a way that you show duplicate data.  You may be asked to show data in a summarized format, yet you only store detail data.  You could create a T-SQL statement and send it off to them, but there is a better way.  You can create a VIEW.

A VIEW is a virtual table based on one or more tables you already have defined in your database.  Since it’s a virtual table, you can do some pretty cool stuff with it.  You can add indexes, triggers, etc.  But that’s getting ahead of our lesson.  Let’s start with the basics.

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

A view_name can be any name that would be acceptable as a table name. I usually start my view_name with “v_” so I remind myself that it’s a view when I call it later. Everything after the AS is a standard SELECT statement, you can include GROUP BY, but you can’t include ORDER BY. You can also include HAVING if you want. There are some additional things you’ll learn about views as you create and work with them. For now, we’ll stick with very simple views. I leave it to you to grow from there.

Let’s say you have a table products, and it lists an id, the name, and a quantityOnHand column. If you wanted to show that, you’d want to create a VIEW that queries the table looking for records with quantityOnHand greater than 0.

CREATE VIEW v_ProductsInStock
AS
   SELECT
        id
      , name
   FROM products
   WHERE
      quantityOnHand > 0

After you run this query, you would then be able to use that query.

SELECT * FROM v_ProductsInStock

This would show you all products you have in stock (quantityOnHand > 0).  The great thing about views is they are always up to date.  Each time you query them, they go back to the source tables and get the latest data.

If you need to make a change to the VIEW, you can use the following query.  But remember, the old version of your VIEW will be lost, so make sure you have the old version in source control!

ALTER VIEW view_name
AS
   <updated T-SQL>

And finally, if you no longer need your view you can delete it, but remember,  once you do this, it’s gone!  Again, make sure you have the previous version saved in source control!

DROP VIEW view_name

That’s it!  The basics of views.  If you have any questions, please send them in!  I look forward to answering any questions you’ve got!

Tags: , , ,

Trackbacks/Pingbacks

  1. SQL 102 – CLUSTERED vs. NONCLUSTERED Indexes | Shannon Lowder - 20110209

    […] Previously, I covered the basics of creating an index using CREATE INDEX.  Today, I’m going to dig a little deeper.  I’d like to teach you the difference in an CLUSTERED versus a NONCLUSTERED index.  If you learn nothing else, know that a CLUSTERED INDEX physically orders the table, a NONCLUSTERED INDEX creates another “table” and orders that, each row of that “table” will point back to a record in the table you’re indexing. […]

  2. Microsoft Exam 70-433 | Shannon Lowder - 20110412

    […] Create and alter views. This objective may include but is not limited to: WITH ENCRYPTION; WITH SCHEMABINDING; WITH CHECK OPTION; manage permissions (GRANT, DENY, REVOKE) […]

  3. SQL201-The Syllabus | Shannon Lowder - 20110607

    […] Views –It’s like a table… but not […]

  4. Studying for the 70-457 | Shannon Lowder - 20130121

    […] Design views. […]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.