Logo
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

SQL Server 2008 : Working with Views -Partitioned Views, Updateable Views & Indexed Views

6/29/2011 6:33:56 PM
Views are virtual tables that represent the result set of a select statement from one or more tables or other views. In most cases, that is unless a view is indexed, a view is basically a predefined query that is stored and executed whenever the view is referenced in a query.

You can use a view to limit user access to sensitive information in the underlying table. For example, you can create a view that only includes a subset of columns in a table and give the user access to the view instead of the underlying table. You can also use a WHERE clause to limit the rows of data that are returned, ensuring the user can see only specific rows.

There are a few limitations you need to be aware of when creating a view. A SELECT statement in a view cannot include any of the following:

  • A COMPUTE or COMPUTE BY clause.

  • An ORDER BY clause without also including the TOP clause. The workaround for this is to select the top 100 percent, as in the following query: SELECT TOP 100 PERCENT FROM <table> ORDER BY <columns>.

  • The INTO keyword used to create a new table.

  • The OPTION clause.

  • A reference to a temporary table or table variable.

The example in Listing 1 creates a view that shows the name and department description of all the employees in a single department. As you can see in the example, we are able to use the view to hide the employee's social security number. We are also able to provide more user-friendly column names by using an alias. The WITH ENCRYPTION option prevents the view definition from being displayed. You should be careful when using the encryption option because you will not be able to retrieve the definition from the database if you need it.

Example 1. Sample Code Used to Create an Encrypted View
USE AdventureWorks2008
GO

CREATE TABLE Employee
(EmpID int NOT NULL CONSTRAINT PK_EMP PRIMARY KEY CLUSTERED,
EmpFirstName Varchar(50),
EmpLastName Varchar(50),
EmpSSN Varchar(9),
DepartmentID int)
GO

CREATE TABLE Department
(DepartmentID int NOT NULL CONSTRAINT PK_DEPT PRIMARY KEY CLUSTERED,
DepartmentDscr Varchar(50))
GO

CREATE VIEW vMarketingEmployees
WITH ENCRYPTION
AS
SELECT dbo.Employee.EmpFirstName AS FirstName,
dbo.Employee.EmpLastName AS LastName,
dbo.Department.DepartmentDscr AS Department
FROM dbo.Department INNER JOIN
dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID
WHERE dbo.Department.DepartmentID = 1


Let's say you found out that the Marketing department is actually DepartmentID 2 instead of 1. You can use the ALTER VIEW statement shown in Listing 2 to make the changes. While making the changes, you also decide to remove the encryption option. All you have to do to remove the encryption is not to specify the option when running the ALTER VIEW statement.

Example 2. Syntax Used to Alter an Existing View
USE AdventureWorks2008
GO

ALTER VIEW vMarketingEmployees
AS
SELECT dbo.Employee.EmpFirstName AS FirstName,
dbo.Employee.EmpLastName AS LastName,
dbo.Department.DepartmentDscr AS Department
FROM dbo.Department INNER JOIN
dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID
WHERE dbo.Department.DepartmentID = 2


To remove a view from the database, all you have to do is issue the DROP VIEW statement followed by the view name, as shown in Listing 3.

Example 3. Syntax to Remove a View from the Database
DROP VIEW vMarketingEmployees

1. Partitioned Views

Distributed partitioned views are those that reference data across multiple servers and combine the data to the user as a single result set. You can use distributed partitioned views to form a federation of database servers, which are separately managed servers used to spread the processing load for a single application across multiple servers. Listing 4 shows an example of a distributed partitioned view.

NOTE

You can create a local partitioned view by referencing only tables on the same server; however, creating partitioned tables is the preferred method for locally partitioning data.

Example 4. Common Syntax Used in a Distributed Partitioned View
CREATE VIEW vDistributedSample
AS
SELECT col1, col2, col3 FROM Server1.DBName.dbo.TableName
UNION ALL
SELECT col1, col2, col3 FROM Server2. DBName.dbo.TableName
UNION ALL
SELECT col1, col2, col3 FROM Server3.DBName.dbo.TableName

