Auto-generate usernames in MS SQL Server or MS Azure SQL Instance
T-SQL Database Function for creating unique usernames.
But WHY ?
If you're wondering why you should ever code complex functions into SQL instead of the backend of your application, then you’re in the right place! Let’s explore the benefits of using SQL functions and how they can help you simplify your code and improve performance. Functions in the database are deterministic, meaning that they always produce the same output for a given input. This ensures that different clients will arrive at the same answers, even if they execute the rules or functions in a different order. By contrast, inconsistent order of processing can lead to inconsistent or incorrect results, which is problematic when working with large datasets or complex queries. Therefore, using immutable rules and functions in a database is essential for ensuring consistency and accuracy across different clients and contexts.
Database schema and functionality.
In MS SQL Server, there are two tables: Users and DeletedUserNames. 1. The Users table has the following columns: userID, First Name, Last Name, Middle Name, and UserName. 2. The DeletedUserNames table has two columns: deleted_username and deletion_date. Functions: * When a user is deleted from the Users table, a DELETE trigger is activated which adds the username of the deleted user and the date of deletion into the DeletedUserNames table. * When a new user is created in the Users table, an INSERT trigger calls a function that generates a new unique username for the user. The function returns a new username that is not found in the Users table and has not been used in the past year after deletion. This ensures that each user has a unique username and that no previously deleted usernames are reused for at least one year.
Installation and Setup
Download and install Microsoft SQL Server Express. MS SQL Server Express download: https://go.microsoft.com/fwlink/p/?linkid=2216019&clcid=0x409&culture=en-us&country=us Download and install SQL Server Management Studio (SMSS): https://aka.ms/ssmsfullsetup Installation guide video: https://www.youtube.com/watch?v=PBG40wvhiG0
Connect to your local SQL Server.
In SMSS connect to your server and open a new query with the current connection. Or use the mssql-cli. It is an interactive command-line tool for querying SQL Server and runs on Windows, macOS, or Linux. Check it out here: https://learn.microsoft.com/en-us/sql/tools/mssql-cli?view=sql-server-ver16
Create the Database
Before we can use T-SQL functions, we need to create a database and use it . To create a database, execute the following command:
CREATE DATABASE new_database;After the databse is created, select it to use it with the following command:
USE new_database;Create a database schema for our new_database. Objects created within a schema are owned by the owner of the schema.
CREATE SCHEMA admin;Create the tables
Once you have created the database, you can create the two tables: Users and DeletedUsernames.
CREATE TABLE admin.Users(
userID INT NOT NULL
IDENTITY (1,1)
PRIMARY KEY,
fname VARCHAR(100) NOT NULL,
mname VARCHAR(100),
lname VARCHAR(100) NOT NULL,
username VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE admin.DeletedUsernames(
deletedUsername VARCHAR(50) NOT NULL,
deletionDate DATE NOT NULL
);The Users table explained: 1) userID : INT - this column holds integers NOT NULL - value can't be empty IDENTITY (1,1) - this value is automatically generated when a row is inserted by starting from 1 and incrementing by 1 PRIMARY KEY - this value is a primary key 2) fname : VARCHAR(100) - this column will store the first names as a string of max 100 characters NOT NULL - value can't be empty 3) mname : VARCHAR(100) - this column will store the middle names as a string of max 100 characters. Can be empty 4) lname : VARCHAR(100) - this column will store the last names as a string of max 100 characters NOT NULL - value can't be empty 5) username : VARCHAR(50) - column will store a string of lenght 50 cahracters NOT NULL - value can't be empty UNIQUE - there can only be one instance of the username in the table by executing the following command: The DeletedUsernames table explained: 1) deletedUsername : VARCHAR(50) - value contains a string of max 50 characters NOT NULL - value can't be empty 2) deletionDate : DATE - value will be a in a date format NOT NULL - value can't be empty
Populate the table with dummy data
To test our T-SQL functions, we need to fill the table with some dummy data. To do this, execute the following command:
INSERT INTO admin.Users(fName,mName,lName,username)
VALUES ('James',NULL,'Harden','jharden'),
('Johny','Big','Cash','cashj'),
('Rick','Dee','Strassman','rstrass'),
('John','NULL','Harden','hardenj'),
('Andy','Cat','Bell','abell')
;
INSERT INTO admin.DeletedUsernames(deletedUsername, deletionDate)
VALUES ('bella','2023-10-04'),
('acbell','2023-10-04'),
('jcash','2021-10-04')
;Now we can display the content of our tables with the commands:
SELECT * FROM admin.Users
SELECT * FROM admin.DeletedUsernamesYou should see this output:
Create a Trigger for inserting a user.
The following code creates a SQL server trigger named InsertUser, that is executed instead of an insert operation on the admin.Users table.
CREATE TRIGGER InsertUser ON admin.Users
INSTEAD OF INSERT
AS
BEGIN
DECLARE @new_username VARCHAR(50);
DECLARE @randNum FLOAT = RAND();
DECLARE @fname VARCHAR(100) = (SELECT fname FROM inserted);
DECLARE @mname VARCHAR(100) = (SELECT mname FROM inserted);
DECLARE @lname VARCHAR(100) = (SELECT lname FROM inserted)
/*create the new username from the function*/
EXEC @new_username = admin.GenerateUniqueUsername @fname ,@mname, @lname, @randNum;
/*insert the new row into Users*/
INSERT INTO Users (fname, mname , lname, username)
SELECT fname,
mname,
lname,
@new_username
FROM inserted ;
/*remove the same username from DeletedUsernames*/
DELETE FROM admin.DeletedUsernames WHERE deletedUsername = @new_username;
END;When a new row is inserted into the admin.Users table, the trigger will execute the code inside the BEGIN and END blocks instead of the original insert statement. The code inside the BEGIN and END blocks will insert a new row into the admin.Users table with columns fname, mname, lname, and username. The values for these columns are obtained from the temporary table inserted, which is created automatically by SQL Server to hold the last inserted value. The value for the username column is generated by calling a function named admin.GenerateUniqueUsername with parameters fname, mname, lname, and a random number generated by the function RAND(). The function returns a unique username in lowercase letters. After a new Row is inserted into Users, we need to Delete that username from the DeletedUsernames table. Logically a username inside Users can't be simultaneously in the DeletedUsernames table ! DO NOT INSERT ANYTHING YET! We need to make the GenerateUniqueUsername Function for this to work. But before that lets make a delete Trigger.
Create a Trigger for deleteting a user
The following code creates a trigger named AfterUserDelete that is executed after a delete operation on the admin.Users table.
CREATE TRIGGER AfterUserDelete ON admin.Users
AFTER DELETE
AS
BEGIN
DECLARE @deleted_username VARCHAR(50);
DECLARE @deletion_date DATE;
SET @deletion_date = GETDATE();
SELECT @deleted_username = DELETED.username FROM DELETED;
INSERT INTO admin.DeletedUsernames (deletedUsername, deletionDate)
VALUES (@deleted_username, @deletion_date);
END;When a row is deleted from the admin.Users table, the trigger will execute the code inside the BEGIN and END blocks. The code inside the BEGIN and END blocks will insert a new row into the DeletedUsernames table with columns deletedUsername and deletionDate. The value for the deletedUsername column is obtained from the temporary table DELETED, which is created automatically by SQL Server to hold the last deleted value. The value for the deletionDate column is set to the current date and time using the function GETDATE().
Try deleting the user Rick Dee Strassman with the username "rstrass"
DELETE FROM admin.Users WHERE username = 'rstrass'SELECT * FROM admin.Users
SELECT * FROM admin.DeletedUsernamesYou should see this output:
Create the function that genarates Unique Usernames
The way this function will work: function takes in 4 parameters (fname, mname, lname, random 3 digit number) Steps that generate the 4 forms of the username: 1) generate a fallback form of the username by combining 1st letter of first name + 6 letters of the last name + random 3 digit number result: msativa398 2) generate the FIRST form of the username 1st letter of first name + 6 letters of the last name result: msativa 3) generate the SECOND form of the username 6 letters of the last name 1st letter of first name result: sativam 4) generate the THIRD form of the username 1st letter of first name + 1st letter of middle* name + 6 letters of the last name result: mjsativa * if middle name is empty, use x result: mxsativa
Next the function will check if the generated usernames are already in the Users table 1) check if the FIRST form is NOT found in the Users table AND also check if it found in the DeletedUsernames table a) if it is in the DeletedUsernames : check if was deleteted over 12 months ago If it is not found or if it was deleted more than 12 months ago, it will be saved as the unique username to be returned. If it does not satisfy the uniqueness or deletion criteria, it continues searching with the second form of the username. If the second form does not satisfy the criteria either, it continues searching with the third form of the username. If none of these forms satisfy the criteria, it returns the fallback form of the username.
First Create a small reusable function that checks uniqueness
This function takes one string argument and returns 1 if the username is Unique and satisfies the deletion criteria. Or returns 0 if the username already exist in the Users table or was deleted less than a year ago.
CREATE FUNCTION admin.CheckIfUnique
(
@temp_username VARCHAR(50)
)
RETURNS BIT
AS
BEGIN
IF NOT EXISTS
( SELECT * FROM admin.Users WHERE username = @temp_username )
AND NOT EXISTS
( SELECT * FROM admin.DeletedUsernames WHERE deletedUsername = @temp_username
AND DATEDIFF (month, deletionDate, GETDATE()) <= 12 )
BEGIN
RETURN 1
END
RETURN 0
ENDTest it with the following command:
PRINT CONCAT('abell is acceptable: ', admin.CheckIfUnique('abell'));
PRINT CONCAT('acbell is acceptable: ', admin.CheckIfUnique('acbell'));
PRINT CONCAT('jcash is acceptable: ', admin.CheckIfUnique('jcash'));
Finally create the function that will automatically generate and insert a unique username
In the final function we will utilize our previously created function CheckIfUnique(username). Function takes in 4 paramaters and generates 4 forms a username according to the username patterns which will be assign to their own variables e.g (fallback_username,first_form, second_form, third_form). It will call our previously created function CheckIfUnique(username) where username will be one of the 4 forms created. It will return a 0 or 1 (0 - not unique, 1 - unique) to our main function which will check the return value. If it is 1 it will return that username in lower case form to the insert trigger.
CREATE FUNCTION admin.GenerateUniqueUsername
(
@first_name VARCHAR(100),
@middle_name VARCHAR(100),
@last_name VARCHAR(100),
@randomNum FLOAT
)
RETURNS VARCHAR(50)
AS
BEGIN
/* Declare all forms of usernames*/
DECLARE @fallback_username VARCHAR(50) =
LEFT(@first_name, 1) +
LEFT(@last_name, 6) +
LEFT(REPLACE ( CAST( @randomNum * 1000 AS VARCHAR(20)),'.','x'), 3);
DECLARE @first_form VARCHAR(50) =
LEFT(@first_name, 1) +
LEFT(@last_name, 6);
DECLARE @second_form VARCHAR(50) =
LEFT(@last_name, 6) +
LEFT(@first_name, 1);
DECLARE @third_form VARCHAR(50) ;
IF @middle_name IS NULL
SET @third_form =
LEFT(@first_name, 1) +
'x' +
LEFT(@last_name, 6);
ELSE
SET @third_form =
LEFT(@first_name, 1) +
LEFT(@middle_name,1) +
LEFT(@last_name, 6)
;
/* call CheckIfUnique function to check uniqness*/
IF admin.CheckIfUnique(@first_form) = 1
RETURN LOWER(@first_form);
IF admin.CheckIfUnique(@second_form) = 1
RETURN LOWER(@second_form);
IF admin.CheckIfUnique(@third_form) = 1
RETURN LOWER(@third_form);
/* if first, second, third forms are not unique, return fallback */
RETURN LOWER(@fallback_username);
END;Congratulations on completing this tutorial! I hope you found it informative and engaging. If you have any questions or feedback, feel free to reach out to me anytime.
Here are a few test cases to try.
Let's test our new function by inserting a new user. We will try to insert a user with the name "Joan Anne Harden"
INSERT INTO admin.Users(fname,mname,lname)
VALUES ('Joan','Anne','Harden');
Test a username already in the Users and DeletedUsernames tables.
Now let's test if our deletion criteria is aplied to a new user whose username already exists in the DeletedUsernames table with a deletion date less than 12 months ago. We will insert a new user named "Angela Claire Bell". In the Users table we already have a username "abell" and the deletedUsername table already contains the usernames "bella" and "acbell" with a deletion date less than 12 months ago. So the new username can't be "abell", "bella" or "acbell".
INSERT INTO admin.Users(fname,mname,lname)
VALUES ('Angela','Claire','Bell');
Test a username previously deleted more than 12 months ago.
Lastly let's test a username that was previously deleted more than 12 months ago. The name is "Jackson David Cash". The DeletedUsernames contains a previously deleted username "jcash" which was deleted over 12 months ago so it can be used again for a new user account. When it is reused it will be removed from the DeletedUsernames table.
INSERT INTO admin.Users(fname,mname,lname)
VALUES ('Jackson','David','Cash');