Menu
I am updating my database to have a column for a GUID for each record in a table.
This table is populated by several different sources and some of them will not provide a GUID for new records.
How can I populate this column with a newid() on the DB side only if there was no value passed in for a new record?
984
-- If you want to generate a new Guid (uniqueidentifier) in SQL server the you can simply use the NEWID() function. -- This will return a new random uniqueidentifier e.g. You can directly use this with INSERT statement to insert new row in table.
DECLARE @guid uniqueidentifier = NEWID(); SELECT @guid as 'GUID'; Here we created a variable named guid of data type uniqueidentifier. To generate a unique identifier, we need to assign a default method of creating it, and for that we have used the NEWID function which generates and returns a RFC4122 compliant GUID.
The globally unique identifier (GUID) data type in SQL Server is represented by the uniqueidentifier data type, which stores a 16-byte binary value. A GUID is a binary number, and its main use is as an identifier that must be unique in a network that has many computers at many sites.
Uniqueidentifier is a Microsoft SQL Server data type that is used to store Globally Unique Identifiers (GUIDs). It can store 16 bytes of data. The Developer tool treats the Uniqueidentifier data type as String. To move or change Uniqueidentifier data, connect the Uniqueidentifier column to a String column.
DO NOT I REPEAT DO NOT PLACE A DEFAULT COLUMN OF NEWID(). If you do the GUID you get will be completely random and if you have any sort of indexing on this column it will be useless. If you are going to do this you want to set the column default to be NEWSEQUENTIALID ( )
Kimberly Tripp has a Great Article and another Great Article on this subject.
To implement NEWSEQUENTIALID ( ) as a default:
CREATE TABLE foo
(unq_id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
cola varchar(10));
Full SQL Fiddle example..
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
