CLR and Indexed view

Question

Post reply

CLR and Indexed view

fayilt

Right there with Babe

Points: 727
More actions
June 5, 2011 at 9:53 am

#237390

I am trying to create a non-clustered index an a CLR-derived column in an indexed view, but it doesn't allow me. Here is the error message:
Cannot create index or statistics 'Ix_IndexedView_One' on view 'vwIndexedViewTest' because cannot verify key column 'idPlusOne' is precise and deterministic. Consider removing column from index or statistics key, marking column persisted in base table if it is computed, or using non-CLR-derived column in key.
Books online says that CLR derived columns can't be part of the clustered index, but doesn't mention non-clustered indexes.
Here is the test script:
The table:
create table [dbo].[IndexedViewTest](
[id] [int] identity(1,1) not null,
[name] [varchar](50) null
constraint PK_id primary key clustered
(
[id] asc
)
)
CLR function:
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true,IsPrecise=true, DataAccess=DataAccessKind.None, SystemDataAccess=SystemDataAccessKind.None)]
public static SqlInt32 AddOne(SqlInt32 number)
{
return number + 1;
}
Indexed view:
create view dbo.vwIndexedViewTest
with schemabinding
as
select id, name, dbo.AddOne(1) as idPlusOne
from dbo.IndexedViewTest
go
create unique clustered index Ix_IndexedView on vwIndexedViewTest(id)
Now trying to create an index on the CLR column:
create index Ix_IndexedView_One on vwIndexedViewTest(idPlusOne) --getting an error
You can definitely create indexes on calculated CLR columns as long as they are persisted. As you can see, all settings for the CLR are correct, I don't see why SQL Server wouldn't be able to create an index on that column..

Viewing 4 posts - 1 through 3 (of 3 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Seems to me to be the way to use a clr fn just to actually use one.

Why don't you use a regular tsql derived column ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

fayilt Right there with Babe Points: 727 More actions · Answer 2

Thanks for your reply.

I need to parse a string using regular expressions, .NET allows to do it easily.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

It is best to always post your exact question documented with your actual code.

The more actual info we get, the better advise we can give you.

If you would have posted the actual case, my response for sure wouldn't have been the one I replied.