Non-deterministic function in a trigger

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Non-deterministic function in a trigger

Author

Topic

bsandell
Starting Member

3 Posts

Posted - 2004-10-01 : 22:50:34

I have a user defined function that may be called multiple times in the same query - here's a rough example

select col1, get_random_number(), col2, col3, get_random_number() from
table1

The problem I am having is that I need to have both calls to the user
defined function return the same value if it is called multiple times
for one row. The values returned from the query would be somethig like
this -

John 23456 Engineer Junior 23456
Frank 33333 Engineer Senior 33333
Tom 80331 Engineer Junior 80331

I have thought about having two seperate udf's like
get_random_number() and use_last_random_number(), but I'm not sure how
to store the value of the get_random_number() to be used by
use_last_random_number(). These udfs are used in bulk load requests,
so performance is a big issue. Any suggestions would be greatly
appreciated. I know the example is kind of silly, but I think it
illustrates the problem.

Someone had suggest this -
select col1, randNum, col2, col3, randNum from
(select coll, get_random_number() as randNum, col2, col3
from table1) as T1

which seemed great, but doesn't work. The optimizer appears to be choosing to invoke the user defined function multiple times.

Any suggestions?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-01 : 23:08:51

I have played with this quite a bit and may be able to help but I'd need to know exactly what your function is because you can't call rand() in a function so what about your UDF is non-deterministic?

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

bsandell
Starting Member

3 Posts

Posted - 2004-10-01 : 23:54:35

It's a user defined function that calls an extended stored procedure which returns a random number. We have a standard api call that we use for random number generation that actually gets the number from a hardware device. Please let me know if you need any more info. Thanks.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-02 : 00:14:56

No promises but try get_random_number() + Length(Left(NEWID(),0))

That just adds a 0 to the result of get_random_number() but since newid() is a built-in non-deterministic UDF, SQL may build a query plan that does not call it multiple times. Even if that works, it's only luck.

Or also try:

create view my_rand as
select dbo.get_Random_number() myrand

Then use

select col1, randNum, col2, col3, randNum from
(select coll, myrand as randNum, col2, col3
from table1, my_rand) as T1

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

bsandell
Starting Member

3 Posts

Posted - 2004-10-02 : 00:43:04

I'll give that a try. Here's something that seems to work, but I haven't tested it to see the performance impact on a large bulk insert, what do you think?. Thanks very much for your help with this

ALTER TRIGGER [REGION_TEST1_INS_TRIG] ON [REGION_TEST1]
INSTEAD OF INSERT
AS
BEGIN
SELECT MASTER.DBO.GN_RNDM(16) AS [RAND_1],
inserted.*
INTO #temp
FROM inserted

INSERT INTO [REGION_TEST1_NEW]
([R_REGIONKEY],
[R_NAME],
[R_RAND_1],
[R_RAND_2 )
SELECT
[#temp].[R_REGIONKEY],
[#temp].[R_NAME],
[#temp].RAND_1,
[#temp].RAND_1
FROM #temp

DROP table #temp

END

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-02 : 01:00:50

I don't think that can work. #temp will not exist outside of the scope of the trigger execution.

You could also change col4 to be a computed column that equals col2.

alter mytable drop column col4
alter mytable add col4 as col2

You would have to SET ARITHABORT ON to insert into it though.

Or you could just

update mytable
set col4 = col2

after inserts.

In fact you might as well

alter mytable drop column col2
alter mytable drop column col4

alter mytable add col2 as MASTER.DBO.GN_RNDM(16)
alter mytable add col4 as col2

Then let it auto-populate and don't worry about doing it manually.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources