question

PerWidell avatar image
PerWidell asked

Randomisefunction in table function

I try to cross join a tablefunction that randomise a name, but I get the same randomised name for all rows. It looks like the function gets cached and not run by each row? I heard that a function can be Deterministic or NonDeterministic but I cant find any way to set that myself? My code is like this: select top 10 T.FirstName, T.LastName, LN.name as NewLastName, T.Gender from MyTable as T cross apply dbo.ScrambleName(T.Gender) as LN My function have code like this and use a view to get the newid() ALTER FUNCTION [dbo].[ScrambleName] (@Gender) RETURNS TABLE AS RETURN ( Select top 1 name from (Select name From slumpnamn where gender=@gender) n(name) Where 1 = 1 order by (Select newID from GetNewID) )
functiontable-valuedrandom-tag3
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
It looks like you have a view which returns one row one column of uniqueidentifier type just to bypass the user-defined function restriction (cannot simply use ***order by newid()*** inside of the function body). The view is probably defined like this: create view dbo.GetNewID as select newid() [NewID]; To check whether the function has been created as deterministic or not, you can check it via select objectproperty(object_id('[dbo].[ScrambleName]'), 'IsDeterministic'); This decision is made by the engine at the time the function is created, there is no good way of setting it outside of creating a table-valued function with schemabinding, but this will have an opposite effect anyway, and will not help in your situation. What should work is the following (I am sure there are other ways, but the solution below will work): Alter the function to accept an extra dummy parameter (and yes, please, get rid of the sub-select and where clause): alter function dbo.ScrambleName(@gender char(1), @unused varchar(50)) returns table as return ( select top 1 [Name] from dbo.slumpnamn where Gender = @gender and @unused is not null order by (select [NewID] from GetNewID) ); go Once the function is altered, you can use it to generate random names and now you have a full control of the situation. For example, say you would prefer if 2 different rows have the same LastName value then they should get same randomized value. The rows with different last names will be randomized to the different values as expected. In this case, you can run the select like this: select t.FirstName, t.LastName, LN.[Name] NewLastName, t.Gender, from MyTable t cross apply dbo.ScrambleName(t.Gender, t.LastName) LN; If you prefer that the names are randomized in such a way that the rows with the same last name still get different randomized names then you can randomize the second dummy parameter (but not with newid() though, that will not work). Suppose your table named MyTable has ID column which is an ever-increasing integer and the ID values are unique across all rows. Then you can run this select: select t.FirstName, t.LastName, LN.[Name] NewLastName, t.Gender, from MyTable t cross apply dbo.ScrambleName(t.Gender, cast( t.ID as varchar(50))) LN; Hope this helps, Oleg
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.