This weeks tip:
In the programming world, there are a few times when dealing with uniqueidentifiers and/or GUIDs that you need an empty GUID – a GUID of all zeros (in fact .NET has a Guid.Empty field).
In TSQL, there are a few ways to get ‘00000000-0000-0000-0000-000000000000’
- You could bookmark this page and every time you need it, come back here and copy and paste the value above into all your code (fine by me, but not the most efficient).
- You could copy and paste any GUID into text pad and line up all the 0s below it, then paste that value into your code (that’s how I used to do it).
- You could replicate the 0s a few times, throw in a few ‘-‘ and cast the entire thing as a uniqueidentier like this:
Cast(replicate('0', 7) + '0-' + replicate('0', 4) + '-' + replicate('0', 4) + '-' + replicate('0', 4) + '-' + replicate('0', 12) as UNIQUEIDENTIFIER)
Or….
The way I recommend, CAST 0 as a binary, then CAST that binary as a uniqueidentifier. This seems the most versatile and easiest to remember –> turn 0 into a binary, then turn that into a uniqueidentifier, you don’t have to count zeros, or line up any dashes in notepad. Here is the syntax:
Select CAST(CAST(0 as BINARY) as UNIQUEIDENTIFIER)
Here are some related posts from the intraweb:
- I found the same question out on StackOverflow here: How do I identify a blank uniqueidentifier in SQL Server 2005?
- A good explanation from Ifinity Software Development here: Detecting an Empty Guid in Sql Server in T-Sql Syntax
Nice trick. One other solution that’s about as clean is: select cast(0×0 as uniqueidentifier) .
This approach is a good one with one very minor problem – you cannot use that syntax to define the default value of a field as an empty guid. You basically have do the same thing but use CONVERT instead of CAST
(CONVERT([uniqueidentifier],CONVERT([binary],(0),0),0))
Some tools will do this for you if you use the CAST syntax and others won’t. In all other situations I have looked at your syntax works great and is definitely easier to remember.
Al