Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Proper index for indexed view Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2014 5:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 24, 2014 10:40 AM
Points: 35, Visits: 50
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



Post #1559740
Posted Wednesday, April 9, 2014 3:01 AM This worked for the OP Answer marked as solution
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:11 AM
Points: 93, Visits: 95
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
Post #1559835
Posted Wednesday, April 9, 2014 9:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 24, 2014 10:40 AM
Points: 35, Visits: 50
I appreciate the response and the info. I don't like the info, but I appreciate it.


Post #1560040
Posted Wednesday, April 9, 2014 2:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:11 AM
Points: 93, Visits: 95
Tell me about it. I'd love to use them more but they're so prohibitive
Post #1560169
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse