asked on

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

blandyuk

You have to SELECT the value from the function:

(select dbo.fncDot2LongIP('217.34.84.90') AS IPNum)

ktola

ASKER

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.

blandyuk

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?

ktola

ASKER

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.

ASKER CERTIFIED SOLUTION

Tim Humphries

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ktola

ASKER

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.