Microsoft SQL Server 2008 Product Overview

Data Platform Vision

Organizations are seeing a confluence of factors meeting to create a data storage explosion. The first amazing fact is the sheer amount of data that is being created in the world. If you look at the data stored digitally on a global basis, we were at 12 exabytes by 1999. By the middle of 2002, the second dozen exabytes was created.

If you look at the types of data being generated you can see that magnetic digital plays a huge role in storage, and will continue to increase in size relative to other formats. Where the real storage explosion happens is in the storage of digital assets: pictures, movies, and more. These formats are increasing the demand for storing structured and unstructured data types.

The cost of storage is rapidly decreas

ing, enabling this storage explosion to continue. In 1980, the world's first gigabyte-capacity disk drive, the IBM 3380, had a price tag of $40,000 USD. Today, the cheapest 500‑GB (gigabyte) hard disk drive is offered for $0.38 USD.

For organizations to be successful and thrive in this world of data, they need a vision that

addresses these key data trends. The Microsoft data platform vision helps organizations meet these data explosion needs and the next generation of data-driven applications. Microsoft will continue to invest in and evolve the following key areas to support their data platform vision:

mission-critical enterprise data platform, dynamic development, relational data, and business intelligence (BI).

Read on to learn about the Microsoft data platform vision and how SQL Server 2008 meets the needs of the next generation of data-driven applications.

Figure 1 Microsoft data platform vision

What’s New in SQL Server 2008

SQL Server 2008 capabilities deliver on the four key areas of the data platform vision.

· Mission-Critical Platform – SQL Server 2008 enables IT groups to be more productive by providing a more secure, scalable, and manageable platform. It includes a new policy-based management framework that shifts from

managing by scripts to managing by rules. SQL Server 2008 also protects valuable information in existing applications and disconnected devices. In addition, SQL Server 2008 delivers predictable query performance with an optimized platform.

· Dynamic Development – SQL Server 2008 along with the .NET Framework enables developers to build the next generation of applications. Developers are more productive because they work with business entities instead of tables and columns. They can build applications that enable users to take their data with them and synchronize their data with back-end servers.

· Beyond Relational Data – SQL Server 2008 enables developers to consume any type of data, from XML to documents, and build applications that incorporate location awareness.

· Pervasive Business Insight – SQL Server 2008 provides a scalable infrastructure that can manage reports and analysis of any size or complexity while at the same time empowering users because of its close integration with the Microsoft Office System. This enables IT to drive business intelligence throughout the organization. SQL Server 2008 makes great strides in data warehousing, enabling users to consolidate data marts in an enterprise data warehouse.

Mission-Critical Platform

In today’s data-driven world, data and the systems that manage that data must always be secure and available. SQL Server 2008 enables IT to be more

productive by providing a more secure, scalable, and manageable enterprise data platform with reduced application downtime.

Secure Trusted Platform for your Data

Building on the strengths of SQL Server 2005, SQL Server 2008 extends its security capabilities with the advancements covered in this section.

Transparent data encryption

SQL Server 2008 enables the encryption of an entire database, data files, or log files, without the need for application changes. One key benefit is that it provides the ability to search encrypted data, including both range and fuzzy searches.

External key management

Currently, in SQL Server 2005, encryption and key management is contained entirely within SQL Server. With the growing demand for regulatory compliance and the overall concern for data privacy, more organizations are leveraging encryption as a way to provide a comprehensive solution. SQL Server 2008 provides a mechanism for SQL Server encryption to work with third-party key management and Hardware Security Module (HSM) products.

Auditing

SQL Server 2008 allows users to create and manage auditing via DDL while also simplifying compliance by providing comprehensive data auditing. This enables organizations to answer common questions such as, “What data was retrieved?”

Transparent failover

The implementation of database mirroring in SQL Server 2005 requires client configuration to allow automatic failover with client redirect. There are times when it is not possible or practical to configure clients in this way. In SQL Server 2008, clients can add database mirroring without requiring application changes. This enables clients to provide a higher level of availability to existing applications.

Automatic recovery from page corruption

