SQL Server: use the NewId() function to create unique identifiers

Tweet

A few months ago, I have published an article mentioning the NewId() SQL function. In that post, I was explaining how to create dynamic keywords and description meta tags. However the NewId() function can be used in different situations and it becomes very handy, when we need unique identifiers.
Let's see how it works.

The basics

The NewId() function returns a unique identifier; that means a unique set of numbers and letters. If we use the function in a query in SQL Server Management Studio like:

print newid()

the result will be a unique identifier like:

5C6C9372-49F9-4A5C-BCFA-DA6CDB525CAB

Interestingly enough, the above result is unique and can be used in various way. For example, we can use the function to generate personal identification numbers (PINs), login passwords, ids, serial numbers or product keys, simple primary keys etc. At the same time we can use the function to create random sorting of data.

Globally Unique Identifier

If we need to create PINs, passwords, or in general, unique identifiers, we can use the NewId() function directly in our tables. To do so, we can set the default value of a field as NewId(). When we insert a new record in that table, we do not need to think about the unique identifier, because it will be inserted automatically and it will be globally unique. The only inconvenience might be the fact that the generated unique identifier could be too long. In those case, I have some times used as column default value something like:

(left(newid(),(4)))

which will limit the lenght of the GUI to 4 characters (changing the 4 in the above code will change the GUI lenght). We can even remove the dashes (-) to the number with:

Replace(newid(),'-','')

and have a 32 chars long string as result.

Order a table randomly

We can use the NewId() function to randomly order a table:

select *
from table
order by newid()

The above query will order the data in a random way. Which might be useful - for example - when we need to retrieve a random record (just one). We can do it with the following query:

select top(1) *
from table
order by newid()

New ideas?

Well, in my experience the NewId() function can be used in different and creative way. I have used it for automatic login password generators and for PINs in a company project protection forms. But whenever I need to create a GUI, I always use it, even when the GUI is not actually saved in a table.

Have a splendid day and let me know what you think about it.