SQL: Order randomly when inserting objects to a table

Question

I have an UDF that selects top 6 objects from a table (with a union - code below) and inserts it into another table. (btw SQL 2005)

So I paste the UDF below and what the code does is:

• selects objects for a specific city and add a level to those (from table Europe)
• union that selection with a selection from the same table for objects that are from the same country and add a level to those
• From the union, selection is made to get top 6 objects, order by level, so the objects from the same city will be first, and if there aren't any available, then objects from the same country will be returned from the selection.

And my problem is, that I want to make a random selection to get random objects from table Europe, but because I insert the result of my selection into a table, I can't use order by newid() or rand() function because they are time-dependent, so I get the following errors:

• Invalid use of side-effecting or time-dependent operator in 'newid' within a function.
• Invalid use of side-effecting or time-dependent operator in 'rand' within a function.

UDF:

ALTER FUNCTION [dbo].[Objects] (@id uniqueidentifier)
RETURNS @objects TABLE
( 
    ObjectId uniqueidentifier NOT NULL,
    InternalId uniqueidentifier NOT NULL
)
AS
BEGIN 
    declare @city varchar(50)
    declare @country int

    select @city = city,
      @country = country
    from Europe
    where internalId = @id

    insert @objects
    select @id, internalId from
    (
     select distinct top 6 [level], internalId from
     (
      select top 6 1 as [level], internalId
      from Europe N4
      where N4.city = @city
      and N4.internalId != @id   

      union select top 6 2 as [level], internalId
      from Europe N5
      where N5.countryId = @country
      and N5.internalId != @id   

     ) as selection_1
     order by [level]
    ) as selection_2
    return
END

If you have fresh ideas, please share them with me.

(Just please, don't suggest to order by newid() or to add a column rand() with seed DateTime (by ms or sthg), because that won't work.)

Answer 1

Perhaps you could take advantage of the guids by adding a position parameter to your inputs and then passing in a randomly generated value and then ordering by Substring(internalID, @Random,1)

Answer 2

What version of the database server are you using?

In SQL Server 2005 you can use rand with getdate as seed, and the function becomes indeterministic.

In earlier versions you can't have indeterministic functions, and you would have to use a stored procedure instead.

Answer 3

I found a good solution myself and I thought it might be handy to share it :)

DECLARE @seed1 int
DECLARE @seed2 int

SET @seed1 = DATEPART(SECOND,GETDATE())

SET @seed2 = DATEPART(MILLISECOND,GETDATE())

SELECT TOP 10 [Column1], [Column2] FROM [TABLE]

ORDER BY ROW_NUMBER() OVER (ORDER BY [KeyColumn]) * seed2 % seed1

I think it's simple enough and it's quite handy