Data that is stored in database files runs the risk of data page corruption due to hardware failures. With SQL Server 2008, database mirroring implementations enable automatic recovery of the corrupted pages from the mirror database, transparent to the end user or application.

Log stream compression

Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants minimizes the network bandwidth used by database mirroring. This provides optimal performance of database mirroring.

Hot Add CPU

Hot Add CPU allows users to address scalability issues on demand within a database, enabling CPU resources to be added to SQL Server 2008 on supported hardware platforms without enforcing any downtime on applications. SQL Server already supports the ability to add memory resources online.

Productive Policy-based Management

SQL Server 2008 introduces a new policy-based management framework that shifts organizations from managing by scripts to managing by rules. This shift enables organizations to reduce the time spent on daily maintenance operations by defining a common set of policies for database operations such as query optimizations, naming conventions, backup and restore operations, and index management. These policies will automatically be enforced and monitored and will enable users to publish these policies to thousands of servers and thus provide a consistent management framework across the enterprise. This section describes some of the advancements in policy-based management.

Declarative Management Framework

The Declarative Management Framework (DMF) raises the level of abstraction so that administrators can manage SQL Server via concepts that are familiar to them. In SQL Server 2008, DMF enables forced compliance to policy, instead of simply monitoring via periodic polling. It enables administrators to author and apply their own policies based on the needs of their organization.

Figure 2 Declarative Management Framework

Streamlined installation

SQL Server 2008 introduces significant improvements to the service lifecycle for SQL Server through the re-engineering of the installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software.

Optimized and Predictable System Performance

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To address this, improvements have been made to the insight into performance that is delivered to administrators by way of extensive performance data collection, a centralized data warehouse for storing performance data, and tools for reporting and monitoring. Following are some of the advancements in system performance.

Data compression

Data compression enables you to store your data more effectively and reduce the storage requirements for your data. SQL Server 2008 provides significant performance improvements for large I/O bound workloads such as data warehousing. SQL Server 2008 also provides native support out-of-the box for backup

compression.

Resource Governor

SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor allows organizations to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance.

Predictable query performance

SQL Server 2008 enables greater query performance stability and predictability by providing functionality to lock down query plans so that, to the maximum extent possible, query plans survive server restart, server upgrade, and production deployments.

Dynamic Development

SQL Server 2008, along with the .NET Framework, enables developers to build the next generation of applications. Developers can be more productive because they can work with business entities instead of tables and columns. They can build applications that enable users to take their data with them and synchronize their data with back-end servers.

Accelerate Your Development with Entities

One common trend among developers who work with data is that, while they may interact with data from a database, they often like to define high-level business objects that they map the data into; in SQL Server 2008 rather than writing application logic against tables and rows, developers use entities such as ‘customer’ or ‘order.’ The ADO.NET Entity Framework enables developers to program against relational data in terms of entities. Programming at this higher level is incredibly productive and allows developers to use Entity-Relationship modeling directly. Some of the advancements in developing with entities in SQL Server 2008 are covered in this

section.

Language Integrated Query

Language Integrated Query (LINQ) enables developers to issue queries against data in terms of their programming language instead of SQL. It enables seamless, strongly typed, set-oriented queries in Microsoft Visual C#® or Microsoft Visual Basic® against the connected ADO.Net stack (SqlClient), ADO.NET DataSet, and the Entity Data Service Mapping provider.

Figure 3 LINQ to entities

ADO.Net Object services

The Object services layer of the ADO.NET stack enables materialization, change tracking, and the persistence of data as CLR objects. Developers using the ADO.NET Framework can program against a database using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development and supports entities.

Occasionally Connected Systems

With mobile devices and workers on-the-go, ‘occasionally connected’ has become a way of life. SQL Server 2008 delivers a unified synchronization platform that enables consistent synchronization across applications, stores, and data types. In combination with Microsoft Visual Studio®, SQL Server 2008 enables the rapid creation of occasionally connected applications through the combination of new synchronization services in ADO.NET and offline designers in Visual Studio.

SQL Server 2008 provides support for change tracking, enabling customers to develop cache-based, synchronization-based, or notification-based applications using a robust implementation with minimal performance overhead.

Beyond Relational Data

