views:

3053

answers:

3

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 must be uniqueidentifier type. the best way (i think ...) is to use 'newid()' function. the problem is I can't use the 'newid()' inside this type of function:

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

+1  A: 

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

SQLMenace
...only if the clustered index is based upon it...
Mitch Wheat
yes, unfortunately some people make it a PK without realizing it is clustered....believe me I have seen many such instances
SQLMenace
A: 

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

João Vieira
He wants a datatype of uniqueidentifier not int
SQLMenace
+5  A: 

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: http://omnibuzz-sql.blogspot.com/2006/07/accessing-non-deterministic-functions.html

-don

Don Dickinson
thanx!, you are right.
Florjon