TSQL -> Manage unique token values using random numbers

Suppose that we have logins accounts table. Each login has password or token that needs to be changed/updated periodically (once in month or week, or day and so on).
Token management rules:
A. each login account has unique token value (integer)
B. token can expire and sometime needs to be updated
C. when updated, we need to check that given token value was never used in the history of our logins

The key TSQL statement for getting unique integer value will be like this one:
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS random_number;

more about getting random numbers in this article:
http://www.sql-server-helper.com/tips/generate-random-numbers.aspx

--
-- [1] create logins table And insert initial data
--

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[logins]') AND type in (N'U'))
DROP TABLE [dbo].[logins]
GO

CREATE TABLE [dbo].[logins]
(
[login_id] [int] IDENTITY(1,1) NOT NULL,
[login_name] [varchar](40) NOT NULL,
[token] [int] NOT NULL,
[login_date] [datetime] NULL DEFAULT (getdate()),
[token_date] [datetime] NULL,
CONSTRAINT [PK_logins] PRIMARY KEY CLUSTERED
([login_id] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tokens_archive]') AND type in (N'U'))
DROP TABLE [dbo].[tokens_archive]
GO

CREATE TABLE dbo.tokens_archive
(
[login_id] [int] NOT NULL,
[token] [int] NOT NULL,
[token_date] [datetime] NOT NULL,
[token_delete_date] [datetime] DEFAULT(GETDATE()) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.logins ([login_name], [token], [token_date])
VALUES ('london', 111111, GETDATE());
INSERT INTO dbo.logins ([login_name], [token], [token_date])
VALUES ('paris', 222222, GETDATE());
INSERT INTO dbo.logins ([login_name], [token], [token_date])
VALUES ('berlin', 333333, GETDATE());
GO

--
-- [2] replace existing token values by new
--

-- [A] archive current token values

INSERT INTO dbo.tokens_archive (login_id, token, token_date)
SELECT login_id, token, token_date
FROM dbo.logins
GO

-- [B] remove all existing tokens
UPDATE dbo.logins SET token = 0
GO

-- [C] get new token value as random number
UPDATE t1
SET t1.token = t2.new_token, t1.token_date = GETDATE()
FROM dbo.logins AS t1 INNER JOIN
(
  SELECT a.login_id, a.new_token
  FROM
  (
    SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS new_token, login_id
    FROM dbo.logins
  ) AS a
  WHERE a.new_token NOT IN (select token from dbo.tokens_archive)
) AS t2
ON t1.login_id = t2.login_id
GO



Logins Table data can look like this:
login_idlogin_nametoken login_datetoken_date
1london5496027982016-08-05 19:14:30 2016-08-05 19:14:32
2paris14649813902016-08-05 19:14:30 2016-08-05 19:14:32
3berlin7360159542016-08-05 19:14:30 2016-08-05 19:14:32



sqlexamples.info