How use uniqueidentifier datatype in SQL Server ?

This post describe what is uniqueidentifier and how use it?

The uniqueidentifier is datatype in SQL Server. It stored hexadecimal digit value of 16 bytes size. As You may see, it take a lot of space. Why? It stores globally unique identifier(GUID). Globally means that no other computer in the world has the same the uniqueidentifier value. It is unique value in network. This value is generating by number of network card of this computer on which is install SQL Server engine.
The uniqueidentifier may be defined as character string format or binary format. Default is character string format. The binary format has the prefix 0x.
For generating this value is used NEWID or NEWSEQUENTIALID function in T-SQL.

If it is not necessary to used this datatype, better will be for primary key use int value, because int datatype take less space than uniqueidentifier. But if You must have globally value, use this  uniqueidentifier datatype.
Below I present examples to create table with column  uniqueidentifier datatype and insert data into this table.
First I create table with column of idGuid name and uniqueidentifier datatype without any clause, and column of name name.

create table ident1
(
idGuid uniqueidentifier,
name varchar(100)
);

insert into ident1 values(NEWID(), 'Indie');

After insert record with NEWID function in idGuid column You see value similar to this.

select * from ident1;

qw1
But if You will be want to insert only value for name column omit idGuid column You see NULL value in this column.

insert into ident1(name) values( 'Germany');
select * from ident1;

qw2
What to do in this situation? You must add for identifier column DEFAULT value as NEWID finction.

create table ident2
(
idGuid uniqueidentifier DEFAULT NEWID(),
name varchar(100)
);

insert into ident2(name) values('Poland');

Select these records show You value in uniqueidentifier column.

select * from ident2;

qw3You may use either NEWSEQUENTIALID function instead of  NEWID function. The NEWSEQUENTIALID function is faster than NEWID function.

So try to do example 1 but with this function.

create table ident11
(
idGuid uniqueidentifier,
name varchar(100)
);

insert into ident11 values(NEWSEQUENTIALID(), 'Indie');

Oh, You see error:
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

With NEWID function You may omit DEFAULT clause, but NEWSEQUENTIALID function must has DEFAULT clause in column.
Below is correct code:

create table ident12
(
idGuid uniqueidentifier DEFAULT NEWSEQUENTIALID(),
name varchar(100)
);

insert into ident12(name) values('Brazil');

The error don’t display so You may try value for this record.

select * from ident12;

qw4

One thought on “How use uniqueidentifier datatype in SQL Server ?

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.