Creating a Primary Key with NEWID()

Question

I have a query that I used pull data from several different tables each night and this pull goes into an upsert table that is loaded to our cloud server. I am trying to set some type of unique identifer/primary key for each row, but I am having issues with it.

SELECT SUBSTRING(CAST(NEWID() AS varchar(38)), 1, 16)

Whenever I rerun the query, it changes the value of the NEWID() each time, so it's loading duplicates into my table every night instead of updating the records. Is there anyway I can keep newid() as static value every time I run the query?

Thanks, Rachel

sorry not sure I get wahat you mean. Are you saying you are inserting into multiple tables but want to same uniqueindetifier for each table? — Fred, Oct 02 '13 at 15:54
I am pulling from multiple tables to insert into one specific table that is upserted. I want a unique id for the upsert table but I have no really good way to uniquely identifying each row. — user2839262, Oct 02 '13 at 17:50

score 2 · Accepted Answer · answered Oct 02 '13 at 15:51

NEWID() is by design returning unique (to your computer) GUID values. Whenever you run

SELECT NEWID()

You will see a different value.

It sounds like your UPSERT code needs to combine data from the source tables into a primary key that you can reliably use in future to determine if the given row needs to be inserted or updated.

score 0 · Answer 2 · answered Oct 02 '13 at 16:01

NEWID() returns a unique value everytime it is called. It isn't the best choice for a primary key and most data professionals perfer using an int identity for the clustered, primary key if possible.

In your case neither solution will work perfectly since both identities and NEWID() return new values. What you need to do is figure out what columns determine if a row is a duplicate and needs to be updated instead of inserted. To do this use the merge statement.

Creating a Primary Key with NEWID()

2 Answers2