• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Why can't I set this computed column to Persisted?

I have a computed column in SQL Server 2008 with the following formula definition:

([dbo].[fncDot2LongIP]([IPAddress]))

Where IPAddress is another column in the table
When I try to set the Persisted flag to true, I am told that the column is not Deterministic - but that makes no sense to me given the function provided below.  I made sure not to use a Float or other such data types - what is wrong here?
FUNCTION dbo.fncDot2LongIP( 
	@IP VarChar(15) 
)
RETURNS BigInt
AS
BEGIN
	DECLARE @ipA BigInt, @ipB Int, @ipC Int, @ipD Int;
	DECLARE @thisPlace Int;
	SET @thisPlace = CHARINDEX('.',@ip);
	SET @ipA = SUBSTRING(@ip,0,@thisPlace);
	SET @ip = SUBSTRING(@ip,@thisPlace + 1, LEN(@ip));
	SET @thisPlace = CHARINDEX('.',@ip);
	SET @ipB = SUBSTRING(@ip,0,@thisPlace);
	SET @ip = SUBSTRING(@ip,@thisPlace + 1, LEN(@ip));
	SET @thisPlace = CHARINDEX('.',@ip);
	SET @ipC = SUBSTRING(@ip,0,@thisPlace);
	SET @ipD = SUBSTRING(@ip,@thisPlace + 1, LEN(@ip));
   
   RETURN ( @ipA * 256*256*256 ) + ( @ipB * 256*256 ) + ( @ipC * 256 ) + @ipD
END

Open in new window

0
ktola
Asked:
ktola
  • 3
  • 2
1 Solution
 
blandyukCommented:
You have to SELECT the value from the function:

(select dbo.fncDot2LongIP('217.34.84.90') AS IPNum)
0
 
ktolaAuthor Commented:
Thak you for your quick response.  I tried entering this as the Formula:
 (Select dbo.fncDot2LongIP(IPAddress) as IPNum)
As I get the following error:
 Subqueries are not allowed in this context. Only scalar expressions are allowed.
 
0
 
blandyukCommented:
mmm, just created a test table with an IP address varchar(15) and passed it into the function as a SELECT:

select dbo.fncDot2LongIP(tblTest.IPAddress) AS IPNum from tblTest

Worked perfectly. Can you post your SQL query so we can look at that?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ktolaAuthor Commented:
That would work without an issue - however I am using the statement as the formula in a computed column.  To test this out, simply create a table with 2 columns - IPAddress VarChar(15) and LongIP.  For LongIP, start with my base formula:
([dbo].[fncDot2LongIP]([IPAddress]))

Once the table is created, then try to insert an IPValue - this will work fine but the LongIP is virtual and you cannot persist the data.
I need to know why the funciton is considered to be non-deterministic.
0
 
Tim HumphriesDirectorCommented:
To use a UDF as a persisted column, you must use WITH SCHEMABINDING in the function definition.

Your fucntion works fine in this context if you do that. Simply place WITH SCHEMABINDING after the RETURNS clause when creating the function.
0
 
ktolaAuthor Commented:
That was it - thanks!  I thought the WITH SCHEMABINDING was just for table functions and fields like XML.  I did not realize you could use it for scalar returns as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now