create view problem

Question

create view problem

pat-261672

Hall of Fame

Points: 3325
More actions
January 19, 2009 at 4:37 pm

#127067

Hi all i am trying to create indexed view as follow:
create vew v1 with schemabinding as
(
if ((select count(*) from table1) > 0)
begin
select col1, col2, col3 from table1)
end
else
begin
select col1, col2, col3 from table2)
end
);
create clustered index idx on v1(col1)
Can it happen? Or can i create view like this? if the table1 has value than select from there or select from table 2
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Sorry... you can't have "control of flow" statements like "IF" in a view.

It could, however, be easily accomplished in a stored procedure and then you could use the result of that stored procedure as if it were a table either using OPENROWSET or INSERT/EXEC. It will cause a recompile, but it can be done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pat-261672 Hall of Fame Points: 3325 More actions · Answer 2

ok,

I tried creating new view. View created fine but now when i try to create index on the column it give me this error.

create unique clustered index idx on vw1(col1); --col1 is datatype int.

error:

Cannot create index on view 'vw1'. It contains text, ntext, image or xml columns.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Well... does it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pat-261672 Hall of Fame Points: 3325 More actions · Answer 4

it is only numbers and integer it does not contains text!!

numbers from 1 to atleast 55000

Thanks

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

pat (1/19/2009)
it is only numbers and integer it does not contains text!!
numbers from 1 to atleast 55000
Thanks

Just guessing here because we can't see what you can. The only way we're going to be able to help is if you...

1. Post the CREATE TABLE statement(s) of the underlying table(s) in the view.

2. Post the code for the view.

3. Post the exact create index statement that's causing the error.

3. Post the copied error you're getting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

pat (1/19/2009)
it is only numbers and integer it does not contains text!!
numbers from 1 to atleast 55000
Thanks

Jeff's not asking about the col1 column. Are there any other columns in the view that are text, ntext or image (the data types)?

One of the limitations on indexed views (of which there are a lot) is (From BoL)

The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

Please check this page to find out how to create Index views. This page even has FAQ. 🙂

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

-Roy

pat-261672 Hall of Fame Points: 3325 More actions · Answer 8

oh yes sorry it took me while to understand.. but the view does have other columns which could ne text, as well. What is the solution?

pat-261672 Hall of Fame Points: 3325 More actions · Answer 9

here is the whole table and view scenario

create table table1

[pid] [int] IDENTITY(1,1) NOT NULL,

[num] [int] NULL,

[flag] [int] NULL,

[tp_id] [int] NULL,

[fe_id] [int] NULL,

[sym] [int] NULL,

[nvarchar](256) NULL,

[lbl] [nvarchar](256) NULL,

[sname] [nvarchar](256) NULL,

[fname] [nvarchar](256) NULL,

[sync] [nvarchar](256) NULL,

[oper] [nvarchar](256) NULL,

[histr] [nvarchar](256) NULL,

[lname] [nvarchar](256) NULL,

[lnum] [nvarchar](256) NULL,

[fldname] [nvarchar](256) NULL,

[fomated] [nvarchar](256) NULL,

[prof] [nvarchar](256) NULL,

[county] [nvarchar](256) NULL,

[st] [nvarchar](256) NULL,

[comments] [ntext] NULL,

create view v1 with schemabinding as

SELECT pid

,num

,flag

,tp_id

,fe_id

,sym

,url

,lbl

,sname

,fname

,sync

,oper

,hist

,lname

,lnum

,fldname

,fomated

,prog

,county

,st

,comments

FROM table1

create unique clustered index idx1 on v1(num)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

pat (1/20/2009)
here is the whole table and view scenario
create table table1
[pid] [int] IDENTITY(1,1) NOT NULL,
[num] [int] NULL,
[flag] [int] NULL,
[tp_id] [int] NULL,
[fe_id] [int] NULL,
[sym] [int] NULL,
[nvarchar](256) NULL,
[lbl] [nvarchar](256) NULL,
[sname] [nvarchar](256) NULL,
[fname] [nvarchar](256) NULL,
[sync] [nvarchar](256) NULL,
[oper] [nvarchar](256) NULL,
[histr] [nvarchar](256) NULL,
[lname] [nvarchar](256) NULL,
[lnum] [nvarchar](256) NULL,
[fldname] [nvarchar](256) NULL,
[fomated] [nvarchar](256) NULL,
[prof] [nvarchar](256) NULL,
[county] [nvarchar](256) NULL,
[st] [nvarchar](256) NULL,
[comments] [ntext] NULL,
create view v1 with schemabinding as
SELECT pid
,num
,flag
,tp_id
,fe_id
,sym
,url
,lbl
,sname
,fname
,sync
,oper
,hist
,lname
,lnum
,fldname
,fomated
,prog
,county
,st
,comments
FROM table1
create unique clustered index idx1 on v1(num)

With that, I can guarantee the indexed view won't work because the Comments column is NText. The fix is to convert the NText column to NVARCHAR(MAX).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

As a sidebar, there isn't really an advantage to having such an indexed view. The underlying indexes on the table are the only advantage. Indexed views become an advantage usually when there is some form of aggragation. I wouldn't bother trying to index this view as the only thing it really accomplishes is slowing down inserts and updates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rio_briones Old Hand Points: 330 More actions · Answer 12

View definition cannot contain the following

TOP

Text, ntext or image columns

DISTINCT

MIN, MAX, COUNT, STDEV, VARIANCE, AVG

SUM on a nullable expression

A derived table

Rowset function

Another view

UNION

Subqueries, outer joins, self joins

Full-text predicates like CONTAIN or FREETEXT

COMPUTE or COMPUTE BY

Cannot include order by in view definition

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

rio_briones (1/20/2009)
View definition cannot contain the following
TOP
Text, ntext or image columns
DISTINCT
MIN, MAX, COUNT, STDEV, VARIANCE, AVG
SUM on a nullable expression
A derived table
Rowset function
Another view
UNION
Subqueries, outer joins, self joins
Full-text predicates like CONTAIN or FREETEXT
COMPUTE or COMPUTE BY
Cannot include order by in view definition

I'm pretty sure it can contain a COUNT... otherwise, averages in indexed views would not be possible. Everything else is pretty much right. And, to be clear, that list isn't for what can't go into a view... it's a list of things that can't go into an indexed view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jeff Moden (1/20/2009)
As a sidebar, there isn't really an advantage to having such an indexed view. The underlying indexes on the table are the only advantage. Indexed views become an advantage usually when there is some form of aggregation. I wouldn't bother trying to index this view as the only thing it really accomplishes is slowing down inserts and updates.

Have to go with the standard "it depends". Yes, if you have aggregates, it helps as the data is materialized on disk by creating the clustered index on the view.

I also use indexed views over my ODS tables to provide me the most current data from the tables. Works nicely.

Also, Jeff, you got to preview another use for indexed views that every one on SSC gets to read about on January 27th. So, back to "it depends".

Question for the OP, what are you trying to accomplish with the indexed view?