Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bb46970
Starting Member
2 Posts |
 Posted - 2013-09-03 : 20:45:17
|
| Hi,I have a T-SQL UDF that takes a single character parameter and returns a string. The UDF is deterministic. I have a view that includes a computed column whose value is based on the UDF. I created a clustered index on the view. The ONLY column in the view is an IDENTITY column.This works fine on SQL Server Express 11.0.2100, running on Windows 8.SQL Server Express 11.0.2218, running on Windows 7, produces:Msg 10133, Level 16, State 1, Line 1Cannot create index on view "<view name>" because function "<function name>" referenced by the view performs user or system data access.This is the exact same script run on two different machines.Thanks,David |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-09-03 : 23:40:00
|
| So what's in the UDF? |
 |
|
|
bb46970
Starting Member
2 Posts |
Posted - 2013-09-04 : 09:41:59
|
BEGIN RETURN CASE WHEN @Input = 'A' THEN 'Require Alt Ship' WHEN @Input = 'B' THEN 'Billing' WHEN @Input = 'H' THEN 'Do not display' WHEN @Input = 'M' THEN 'Moved' WHEN @Input = 'N' THEN 'Non-mailing' WHEN @Input = 'O' THEN 'Other' WHEN @Input = 'S' THEN 'Shipping' WHEN @Input = 'U' THEN 'Do not use' WHEN @Input = 'V' THEN 'Vacant' WHEN @Input = '?' THEN '<Unset>' ELSE 'INVALID ENTRY' ENDENDquote:
Originally posted by LoztInSpace
So what's in the UDF?
|
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-09-04 : 10:59:16
|
| Strange.Anyway, how would your identity column correlate to this data?Maybe remove the UDF and have a view (or better still a pre-built table) asselect 'A' as code, 'Require Alt Ship' as description union allselect 'B', 'Billing' ...Then just join to it. It will be a lot faster. |
|
|
|
|
|
|
Cannot create index on view...