One way would be to add an
ID column in your users table, with a default value based on a sequence object, then add a computed column to create the key using a date conversion to string.
To create the sequence, something like:
CREATE SEQUENCE [dbo].[userid_sq]
START WITH 1
INCREMENT BY 1
I wouldn't limit yourself to a value of 99 if you are not 100% sure you are going to stay under that number of users per day.
You want a fresh cycle every day, right? So you can force it with an evening job/script to ensure it is back at 1 the next day.
ALTER SEQUENCE [dbo].[userid_sq] RESTART WITH 1;
So just to illustrate the point, say I'm creating a new table,
UserDetails, and want to include this logic, I might do something like:
CREATE TABLE UserDetails (
ID INT NOT NULL DEFAULT NEXT VALUE FOR userid_sq
,Created DATETIME DEFAULT GETDATE()
CONVERT(VARCHAR(6), Created, 12) +
RIGHT(CONVERT(VARCHAR(11), 1000 + ID), 3)
,UserName VARCHAR(50) NOT NULL
INSERT INTO UserDetails (UserName)
My table content would be:
ID - 1
UserKey - K160531001
UserName - TestUser
You asked about indexing, so, ignoring the fact that the above example would be non-deterministic and therefore ineligible to be used a primary key, if you want to index the computed column, you'll need to PERSIST the column in the database. This stores the values to disk rather than as a calculated virtual value.
ALTER TABLE table_name ALTER COLUMN computed_column_name ADD PERSISTED;
Note that there are various restrictions with what you can do with computed columns so I would implore you to read the following information to get a proper understanding.
Here's a relevant snippet from that article, which also relates to the above example:
A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint. For example, if the table has integer columns a and b, the computed column a + b may be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed, because the value might change in subsequent invocations.
A computed column cannot be the target of an INSERT or UPDATE statement.