NewID () inside a UDF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

NewID () inside a UDF

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-08-10 : 08:09:22

Paul writes "I was trying to use the NewID() function inside of a User Defined Function but I get the error message: Invalid use of 'newid' within a function. Is there another way of generating a new uniqueidentifier inside of a UDF?"

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2008-04-11 : 06:14:29

This post is ancient history. Though I'm wondering the same thing still

Henri
~~~~
There's no place like 127.0.0.1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 07:12:01

You can have the NewID value passed to the function as one of the function input parameters.

E 12°55'05.25"
N 56°04'39.16"

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2008-04-11 : 07:21:10

So simple! Brilliant!

Henri
~~~~
There's no place like 127.0.0.1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 07:25:26

You're welcome.

E 12°55'05.25"
N 56°04'39.16"

Imukai
Starting Member

29 Posts

Posted - 2008-04-11 : 07:37:37

Well now the question has sparked curiosity.. why can't you use NewID within a UDF? Just "one of those things" or is there an actual reason for it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 08:16:24

SQL Server 2000 has a specific prohibition against allowing you to use anything non-deterministic in creating a user-defined function.
Non-deterministic = the result changes every time you run it, which is essentially the definition of what NEWID() does.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 09:04:15

Also note that you can use those non-deterministic functions directly in a function in SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-11 : 09:25:05

create view v_newid as
select newid() as newid
go

create function fn_newid() returns uniqueidentifier as
begin
    declare @x uniqueidentifier
    select @x = newid from v_newid
    return @x
end
go

select dbo.fn_newid()

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2008-04-11 : 09:35:40

thx, Ryan.

Now comes a hard question: Good I do a similar thing with NEWSQUENTIALID() ?

I'd like to adapt NEWSQUENTIALID() but thousand lines of code are written like SET @guid = NEWID() and than inserting records and such. I'd like to replace SET @guid = NEWSQUENTIALID(), or something like that. I made some workaround with OUTPUT parameter and a tblGUID, but that doesn't perform at all.

Henri
~~~~
There's no place like 127.0.0.1

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-04-11 : 10:04:44

The short answer is that if you know that your functions are deterministic, you can move them to where they'll be most efficient in the execution plan without worrying about what it does to the semantics.

There is actually a well known way of making non-deterministic UDFs that call NEWID() and so on: wrap the call to NEWID() in a view.


CREATE VIEW dbo.NewID_V AS SELECT NEWID() AS n

CREATE FUNCTION dbo.NewID_F() RETURNS uniqueidentifier 
BEGIN
  RETURN (SELECT n FROM dbo.NewID_V)
END

So what would happen if you used it? Let's try 1000 trials of selecting a random sample of 100000 rows and counting how many trials resulted in a given number of rows:


CREATE TABLE #T (i int PRIMARY KEY, ct int NOT NULL)

DECLARE @i int
SET @i = 0
SET NOCOUNT ON
WHILE @i < 1000 BEGIN
  ;WITH N AS (
     SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
      UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    )
  INSERT INTO #T
  SELECT @i, COUNT(*) AS ct
  FROM N AS N0, N AS N1, N AS N2, N AS N3, N AS N4
  WHERE dbo.NewID_F() LIKE 'A%'

  SET @i = @i + 1
END
SET NOCOUNT OFF

SELECT ct, COUNT(*) AS cct
FROM #T
GROUP BY ct
WITH ROLLUP
ORDER BY ct

DROP TABLE #T

Why do I get a result like that? Because the query plan happens to have put a call to dbo.NewID_F() to filter each constant coming from one usage of the CTE, but not from the others. You might get completely different results.

Edit:

Oh boy, am I slow at writing these things!
(I started before Ryan had added his response)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-11 : 10:29:44

Well your answer was far more interesting, Arnold

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources