Server version is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Been having some challenges with the use of an indexed view. We've worked
most of them out, but this one hit us today, and I haven't got a clue.
Dropping the view instantly made the error go away, so we're positive the
error is caused by the presence of the view.
Came up dry in the MS KB, with one reference in the fixlist for SQL 7 SP2. A
google search turned up a couple of unanswered threads, where the core cause
was the table rowsize obviously too big. Neither of those appear to apply in
this case.
ERROR MESSAGE:
Msg 8618, Level 16, State 2, Line 1
Warning: The query processor could not produce a query plan from the
optimizer because the total length of all the columns in the GROUP BY or
ORDER BY clause exceeds 8000 bytes.
CAUSED BY THIS UPDATE QUERY:
update tblAnswer
set answer = null
where row_ordinal = 5559208
CREATE TABLE [dbo].[tblAnswer] (
[ROW_ORDINAL] [decimal](38, 0) IDENTITY(1,1) NOT NULL,
[SurveyOrdinal] [int] NOT NULL,
[UserID] [varchar](40) NOT NULL,
[QuestionNumber] [nchar](50) NOT NULL,
[Answer] [nchar](2000) NULL,
[SurveyID] [int] NOT NULL,
[EntryDate] [datetime] NOT NULL,
[Change] [bit] NULL,
CONSTRAINT [PK_tblANSWER] PRIMARY KEY NONCLUSTERED
(
[ROW_ORDINAL] ASC
) ON [PRIMARY]
) ON [PRIMARY]
The table tblAnswer contains 554,127 rows as of today.
Because of the high volume of hits against the Answer column, and the
(unfortunate, but necessary) declaration of that column as 'nchar 2000', as
well as the high incidence of NULL values in that column, which we
(historically) have filtered since we don't really care about non-responses,
I opted to create an indexed view on a subset of the columns, where Answer
is not null. Note, also, that I'm trimming the 'nchar 2000' Answer column to
a 'varchar 400', so that the column can be used in the index.
This is the view definition:
set ansi_nulls on
go
set ansi_padding on
go
set ansi_warnings on
go
set arithabort on
go
set concat_null_yields_null on
go
set quoted_identifier on
go
set numeric_roundabort off
go
set nocount on
go
create view vwAnswerCurrent
with schemabinding
as
select row_ordinal, userid, questionnumber, rtrim(left(answer,400)) as
answer, surveyid
from dbo.tblAnswer
where change is null
go
create unique clustered index idxRowOrd
on vwAnswerCurrent (row_ordinal)
go
create nonclustered index idxUSQ
on vwAnswerCurrent (userid, surveyid, questionnumber)
go
create nonclustered index idxAnswer
on vwAnswerCurrent (answer)
go
In addition, because of a high volume of data access to this table, and the
presence of the view, via ASP.NET v1.1 code, we opted to SET ARITHABORT = ON
for the entire database, using 'ALTER DATABASE SET ARITHABORT = ON'. This
resolved our access issues via ASP.NET, and no other issues occurred since
enabling the ARITHABORT flag early last w
. Until Today.
So.. my question is: What the heck am I missing, or misunderstanding, about
the use of indexed views that's not working here.
(p.s. Don't ask why the PK on Row_Ordinal is not clustered. I don't know,
but if I had my choice, it would be! But, it's not, in the table, but it is,
in my view declaration. <g> )"Lawrence Garvin" <onsitech@.news.postalias> wrote in message
news:u%23bVpMeJGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Server version is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Been having some challenges with the use of an indexed view. We've worked
> most of them out, but this one hit us today, and I haven't got a clue.
> Dropping the view instantly made the error go away, so we're positive the
> error is caused by the presence of the view.
> Came up dry in the MS KB, with one reference in the fixlist for SQL 7 SP2.
> A google search turned up a couple of unanswered threads, where the core
> cause was the table rowsize obviously too big. Neither of those appear to
> apply in this case.
>
> ERROR MESSAGE:
> Msg 8618, Level 16, State 2, Line 1
> Warning: The query processor could not produce a query plan from the
> optimizer because the total length of all the columns in the GROUP BY or
> ORDER BY clause exceeds 8000 bytes.
> CAUSED BY THIS UPDATE QUERY:
> update tblAnswer
> set answer = null
> where row_ordinal = 5559208
> CREATE TABLE [dbo].[tblAnswer] (
> [ROW_ORDINAL] [decimal](38, 0) IDENTITY(1,1) NOT NULL,
> [SurveyOrdinal] [int] NOT NULL,
> [UserID] [varchar](40) NOT NULL,
> [QuestionNumber] [nchar](50) NOT NULL,
> [Answer] [nchar](2000) NULL,
> [SurveyID] [int] NOT NULL,
> [EntryDate] [datetime] NOT NULL,
> [Change] [bit] NULL,
> CONSTRAINT [PK_tblANSWER] PRIMARY KEY NONCLUSTERED
> (
> [ROW_ORDINAL] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
For what it's worth,
First, I have no idea what's really going on.
Second, this does not repro on SQL 2005.
Third the cause is QuestionNumber, not Answer.
[QuestionNumber] [nchar](50) NOT NULL,
Is a highly questionable choice of data type. Answer is variable length,
but QuestionNumber will always occupy 100 bytes on the page, no matter how
much data is in it.
Changing QuestionNumber to
[QuestionNumber] [nvarcharchar](50) NOT NULL,
Resolves the issue.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O9LaaVfJGHA.1132@.TK2MSFTNGP10.phx.gbl...
> For what it's worth,
> First, I have no idea what's really going on.
> Second, this does not repro on SQL 2005.
This is good news. :-)
> Third the cause is QuestionNumber, not Answer.
Interesting!
> [QuestionNumber] [nchar](50) NOT NULL,
> Is a highly questionable choice of data type.
Yes, well, unfortunately, the /schema/ is not something I have a lot of
choice in the matter about. Whether the field needs to be Unicode, or
whether the field needs to be fixed length, are both points I'd probably
agree with you in a debate. Unfortunately, it's what I have to work with.
> Answer is variable length,
Actually, answer is 'nchar 2000' -- also fixed length.
The history of these column specs is a function of the -source- database,
which is an Oracle database on the back-end of a web front-end. The SQL
Server is being used for analysis and reporting.
> but QuestionNumber will always occupy 100 bytes on the page, no matter how
> much data is in it.
> Changing QuestionNumber to
> [QuestionNumber] [nvarcharchar](50) NOT NULL,
> Resolves the issue.
Thank you for the tip. I'll try reproducing this 'fix' on my development
server.
> David
>|||"Lawrence Garvin" <onsitech@.news.postalias> wrote in message
news:%23iAAGRqJGHA.2668@.tk2msftngp13.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:O9LaaVfJGHA.1132@.TK2MSFTNGP10.phx.gbl...
>
> This is good news. :-)
>
> Interesting!
>
> Yes, well, unfortunately, the /schema/ is not something I have a lot of
> choice in the matter about. Whether the field needs to be Unicode, or
> whether the field needs to be fixed length, are both points I'd probably
> agree with you in a debate. Unfortunately, it's what I have to work with.
>
> Actually, answer is 'nchar 2000' -- also fixed length.
>
No nullable char or nchar are actually variable length columns. That's a
SQL Server engine technical detail, but I suspect it's somehow at the root
of the problem here.
David|||The view that you are trying to create is not valid for an indexed
view.
Note the error from database engine :
Warning: The optimizer cannot use the index because the select list of
the view contains a non-aggregate expression.
Cheers,
Arun|||Gee.. thanks, Arun.
Do you have a WHY to go along with your statement?
The view -is- created, and it -is- usable, and it -did- cause errors when
certain other issues were not set correctly, -and- David's suggestion to
make questionnumber a nvarchar field -did- eliminate the reported error
message.
<arun.ns@.gmail.com> wrote in message
news:1138795905.429665.271770@.g43g2000cwa.googlegroups.com...
> The view that you are trying to create is not valid for an indexed
> view.
> Note the error from database engine :
> Warning: The optimizer cannot use the index because the select list of
> the view contains a non-aggregate expression.
>
> Cheers,
> Arun
>
)