Why does "DEFAULT" is causing syntax problem-CodePudding

I'm new in using SQL Server and right now I'm trying to change "Id" column from default ID to GUID. When using this code

ALTER TABLE dbo.Bookings ALTER COLUMN Id UNIQUEIDENTIFIER DEFAULT NEWID();

It gets me an error

Incorrect syntax near 'DEFAULT'

And I don't understand where is the syntax problem. Can anyone point it out what is causing this error?

CodePudding user response：

ALTER TABLE dbo.Bookings
ALTER COLUMN id UNIQUEIDENTIFIER ;

ALTER TABLE dbo.Bookings
ALTER id SET DEFAULT  NEWID();

Its possible using the alters separately.

Its the best to backup current table to avoid potential data loss.

CodePudding user response：

You should use the add constraint syntax:

ALTER TABLE dbo.Bookings ADD CONSTRAINT DF_dbo_Bookings_Id DEFAULT(NEWID()) FOR Id;

Also, if your table have the primary key clustered on that Id column, you could use the NEWSEQUENTIALID for creating unique identifiers that will have less impact on your writes:

ALTER TABLE dbo.Bookings ADD CONSTRAINT DF_dbo_Bookings_Id DEFAULT(NEWSEQUENTIALID()) FOR Id;

Example, on dbfiddle