Adding a Uniqueidentifier Column and Adding the Default to Generate New Guid

Adding a uniqueidentifier column and adding the default to generate new guid

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]

How to use default NEWID()/NEWSEQUENTIALID() with Guid/uniqueidentifier?

You can do it with a check constraint:

ALTER TABLE [dbo].[SandTable] 
    ADD CONSTRAINT [DF_SandTable_Id] DEFAULT (NEWID()) FOR [Id]

ALTER TABLE [dbo].[SandTable] 
    WITH CHECK ADD CONSTRAINT [CK_SandTable_Id_Empty] 
    CHECK (
        [Id] <> CAST(0x0 AS UNIQUEIDENTIFIER)
    )

Set some default guid value in uniqueidentifier in sql

Here's your script.

ALTER TABLE TestTable drop COLUMN TestID 

ALTER TABLE TestTable ADD TestID uniqueidentifier DEFAULT cast('DA74F684-B228-48D5-9692-69465BE6D720' as uniqueidentifier);

then, update exsting records

UPDATE TestTable  set TestID = 'DA74F684-B228-48D5-9692-69465BE6D720'

How to add an uniqueidentifier column and insert values

This should work just fine.

UPDATE table
SET UniqueIdentifierColumn = NEWID()
WHERE ...

Notice that doing the update in a single set-based statement populates each row with a different GUID.

Sample Code

CREATE TABLE dbo.HugeTable (
    ColID       int     IDENTITY    PRIMARY KEY,
    ColGUID     uniqueidentifier,
    ColInt      int
)

DECLARE @ct     int

SET @ct = 0
WHILE @ct < 10 BEGIN
    SET @ct = @ct + 1
    INSERT INTO dbo.HugeTable (ColInt) VALUES (@ct)
END
GO

SELECT COUNT(*) AS Ct FROM dbo.HugeTable

UPDATE dbo.HugeTable
SET ColGUID = NEWID()
WHERE ColID BETWEEN 3 AND 7

SELECT * FROM dbo.HugeTable

Results

         Ct
-----------
         10

      ColID ColGUID                                   ColInt
----------- ------------------------------------ -----------
          1 NULL                                           1
          2 NULL                                           2
          3 E45E13D8-CFF0-4FC7-B7C9-1D53E95C502D           3
          4 33C3CCBC-B6BB-4CAA-AB10-338AA95F366E           4
          5 82136767-396E-4B33-B9DD-FFD30FCF4680           5
          6 EFA24EC9-F8F9-47CF-839F-D588F69D167F           6
          7 546F7C14-BDDA-4226-B45C-B0DDCD43E7DB           7
          8 NULL                                           8
          9 NULL                                           9
         10 NULL                                          10

Creating New GUID automatically while inserting a new row to an existing table Not Working

Sourav's question about triggers got me thinking, so I tried a little test. Why?

Imagine a scenario where an application has already been written with thousands of INSERT statements that leave off the column list. In this case, if you could write an INSTEAD OF INSERT trigger that provides the column list, you could hopefully save yourself from correcting thousands of INSERT statements due to a newly added column.

Off the top of my head, I admittedly did not know if this could work.

So I wrote this little test:

CREATE TABLE tt (ColA varchar(1));

INSERT INTO tt VALUES ('a');

ALTER TABLE tt
ADD ColB uniqueidentifier DEFAULT NEWID();

GO

CREATE TRIGGER tr_tt
ON tt
INSTEAD OF INSERT
AS 

INSERT INTO tt (ColA)
SELECT ColA FROM inserted;

GO

INSERT INTO tt VALUES ('a');

SELECT * FROM tt;

DROP TABLE tt;

I also tried a variation of the TRIGGER with the following INSERT just to be thorough:

INSERT INTO tt (ColA, ColB)
SELECT ColA, NEWID() FROM inserted;

The result was the same in both cases: The same error as reported in the question. So to answer the question:

Can't we use a trigger here which can do it?

The answer is NO. Even if you put an INSTEAD OF INSERT TRIGGER on the table, the parser will still not let you write an INSERT..VALUES() statement unless the number and order of VALUES exactly matches the definition of the table. A TRIGGER cannot be used to get around it.

Sooner or later, lazy coding exacts its price.

How to generate a Guid in SQL Server?

This will generate a Guid in SQL Server:

SELECT NEWID()

---

Related Topics