Published on: 18 Feb, 2020

Learn how to create a table dynamically in code with GUID as primary key

Tumblr Digg Google Plus

In this tutorial, you will learn how to create a table dynamically via SQL query and use GUID as a primary key.

You probably want to ask why we need to use the GUID key rather than a number. A GUID key is a 32-character hexadecimal string. It is unique and hard to guess. It will be better as a unique identifier compare to a number.

You can imagine if you build an app if you pass a number value in the URL string. It will be easier to identify and guess if this particular number represents a record.

To create a GUID in TSQL, we need to use the keyword uniqueidentifier. In this example, we are going to create a User Table. Here is the SQL query code.

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

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

The NewID() keyword basically return a uniqueidentifier key value.

Comments

There are no comments available.

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