• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

Indexed Views

SQL Server 2000 introduced indexed views. They provide you with greatly improved performance by allowing you to create a unique clustered index for a view. Here's the reason why. SQL Server does not store the result set of a standard view in the database. Each time a query references the view, SQL Server dynamically creates the result set. The overhead of building the result set can be substantial, particularly for complex views. You can greatly improve performance by creating a unique clustered index for the view. When you create a unique clustered index, SQL Server stores the data that exists at the time you create the view. SQL Server then reflects all modifications to table data within the stored view. This improves the efficiency of data retrieval. Once you create a unique clustered index for the view, you can then create additional nonclustered indexes. An example of the syntax to create a unique clustered index is:

CREATE UNIQUE CLUSTERED INDEX [vwCustomerInfoCustomerID] 
ON [dbo].[vwCustomerInfo] ([CustomerID])


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint