Examples for using Indexed Views

schmidro

I understand that for comparing subsets of records in a table with each other I need to use Indexed Views however I would like to see some examples how to do this ...

the basic requirement is to only compare records that fullfil a certain criteria, e.g. customerid='xyz'. my specific problem is that it almost seems that i need to make one view for each customerid which seems unreasonable but i am proably missing something.

appreciate any guides or help you can provide on building the indexes and then running them in sql data compare.

thanks,

robert

Brian Donahue

Hi Robert,

Making an indexed view is only slightly more complex than making a normal view. Before you start, though, you need to make sure you can meet the criteria for an indexed view:

• The view needs to have the same owner as the underlying table(s)
• The view must be created WITH SCHEMABINDING
• You need to explicitly name all of the columns (SELECT * FROM... is not allowed)
• You can join two tables, but you can't use outer joins, self-joins, or UNIONs

Once you create the view that meets the criteria, then you can click on 'manage indexes' in the table designer and create a new index. The index should be UNIQUE and CLUSTERED and defined on a column of data that is known not to contain duplicate data or allow NULLs.