IMG-LOGO

How to create a table with uniqueidentifier in TSQL?

andy - 05 Jan, 2021 4852 Views 0 Comment

A Guid is a 16-byte binary data type TSQL. This data type can be used as another alternative unique identifier for a record. Usually, we use numbers for the primary key. But on some occasions, if we want to make it harder for users to guess the primary key we can use this Guid type alternative. For example: If we have a user table with a primary key integer. If we want to do a query against it we can easily pass any integer number. But with Guid type, it will be a bit hard to guess. So this can be a good choice if you want to build a better application.

Here is the full query to build a table with a uniqueidentifier in SQL Server.


IF NOT EXISTS (SELECT * from dbo.sysobjects where id = object_id(N'Users') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE Users
(
    [UserId] uniqueidentifier NOT NULL DEFAULT NewID(),
    [FirstName] nvarchar(30),
    [LastName] nvarchar(30),
    [Email] nvarchar(50)
)

ALTER TABLE [Users] ADD CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED  ([UserId])
END
GO

A primary key cannot be null. Therefore we put a keyword NOT NULL when declaring the UserId Column. The keyword NEWID() represents an identifier that will give a unique value.

Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.

Related Articles

How to restore database using SQL query in TSQL?

If you have a database backup bak file extension and want to restore it using SQL query You can use the built in RESTORE DATABASE function Remember in order to be able to restore a database successfully You need to ...

How to get all table sizes in TSQL?

To get the information about how much space or size used by tables You can retrieve the size information by linking multiple tables in sys tables There are two tables that hold this information The first one is the sys ...