Increasingly, applications are incorporating a much wider variety of data types than are traditionally supported by a database. SQL Server 2008 builds on the strong legacy of supporting nonrelational data by providing new data types that enable developers and administrators to store unstructured data such as documents and images. SQL Server provides a rich set of services on the different data types while providing the reliability, security, and manageability of the data platform. The following sections outline some of the advancements in the area of data storage.

Store Any Type of Data

SQL Server 2008 allows for seamless transition between relational and nonrelational data, enabling users to access documents as data, encode complex hierarchies within XML, and query across relational and text data.

FileStream data

The SQL Server FileStream object allows large binary data to be stored in the file system yet remain an integral part of the database with transactional consistency. This enables the scale out of large binary data to be managed by the database and yet stored on more cost-effective storage and file systems.

Integrated Full-Text Search

Integrated Full-Text Search makes the transition between text searching and relational data seamless, while enabling users to use text indexes to perform text searches on large text columns.

Sparse columns

Sparse columns allow users to store object models onto relational data without undergoing large space costs. They also allows users to build complex content management applications with the underlying database.

Large user-defined types

Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.

Location Intelligence

Geographical information is rapidly becoming main stream to many business applications. SQL Server 2008 provides new spatial data types for developers to build location-aware applications.

Spatial data

SQL Server 2008 includes a vector-based spatial solution that conforms to industry spatial standards. This enables developers to build location-aware applications that capture geographical information from within the organization and integrate the data into applications.

Location data

SQL Server 2008 enables users to capture location data from across the organization and integrate location intelligence into existing applications, creating location-aware applications.

Pervasive Business Insight

End-to-end business insight enables better decision making through technology that allows customers to collect, clean, store, and prepare their business data for the decision-making process. Building on the strong momentum in the BI market, SQL Server 2008 provides a more scalable infrastructure that enables IT to drive business intelligence throughout the organization, manage reports and analysis of any size or complexity, and at the same time provide deep integration with the Microsoft Office System. SQL Server 2008 enables organizations to deliver business insights to all employees leading to better, faster, more relevant decisions.

Next-Generation Data Warehousing

Organizations continue to invest in deriving business value from their data by investing in a BI and data warehousing solution. SQL Serve 2008 provides a comprehensive and scalable data warehouse platform that enables organizations to integrate data into the data warehouse faster, scale and manage growing volumes of data and users, while delivering insights to all users. Following are some of the advancements in data warehousing.

Data compression

The key goal of data compression is to shrink data warehouse fact tables, while also improving query performance.

Backup compression

Keeping disk-based backups online is expensive and time consuming. With backup compression, less storage is required to keep backups online and backups run significantly faster because less I/O is required.

Partitioned table parallelism

Partitions enable organizations to more effectively manage large, growing tables by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by increasing the performance on large partitioned tables.

Star join query optimizations

SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star join query optimizations reduce query response time by recognizing data warehouse join patterns.

Resource Management

Use Resource Governor to manage CPU and memory resources within an instance of the relational engine.

Grouping Sets

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple grouping in the same query. Grouping Sets produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.

Change Data Capture

With Change Data Capture, changes are captured and placed in change tables. This feature captures the complete content of changes and maintains cross-table consistency and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.

MERGE SQL statement

With the introduction of the MERGE SQL statement, developers can more effectively handle common data warehousing scenarios such as checking whether a row exists and then executing inserts or updates.

Scalable Integration Services

The two key advancements in scalability of Integration Services include:

· SQL Server Integration Services (SSIS) pipeline improvements

Data Integration packages can scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.

· SSIS persistent lookups

Performing lookups is a common operation, especially in data warehousing, where fact records must use lookups to transform business keys to their corresponding surrogates. SQL Server Integration Services (SSIS) will increase the performance of lookups so that it scales to meet the largest tables.

Scalable Analysis Platform

With SQL Server 2008, organizations can perform highly complex analysis with a large number of dimensions and aggregations. To facilitate the process, SQL Server Analysis Services has made the following advancements in analytics.

Analysis scale and performance

