Can you define a new column in a SQL Server table which auto-generates Unique Identifiers for new rows?

Question

Auto-increment columns in SQL Server get populated automatically; is it possible to define a UniqueIdentifier column to auto-generate on insert without the use of a trigger?

This will be a secondary unique key on the table. I need to create it because we need a public primary key now which can be used within a query string.

Legacy infrastructure still relies on the old int primary key. Given that the old infrastructure creates the record in the first place, I would like SQL Server to transparently create the secondary GUID key on insert - if at all possible.

Many thanks

Answer 1

You can use a column with Uniqueidentifier type with default value of NEWID()

Answer 2

If you add a column to your table with a default of NEWID() and then update existing rows to have a new id too. You may wa

--  Create test table
CREATE TABLE Test1
(
ID int IDENTITY(1,1)
,Txt char(1)
);

-- Insert data
INSERT INTO Test1(Txt)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd' UNION ALL
SELECT 'e';

-- Add column
ALTER TABLE Test1
ADD GlobalID uniqueidentifier DEFAULT(NEWID());

-- View table, default value not added for existing rows
SELECT *
FROM Test1;

-- Update null ids with guids
UPDATE Test1
SET GlobalID = NEWID()
WHERE GlobalID IS NULL

-- Insert new data
INSERT INTO Test1(Txt)
SELECT 'f' UNION ALL
SELECT 'g' UNION ALL
SELECT 'h' UNION ALL
SELECT 'i' UNION ALL
SELECT 'j';

-- View table
SELECT *
FROM Test1;

-- Drop table
DROP TABLE Test1