Whats the idea with this deterministic stuff????

Question

Whats the idea with this deterministic stuff????

BenWard

SSCertifiable

Points: 5903
More actions
December 22, 2010 at 5:18 am

#148023

Hi Guys,
Just to be clear, I'm using SSMSEE to do what I'm doing as I'm a lazy so and so who can't be bothered to learn the SQL for it.
I've got a date column, and a scalar function that returns a char(8) value from a date, lets call it sMyDate and dbo.funDateThingy
I want a computed column to store this char(8) value for use in a unique compound key.
Clearly I need to use Is Persisted = Yes otherwise it wont store the data, but when I click yes to persisted and try to save it it tells me that the value is not deterministic.
what is deterministic and how do I make my column deterministic?
Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it has to do with your function...if you did not use WITH SCHEMABINDING in the definition, i'm sure the function is non-deterministic, and thus any column that uses the function as a calcuated column would be the same;

here's an example:

create function dbo.funDateThingy(@Thedate datetime)

returns char(8)

AS

BEGIN

RETURN CONVERT(CHAR(8),@Thedate,112)

END

GO

--returns 0/false

SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.funDateThingy'), 'IsDeterministic')

GO

ALTER function dbo.funDateThingy(@Thedate datetime)

returns char(8)

WITH SCHEMABINDING

AS

BEGIN

RETURN CONVERT(CHAR(8),@Thedate,112)

END

GO

--returns 1/true

SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.funDateThingy'), 'IsDeterministic')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BenWard SSCertifiable Points: 5903 More actions · Answer 2

Cool thanks.

The function does have schema binding but according to the supplied query is still not deterministic. is this because the scalar function calls a second scalar function? interestingly the function that is called by datethingy IS deterministic.

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

It's pretty likely you're using one of the non-deterministic calls within the function. Check here to validate that.

"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

BenWard SSCertifiable Points: 5903 More actions · Answer 4

aha! TVM!

I have discovered that I was using getdate....

unfortunately it is still reporting as non deterministic...

here is my function...

ALTER FUNCTION [dbo].[funMonthYear]

(

@date SMALLDATETIME

)

RETURNS char(8) WITH SCHEMABINDING

AS

BEGIN

DECLARE @dateToUse AS SMALLDATETIME

SELECT @dateToUse = @date

IF (@dateToUse IS NULL)

BEGIN

SELECT @dateToUse = '01 Jan 1900'

END

RETURN dbo.funMonthName(DATEPART(MONTH,@dateToUse)) + '-' + CAST(DATEPART(YEAR,@dateToUse) AS CHAR(4))

END

why it is still non deterministic? is it to do with the cast?

also, what IS deterministic? I dont understand why I need it in a persisted computed column. //edit - I just read the first part of that page. I understand now 🙂

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

BenWard SSCertifiable Points: 5903 More actions · Answer 5

AH HA!

I have fixedized it!

I have rebuilt everything from scratch and now it works 🙂

Thanks for your helps!

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------