Drive broader analysis with enhanced analytical capabilities, including more complex computations and aggregations. Improved dimension designers to help users avoid performance issues. Address current limitations and increase the flexibility of report layout with report engine improvements, including on-demand processing and instance based rendering.

Block computations

Block computations provide a significant improvement in analysis cube performance, enabling users to increase the depth of their hierarchies and complexity of the computations.

Writeback

While they are viewing analytical data, users would like to make changes to the underlying data. SQL Server 2005 provides writeback support; with SQL Server 2008 the performance is greatly improved for writeback scenarios.

Scalable Reporting

For many organizations, the challenge is to get the right information to the right people at the right time. SQL Server 2008 provides a high-performance reporting engine for processing and formatting reports, along with a complete set of tools for creating, managing, and viewing reports. An extensible architecture and open interfaces enable easy integration of reporting solutions in diverse IT environments.

Enterprise Reporting Engine

The reporting engine provides simplified deployment and configuration, making it easier to deliver reports throughout the organization. This enables users to easily create and share reports of any size or complexity.

Better scale-out configuration

To enable better scale-out configuration, SQL Server 2008 provides the tools needed to support the management of multiple report servers.

Internet report deployment

Reach customers and suppliers by deploying reports over the Internet.

Manage the reporting infrastructure

SQL Server 2008 enables increased supportability and the ability to control server behavior with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.

Rich Information Experiences

SQL Server 2005 provides end-to-end services supporting business intelligence applications: data warehousing, Integration Services, Analysis Services, and Reporting Services. Microsoft Office 2007 makes it even easier to support strategic and tactical decision makers with rich analytics in Microsoft Excel® and Excel Server and collaborate around BI through SharePoint® Report Center. Because users can access the power of Reporting Services by leveraging their existing familiarity with Office, more users can create and deploy reports. SQL Server 2008 empowers users with actionable business insight with the reporting advancements covered in this section.

Report Builder enhancements

Easily build ad-hoc reports and author reports with any structure by using Report Designer.

Figure 4 Report Designer

Built-in forms authentication

Built-in forms authentication enables users to easily switch between windows and forms.

Report Server application embedding

Report Server application embedding enables the URLs in reports and subscriptions to point back to front-end applications.

Office integration

SQL Server 2008 enables users to easily author reports directly in Microsoft Word and Excel and publish and share them within the organization with Microsoft Office SharePoint Server.

Conclusion

SQL Server 2008 provides the technology and capabilities that organizations count on. With significant advancements in the key areas of mission-critical platform, dynamic development, beyond relational data, and pervasive insight, the benefits of SQL Server 2008 are substantial.

Special Thanks :Michelle Dumler

Dynamic Management Views

As with a lot of the new features we have seen with SQL Server 2005 and now with SQL Server 2008, Microsoft introduced a few more dynamic management views to also assist with identifying possible index candidates based on query history.

The dynamic management views are:
• sys.dm_db_missing_index_details - Returns detailed information about a missing index
• sys.dm_db_missing_index_group_stats - Returns summary information about missing index groups
• sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
• sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for an index. This is a function and requires the index_handle to be passed.


How to get index usage information in SQL Server
sys.dm_db_index_operational_stats
This view gives you information about insert, update and delete operations that occur on a particular index. In addition, this view also offers data about locking, latching and access methods. There are several columns that are returned from this view, but these are some of the more interesting columns:
• leaf_insert_count - total count of leaf level inserts
• leaf_delete_count - total count of leaf level inserts
• leaf_update_count - total count of leaf level updates
sys.dm_db_index_usage_stats
This view gives you information about overall access methods to your indexes. There are several columns that are returned from this DMV, but here are some helpful columns about index usage:
• user_seeks - number of index seeks
• user_scans- number of index scans
• user_lookups - number of index lookups
• user_updates - number of insert, update or delete operations

INDEXED VIEW

Why Indexed Views?

