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.ConsultFactorTypes
(
--source:
--A06/SQL52.SPV.NDim.ConsultFactorType
--fields
ConsultFactorTypeID smallint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
RecordStatusID tinyint NULL, --EPRS assigned (FK), references RecordStatus, OpsCode
BatchLogID bigint NOT NULL, --EPRS assigned (FK), reference BatchLogs
CDWConsultFactorTypeSysNumber smallint NULL, --ConsultFactorTypeSID
CDWConsultFactorTypeNumber smallint NULL, --ConsultFactorTypeID
CDWExtractBatchNumber int NULL, --ExtractBatchID
ConsultFactorType varchar(50) NULL, --ConsultFactorType
ConsultFactorTypeText varchar(300) NULL, --ConsultFactorText
SearchString varchar(100) NULL, --SearchString
IsConsultToolboxRelated bit NOT NULL, --CTBItemFlag, convert to 1 (true) or 0 (false)
--primary key
CONSTRAINT PK_ConsultFactorTypes PRIMARY KEY CLUSTERED
(
ConsultFactorTypeID ASC
)
)
ON DefinitionData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO
/* Create table [SPV].[NDim].[ConsultFactorType]
--fields
[ConsultFactorTypeSID] int NOT NULL, --CDWConsultFactorTypeSysNumber
ConsultFactorTypeID int NOT NULL, --CDWConsultFactorTypeNumber
SearchString varchar(100) NULL, --SearchString
ConsultFactorType varchar(50)NULL, --ConsultFactorType
ConsultFactorText varchar(300)NULL, --ConsultFactorTypeText
AutoTrackFlag char(1) NULL, --ignore
TrackableFlag char(1) NULL, --ignore
ReportableFlag char(1) NULL, --ignore
CTBItemFlag char(1) NULL, --IsConsultToolboxRelated
HasParameter varchar(50) NULL, --ignore
AdditionalSQL varchar(200) NULL, --ignore
SQLParameter varchar(50) NULL, --ignore
RecordStartDateTime datetime2(0) NULL, --ignore
RecordEndDateTime datetime2(0 )NULL, --ignore
ExtractBatchID int NULL, --CDWExtractBatchNumber
OpCode char(1) NULL, --RecordStatuses table
ETLCreateDate datetime NULL, --ignore
ETLEditDate datetime NULL, --ignore
BatchLogID BIGINT --EPRS driven
*/
--foreign keys
ALTER TABLE dbo.ConsultFactorTypes
ADD CONSTRAINT FK_ConsultFactorTypes_RecordStatuses
FOREIGN KEY (RecordStatusID)
REFERENCES dbo.RecordStatuses (RecordStatusID)
GO
ALTER TABLE dbo.ConsultFactorTypes
ADD CONSTRAINT FK_ConsultFactorTypes_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
--indexes
CREATE NONCLUSTERED INDEX IX_ConsultFactorTypes_RecordStatusID ON dbo.ConsultFactorTypes
(
RecordStatusID
) 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 = NONE --use page compression; default = NONE
)
ON DefinitionIndex
GO
CREATE NONCLUSTERED INDEX IX_ConsultFactorTypes_ConsultFactorType ON dbo.ConsultFactorTypes
(
ConsultFactorType
) 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 = NONE --use page compression; default = NONE
)
ON DefinitionIndex
GO
--constraints
ALTER TABLE dbo.ConsultFactorTypes
ADD CONSTRAINT DF_ConsultFactorTypes_IsConsultToolboxRelated
DEFAULT 0
FOR IsConsultToolboxRelated
GO
--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS, source: NDim.ConsultFactorType',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'ConsultFactorTypeID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, relates to RecordStatuses table; source: opscode',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'RecordStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, relates to Batch Logs table',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Consult Factor Type system number; source: ConsultFactorTypeSID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'CDWConsultFactorTypeSysNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW Consult Factor Type number; source: ConsultFactorTypeID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'CDWConsultFactorTypeNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'CDW extract batch number; source: ExtractBatchID',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'CDWExtractBatchNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Alpha-numeric abbreviation for consult factor type; source: ConsultFactorType',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'ConsultFactorType'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Description of the consult factor type',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'ConsultFactorTypeText'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Character/text patterns used to search consult factor comment fields in order to associate with the consult factor type',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'SearchString'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/false is the user a point of contact in this role',
N'SCHEMA', N'dbo',
N'TABLE', N'ConsultFactorTypes',
N'COLUMN', N'IsConsultToolboxRelated'
GO