Summary Table

Categories Total Count
PII 0
URL 0
DNS 0
EKL 0
IP 0
PORT 0
VsID 0
CF 0
AI 0
VPD 0
PL 0
Other 0

File Content

?CREATE TABLE dbo.Patients
(
--fields
PatientID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
StationID [smallint] NOT NULL,
BatchLogID [bigint],
PatientIEN [bigint] NOT NULL,
IntegrationControlNumber bigint,
PatientName [varchar] (200),
SSN [varchar] (12),
DateSynchronized [datetime2](7),

--primary key
CONSTRAINT PK_Patients PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
PatientID ASC
),

--unique key
CONSTRAINT UK_Patients UNIQUE NONCLUSTERED
(
StationID ASC,
PatientIEN ASC
),
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO

--foreign keys
ALTER TABLE dbo.Patients
ADD CONSTRAINT [FK_Patients_Stations]
FOREIGN KEY (StationID)
REFERENCES Stations (StationID)
GO

ALTER TABLE dbo.Patients
ADD CONSTRAINT FK_Patients_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES BatchLogs (BatchLogID)
GO

--indexes
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_Patients ON dbo.Patients
WITH
(
/*DROP_EXISTING = ON --if the index doesn't exist and this is active,
-- the code will go boom
,*/ MAXDOP = 1 --while 0 uses max degrees of parallelism, 1 in
-- this case helps tighten the ordering when
-- initially using a traditional clustered index
--SQL 2016-- , COMPRESSION_DELAY = 10 --in minutes; increase to 60 if doing OLTP to
--maintain data in deltarowgroup for 60 minutes
, DATA_COMPRESSION = COLUMNSTORE --or COLUMNSTORE_ARCHIVE
)
ON CoreData --keep the clustered columnstore with the data filegroup rather than index filegroup
GO

--TODO: Are the traditional non-clustered indexes still needed?
CREATE NONCLUSTERED INDEX IX_Patients_StationID ON dbo.Patients
(
StationID
) WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON CoreIndex
GO

CREATE NONCLUSTERED INDEX IX_Patients_PatientIEN ON dbo.Patients
(
PatientIEN
) WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON CoreIndex
GO

CREATE NONCLUSTERED INDEX IX_Patients_IntegrationControlNumber ON dbo.Patients
(
IntegrationControlNumber
) WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON CoreIndex
GO

--constraints

--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS',
N'SCHEMA', N'dbo',
N'TABLE', N'Patients',
N'COLUMN', N'PatientID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Stations table',
N'SCHEMA', N'dbo',
N'TABLE', N'Patients',
N'COLUMN', N'StationID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'VistA IEN (DFN), also the CDW row_id for the patient identifier',
N'SCHEMA', N'dbo',
N'TABLE', N'Patients',
N'COLUMN', N'PatientIEN'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifier assigned to the patient, should be unique across all VistA systems',
N'SCHEMA', N'dbo',
N'TABLE', N'Patients',
N'COLUMN', N'IntegrationControlNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Name of the patient',
N'SCHEMA', N'dbo',
N'TABLE', N'Patients',
N'COLUMN', N'PatientName'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Social security number',
N'SCHEMA', N'dbo',
N'TABLE', N'Patients',
N'COLUMN', N'SSN'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Date of synchronization with CDW',
N'SCHEMA', N'dbo',
N'TABLE', N'Patients',
N'COLUMN', N'DateSynchronized'
GO