SQL Server Performance Forum – Threads Archive

indexed view, disallowed

I have a simple view based on some union’ed select’s: create view v_myView
with schemabinding
as
(
select id1, id2, id3 from tbl1 union all
select id1, id2, id3 from tbl2 union all
…
select id1, id2, id3 from tbln
) id2 and id3 are calculated by using COALESCE, charindex, and substring.
Are those functions DISALLOWED CONSTRUCTS? Becasue I couldn’t create
index on column id1 and the error msg is: one or more disallowed constructs

---

It’s the UNION that is causing you the problem. You cannot use UNION on indexed views… Bambola.

---

zackhu, did you execute your code outside view defination? Does it work there? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

I created the index like that
then went to EM to create the index,
that’s when I received the msg.
I even tried to use some user defined functions for id2 and id3
(with schemabinding) doesn’t work either.
If union all does not work in indexed view, what is the purpose
of indexed view. There are a lot of views are constructed from
union selects.

---

when you create a view with schemabinding you should specify the owner name. in case of dbo that would be create view v_myView
with schemabinding
as
(
select id1, id2, id3 from dbo.tbl1 union all
select id1, id2, id3 from dbo.tbl2 union all
…
select id1, id2, id3 from dbo.tbln
) Gaurav, you can create a "noraml" view with schemabinding and union. you cannot an index view… so I assumed we were talking indexed view… Bambola.

---

So basically if I have UNION ALL stmts
in my view creatation, I cannot build an index
on it?

---

Corrected my mistake… Follow Bambola’s Suggestion and try creating index from Query analyzer. Enterprise manager behaves weird when it comes to views… Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

That’s correct. UNION is one of the things you cannot use in an indexed view. There are others. You can find them all in BOL under indexed views. Bambola.

---

> Enterprise manager behaves weird when it comes to views… I dont know how true this is, but I heard that on non-enterprise editions, enterprise manager does not allow creation of indexed views, but you can create them via query analyser without problem. Maybe someone can confirm this?

---

I created one on personal edition using EM. Bambola.

---

Babbola, SQL Server Personal Edition is the same as Enterprise edition with the exception of a EULA which prevents the personal edition to be used for deployement on production system. I think Stanard Edition will be a better version to check. Chappy, for looking at weird behavior of EM, see article Partitioning Tips InstantDoc #37889
athttp://www.sqlmag.com. This is an excellent article Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

How do you index a view when you have used UNION ALL? If you can’t do it using UNION ALL, how do you create a view without using UNION ALL so that it can be indexed? Here is my sample code that isn’t working… –Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO –Create the view.
CREATE VIEW vw1
WITH SCHEMABINDING
AS
SELECT
[col1],
[col2],
[col3],
[col4],
[col5],
[col6],
[col7]
FROM dbo.tbl1
UNION ALL
SELECT
[col1],
[col2],
[col3],
[col4],
[col5],
[col6],
[col7]
FROM dbo.tbl2
UNION ALL
SELECT
[col1],
[col2],
[col3],
[col4],
[col5],
[col6],
[col7]
FROM dbo.tbl3
UNION ALL
SELECT
[col1],
[col2],
[col3],
[col4],
[col5],
[col6],
[col7]
FROM dbo.tbl4
GO –Create index on the view.
CREATE UNIQUE CLUSTERED INDEX idx1 ON vw1 (
[col1],
[col2],
[col3],
[col4],
[col5],
[col6],
[col7]
)
GO I can create an index if I create a view using only one table (i.e. don’t use the UNION ALL statement).

---

The problem is just as Bambola stated, you cannot index a view if you are using any kind of UNION in it. You can create the view and use it normally, but you simply cannot index it. Having appropriate indexes and constraints on the underlying tables will help with performance, but that is all you will be able to do. If you really want to index this data set, you will need to put it all in 1 table.

---

I guess that what I’m looking for then would be a replacement for using "UNION ALL" to join all of my tables, but I don’t know of one. The data being used grows at 200,000+ records a day so a single table is just not the most attractive idea, but maintaining multiple indexes on multiple tables isn’t either. It would be soooo nice to actually be able to create a meaningful view (not just one of a single table) and be able to index it.

---

Have you tested performance using a regular view without an index? It may perform well enough without an indexed view. I do not think there is a way to change your view so it does not use UNION statements, and still returns the same results.

---

I have a problem with the option
‘QUOTED_IDENTIFIER’
I set it to on inside my proc, but still I got an error:
Server: Msg 1935, Level 16, State 1, Line 1
Cannot create index. Object ‘v_ViewName’ was created with the following SET options off: ‘QUOTED_IDENTIFIER’.
CREATE PROCEDURE createView
@Name varchar(100)
AS
declare @sqlStr nvarchar(2000)
–here a view named v_ViewName is created
–next, try to add an index on it:
SET QUOTED_IDENTIFIER ON SET @sqlStr = N’CREATE UNIQUE CLUSTERED INDEX ‘ +
N’ [PK_’ + @Name + ‘] ON ‘ + @Name +
N'([name1], [name2], [name3])’ EXECUTE dbo.sp_executesql @sqlStr
GO
exec [thisdb].[dbo].[createView] ‘thisname’

---

May be this help: The session creating the indexed view must have the following options on: ARITHABORT, CONCAT_NULL_YEILDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNING. This setting can be made in SQL Server Enterprise Manager. from BOL
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

Thanks but it doesn’t make the result any different.
I have the SET QUOTED_IDENTIFIER ON inside the proc.
Apparently SQL doesn’t take it.

---

]]>