April 8, 2014 at 5:09 pm
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
April 9, 2014 at 3:01 am
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
April 9, 2014 at 9:58 am
I appreciate the response and the info. I don't like the info, but I appreciate it. 😛
April 9, 2014 at 2:04 pm
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