StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreate an index on SQL view with UNION operators? Will it really improve performance?
text
Body
copied!<p>I am trying to create an index on the following view:</p> <pre><code>SELECT 'Candidate' AS Source, CandidateID AS SourceId, LastName + ', ' + FirstName AS SourceName FROM dbo.Candidates UNION SELECT 'Resource' AS Source, ResourceID AS SourceId, LastName + ', ' + FirstName AS SourceName FROM dbo.Resources UNION SELECT 'Deal' AS Source, DealID AS SourceId, CONVERT(varchar, Number) + '-' + CONVERT(varchar, RevisionNumber) AS SourceName FROM dbo.Deals UNION SELECT 'Job Order' AS Source, JobOrderID AS SourceId, CustomerNumber AS SourceName FROM dbo.JobOrders </code></pre> <p>I am getting the following error:</p> <pre><code>Msg 1939, Level 16, State 1, Line 2 Cannot create index on view '_Source' because the view is not schema bound. </code></pre> <p>I added WITH SCHEMABINDING to the CREATE and now get the following error:</p> <pre><code>Msg 10116, Level 16, State 1, Line 2 Cannot create index on view 'DEALMAKER.dbo._Source' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view. </code></pre> <p>My questions are:</p> <p>How would I create an index on this view? Would creating separate indexed views <strong>really</strong> work? </p> <p>Lastly, am I <strong>really</strong> going to see a performance improvement for any queries that may JOIN this view?</p> <p>Thanks in advance!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload