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.FBCSUBClaimValueCodes
(
--authoritative sources:
--A06.CDWWork.FBCS.UB92
--,<valuecode_box39a, varchar(8000),>
--,<valuecodeamt_box39a, varchar(8000),>
--,<valuecode_box39b, varchar(8000),>
--,<valuecodeamt_box39b, varchar(8000),>
--,<valuecode_box39c, varchar(8000),>
--,<valuecodeamt_box39c, varchar(8000),>
--,<valuecode_box39d, varchar(8000),>
--,<valuecodeamt_box39d, varchar(8000),>
--,<valuecode_box40a, varchar(8000),>
--,<valuecodeamt_box40a, varchar(8000),>
--,<valuecode_box40b, varchar(8000),>
--,<valuecodeamt_box40b, varchar(8000),>
--,<valuecode_box40c, varchar(8000),>
--,<valuecodeamt_box40c, varchar(8000),>
--,<valuecode_box40d, varchar(8000),>
--,<valuecodeamt_box40d, varchar(8000),>
--,<valuecode_box41a, varchar(8000),>
--,<valuecodeamt_box41a, varchar(8000),>
--,<valuecode_box41b, varchar(8000),>
--,<valuecodeamt_box41b, varchar(8000),>
--,<valuecode_box41c, varchar(8000),>
--,<valuecodeamt_box41c, varchar(8000),>
--,<valuecode_box41d, varchar(8000),>
--,<valuecodeamt_box41d, varchar(8000),>
--fields
FBCSUBClaimValueCodeID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
FBCSUBClaimID bigint, --EPRS assigned (FK), references FBCSUBClaims
ValueCodeID smallint NOT NULL, --EPRS assigned (FK), references ValueCodes
BatchLogID bigint, --ERPS assigned (FK), references BatchLogs
PositionCode varchar(20) NOT NULL, --identifies box that comes from (39-41, a-d combos)
ValueAmount money, --will need to convert from string to money
--primary key
CONSTRAINT PK_FBCSUBClaimValueCodes PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSUBClaimValueCodeID 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.FBCSUBClaimValueCodes
ADD CONSTRAINT FK_FBCSUBClaimValueCodes_FBCSUBClaims
FOREIGN KEY (FBCSUBClaimID)
REFERENCES dbo.FBCSUBClaims (FBCSUBClaimID)
GO
ALTER TABLE dbo.FBCSUBClaimValueCodes
ADD CONSTRAINT FK_FBCSUBClaimValueCodes_ValueCodes
FOREIGN KEY (ValueCodeID)
REFERENCES dbo.ValueCodes (ValueCodeID)
GO
ALTER TABLE dbo.FBCSUBClaimValueCodes
ADD CONSTRAINT FK_FBCSUBClaimValueCodes_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
--indexes
/**
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSUBClaimValueCodes ON dbo.FBCSUBClaimValueCodes
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_FBCSUBClaimValueCodes_FBCSUBClaimID ON dbo.FBCSUBClaimValueCodes
(
FBCSUBClaimID
) 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
CREATE NONCLUSTERED INDEX IX_FBCSClaimValueCodes_ValueCodeID ON dbo.FBCSUBClaimValueCodes
(
ValueCodeID
) 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
CREATE NONCLUSTERED INDEX IX_FBCSClaimValueCodes_PositionCode ON dbo.FBCSUBClaimValueCodes
(
PositionCode
) 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 A06.CDWWork.FBCS.ub92',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimValueCodes',
N'COLUMN', N'FBCSUBClaimValueCodeID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to the FBCS UB Claims table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimValueCodes',
N'COLUMN', N'FBCSUBClaimID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Value Codes table, source: various valuecode fields',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimValueCodes',
N'COLUMN', N'ValueCodeID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Batch Logs table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimValueCodes',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Reference for the value position code, which box it comes from (e.g., 39a, 39b, 40a, 40b, etc.)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimValueCodes',
N'COLUMN', N'PositionCode'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Value amount (dollars), source: various valuecodeamt fields',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimValueCodes',
N'COLUMN', N'ValueAmount'
GO