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.
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
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
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