newid() inside sql server function

Question

I have to insert a fake column at the result of a query, which is the return value of a table-value function. This column data type must be unique-identifier. The best way (I think...) is to use newid() function. The problem is, I can't use newid() inside this type of function:

Invalid use of side-effecting or time-dependent operator in 'newid()' within a function.

Borodino

Functions are not allowed to contain non-deterministic operators. Can you post your function definition? – Epizoon

Answer 1

Here's a clever solution:

    create view getNewID as select newid() as new_id
    
    create function myfunction ()
    returns uniqueidentifier
    as begin
       return (select new_id from getNewID)
    end

That I can't take credit for. I found it here

Earthlight

Answer 2

You can pass NEWID() as a parameter to your function.

CREATE FUNCTION SOMEIDFUNCTION
(
    @NEWID1 as varchar(36), @NEWID2 as varchar(36)
)
RETURNS varchar(18)
AS
BEGIN
    -- Do something --
    DECLARE @SFID varchar(18)
    SELECT @SFID = 'DYN0000000' + LOWER(LEFT(@NEWID1,4)) + LEFT(@NEWID2,4) 
    RETURN @SFID
END
GO

Call the function like this;

SELECT dbo.SOMEIDFUNCTION(NewID(),NewID())

Lonnielonny

Answer 3

use it as a default instead

create table test(id uniqueidentifier default newsequentialid(),id2 int)

insert test(id2) values(1)

select * from test

NB I used newsequentialid() instead of newid() since newid() will cause pagesplits since it is not sequential, see here: Some Simple Code To Show The Difference Between Newid And Newsequentialid

Privileged

yes, unfortunately some people make it a PK without realizing it is clustered....believe me I have seen many such instances – Privileged

Invalid use of a side-effecting operator 'newsequentialid' within a function. – Girardo

Answer 4

You could use ROW_NUMBER function:

SELECT
(ROW_NUMBER() OVER (ORDER BY recordID) ) as RowNumber ,
recordID,
fieldBla1
FROM tableName

Find more information at http://msdn.microsoft.com/pt-br/library/ms186734.aspx

Sporting