Views have been available throughout the history of Microsoft SQL Server. However, using views that return very large result sets can lead to poor performance, as the result set is not indexed and the entire result must be table scanned if the view is used in a join or a subquery of a T-SQL command. Additionally, products like Oracle have come out with the concept of a Materialized View that give an additional performance boost by being able to have indexes built on a view. So in the continuing evolution of the SQL Server product line and in response to Oracle�s Materialized View, Microsoft SQL Server 2000 has a new feature called the View Index. View Indexes give the product the capability to define an index on a view. Additionally, SQL Server View Indexes are dynamic in that changes to the data in the base tables are automatically reflected in the indexed view. Also the SQL Server query optimizer will try to use an indexed view even if the view is not referenced in the from clause of a T-SQL command. These features are not available in Oracle�s Materialized Views.

Before SQL Server 2000, what was a View?

Typically a view is thought of as a virtual table, or a stored query. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views. This T-SQL select command is stored as a database object (a view). Developers can use the results from the view by referencing the view name in T-SQL statements the same way they would reference a real table. When referenced, the stored T-SQL that represents the view is merged with the referencing T-SQL code and executed to come up with the final results. Views have additional benefits of:

1. Views provide a security mechanism by subsetting the data by rows (All Active Customers, all customers in a certain state).

2. Views provide a security mechanism by subsetting the data by columns (Payroll fields not shown in the Employee Phone List View).

3. Views can simplify complex queries into a single reference. Complex Join operations that can make a normalized database design of several tables into a single row in the result set of the view. This is great for reporting tools like Crystal and Cognos.

4. Views give us aggregation capabilities (Min, Max, Count, Sum) where the data is not stored but calculated.

5. Views can create other calculated fields based on values in the real underlying tables.

6. Views can reference another view as one its �Base Tables�.

7. Views can hide the complexity of partitioned data (Sales from 1998 are in the 1998 table, Sales from 1999 are in the 1999 table, Sales from 2000 are in the Current Table) .

8. Views can be updateable in certain situations (only update to 1 of the base tables!).

9. Views do not incur overhead of additional permanent storage.

What are SQL Server 2000 Indexed views?

Views in SQL Server 2000 are very similar to those in previous versions with a few major exceptions when using Indexed views. When a clustered index is created on the view, SQL Server immediately allocates storage space to store the results of the view. You can then treat the view like any other table by adding additional nonclustered indexes.

What are the requirements for Indexed views?

There are several requirements that you must take into consideration when using Indexed views.

1. View definition must always return the same results from the same underlying data.

2. Views cannot use non-deterministic functions.

3. The first index on a View must be a clustered, UNIQUE index.

4. If you use Group By, you must include the new COUNT_BIG(*) in the select list.

5. View definition cannot contain the following

a. TOP

6. Text, ntext or image columns

7. DISTINCT

8. MIN, MAX, COUNT, STDEV, VARIANCE, AVG

9. SUM on a nullable expression

10. A derived table

11. Rowset function

12. Another view

13. UNION

14. Subqueries, outer joins, self joins

15. Full-text predicates like CONTAIN or FREETEXT

16. COMPUTE or COMPUTE BY

17. Cannot include order by in view definition

Notice that Indexed Views change the very essence of what a view was before this version of Sql Server. First, the data represented by the view is actually stored in the database. Secondly, the view definition must always return the same results for the same underlying data and all functions and expressions must be deterministic no matter what the current session settings.

To make sure that you can meet this requirement, the following session options must be set when you create an index view, when you modify any of the tables included in an indexed view or when the optimizer decides to use the indexed view as part of a query plan. Session Options that must be on

ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT

CONCAT_NULL_YEILDS_NULL

QUOTED_IDENTIFIERS

Session options that must be off

NUMERIC_ROUNDABORT

Functions like GetDate(), rand() are non-deterministic because of different session settings can return different values and the settings for one user may not be the same as for another. The list of deterministic and non-deterministic functions will be included in the final version of Books Online for SQL Server 2000. (Look for topic Deterministic and Nondeterministic Functions in the Books Online)

Besides these restrictions, the underlying tables that make up the view must be protected from schema changes. Part of the syntax of the create view command is the �with SCHEMABINDING� phrase. This is required to create a View Index and this will prevent the dropping or altering of tables participating in an Indexed View. Note that dropping the clustered index of an Indexed View will return it to the standard SQL view as it was as described above in the section Before SQL Server 2000, what was a View?

