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!