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

FBCSHCFAScoreID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned
FBCSFacilityID bigint, --EPRS assigned (FK)
FBCSHCFAID bigint, --EPRS assigned (FK)
FBCShcfaLinesID bigint, --EPRS assigned (FK)
DatabaseID varchar(10), --DatabaseID (EPRS assigned (FK))
DateScoreRcvdstamp datetime2(7), --ScoreRcvdDtstamp
DateFeed datetime2(7), --FeedDate
DateScore datetime2(7), --ScoreDate
ScoreNumber int, --id
RowNumber int, --RowNumber
StationNumber varchar(10), --StationID
LineNumber bigint, --LineID
Pay varchar(5), --Pay
Score bigint, --Score
ReasonCode varchar(50), --ReasonCode
ReasonDescription varchar(8000), --ReasonDescription




--primary key
CONSTRAINT PK_FBCSHCFAScores PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSHCFAScoreID 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_FBCSHCFAScores ON dbo.FBCSHCFAScores
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
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