Views and its types in SQL Server 2008

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.

SQL Server 2008 organizes user-defined views into three major categories:

1. Standard Views: Most of the time, we create this kind of view where we use single or more that one tables, use JOINs and other clauses and pull the data out. The tables are saved on a single physical file, most of the time on primary data file – that is .mdf file. All tables don’t have any kind of partitions – I’ll discuss partitions in Partitions Views section below. So, most of views that we write come under this category.                                                                                                                               
2. Indexed Views: Usually views pull the data out from underlying tables and utilize indexes created on table columns. However, an indexed view allows us to create a unique clustered index on it – allowing us to physically keep a separate copy of sorted data based on which column we created a unique clusetered index. This dramatically improves the view’s performance for some types of queries. An indexed view works best for queries that are giving summary data. However, this type of view is not recommended in case there is high frequencyof data insert, update or delete on underlying table(s).
3. Partitioned Views:This type of view joins horizontally partitioned data from a set of members table across one or more servers. We partition a table horizontally and keep data partitions either on one server or on multiple servers (federated servers) in order to gain performance. We may sub-categorize this category into two:
• Local Partitioned Views: This joins required tables on the same instance of SQL Server ie. same SQL Server engine.
• Distributed Partitioned Views: This joins required tables across servers i.e. multiple SQL Server engines.

Standard Views:

In SQL Server a view represents a virtual table. You can say it is a subset of a table. Just like a real table, a view consists of rows with columns, and we can retrieve data from a view (sometimes even update data in a view). The fields in the view's virtual table are the fields of one or more real tables in the database.

Creating a view, by example

The following is an example of a simple SQL Server view using the Student table. Note: You may have to update some of the rows to add data to see the effects of this view.

IF ( OBJECT_ID('dbo.vw_students1') IS NOT NULL ) 
   DROP VIEW dbo.vw_students1 
GO

CREATE VIEW dbo.vw_students1 
AS
  SELECT 
    lastname    , 
    firstname   , 
    (firstname + ' ' + lastname) as "Fullname_fl",
    (lastname + ', ' + firstname) as "Fullname_lf",
    birth_dttm,  
    DATEDIFF(yy, birth_dttm, GETDATE()) - 
    CASE 
       WHEN MONTH(birth_dttm) > MONTH(GETDATE()) 
            OR 
            (MONTH(birth_dttm) = MONTH(GETDATE()) 
             AND DAY(birth_dttm) > DAY(GETDATE())
            ) 
    THEN 1 ELSE 0 
    END as "Age"  
    FROM   Students        
GO  

In general, you should adopt some naming standard for your views. This standard is vw_<name of view>. The name should be somewhat reflective of the purpose of the view. You can clearly see that I did not do such a good job with this. Sometimes coming up with a short descriptive name for your view is easier said than done.

The syntax for creating a view is...

CREATE OR REPLACE VIEW `<your_view_name>` 

AS

...followed by a normal SQL SELECT. This SELECT can include a WHERE clause or anything else for that matter that can be put into a SELECT statement. The scenarios are endless. It really depends on the purpose of the view.

As you can see in our view we are formatting the first and last name. This is a pretty common thing to do, By having a view that already does this we save having to write that function in every query where this is a requirement. You can also see that we have take the birth date column and calculated age.

Executing a View

Execute an SQL View

The example below shows all of the code from the view. You could also do a SELECT *, or further restrict the columns you want to see. You can also add additional row restriction to the view as we have done.

SELECT  TOP 100
        lastname     , 
        firstname    , 
        Fullname_fl  , 
        Fullname_lf  , 
        birth_dttm   , 
        Age         
FROM    dbo.vw_students1 (NOLOCK) 
WHERE Age is not null
GO  

Indexed View

On the surface, an indexed view is the same thing as a normal view, however the critical difference is that an indexed view actually allows you to create a clustered index on it, effectively working around the "one clustered index per table" limitation. The downside to this is that the indexed view is a full copy of the data from the underlying tables, so for space considerations you will need to take this into account when using them.

Let's look first at how to create a normal view, so that we have something to compare to as we create an indexed view

create view ContactsView as 
( 
      select [Users].[FirstName], [Users].[LastName], [Contacts].* 
        from [Users] 
       inner join [Contacts] on [Contacts].[UserID] = [Users].[UserId] 
);

