Most of the time, you want to have a SQL result in a particular order. Occasionally you just don't care. Just now and then,you actually want to shake the dice and get your data back in a random order. I quite often like to get a random order so as to introduce a sort of  human randomness into strings. OK, I'm slightly unusual in my liking for databases and websites that produce emails and messages that seem to be hand-crafted, but there are several real-life commercial applications where a certain randomness in messages and strings is an advantage. When I was a child, I was sat down at a table by my father and made to write thank-you letters on Boxing Day, and whereas my siblings fretted, and chewed their pencils, I constructed several banks of phrases that would pop into thank-you letters. I'm afraid to confess that, in those far-off days, computers were referred to as 'Electronic Brains' and one had to travel to the metropolis to see one. I therefore used spinning tops and circular pieces of paper, with a nice Aunt-friendly phrase on each pie-slice. Eventually, of course, it occurred to me that all I needed was a conventional list and a dice.

Here is a SQL Server technique very much in the spirit of the dice. Obviously, the advantage of using a sequenced list from a table, over just picking from a list over and over again, via random numbers, is that you don't pick the same phrase twice.

Dear Aunt Matilda

Thank you for the socks. I will always cherish them and I will always cherish them.

isn't as endearing as

Dear Aunt Matilda

Thank you for the socks. I will always cherish them and they will always remind me of your sweet generosity to an undeserving nephew.

(Since writing this, Kristofer Andersson has come up with a much neater solution in a comment below )

The reason that I like using the CROSS APPLY technique is that you don't need to have any unique index in the result. Before you curl your lip in disdain, be clear that a derived table may indeed not have one, and so it is rather nice not to worry about stuffing in an identity field or whatever.

Here is a little routine that gives you a few excuses as to why your project is failing. (Data is taken from an old article I did way back)

Because of the assumption that functions are deterministic, you have to fool both the parser and the query optimizer in order to do this. The former is hoodwinked by slipping in a RAND() function embedded in a view, and the latter by passing a dummy parameter that is computed. It is easier to show via an example. Play about with it and I'd love to hear of an improvement to it.

/* we create our utility view which hoodwinks the function into accepting a random number. (note, this can be counter-productive */
IF OBJECT_ID (N'vRandomNumber ') IS NOT NULL
  DROP VIEW vRandomNumber
GO
CREATE VIEW vRandomNumber
      AS
SELECT RAND() AS RandomNumber
GO
      /*Now a very simple table-valued function that simply returns a row with a single column of a random number */
IF OBJECT_ID (N'Randomly') IS NOT NULL
  DROP FUNCTION dbo.Randomly
GO
CREATE FUNCTION dbo.Randomly(@Multiplier INT,@number INT)
RETURNS @random TABLE
  (
        number INT NOT NULL
  )
      AS
  -- body of the function
  BEGIN
  INSERT INTO @random (number)
    SELECT randomnumber*@multiplier
        AS [random]
    FROM vRandomnumber
  RETURN
  END
GO
      /* now let's create some data. We'll make a table of excuses and pick one row out of the table at random */
DROP TABLE #ExcusesForProjectFailure
GO
CREATE TABLE #ExcusesForProjectFailure
  (Excuse VARCHAR(255) )
INSERT INTO #ExcusesForProjectFailure (Excuse)
            SELECT   'unclear objectives'
  UNION ALL SELECT   'changing objectives'
  UNION ALL SELECT   'lack of developer training'
  UNION ALL SELECT   'insufficient resources'
  UNION ALL SELECT   'impossible schedules'
  UNION ALL SELECT   'unrealistic expectations'
  UNION ALL SELECT   'unclear roles and responsibilities'
  UNION ALL SELECT   'Insufficient feedback to stakeholders'
  UNION ALL SELECT   'corporate  politics'
  UNION ALL SELECT   'poor communication'
  UNION ALL SELECT   'personnel turnover'
  UNION ALL SELECT   'changing technology'
  UNION ALL SELECT   'constraining rules and regulations'
  UNION ALL SELECT   'lack of sponsorship'
  UNION ALL SELECT   'poor planning'
  UNION ALL SELECT   'unclear goals and objectives'

DECLARE @FinalExcuse VARCHAR(2000)
SELECT TOP 3 @FinalExcuse=COALESCE(@finalExcuse+', ',
                                   'It wasn''t my fault! It was ')
                          +excuse
  FROM #ExcusesForProjectFailure
      CROSS apply  dbo.randomly (1000,LEN(excuse))
  ORDER BY number
SELECT @finalexcuse
      /* if you add the second parameter that is never used, (len(excuse)) this fools the query optimiser into calling the function for every row. Try rewriting the code without this extra parameter just to see what happens, and compare the execution plans!*/
      

It wasn't my fault! It was changing objectives, Insufficient feedback to stakeholders, unclear roles and responsibilities

(1 row(s) affected)

It wasn't my fault! It was lack of developer training, insufficient resources, unrealistic expectations

It wasn't my fault! It was poor planning, impossible schedules, changing technology

---and so on '''