Adding a uniqueidentifier column and adding the default to generate new guid
I have the following SQL command:
ALTER TABLE dbo.UserProfiles ADD ChatId UniqueIdentifier NOT NULL, UNIQUE(ChatId), CONSTRAINT "ChatId_default" SET DEFAULT newid()
I want to be able to make this column unique, and I want it to be able to generate a new guid every time a row is added to the table. This column is not an IDENTITY column because I already have one. This is something separate. How would I go about adding this column to a table with users already in it.
see this sample:
create table test (mycol UniqueIdentifier NOT NULL default newid(), name varchar(100)) insert into test (name) values ('Roger Medeiros') select * from test
for add a not null field on a populated table you need this.
alter table test add mycol2 UniqueIdentifier NOT NULL default newid() with values CREATE UNIQUE NONCLUSTERED INDEX IX_test ON dbo.test ( mycol ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Don't use newid() as default, instead use newsequentialid(). newid() creates a lot of fragmentation and that's bad for indexes.
As far as adding the new column to a table with existing data, simply do this:
ALTER TABLE your_table ADD your_column UNIQUEIDENTIFIER DEFAULT newsequentialid() NOT null