6

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.

9

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.

  • I code works welk except that everyday it updates the userkeys on database. I dnt understand how but as date chancges it updates userkey. Please help me to fix this. – Jubilate Jun 2 '16 at 11:41
  • Thanks I just saw that, and hiw would I use the userkey as primary key? – Jubilate Jun 6 '16 at 8:09
  • Thanks I have checked it out, and when I add persisted its says userkey cannot be persisted because the column is non-deterministic – Jubilate Jun 6 '16 at 9:42
  • Yes, unfortunately that would be because it's using the field Created which is using the getdate() function. – Molenpad Jun 6 '16 at 19:42
  • I noticed that after reading more non-deterministic functions, so but is there no other way I can use to generate these userkeys? – Jubilate Jun 6 '16 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.