April 28, 2011 at 5:00 am
Hi
I am having a strange issue selecting data from an indexed view...
I have created an indexed view with two columns - 'DataValue' and 'EventFk'. 'DataValue' is nvarchar(max) and 'EventFk' is integer.
'DataValue' holds call number details in the format '123456.0', which I have converted to integer in the view, so that I can join this view to a table in another database, i.e.
[font="Courier New"]cast(cast(DataValue as decimal) as int) as 'CallNo'[/font]
I have successfully indexed the view, however I am having problems when it comes to selecting individual values from the view - for example:
[font="Courier New"]select * from dbo.vwMapToCLEOSSRSNEW where CallNo = 764266[/font]
is returning the following error:
[font="Courier New"]Error converting data type nvarchar to numeric.[/font]
If I then drop the index and change the view to return the top 99 percent of records, the problem does not occur, which initially led me to believe that there is a problem with the last 1% of records - however I have checked the data being returned by the view, and I can't see anything wrong - I have confirmed that everything being returned is numeric, and there are no NULL values.
I have posted my full code below, any help is appreciated!
Thanks
[font="Courier New"]--Create the view
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwMapToCLEOSSRSNEW] WITH SCHEMABINDING
AS
SELECT /*TOP 99 PERCENT*/ cast(cast(DataValue as decimal) as int) as 'CallNo',EventFk
FROM dbo.[EventData] a
INNER JOIN dbo.DataItem b ON a.EventDataId =b.EventDataFk
WHERE b.[EventDataFK] = a.EventDataID
AND DataKey = N'AuditCallRef'
GO
--create index
create unique clustered index ucidx_CallNo_EventFk on dbo.vwMapToCLEOSSRSNEW(CallNo,EventFk)[/font]
April 28, 2011 at 5:12 am
From the error, I would assume that you have a value that is not numeric stored in that column.
"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
April 28, 2011 at 5:45 am
'DataValue' comes from the table 'dbo.DataItem', which is nvarchar(max), but I am explicitly casting 'DataValue' to integer in the view. I have used the ISNUMERIC function to verify that everything being returned by the view is indeed numeric.
As I am selecting from the view, not the underlying table, I would expect to be able to use an integer value as my search condition without this error occurring, which is indeed the case when I tell the view to return only 99% of the records.
What's puzzling to me is why the following statement initially fails...
[font="Courier New"]select * from dbo.vwMapToCLEOSSRSNEW where CallNo = 764266[/font]
...but works when I tell the view to return the top 99% of records.
I am also able to select all records directly from the view.
April 28, 2011 at 6:12 am
Try
SELECT /*TOP 99 PERCENT*/ cast(cast(DataValue as decimal) as int) as 'CallNo',EventFk
FROM dbo.[EventData] a
INNER JOIN dbo.DataItem b ON a.EventDataId =b.EventDataFk
WHERE b.[EventDataFK] = a.EventDataID
AND DataKey = N'AuditCallRef'
AND DataValue LIKE '%[^0-9]%'
Will show if there is any non-numeric value in that column
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
April 28, 2011 at 6:17 am
Looking at ISNUMERIC()
SELECT ISNUMERIC('1.')
Returns True (or 1)
However
SELECT CAST('1.' AS INT)
Won't cast to an integer
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
April 28, 2011 at 6:35 am
Thanks for suggestions but still no luck I'm afraid. The same number of rows are being returned.
'DataValue' is initially being casted to decimal and then to integer, as nvarchar(max) can't be explicitly converted to int, so SELECT CAST(CAST('1.' AS DECIMAL) AS INT) would be ok. I had to use decimal as this is an indexed view and something like float is not allowed... perhaps I'm using the wrong data type... I'll try another data type.
May 9, 2011 at 8:44 am
FYI. Please see reply below (from MSDN) from Erland Sommarskog, SQL Server MVP. Thanks.
"Did you inspect the query plan to ensure that the view is actually being used?
When you run a query against a view, SQL Server expands the view definition and the optimizes the expanded query. If you have Enterprise, Developer or Datacentre Edition, the optimization includes matching the query against indexed views. But just because there is an indexed view does not mean that the optimizer will use it for one reason or another. And if you are on Standard or Expression edition, the optimizer will not even try.
However, there is a table hint you can apply, NOEXPAND as in:
select * from dbo.vwMapToCLEOSSRSNEW WITH (NOEXPAND) where CallNo = 764266
This hint prevents the expansion from happen, and since you seem to have created the view to work around this conversion problem, I suggest that you should always use the hint for this view, no matter which edition you have."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply