Menu
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. 
848
In SQL Server, you can use the NEWID() function to create a unique value. More specifically, it's an RFC4122-compliant function that creates a unique value of type uniqueidentifier. The value that NEWID() produces is a randomly generated 16-byte GUID (Globally Unique IDentifier).
Using NEWID in a CREATE TABLE statement. The following example creates the cust table with a uniqueidentifier data type, and uses NEWID to fill the table with a default value. In assigning the default value of NEWID() , each new and existing row has a unique value for the CustomerID column.
The key here is the NEWID function, which generates a globally unique identifier (GUID) in memory for each row. By definition, the GUID is unique and fairly random; so, when you sort by that GUID with the ORDER BY clause, you get a random ordering of the rows in the table.
Long story in short: NEWID() can generate duplicate value, however, the probability is one in a billion which is very negligible.
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
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
