Queries, Cursors, Views  «Prev  Next»
Lesson 3 Queries, Cursors, and Views Requirements
Objective Learn the requirements to take this course.

Platform support


You can take this course on Windows, Macintosh, or Unix platforms.

Software

It is optional for you to have a copy of SQL Server 2016. If you do not currently own SQL Server 2016, you can order have an evaluation copy from Microsoft.
SQL Server requires either Windows or Windows Server as an operating system.
In the next lesson, the course resources will be discussed.

Making your view look like a Table with VIEW_METADATA

This option has the effect of making your view look very much like an actual table to DB-LIB, ODBC, and OLE DB clients. Without this option, the metadata passed back to the client API is that of the base table(s) that your view relies on. Providing this metadata information is required to allow for any client-side cursors (cursors your client application manages) to be updatable. Note that, if you want to support such cursors, you are also going to need to use an INSTEAD OF trigger.
alter view dbo.sales(vi)
with view_metadata
as
   select o.[SalesOrderID] 
   from [Sales].[SalesOrderHeader] o 

   select * from dbo.sales

INDEXED (MATERIALIZED) VIEWS

When a view is referred to, the logic in the query that makes up the view is essentially incorporated into the calling query.
Unfortunately, this means that the calling query just gets that much more complex. The additional overhead of determining the impact of the view (and what data it represents) on the fly can actually become very high. What is more, you are often including additional joins into your query in the form of the tables that are joined in the view.
Indexed views give you a way of taking care of some of this impact before the query is run. An indexed view is essentially a view that has a set of unique values materialized into the form of a clustered index. The advantage of this is that it provides a very quick lookup in terms of pulling the information behind a view together. After the first index (which must be a clustered index against a unique set of values), SQL Server can also build additional indexes on the view using the cluster key from the first index as a reference point. That said, nothing comes for free and there are some restrictions about when you can and cannot build indexes on views.