SQL Server NEWID() Function

The NEWID() function is one of the functions in SQL Server used to generate globally unique identifiers (GUIDs). A GUID is a 128-bit number used to uniquely identify an entity in a computer system. The NEWID() function returns a new GUID value composed of a randomly generated set of numbers and letters.

Syntax

The syntax for the NEWID() function is very simple, as shown below:

NEWID();

Use Cases

The NEWID() function is commonly used in SQL Server databases to generate unique identifiers that can be passed and compared between different tables and systems. Some common use cases for the NEWID() function include:

• Creating unique identifier columns
• Generating random passwords and tokens
• Creating unique data rows

Examples

Below are two examples of using the NEWID() function.

Example 1: Creating a Unique Identifier Column

Suppose there is a table named User, and we need to add a new unique identifier column to that table. We can use the NEWID() function to generate a unique GUID value for that column. Here is the complete example code:

CREATE TABLE User
(
    ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

INSERT INTO User (Name)
VALUES
('Tom Smith'),
('Mary Johnson'),
('David Lee'),
('Alex Chen');

SELECT * FROM User;

In this example, we first create a table named User and use the NEWID() function to add a default value to the ID column. This means that whenever we insert a new data row into the User table, a new GUID value will be automatically generated for the ID column. We then add some sample data to the User table and use the SELECT statement to query all data in the User table.

After executing the above code, we get the following result:

ID                                    Name
------------------------------------ ----------
AFB3EE0B-41D6-4F9E-B2D8-36C0D1DBD473 Tom Smith
C0E044B4-4C1A-4C9E-9D6D-7A050A3645ED Mary Johnson
CE2E50ED-FFAE-4132-9E9F-3A3A5BC7CB0A David Lee
A7A1F97B-4F4F-4F6E-8A0F-7A5B5D5B7B5E Alex Chen

In the above result, we can see that the ID column contains automatically generated unique identifier values.

Example 2: Generating Random Passwords and Tokens

Suppose we need to generate random passwords and tokens for users. We can use the NEWID() function to generate unique identifiers. Here is an example that demonstrates how to use the NEWID() function to generate random passwords and tokens:

Create a table named Users with columns Id, Username, Password, and Token:

CREATE TABLE Users (
  Id INT PRIMARY KEY,
  Username NVARCHAR(50),
  Password NVARCHAR(50),
  Token UNIQUEIDENTIFIER
);

Insert some sample data:

INSERT INTO Users (Id, Username) VALUES (1, 'user1');
INSERT INTO Users (Id, Username) VALUES (2, 'user2');
INSERT INTO Users (Id, Username) VALUES (3, 'user3');

Update the Password and Token columns using the NEWID() function to generate unique identifiers:

UPDATE Users
SET Password = CAST(NEWID() AS NVARCHAR(50)),
    Token = NEWID()
WHERE Id = 1;

UPDATE Users
SET Password = CAST(NEWID() AS NVARCHAR(50)),
    Token = NEWID()
WHERE Id = 2;

UPDATE Users
SET Password = CAST(NEWID() AS NVARCHAR(50)),
    Token = NEWID()
WHERE Id = 3;

Query the table to view the generated passwords and tokens:

SELECT * FROM Users;

In the above example, we created a table named “Users” and added some sample data to it. Then, we used the NEWID() function to update the Password and Token columns, setting them to randomly generated unique identifiers. Finally, we queried the table to view the generated passwords and tokens.

Conclusion

The NEWID() function is a very useful function that can be used to generate unique identifiers. Unique identifiers are important in many scenarios, such as creating unique keys in a database or generating random passwords and tokens. The syntax of the NEWID() function is very simple, making it easy to use to generate unique identifiers.