Published on: 05 Jan, 2021

How to create a table with uniqueidentifier in TSQL?

Tumblr Digg Google Plus

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.