What this will do is create a pretty flexible view that will only pull the first and last name of the user from the users table, and all of the columns from the Contacts table and automatically pick up any new columns that are added to the Contacts table, with no further modifications.  Indexed views require a more rigid definition of what they will return.  For this reason, we cannot use a wildcard (*) in the underlying query.  Each column must be individually called out; also, each table named in the query must be in 2 part dot notation, referencing both the schema of the table and the table name itself, and the view must be declared with schemabinding enabled for it.





 Below is the modified query to create a view capable of being an indexed view:


create view [dbo].[OrganizationContactsView] with schemabinding as 
( 
      select [FirstName], [LastName], [Contacts].[UserID],[Contacts].[ContactDescription], [Contacts].[IsPublic] 
        from [dbo].[Contacts] 
       inner join [dbo].[Users] on [Contacts].[UserID] = [Users].[UserId] 
 );

 Now, we need to create the clustered index on it.  Clustered indexes on a view must be unique, otherwise you will not be able to create one; nonclustered indexes will work fine.  For the present example, a UserId will only be in the contacts table once per Organization, so we can make a unique clustered index spanning the OrgId and UserId columns, like this:




create unique clustered index IX_OrganizationContactsView_OrgId on[OrganizationContactsView](OrgId,UserId)




Partitioned views




Partitioned views are used to access data that has been horizontally split, or partitioned, across multiple tables. These tables can be in the same or different databases—or even spread across multiple servers. Partitioning of tables is done to spread the I/O and processing load of large tables across multiple disks or servers.




You combine the tables in a partitioned view by using a UNION ALL statement that causes the data from the separate tables to appear as if they were one table. These separate tables are referred to as member tables or base tables. The member tables in a SELECT statement of the view must all be structured in the same way, and the view must adhere to the following restrictions:


All the columns from the member tables should be included in the view definition.


Columns with the same ordinal position in the SELECT list should have the same data type.


The same column cannot be used multiple times in the SELECT list.


A partitioning column that segments the data must be identified and needs to have the same ordinal position across all the member table SELECT statements.


The partitioning column cannot be a computed column, an identity, a default, or a time stamp.


The data values in the partitioning column cannot overlap in the underlying tables.


The partitioning column must be part of the primary key of the member table.


The member tables in the partitioned view need a CHECK constraint on the partitioning column.


A table can appear only once as part of the UNION ALL statement.


The member tables cannot have indexes created on computed columns in the table.


The number of columns in the member table primary key constraints should be the same.


All member tables should have the same ANSI PADDING setting when created.



The list of restrictions for creating partitioned views is extensive, but the creation of a partitioned view is relatively straightforward and intuitive. Consider, for example, the Sales.SalesOrderHeader table in the Adventureworks2008database. This table is relatively small, but it is the type of table that could have a large number of rows and experience heavy utilization. To balance the workload against this table, you could use a partitioned view that utilizes base tables that each contain a separate year’s data. Listing 1 shows the CREATE TABLE statements to create the base tables for each year. The yearly tables are intended to hold summarized daily numbers, and each contains only a subset of the columns in the Sales.SalesOrderHeader table.


Listing 1. Creating the Base Tables for a Partitioned View

CREATE TABLE Sales.Sales_2001
(
    OrderDay datetime NOT NULL
        CHECK (OrderDay BETWEEN '20010101' AND '20011231'),
    SubTotal money NOT NULL ,
    TaxAmt money not null,
    Freight money not null,
 CONSTRAINT PK_Sales_2001_OrderDay PRIMARY KEY CLUSTERED (OrderDay ASC)
)

CREATE TABLE Sales.Sales_2002
(
    OrderDay datetime NOT NULL,
        CHECK (OrderDay BETWEEN '20020101' AND '20021231'),
    SubTotal money NOT NULL ,
    TaxAmt money not null,
    Freight money not null,
 CONSTRAINT PK_Sales_2002_OrderDay PRIMARY KEY CLUSTERED (OrderDay ASC)
)

