Author: Steve

Encrypted MRN

USE [DB];
GO

/* Drop existing table and create new one — should be used for test only. */

IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘dbo’
AND TABLE_NAME = ‘NewMRN’))
DROP TABLE [DB].dbo.NewMRN;
GO

CREATE TABLE NewMRN
(
MRN VARCHAR(100) ,
EncryptedMRN uniqueidentifier CONSTRAINT [DF_EncryptedMRN] DEFAULT (NEWID()) NOT NULL
CONSTRAINT PK_MRN PRIMARY KEY (MRN)
);
GO
/* Load data via Tally table */

DECLARE @max_rows AS BIGINT;
SET @max_rows = 999999;

WITH
lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b), — 4
lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b), — 16
lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b), — 256
lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b), — 65,536
lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b), — 4,294,967,296

Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv5)

INSERT INTO NewMRN
(MRN)
SELECT TOP (@max_rows) n FROM Nums ORDER BY n;
/* Update varchar(100) columns with preceeding zero for matching CPR+ MRN numbers */

UPDATE dbo.NewMRN — 90000 rows updated
SET MRN = ‘0’+MRN
WHERE LEN(MRN) = 5;
GO

UPDATE dbo.NewMRN — 9000 rows updated
SET MRN = ’00’+MRN
WHERE LEN(MRN) = 4;
GO

UPDATE dbo.NewMRN — 900 rows updated
SET MRN = ‘000’+MRN
WHERE LEN(MRN) = 3;
GO

UPDATE dbo.NewMRN — 90 rows updated
SET MRN = ‘0000’+MRN
WHERE LEN(MRN) = 2;
GO

UPDATE dbo.NewMRN — 9 rows updated
SET MRN = ‘00000’+MRN
WHERE LEN(MRN) = 1;
GO

–SELECT *
–FROM NewMRN;
–WHERE LEN(MRN) <> 6 — 0 rows
GO
/* Triggers to control accidental edits */

CREATE TRIGGER TR_UPD_Data ON dbo.NewMRN
AFTER UPDATE
AS
BEGIN
IF ( UPDATE (MRN) OR UPDATE (EncryptedMRN) )
BEGIN
RAISERROR (‘NO records should be modified in this table.’, 16, 1);
ROLLBACK;
END;
END;
/* Non clustered index for searching */

CREATE NONCLUSTERED INDEX [IX_EncryptpedMRN] ON [dbo].[NewMRN]
(
[EncryptedMRN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO
–SELECT [MRN]
— ,[EncryptedMRN]
— FROM [dbo].[NewMRN]
–GO