How to create empty guid in SQL Server – c# Guid.Empty() equivalent

In dotnet you can create a new Guid by using the static Guid class like this

(c# syntax)

Guid g = Guid.NewGuid();

And you can create an empty guid like this

Guid g = Guid.Empty;

In SQL Server you can create a new guid like this

DECLARE @g uniqueidentifier = newid()

select @g

But SQL Server does not have a function for creating an empty guid.

One way (a poor way) of achieving this is to create one from a string.

DECLARE @g uniqueidentifier = '00000000-0000-0000-0000-000000000000'

But if you get this wrong then an error will occur and its not easy to see why. E.g.

DECLARE @g uniqueidentifier = '00000000-0000-0000-0000-00000000000'

(hint: its got a 0 missing)

A safer way is to cast 0 to a binary and then the result to a uniqueidentifier

Declare @g uniqueidentifier = cast(cast(0 as binary) as uniqueidentifier)

SELECT @g

I hope this helps someone out there to produce safer, more reliable and readable code.

If you use this often and really want to make your code more readable then encapsulate this in a function.

 

-- =============================================
-- Author     : David Bridge
-- Create date: June 2014
-- Description: Returns an empty guid
-- =============================================
CREATE FUNCTION GuidEmpty()
RETURNS uniqueidentifier
AS
BEGIN
RETURN cast(cast(0 as binary) as uniqueidentifier)
END

Then use

select dbo.GuidEmpty()

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , . Bookmark the permalink.

One Response to How to create empty guid in SQL Server – c# Guid.Empty() equivalent

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s