2. Updateable Views

You can use a view to insert, update, and delete data in the underlying tables as long as certain conditions are met:

  • All the columns being modified must be in the same base table.

  • The columns must also directly reference the base table; you cannot modify computed columns or columns that are derived from or affected by aggregate functions.

  • If the WITH CHECK option is specified, as shown in Listing 5, the view cannot be updated in any way that would cause the updated record to disappear from the result set.

For example, given the view created in Listing 5, you could not run an UPDATE statement to set the DepartmentID = 2.

Example 5. Syntax to Create an Updatable View Using the WITH CHECK Option
USE AdventureWorks2008
GO

--Drop the view if it currently exists
IF OBJECT_ID('dbo.vMarketingEmployees', 'V') IS NOT NULL
DROP VIEW dbo.vMarketingEmployees;

GO

--Create a view using the WITH CHECK option
CREATE VIEW vMarketingEmployees
AS
SELECT dbo.Employee.EmpFirstName AS FirstName,
dbo.Employee.EmpLastName AS LastName,
dbo.Department.DepartmentID,
dbo.Department.DepartmentDscr AS Department
FROM dbo.Department INNER JOIN
dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID
WHERE dbo.Department.DepartmentID = 1
WITH CHECK OPTION


If you look at Figure 1, you can see that trying to change DepartmentID fails, since it will violate the CHECK OPTION constraint, but changing Department is successful.

Figure 1. Results of updating a view that uses the WITH CHECK option

3. Indexed Views

If you have a process-intensive view that is run often, you can improve performance and reduce the processing time needed to execute the view by creating an index on the view. The first index you create on a view must be a unique clustered index, which causes the result set of the view to be stored in the database. Thereafter, you can create nonclustered indexes.

Listing 6 shows an example of creating an indexed view. Notice the WITH SCHEMABINDING clause in the CREATE VIEW statement. If you plan to create an index on a view, then you must first create the view using that clause, which prevents any changes to the underlying tables referenced by the view that would affect the view definition. An indexed view is essentially a stored result set returned by the view, so SQL Server does not have to process the query each time the view is referenced. By creating the view WITH SCHEMABINDING, SQL Server can ensure that no underlying changes to the data will invalidate the stored results.

There are several other requirements that must be met in order to create an indexed view. For the specific requirements, refer to the topic "Creating Indexed Views" in SQL Server Books Online.

Example 6. Syntax to Create an Indexed View
USE AdventureWorks2008
GO

CREATE VIEW vEmployees
WITH SCHEMABINDING
AS
SELECT dbo.Employee.EmpFirstName AS FirstName,
dbo.Employee.EmpLastName AS LastName,
dbo.Department.DepartmentID,
dbo.Department.DepartmentDscr AS Department
FROM dbo.Department INNER JOIN
dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID
GO
--Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_vEmployee_Dept
ON vEmployees (DepartmentID);
GO


Indexed views are best suited for situations where the underlying data is rarely updated; because as the data is updated in the base tables, the data must also be updated in the indexed view to reflect the changes. If the data is frequently updated, maintaining the index could actually lead to performance issues instead of performance gains. There are many caveats to using indexed views that could lead to an administrative headache, so you shouldn't go around creating indexes on all your views. However, there are certain situations, especially in a data warehouse environment, where indexed views can provide an enormous performance benefit.

You can create an indexed view in any edition of SQL Server 2008, but you receive some extra benefits when you are running the Enterprise Edition. In the Enterprise Edition, the query optimizer can automatically take advantage of an index created on a view, even if the view is not specifically referenced in a query.

