Since generated GUID (globally unique identifier) values are considered to be unique across all databases and servers, they are great for assigning row ids that will never be duplicated anywhere else. However, the values generated for GUID are not user friendly, so an auto-generated record id is often needed to identify a row of data in a human-friendly manner. The solution is to create a sequence for the table and auto-assign the next sequence number upon insert.
The NEWID() function returns a new GUID in SQL Server.
The following SQL statements assume the “employee” schema already exists in the database.
create sequence employee.employee_id start with 100001 increment by 1;
create table employee.employees (
empl_rowid uniqueidentifier primary key default newid(),
empl_id bigint default next value for employee.employee_id,
empl_first_name varchar(150),
empl_last_name varchar(150),
empl_date_created datetime2 default getdate(),
empl_date_updated datetime2
);
create unique index employee_empl_id on employee.employees (empl_id);
insert into employee.employees (
empl_first_name,
empl_last_name )
values ('Paul', 'Myrick'),
('Shaggy', ‘Dog');
Executing the above SQL statements results in the following table and data:
