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.FBCSHCFALines
(
--authoritative source: A06.CDWWork.FBCS.hcfalines
--fields

FBCShcfaLinesID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned
FBCSHCFAClaimID bigint, --EPRS assigned (FK)
DateFrom datetime2(7), --Box24AFrom
DateTo datetime2(7), --Box24ATo
VISNNumber varchar(30), --station__no
PlaceOfService varchar(100), --Box24B
MedicareProvidersEMG varchar(100), --Box24C
Code varchar(100), --Box24DCPT
Modifier varchar(100), --Box24DMods
DiagnosisPointerCodes varchar(100), --Box24E
Charges numeric(28), --Box24F
DaysUnits int, --Box24G
DiagnosisPointerNumber int, --Position
Repriced int, --Repriced
Pay int, --Pay
Authorized varchar(100), --Authorized
Reason int, --Reason
Paid numeric(28), --Paid
PaymentStatus int, --PaymentStatus
RenderingProvider varchar(100), --RenderingProvider
Codesearch varchar(5), --Codesearch
Modifiersearch varchar(2), --Modifiersearch
VAFundControlPoint varchar(1000), --vafcp
VABatchNumber varchar(1000), --vafb
VAObligationNumber varchar(1000), --vaon
VAInvoiceNumber varchar(2000), --vain
AllowableType varchar(50), --AllowableType
vistaallowableamount numeric(28), --vistaallowableamount



/*

[station__no] [varchar](30) NOT NULL,
[HCFAID] [bigint] NULL,
[ID] [bigint] NULL,
[Box24AFrom] [smalldatetime] NULL,
[Box24ATo] [smalldatetime] NULL,
[Box24B] [varchar](8000) NULL,
[Box24C] [varchar](8000) NULL,
[Box24DCPT] [varchar](8000) NULL,
[Box24DMods] [varchar](8000) NULL,
[Box24E] [varchar](8000) NULL,
[Box24F] [numeric](28, 6) NULL,
[Box24G] [bigint] NULL,
[Position] [int] NULL,
[RepricedAmount] [numeric](28, 6) NULL,
[Repriced] [int] NULL,
[Pay] [int] NULL,
[Authorized] [varchar](8000) NULL,
[Reason] [bigint] NULL,
[HealthNetRepricedAmount] [numeric](28, 6) NULL,
[paid] [numeric](28, 6) NULL,
[PaymentStatus] [int] NULL,
[VistaID] [varchar](50) NULL,
[RenderingProvider] [varchar](8000) NULL,
[codesearch] [varchar](5) NULL,
[modifiersearch] [varchar](2) NULL,
[vafcp] [varchar](8000) NULL,
[vafb] [varchar](8000) NULL,
[vaon] [varchar](8000) NULL,
[vain] [varchar](8000) NULL,
[AllowableType] [varchar](50) NULL,
[OtherAllowableType] [varchar](20) NULL,
[OtherAllowableAmount] [numeric](28, 6) NULL,
[MedicareFee] [numeric](28, 6) NULL,
[CheckNumber] [varchar](50) NULL,
[VoucherDate] [datetime2](7) NULL,
[Minutes] [numeric](28, 6) NULL,
[Mileage] [numeric](28, 6) NULL,
[VistaAllowableType] [varchar](2000) NULL,
[VistaAllowableAmount] [numeric](28, 6) NULL,
[CMSAllowableType] [varchar](2000) NULL,
[CMSAllowableAmount] [numeric](28, 6) NULL,
[VAFBINT] [varchar](8000) NULL,
[ContractNumber] [varchar](20) NULL,
[LocalRejectReason] [varchar](60) NULL,
[CFRejectReasons] [varchar](100) NULL,
[LineChosenAmountToPay] [numeric](28, 6) NULL,
[DuplicateJustificationID] [varchar](8000) NULL,
[note_text] [varchar](500) NULL

*/

--primary key
CONSTRAINT PK_FBCSHCFALines PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSHCFALinesID ASC
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO

--foreign keys

--indexes

/**
CREATE INDEX statement failed because a clustered columnstore index cannot be created on a table that has a nonclustered index. Consider dropping all nonclustered indexes and trying again.
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSHCFALines ON dbo.FBCSHCFALines
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?

--constraints

--Metadata descriptions for each field
/*Commenting these out for now, because we they're incomplete, and they are also duplicates of other tables' metadata
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAScores',
N'COLUMN', N'FBCSHCFAScoreID'
GO

*/