Other -----------------
- SQL Server 2008 : Post-Installation - Preproduction Tasks
- Microsoft PowerPoint 2010 : Expanding PowerPoint Functionality - Simplifying Tasks with Macros
- Microsoft PowerPoint 2010 : Enhancing a Presentation with VBA & Setting Developer Options
- Windows Server 2008 R2 : Manage Disk Storage - Manage Disk Storage Quotas
- Windows Server 2008 R2 : Work with RAID Volumes - Understand RAID Levels & Implement RAID
- Exchange Server 2010 : Perform Essential Public Folder Management (part 3) - Configure Client Connectivity
- Exchange Server 2010 : Perform Essential Public Folder Management (part 2) - Define Public Folder Permissions
- Exchange Server 2010 : Perform Essential Public Folder Management (part 1) - Manage Public Folder Content
- Microsoft Dynamics CRM 2011 : Using Microsoft Dynamics CRM for Outlook - Configuring Synchronization Filters
- Microsoft Dynamics CRM 2011 : Going Offline with Microsoft Dynamics CRM for Outlook
- Microsoft Dynamics CRM 2011 : Deleting Records in Microsoft Dynamics CRM for Outlook
- SharePoint 2010 : Securing a Web Application (part 2) - Managing Web Part Security & Self-Service Site Creation
- SharePoint 2010 : Securing a Web Application (part 1) - How Zones, Web Applications, and Security Work Together to Provide Secure Solutions
- BizTalk 2009 : WCF LOB Adapter SDK (part 6)
- BizTalk 2009 : WCF LOB Adapter SDK (part 5) - Implementing the Message Exchange Handlers
- BizTalk 2009 : WCF LOB Adapter SDK (part 4) - Implementing the Metadata Handlers
- BizTalk 2009 : WCF LOB Adapter SDK (part 3) - Implementing the Connection
- BizTalk 2009 : WCF LOB Adapter SDK (part 2) - UI Logical Grouping
- BizTalk 2009 : WCF LOB Adapter SDK (part 1) - Generating Adapter Skeleton Code
- SQL Server 2008 : Configuring the Instance (part 3)
 
 
Most view of day
- System Center Configuration Manager 2007 : ConfigMgr Classic Reports Versus SQL Reporting Services
- Microsoft Visio 2010 : Sharing and Publishing Diagrams - Publishing Visio Drawings to SharePoint 2010 Visio Services
- Extending the Real-Time Communications Functionality of Exchange Server 2007 : Exploring Office Communications Server Tools and Concepts
- Advanced Windows 7 Programming : Working in the Background - DEVELOPING TRIGGER-START SERVICES (part 6)
- Workflow in Dynamics AX 2009 : Workflow Life Cycle (part 3) - Activating the Workflow
- BizTalk Server 2009 : Editing and Resubmitting Suspended Messages (part 2) - Pseudo-Walkthrough to Perform Edits and Resubmits
- Windows Server 2012 : File Services and Storage - Configuring iSCSI storage (part 1) - Understanding iSCSI storage
- Windows Phone 8 : Configuring Basic Device Settings - Passwords and Screen Timeouts (part 1) - Setting the Screen Timeout
- Securing the Workstation : Applying the Castle Defense System (part 6) - Working with external access - Working with the Windows Firewall with Advanced Security
- SQL server 2008 R2 : Reverting to a Database Snapshot for Recovery
Top 10
- Microsoft Project 2010 : Linking Tasks (part 8) - Auditing Task Links,Using the Task Inspector
- Microsoft Project 2010 : Linking Tasks (part 7) - Creating Links by Using the Mouse,Working with Automatic Linking Options
- Microsoft Project 2010 : Linking Tasks (part 6) - Creating Links by Using the Entry Table
- Microsoft Project 2010 : Linking Tasks (part 5) - Creating Links by Using the Task Information Dialog Box
- Microsoft Project 2010 : Linking Tasks (part 4) - Entering Leads and Lags, Creating Links by Using the Menu or Toolbar
- Microsoft Project 2010 : Linking Tasks (part 3) - Using the Start-to-Start Relationship,Using the Finish-to-Finish Relationship
- Microsoft Project 2010 : Linking Tasks (part 2) - Using the Start-to-Start Relationship,Using the Finish-to-Finish Relationship
- Microsoft Project 2010 : Linking Tasks (part 1) - Defining Dependency Links
- Microsoft Project 2010 : Defining Task Logic - Manipulating Your Schedule
- Microsoft Lync Server 2013 : Director Troubleshooting (part 3) - Synthetic Transactions,Telnet
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro