Indexes on Views - with UNION

Question

Indexes on Views - with UNION

Minnu

SSCertifiable

Points: 6451
More actions
November 4, 2016 at 2:17 am

#314613

Hi Team,
Want to create a view using below query, and also want to apply indexes on that view.
SELECT NT.ID, U.acc_name
FROM dbo.NR_VT NT WITH (NOLOCK),
dbo.ACCOUNTS U WITH (NOLOCK)
WHERE NT.VT = U.acc_name
UNION
SELECT NT.ID, U.acc_name
FROM dbo.NR_VT NT WITH (NOLOCK),
dbo.ACCOUNTS U WITH (NOLOCK),
dbo.Wat_GRP SG WITH (NOLOCK)
WHERE U.acc_name = SG.acc_name
AND NT.VT = CONVERT(NVARCHAR(150), SG.Wat_GRP_ID);
while creating indexes on view getting below error
Cannot create index on view 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.

Viewing 9 posts - 1 through 8 (of 8 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Minnu (11/4/2016)
Hi Team,
Want to create a view using below query, and also want to apply indexes on that view.
SELECT NT.ID, U.acc_name
FROM dbo.NR_VT NT WITH (NOLOCK),
dbo.ACCOUNTS U WITH (NOLOCK)
WHERE NT.VT = U.acc_name
UNION
SELECT NT.ID, U.acc_name
FROM dbo.NR_VT NT WITH (NOLOCK),
dbo.ACCOUNTS U WITH (NOLOCK),
dbo.Wat_GRP SG WITH (NOLOCK)
WHERE U.acc_name = SG.acc_name
AND NT.VT = CONVERT(NVARCHAR(150), SG.Wat_GRP_ID);
while creating indexes on view getting below error
Cannot create index on view 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.

The error message informs you that you cannot create an indexed view on your query because it contains the UNION operator and suggests alternative query forms to work around this restriction. What is your question?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

And since you have NOLOCK everywhere, it's worth pointing out that the use of NOLOCK can lead to missing or extra rows in your query. Be very cautious in how you use that query hint. It's not a magic "run faster" switch. It comes with a cost.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'd also suggest, while you're cleaning out the NOLOCK hints, replace the old-style joins in the WHERE clause with the JOIN clauses.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Hi,

Want to combine two select queries in to one by excluding UNION.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Why?

Union does that, to do it without union is going to be tricky, probably will need temporary tables or conditional joins (and those will be slow)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Minnu (11/4/2016)
Hi,
Want to combine two select queries in to one by excluding UNION.

Indexed views - typically these are used as a performance-enhancing trick. Are you experiencing performance issues with these two queries? One of them has an obvious flaw and is likely to be much slower than the other. Would it not be better to fix this flaw, rather than attempt to twist TSQL in a direction which is unlikely to be very successful? The performance improvement may well prove sufficient to eliminate the need for an indexed view.

If you wan help with this, then post up the [actual] execution plans of both queries, as .sqlplan attachments.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

ChrisM@Work (11/4/2016)
Minnu (11/4/2016)
Hi,
Want to combine two select queries in to one by excluding UNION.
Indexed views - typically these are used as a performance-enhancing trick. Are you experiencing performance issues with these two queries? One of them has an obvious flaw and is likely to be much slower than the other. Would it not be better to fix this flaw, rather than attempt to twist TSQL in a direction which is unlikely to be very successful? The performance improvement may well prove sufficient to eliminate the need for an indexed view.
If you wan help with this, then post up the [actual] execution plans of both queries, as .sqlplan attachments.

If the problem is indeed performance, I'm sure that non-SARGable predicate in the JOIN clause is a contributor.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ManicStar SSCoach Points: 17992 More actions · Answer 8

Grant Fritchey (11/4/2016)
And since you have NOLOCK everywhere, it's worth pointing out that the use of NOLOCK can lead to missing or extra rows in your query. Be very cautious in how you use that query hint. It's not a magic "run faster" switch. It comes with a cost.

hehehehe...