SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 NewID () inside a UDF
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/10/2001 :  08:09:22  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

Netherlands
273 Posts

Posted - 04/11/2008 :  06:14:29  Show Profile  Visit henrikop's Homepage  Reply with Quote
This post is ancient history. Though I'm wondering the same thing still

Henri
~~~~
There's no place like 127.0.0.1
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 04/11/2008 :  07:12:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
273 Posts

Posted - 04/11/2008 :  07:21:10  Show Profile  Visit henrikop's Homepage  Reply with Quote
So simple! Brilliant!

Henri
~~~~
There's no place like 127.0.0.1
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 04/11/2008 :  07:25:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You're welcome.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Imukai
Starting Member

29 Posts

Posted - 04/11/2008 :  07:37:37  Show Profile  Click to see Imukai's MSN Messenger address  Reply with Quote
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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 04/11/2008 :  08:16:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 04/11/2008 :  09:04:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 04/11/2008 :  09:25:05  Show Profile  Reply with Quote

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.
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
273 Posts

Posted - 04/11/2008 :  09:35:40  Show Profile  Visit henrikop's Homepage  Reply with Quote
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
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 04/11/2008 :  10:04:44  Show Profile  Reply with Quote
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



ct	cct
NULL	1000
0	527
10000	345
20000	105
30000	19
40000	4


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)


Edited by - Arnold Fribble on 04/11/2008 10:08:38
Go to Top of Page

RyanRandall
Flowing Fount of Yak Knowledge

United Kingdom
1074 Posts

Posted - 04/11/2008 :  10:29:44  Show Profile  Reply with Quote
Well your answer was far more interesting, Arnold

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000