Chapter 9 lesson 1 Views

Chapter 9 Lesson 1 deals with views.  Creating and altering views along with rules associated with views.  A couple of the rules that should be known:

• Technically you can only have one SELECT statement in a view, but you can work around this by using a UNION clause
• The CREATE VIEW statements must be the first statement in a batch
• You can set the columns name of a view at the beginning of the statement instead of during the SELECT statement which looks like this:

CREATE VIEW Sales.salesrep (yearstarted, amountsold) WITH SCHEMABINDING
AS
SELECT
YEAR(A.hiredate),
SUM(B.sales)
FROM hr.employees AS A
  JOIN sales.metrics AS B
    ON a.empid = b.empid
GROUP BY YEAR(hiredate)

Instead of
CREATE VIEW Sales.salesrep WITH SCHEMABINDING
AS
SELECT
YEAR(A.hiredate) AS yearstarted,
SUM(B.sales) AS amountsold
FROM hr.employees AS A
  JOIN sales.metrics AS B
    ON a.empid = b.empid
GROUP BY YEAR(hiredate)

Small difference but worthing writing out and taking note of.

There are a few options when creating a VIEW as well.  These options can help with data integrity, and keeping things clean (a very technical way of putting it right?).

1. Option one is WITH SCHEMABINDING binds the view to the tables that it refers to.  "The view cannot have its schema definitions changed unless the view is dropped" according to the training kit
2. WITH ENCRYPTION makes it more difficult for users to see the SELECT text in the view
3. WITH VIEW_METADATA returns the metadata of the view which could be a part of the DB-Library, or API.. instead of the base tables metadata.
4. WITH CHECK OPTION is a filter that makes sure that when updating a view it restricts modifications that don't meet criteria that you set in the WHERE clause. 

I realized that I didn't really define what a view was… A view is an object that is similar to a table.  It holds data that is referenced to a base table.

To create view you write: CREATE VIEW abc.xyz WITH SCHEMABINDING AS.. (with schema binding optional)

To alter a view you write: ALTER VIEW abc.xyz WITH SCHEMABINDING AS… (with schema binding optional)

To drop a view you write: DROP VIEW abc.xyz;

Before creating a new view in place of an old one you should start with:

IF OBJECT_ID('abc.xyz', N'V') IS NOT NULL DROP VIEW abc.xyz

GO

CREATE VIEW abc.xyz…

Lastly, here is a way to check what views you currently have in your database:

USE (some DB)

GO

SELECT name

FROM sys.views;

That's all I've got for now…. more on Inline Functions later!!!

Cheers!

credit: querying microsoft sql server 2012; Itzik Ben-gan, Dejan Sarka, Ron Talmage