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.FBCSUBClaimProcedureCodes
(
--authoritative sources:
--A06.CDWWork.FBCS.UB92
--,<prinproccode, varchar(8000),>
--,<prinprocdate, datetime2(7),>
--,<otherproccodea, varchar(8000),>
--,<otherprocdatea, datetime2(7),>
--,<otherproccodeb, varchar(8000),>
--,<otherprocdateb, datetime2(7),>
--,<otherproccodec, varchar(8000),>
--,<otherprocdatec, datetime2(7),>
--,<otherproccoded, varchar(8000),>
--,<otherprocdated, datetime2(7),>
--,<otherproccodee, varchar(8000),>
--,<otherprocdatee, datetime2(7),>
--,<otherproccodef, varchar(8000),> --note that no dates after this point
--,<otherproccodeg, varchar(8000),>
--,<otherproccodeh, varchar(8000),>
--,<otherproccodei, varchar(8000),>
--,<otherproccodej, varchar(8000),>
--,<otherproccodek, varchar(8000),>
--,<otherproccodel, varchar(8000),>
--,<otherproccodem, varchar(8000),>
--,<otherproccoden, varchar(8000),>
--,<otherproccodeo, varchar(8000),>
--,<otherproccodep, varchar(8000),>
--,<otherproccodeq, varchar(8000),>
--,<otherproccoder, varchar(8000),>
--,<otherproccodes, varchar(8000),>
--,<otherproccodet, varchar(8000),>
--,<otherproccodeu, varchar(8000),>
--,<otherproccodev, varchar(8000),>
--,<otherproccodew, varchar(8000),>
--,<otherproccodex, varchar(8000),>
--,<otherproccodey, varchar(8000),>
--,<otherproccodez, varchar(8000),>

--fields
FBCSUBClaimProcedureCodeID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
FBCSUBClaimID bigint, --EPRS assigned (FK), comes from FBCSUBClaims PK
ProcedureCodeID int NOT NULL, --EPRS assigned (FK), comes from DiagnosisCodes
BatchLogID bigint, --ERPS assigned (FK)
PositionCode char(1) NOT NULL, --0(=principal), A thru Z
ProcedureDate datetime2(7) --various procdate fields

--primary key
CONSTRAINT PK_FBCSUBClaimProcedureCodes PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSUBClaimProcedureCodeID 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.FBCSUBClaimProcedureCodes
ADD CONSTRAINT FK_FBCSUBClaimProcedureCodes_FBCSUBClaims
FOREIGN KEY (FBCSUBClaimID)
REFERENCES dbo.FBCSUBClaims (FBCSUBClaimID)
GO

ALTER TABLE dbo.FBCSUBClaimProcedureCodes
ADD CONSTRAINT FK_FBCSUBClaimProcedureCodes_ProcedureCodes
FOREIGN KEY (ProcedureCodeID)
REFERENCES dbo.ProcedureCodes (ProcedureCodeID)
GO

ALTER TABLE dbo.FBCSUBClaimProcedureCodes
ADD CONSTRAINT FK_FBCSUBClaimProcedureCodes_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO

--indexes
/**
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSUBClaimProcedureCodes ON dbo.FBCSUBClaimProcedureCodes
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_FBCSUBClaimProcedureCodes_FBCSUBClaimID ON dbo.FBCSUBClaimProcedureCodes
(
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_FBCSUBClaimProcedureCodes_ProcedureCodeID ON dbo.FBCSUBClaimProcedureCodes
(
ProcedureCodeID
) 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_FBCSUBClaimProcedureCodes_PositionCode ON dbo.FBCSUBClaimProcedureCodes
(
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'FBCSUBClaimProcedureCodes',
N'COLUMN', N'FBCSUBClaimProcedureCodeID'
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'FBCSUBClaimProcedureCodes',
N'COLUMN', N'FBCSUBClaimID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Diagnosis Codes table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimProcedureCodes',
N'COLUMN', N'ProcedureCodeID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, relates to Batch Logs table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimProcedureCodes',
N'COLUMN', N'BatchLogID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Position order for the diagnosis code (e.g., 0=Admission, A thru Z; no dates F-Z)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimProcedureCodes',
N'COLUMN', N'PositionCode'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Date of Procedure',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaimProcedureCodes',
N'COLUMN', N'ProcedureDate'
GO