How do I create an Indexed View?

1. Make sure that session properties are properly set.

2. Create a deterministic view with new SCHEMABINDING syntax.

3. Create unique Clustered Index.

4. Optionally, create additional nonclustered indexes.

Below you will find the code that you can paste into the Sql Server Query Analyzer to test this yourself. This example is based on the Northwind sample database.

-- Use the northwind database
USE NORTHWIND
GO

-- Make sure that all of the session settings are set properly
IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
go

-- Create the view, it must comply with the rules (deterministic)
CREATE VIEW PRODUCTS_BY_CUSTOMER WITH SCHEMABINDING AS
select customers.companyname,
products.productname,
sum(odetail.unitprice*odetail.quantity) as TotalPurchase,
count_big(*) as cnt
from dbo."order details" as odetail
inner join dbo.orders as omain
on omain.orderid = odetail.orderid
INNER join dbo.customers as customers
on customers.customerid = omain.customerid
INNER join dbo.products as products
on products.productid = odetail.productid
group by
customers.companyname,
products.productname
go

-- the following statement will cause an error if the view has not been
-- indexed
--EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'
--Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
--Views do not have space allocated.

-- Check to see if the indexes can be created
if ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1
BEGIN
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX PRODUCTS_BY_CUSTOMER_UNIQUE ON
PRODUCTS_BY_CUSTOMER(COMPANYNAME, PRODUCTNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

-- Create NonClustered Indexes
CREATE INDEX PRODUCTS_BY_CUSTOMER_1 ON
PRODUCTS_BY_CUSTOMER(COMPANYNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

-- Create NonClustered Indexes
CREATE INDEX PRODUCTS_BY_CUSTOMER_2 ON
PRODUCTS_BY_CUSTOMER(PRODUCTNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

END

Please note the ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1 in the above code listing. This command will tell you if all of the requirements for indexing a view have been met so that you can programmatically determine if a view can be indexed or not.

Also note that no space is allocated in the database for this view until the clustered index is created. If you try to use the SP_SPACEUSED stored procedure on a view that is not indexed, you get an error. The results of the SP_SPACEUSED commands that are sprinkled throughout the above code listing gives the following results on my test machine.

	# of Rows	Data	Index	Total Used
After Clustered Index Created	1685	168 kb	16 kb	184 kb
After NonClustered Index 1	1685	168 kb	168 kb	336 kb
After NonClustered Index 2	1685	168 kb	320 kb	488 kb

How do I use the Indexed View?

You can use the view like you would any other view. Also, the SQL Server query optimizer will attempt to use a View Index even if the view is not referenced in the from clause, although you can override this behavior with the Expand Views hint.

From the sample created in the above code example, you could use the view as follows

Example 1: select * from products_by_customer

Example 1 above lets the query optimizer determine whether or not to use the view and its indexes or to use the base tables. Surprising on my test machine, this example uses the base tables, not the Indexed View. The query optimizer is a complex piece of technology but it isn�t always perfect. Based on my testing with this sample data in the Northwind database, I had to use the (noexpand) hint seen in the next example to force the optimizer to use the View Index. The speed of this on my test machine was about 3 times faster with 1685 records. By increasing the number of records in the base tables (orders 3000 records and order details 224,696 records), I found that the query optimizer did use the View Index without specifying the hint and the resulting query speeds where approximately 50 times faster. The # of records in the view, after adding all of these records in the base tables, was 1880 records. I conclude that the query optimizer with a small number of records in the base table (Orders had about 830 and order details had about 2155 records when I started) lean towards using the base tables instead of the View index. More testing would be needed to nail down the break even point but this just points out why the hints are still around and how much faster performance can be when the View Indexes are used.

Example 2: select * from products_by_customer with (noexpand)

Example 2 uses a hint to force the query optimizer to consider only the view and its indexes in the execution plan.

Example 3: select * from products_by_customer option (Expand Views)

Example 3 uses a hint to force the query optimizer to expand all indexed views into their underlying Select statements so the optimizer won�t consider any View Indexes in the execution plan.

When would I want to use a View Index?

If you have an application that is a Data-Mart, Data-Mining or decision-support type application, you can improve performance with View Indexes. Applications that do any of the following may benefit as well:

· Joins and aggregations of big tables

· Repeated patterns of queries

· Repeated aggregations on the same or overlapping sets of columns

· Repeated joins of the same tables on the same keys

Also, situations where you might consider de-normalizing a set of tables by storing aggregate information in the parent table may be good situations to consider creating an aggregate view on the child table and creating the appropriate View Index. In essence, the Indexed View replaces your de-normalized fields and all the work of keeping this de-normalized aggregate field up to date is taken care of by the database engine.

When would I NOT want to use a View Index?

You obviously cannot use a View Index if you need to include syntax in the view definition that is not allowed. It seems to me that Top, Min, Max, Count, using another view, union, subqueiries and outer joins are serious restrictions that would disqualify a large number of views that I might want to optimize using the View Index.

Storage may also be a major consideration as the data in the view is physically and permanently stored not only in its base table, but in the clustered index of the View Index. Effectively, the data is stored twice, once in its base table and once in the clustered index of the Indexed View.

Also, On-Line Transaction Processing systems (OLTP) will actually suffer performance loss if you try to use View Indexes. Databases with frequent inserts, updates, deletes suffer as these commands must not only process the table, but any associated Indexed Views that the base table is participating in. Also, views that are simply subsets of rows or columns with no aggregation or computation provide no benefit over a standard SQL view or a T-SQL command directly against the base tables. This additional overhead to update the data in the clustered index of the view I believe is the reason that the clustered index must be unique for an Indexed view. It uses this unique value to quickly update the appropriate record in the Indexed View. Without a unique index, the processing for updating the Indexed View records could be unacceptably long.

What are the performance benefits?

As I indicated earlier, I experienced query times 3 times quicker using the Indexed Views over the same query not using the Indexed Views on the sample data in the NorthWind database. With a much bigger data set and with the same database objects defined, I got query times as much as 50 times faster. Microsoft has reported performance improvements of 10 to 100 times with applications that access indexed views instead of base tables. I also experimented with using other queries that did not directly reference the Indexed View and got similar performance gains when the optimizer selected the Indexed View over other possible execution plans.

Summary

As you can see, even with its restrictions, the View Index is a powerful new tool in the SQL Server Developer�s toolbox. Because the optimizer can use a View Index, you won�t even have to change your existing T-SQL to take advantage of the performance benefits of the View Index. So take into consideration the information above when evaluating whether a View Index is right for your application.

SQL Server maximum values

SQL Server maximum values
Bytes per short string column 8,000 Bytes per GROUP BY, ORDER BY 8,060 Columns in GROUP BY, ORDER BY Limited only by number of bytes Bytes per index key 900 Bytes per foreign key 900 Bytes per primary key 900 Bytes per row 8,060 Bytes per varchar(max), varbinary(max), xml, text, or image column 2^31-1 Characters per ntext or nvarchar(max) column 2^30-1 Clustered indexes per table 1 Columns per index key 16 Columns per foreign key 16 Columns per primary key 16 Columns per base table 1,024 Columns per SELECT statement 4,096 Columns per INSERT statement 1,024 Connections per client 32,767 Database size 1,048,516 terabytes Databases per instance of SQL Server 32,767 Filegroups per database 32,767 Files per database 32,767 File size (data) 16 terabytes File size (log) 2 terabytes Foreign key table references per table 253 Identifier length (in characters) 128 Instances per computer 50 (Workgroup Edition only 16) Locks per connection Maximum locks per server Locks per instance of SQL Server Up to 2,147,483,647 Nested stored procedure levels 32 Nested subqueries 32 Nested trigger levels 32 Nonclustered indexes per table 249 Parameters per stored procedure 2,100 Parameters per user-defined function 2,100 REFERENCES per table 253 Rows per table Limited by available storage Tables per database Limited by number of objects in a database Partitions per partitioned table or index 1,000 Statistics on non-indexed columns 2,000 Tables per SELECT statement 256 Triggers per table Limited by number of objects in a database UNIQUE indexes or constraints per table 249 nonclustered and 1 clustered User connections 32,767 XML indexes 249