How to use NEWID() in SQL Server User Defined Functions (UDF)

Using NEWID() in an SQL Server user defined function returns an error message:
Msg 443, Level 16, State 1, ...
Invalid use of a side-effecting operator 'newid' within a function.

Here is a trick to get round this. Create a simple view that returns one row containing one column:
CREATE VIEW dbo.vu_newid
AS
    SELECT    NEWID() as mynewid


Test this out to prove it is working and returns a different NEWID each time.

Then in your function, use the view to return a NEWID or set of NEWIDs as follows:

To populate a variable:
DECLARE @mynewid uniqueidentifier
...
SELECT @mynewid = mynewid FROM dbo.vu_newid

To use in ORDER BY:
SELECT mt.*, vid.mynewid as mynewid
FROM   mytable mt
  CROSS JOIN dbo.vu_newid vid
ORDER BY vid.mynewid

To use in ROW_NUMBER():
SELECT ROW_NUMBER() OVER(ORDER BY vid.mynewid) as rn,
       mt.*
FROM   mytable mt
  CROSS JOIN dbo.vu_newid vid

Happy coding!

MS Sql Server Queries: Grouping with ROLLUP or CUBE

Working on a GROUP BY query I decided I needed ROLLUP. That bit is pretty easy:
SELECT X, Y, COUNT(*) AS vol 
FROM Tbl 
GROUP BY X, Y WITH ROLLUP
The sub-total lines contain NULL in the X and Y columns. This all applies to CUBE as well.

However, in this case, X and Y contained NULLs, and that makes the output confusing. I remembered there was a way to change the X and Y values in the sub-total lines and had to google for it. Here is the syntax as a reminder to self!

SELECT   CASE WHEN (GROUPING(X) = 1) THEN 'ALL' ELSE X END AS X,
         CASE WHEN (GROUPING(Y) = 1) THEN 'ALL' ELSE Y END AS Y,
         COUNT(*) AS vol
FROM     Tbl
GROUP BY X, Y WITH ROLLUP

The GROUPING(colname) function returns 1 when on a sub-total line and 0 when on a data line.

One little caveat! Where I have put the value 'ALL' - that needs to be the same datatype as the column you are testing. Alternatively, convert/cast the column as a string.

A more standards-compliant syntax for the GROUP BY ... ROLLUP clause, which is supported by later versions of MS SQL Server, is:

GROUP BY ROLLUP(X, Y)

Reminder:

ROLLUP gives you sub-totals and a grand-total based on the GROUP BY hierarchy.

CUBE gives you sub-totals and a grand-total based on every possible combination in the GROUP BY list.