SQL Server – Create Function to Generate Column Value in Specific Format

sql serversql-server-2012t-sql

I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, each of the registered users should be assigned a userId like K16060600, the format being Kyymmddxx, where

  • K is constant for every userId,
  • yy stands for year (e.g. 16 for 2016),
  • mm stands for month,
  • dd stands for day,
  • xx is a number for 00 to 99 and it should increment from 00 whenever a new row/user is added.
  • It is guaranteed that I will add fewer than 100 each day.

How can I do that?

I'm using Visual Studio 2012 and SQL Server 2012.

Best Answer

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] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 500
 CYCLE

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()
    ,UserKey AS 
        'K' + 
        CONVERT(VARCHAR(6), Created, 12) + 
        RIGHT(CONVERT(VARCHAR(11), 1000 + ID), 3)
    ,UserName VARCHAR(50) NOT NULL
    )

INSERT INTO UserDetails (UserName)
VALUES ('TestUser')

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.

MSDN Link

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.

Related Question