ansaurus

Question

TSQL: Why cannot use newid() inside a scalar-valued function

Answer 1

+3 A:

In SQL 2000 you couldn't use non-deterministic functions inside other functions.

The best way of doing this is to use a subquery inside your outer query. It will perform better than a scalar function anyway, as per my blog at: http://msmvps.com/blogs/robfarley/archive/2009/12/05/dangers-of-begin-and-end.aspx

Rob Farley 2009-12-21 05:35:52

Functions are not inherently bad, just abused. Multi-valued table valued ones, or scalars with table access (in SELECT clause, say) is what people misuse. But wrapping a calculation, for example, can only be a good thing. Speed is not everything especially at the expense of maintenance

gbn 2009-12-21 07:25:22

Hi gbn. Sorry, but even if you have just wrapped a calculation into a function, if it's a scalar function or multi-line table-valued one, SQL will execute it in a new context, which becomes very costly. You should always strive to make simplifiable functions so that the Query Optimizer can do its job better. A multi-valued table-valued function is just fine, so long as you haven't used BEGIN/END (which makes it procedural, not simplifiable).

Rob Farley 2009-12-21 21:29:07

Answer 2

A:

The only way i have found to do this is by creating a view that selects the NEWID() value, and then use this view inside the function.

astander 2009-12-21 05:39:17

Answer 3

A:

Why not pass in NEW_ID() as a parameter all the time? It's overkill to use a view, IMHO

ALTER FUNCTION dbo.MyFunc (
    @param1 int, 
    @param2 datetime,
    @GUID uniqueidentifier
)
RETURNS int
AS
....



SELECT dbo.MyFunc (1, '20091221', NEWID())

gbn 2009-12-21 07:22:08

ansaurus

tags:

views:

answers:

TSQL: Why cannot use newid() inside a scalar-valued function

related questions