CREATE TABLE Sales.Sales_2003
(
    OrderDay datetime NOT NULL
        CHECK (OrderDay BETWEEN '20030101' AND '20031231'),
    SubTotal money NOT NULL ,
    TaxAmt money not null,
    Freight money not null,
 CONSTRAINT PK_Sales_2003_OrderDay PRIMARY KEY CLUSTERED (OrderDay ASC)
)

CREATE TABLE Sales.Sales_2004
(
    OrderDay datetime NOT NULL
        CHECK (OrderDay BETWEEN '20040101' AND '20041231'),
    SubTotal money NOT NULL ,
    TaxAmt money not null,
    Freight money not null,
 CONSTRAINT PK_Sales_2004_OrderDay PRIMARY KEY CLUSTERED (OrderDay ASC)
)

Notice that each table has a primary key on OrderDay, the partitioning column. Also notice that a CHECK constraint is defined for each table; it ensures that only orders for the given year can be stored in the table.



To demonstrate the power of a partitioned view, it is best to populate the base tables that will be used by the view.Listing 2 contains a series of INSERT statements that select from the Sales.SalesOrderHeader table and populate the base tables. The SELECT statements summarize several key columns by day and contain a WHERE clause that limits the result to orders for the respective years.


Listing 2. Populating the Base Tables for a Partitioned View
INSERT Sales.Sales_2001
   SELECT CONVERT(VARCHAR(8),OrderDate,112),
      SUM(SubTotal), SUM(TaxAmt), SUM(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate between '20010101' AND '20011231'
    GROUP BY CONVERT(VARCHAR(8),OrderDate,112)
INSERT Sales.Sales_2002
   SELECT CONVERT(VARCHAR(8),OrderDate,112),
      SUM(SubTotal), SUM(TaxAmt), SUM(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate between '20020102' AND '20021231'
    GROUP BY CONVERT(VARCHAR(8),OrderDate,112)

INSERT Sales.Sales_2003
   SELECT CONVERT(VARCHAR(8),OrderDate,112),
      SUM(SubTotal), SUM(TaxAmt), SUM(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate between '20030101' AND '20031231'
    GROUP BY CONVERT(VARCHAR(8),OrderDate,112)

INSERT Sales.Sales_2004
   SELECT CONVERT(VARCHAR(8),OrderDate,112),
      SUM(SubTotal), SUM(TaxAmt), SUM(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate between '20040102' AND '20041231'
    GROUP BY CONVERT(VARCHAR(8),OrderDate,112)

Now that you have the populated base table, you can create a partitioned view and ensure that the view is selecting only from the base tables that it needs.

Two types of partitioned views are discussed in this article: local and distributed. A local partitioned view utilizes base tables found on the same server. A distributed partitioned view contains at least one base table that resides on a different (remote) server. The focus in the section is on local partitioned views. The T-SQL for creating a local partitioned view named Sales.vw_Sales_Daily is shown in Listing 3.


Listing 3. Creating a Local Partitioned View
Create View Sales.vw_Sales_Daily
 as
      SELECT * FROM Sales.Sales_2001
       UNION ALL
      SELECT * FROM Sales.Sales_2002
       UNION ALL
      SELECT * FROM Sales.Sales_2003
       UNION ALL
      SELECT * FROM Sales.Sales_2004


The best way to validate that a partitioned view is working properly is to run a conditional SELECT against the view and display the execution plan. If the partitioned view is functioning properly, it should be accessing only the base tables it needs to satisfy the SELECT and should not access all the tables in the view unless it needs to. The following example shows a sample SELECT against the new partitioned view:




SELECT * FROM Sales.vw_Sales_Daily
 WHERE OrderDay > '20040701'
   and SubTotal > 2000




If you execute this statement and review the actual execution plan, you see that an index seek is performed against the Sales.Sales_2004 table. This is the correct result, given that the SELECT statement is targeting order data from 2004.


Modifying Data Through a Partitioned View


You can modify data via a partitioned view if the SQL statement performing the modification meets certain conditions, as described here:


All columns in the partitioned view must be specified in the INSERT statement. Columns that include a DEFAULT constraint or allow nulls are also subject to this requirement.


The DEFAULT keyword cannot be used on inserts to partitioned views or on updates to partitioned views.


UPDATE statements cannot modify PRIMARY KEY columns if the member tables have text, ntext, or image columns.


Inserts and updates to a partitioned view are not allowed if the view contains a time stamp.


Identity columns in a partitioned view cannot be modified by an INSERT or UPDATE statement.


INSERT, UPDATE, and DELETE statements are not allowed against a partitioned view if there is a self-join with the same view or with any of the member tables in the statement.




Note

Data can be modified through partitioned views only in the Enterprise and Developer Editions of SQL Server 2008.


In addition to the conditions shown in this list, you must also satisfy any restrictions that apply to the member tables. Check constraints, foreign key constraints, and any other table-level restrictions must be accounted for in the modification statement. The user executing the modification against the partitioned view must have the appropriate INSERT, UPDATE, or DELETE permissions on the member tables for the update to succeed.


Distributed Partitioned Views


Microsoft provides distributed partitioned views (DPVs) as a primary means to scale out a database server. Scalability allows an application or a database to utilize additional resources, which allows it to perform more work. There are two kinds of scalability: scaleup and scaleout. A scaleup solution focuses on a single server scaled to provide more processing power than its predecessor. An example of scaleup would be migrating from a server with a single dual-core processor to a machine with 4-quad-core processor. Scaleout solutions include the addition of servers to augment the overall processing power.

DPVs are similar to local partitioned views, but they utilize one or more tables located on a remote server. The placement of partitioned data on remote servers allows the processing power of more than one server to be utilized. The partitioning is intended to be transparent to the application and allow for additional partitions and servers as the application’s needs scale.

The following list outlines the basic requirements for creating a DPV:


A linked server definition is added to each member server that will contain the partitioned data. The linked server contains the connection information required to run distributed queries on another member server.


The lazy schema validation option is set to true on each of the member servers, using sp_serveroption. This option is set for performance reasons and allows the query processor to skip schema checking of remote tables if the query can be satisfied on a single member server.


A DPV is created on each member server. This DPV references the local tables in addition to the tables found on the other member servers.



Listing 3 shows SQL commands that can be used to satisfy the requirements in the preceding list. The DPV created in the last portion of the script is similar to the local partitioned view created in the previous section. The key difference in this DPV example is the inclusion of a distributed query that retrieves records for Sales.Sales_2002from a remote server. The remote server in this example is named DbSvrXP.


Listing 3. Creating a Distributed Partitioned View
Exec sp_addlinkedserver @server='dbsvrxp',
      @srvproduct='',
      @provider='MSDASQL',
      @provstr='DRIVER={SQL Server};
SERVER=dbsvrxp;UID=linklogin;PWD=pw;Initial Catalog=Adventureworks2008'
—Set the server option for improved DPV performance
exec sp_serveroption dbsvrxp, 'lazy schema validation', true

Create View Sales.vw_Sales_Daily
 as
      SELECT * FROM Sales.Sales_2001
       UNION ALL
      SELECT * FROM dbsvrxp.Adventureworks2008.Sales.Sales_2002
       UNION ALL
      SELECT * FROM Sales.Sales_2003
       UNION ALL
      SELECT * FROM Sales.Sales_2004


The DPV created in Listing 3 contains only one remote table. The example could be further expanded to have each table in the UNION clause on a different remote server. Keep in mind that the DPV CREATE statement needs to be adjusted when run on the remote server(s). The tables that are local on one server are now remote on the other server, and those that are remote can now be local.

If the DPVs are properly defined, SQL Server 2008 attempts to optimize their performance by minimizing the amount of data transferred between member servers. The query processor retrieves the CHECK constraint definitions from each member table. This allows the query processor to map the specified search arguments to the appropriate table(s). The query execution plan then accesses only the necessary tables and retrieves only the remote rows needed to complete the SQL statement.

Data can be modified through a DPV as well. Updatable DPVs, which were introduced in SQL Server 2000, are still available in SQL Server 2008. Data modifications are performed against a view, allowing true transparency. The view is accessed as if it were a base table, and the user or application is unaware of the actual location of the data. If it is configured properly, SQL Server determines via the WHERE clause specified in the update query which partition defined in the view must be updated rather than updating all tables in the join.


Note

Data can be modified through distributed partitioned views only in the Enterprise and Developer Editions of SQL Server 2008