How can we help you today? How can we help you today?

Examples for using Indexed Views

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
schmidro
0

Comments

1 comment

Sort by
  • 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.
    Brian Donahue
    0

Add comment

Please sign in to leave a comment.