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
Querying Microsoft SQL Server 2012
Used to prepare for Microsoft exam 70-461
Good reference for all query questions.
New SQL-Steve site
Extra! Extra! Read all about it! I will be posting SQL tips and tricks on a weekly basis in this space. I look forward to your feedback.