uniqueidentifier and default value not supported?

I have a table that has an ID column which is the primary key and I have the type set to uniqueidentifier.  Now I want Sql Azure to populate this field for me with newid() however in the managment portal I can not enter anything into the Default Value slot.  So I tried to manually add the constraint to the column using managment studio.  It takes however when I go to actually insert a row it complains saying ID can not be null.  When I go back to the managment portal and look at the table it has <unsupported> in as the default value.

 

How can I get sql azure to generate a uniqueidentifier?  is this not supported?  if its not supported would it be bad to have a varchar(36) and populate that with newid() instead?

 

Thanks,

November 7th, 2011 8:10am

Hi

i just test it with default as NEWID() and its work OK

so can you try that?

pini

Free Windows Admin Tool Kit Click here and download it now
November 7th, 2011 10:47am

When i am on the managment portal and i try and click into the "default value" box it wont let me type anything in.   I just re-tried setting up a default constraint and it still does not work.

 

This works perfect under sql 2008 r2, just not Sql Azure, can you link your create scheme for the table you tried this on?

 

Thanks, 

November 7th, 2011 10:50am

create table testnewid (

calc as (newid()),

data int null

)

now - add the cluster index here:

alter table testnewid add contraint pk_testnewid primary key clustered

(

data desc

)

insert into testnewid (data) values ('ddd')

select * from testnewid

Free Windows Admin Tool Kit Click here and download it now
November 7th, 2011 11:04am

i don't see where your column type is  uniqueidentifier ...
November 7th, 2011 11:21am

hi it is not but if you put the NEWID method as default you will have the same value, i think.

do not you think?

Free Windows Admin Tool Kit Click here and download it now
November 7th, 2011 11:41am

no, you dont.... try my repro and you will see its not supported in sql azure with a column type as uniqueidentifier 
November 7th, 2011 11:42am

Hi,

I tried to reproduce the problem. Here're the results.

1. Created a table with default constraint for uniqueidentifier column(primary key) using T-SQL, and insert several data in Management Portal.
 
  - Management Portal shows "unsupported" for values in uniqueidentifier column,
    and "Default Value" is also "unsupprted" in design page.

     
    
  - From Management Studio to SQL Azure, values in uniqueidentifier column are listed correctly.

     

I guess this is just a Portal issue or limitation, so you can make SQL Azure to generate
a uniqueidentifier as a default value.

2. By the way, I created a table which has an uniqueidentifier column (not primary key, no default constraint), and insert several data.
  
   - In management Portal, values in uniqueidentifier column are listed as "unsupported".
   - From Management Studio to SQL Azure, values in uniqueidentifier column are listed correctly.

SQL Portal cannot display uniqueidentifier values as well as the above case.

Hope this helps.

Free Windows Admin Tool Kit Click here and download it now
November 7th, 2011 1:37pm

Thanks,

 

I had to drop the table and recreate it, but now it seems to be working, not sure what I did wrong last time.

November 7th, 2011 9:25pm

I have found a similar problem when porting a table to azure. Is there a way to modify the 'default value' value after the table is already created? unfortunately, sql is not my field so I am a bit stuck.

The column is 'siteGuid',  type is uniqueidentifer, and I'd like the default value to be NEWID()

any help that does not include rebuilding the whole thing would be appreciated.

Free Windows Admin Tool Kit Click here and download it now
December 9th, 2012 5:13am

You can use empty guid as default:

NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'

July 10th, 2013 9:20am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics