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