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.SConsultActivityComments
(
--authoritative source:
--SQL52.SPV.Spatient.SConsultActivityComment
--fields
SConsultActivityCommentID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
ConsultID bigint NULL, --EPRS assigned (FK), referencess Consults; ConsultSID
StationID smallint NULL, --EPRS assigned (FK), references Stations; Sta3n
BatchLogID bigint NULL, --EPRS assigned (FK), referencess BatchLogs
CDWSConsultActivityCommentsysnumber BIGINT NULL, --SConsultActivityCommentSID
CDWConsultSysNumber BIGINT NULL, --ConsultSID
CDWConsultActivitySysNumber bigint null, --ConsultActivitySID
CDWExtractBatchSysNumber bigint NULL, --extractBatchID
ConsultIEN int NULL, --CDW Station specific assigned ID
ConsultActivityIEN INT NULL, --CDW Station specific assigned ID
RequestDateTime DATETIME2 (7) NULL, --Date Consult entered into VISTA
ConsultActivityComment VARCHAR (8000) NULL, --Comment Captured during the creation of an activity on the consult
--primary key
CONSTRAINT PK_SConsultActivityCommentID PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
SConsultActivityCommentID ASC
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO
/* RAW Fields:
--[SConsultActivityCommentSID] BIGINT NOT NULL, --EPRS PK: SConsultActivityCommentID
--[ConsultSID] BIGINT NULL,
--[ConsultIEN] VARCHAR (50) NOT NULL,
--[ConsultActivitySID] BIGINT NULL,
--[ConsultActivityIEN] VARCHAR (50) NOT NULL,
--[Sta3n] SMALLINT NOT NULL,
--[RequestDateTime] DATETIME2 (0) NULL,
--[RequestVistaErrorDate] VARCHAR (50) NULL, --IGNORE
--[RequestDateTimeTransformSID] BIGINT NULL, --IGNORE
--[ConsultActivityComment] VARCHAR (8000) NULL,
--[ETLBatchID] INT NULL, --IGNORED due to region specific
--[OpCode] CHAR(1) NOT NULL, --Moved to the RecordStatuses Table
--ExtractBatchID BIGINT NOT NULL,
--BatchLogID BIGINT
--[VistaCreateDate] DATETIME NULL, --IGNORE
--[VistaEditDate] DATETIME NULL, --IGNORE
*/
--foreign keys
--TODO: Determine if there will be a claims table. If so, uncomment the following. Otherwise, assume no FK need on ClaimID
ALTER TABLE dbo.SconsultActivityComments
ADD CONSTRAINT FK_SconsultActivityComments_Consults
FOREIGN KEY (ConsultID)
REFERENCES dbo.Consults (ConsultID)
GO
ALTER TABLE dbo.SconsultActivityComments
ADD CONSTRAINT FK_SconsultActivityComments_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO
ALTER TABLE dbo.SconsultActivityComments
ADD CONSTRAINT FK_SconsultActivityComments_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
--indexes
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_SconsultActivityComments ON dbo.SconsultActivityComments
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_SconsultActivityComments_PatientID ON dbo.SconsultActivityComments
-- (
-- PatientID
-- ) WITH (
-- PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
-- FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
-- SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
-- IGNORE_DUP_KEY = OFF,
-- STATISTICS_NORECOMPUTE = OFF,
-- STATISTICS_INCREMENTAL = OFF,
-- DROP_EXISTING = OFF,
-- ONLINE = OFF,
-- ALLOW_ROW_LOCKS = ON,
-- ALLOW_PAGE_LOCKS = ON,
-- MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
-- DATA_COMPRESSION = PAGE --use page compression; default = NONE
-- )
-- ON CoreIndex
--GO
----constraints
----Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS; authoritative source table is SQL52.SPV.Spatient.SConsultActivityComment',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'SConsultActivityCommentID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to ConsultActivities table, source field: ConsultSID',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'ConsultID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Stations table, source field: Sta3n',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to BatchLogs table',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW assigned national ID; source: ExtractBatchID',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'CDWExtractBatchSysNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW assigned national ID; source: ConsultSID',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'CDWConsultSysNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW assigned national ID; source: ConsultActivitySID',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'CDWConsultActivitySysNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW assigned national ID; source: SConsultActivityCommentSID',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'CDWSConsultActivityCommentSysNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Station specific assigned ID',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'ConsultIEN'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Station specific assigned ID',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'ConsultActivityIEN'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Date Consult entered into VISTA',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'RequestDateTime'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Comment Captured during the creation of an activity on the consult',
N'SCHEMA', N'dbo',
N'TABLE', N'SconsultActivityComments',
N'COLUMN', N'ConsultActivityComment'
GO