Proper index for indexed view

  • I have a view that is basically a UNION query on Great Plains data. The data I need to pull can be stored in any of 4 different header tables (each with an associated detail table), hence the view to pull them together. When I pull data from the view, the WHERE clause looks like:

    WHERE AR.Line1Date Between @FmDate And @ToDate AND

    AR.Company = @CoCode AND

    AR.CustomerAccount BETWEEN @FmCust AND @ToCust

    I'm not normally the one to do this type of thing, so I'm unclear whether 3 indexes on the 3 fields or a single index on the combination (is that a clustered index?) would be best. Any pointers would be appreciated.

    TIA,

    Paul

  • Hi Pbaldy,

    unfortunately there are quite hefty restrictions on what can't be included in an indexed view an a union is one of them, please see the books online article below for the full list of restrictions.

    http://msdn.microsoft.com/en-us/library/ms191432.aspx

    just as an extra pointer using all 3 columns would be creating a composite index a clustered index is an entirely different creature, although you could use all 3 columns in a clustered index and you need a clustered index on an indexed view before you can add non clustered index's.

    I have included an article on clustered and non clustered index's below that you might find interesting.

    http://technet.microsoft.com/en-us/library/ms190457.aspx

    Hope this was helpful.

    J

  • I appreciate the response and the info. I don't like the info, but I appreciate it. 😛

  • Tell me about it. I'd love to use them more but they're so prohibitive

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply