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.BatchLogs
(
--source:
--EPRS specific
--fields
BatchLogID bigint IDENTITY(1,1) NOT NULL,
TableName varchar (50) NOT NULL,
FilePathName VARCHAR(400) NULL,
BatchDate datetime2(7) NOT NULL,
LoadStatus varchar(50) NOT NULL,
LoadCompleteDate datetime2(7),
BatchRecordCount bigint NOT NULL,
SourceBatchNumber bigint NOT NULL,
BadBatch BIT NOT NULL,
--primary key
CONSTRAINT PK_BatchLogs PRIMARY KEY CLUSTERED
(
BatchLogID ASC
)
)
ON AuditData
--when using clustered columnstore index, can't set data compression to PAGE
WITH (DATA_COMPRESSION = PAGE)
GO
--foreign keys
--indexes
CREATE NONCLUSTERED INDEX IX_BatchLogs_TableName ON dbo.BatchLogs
(
TableName
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --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 AuditIndex
GO
CREATE NONCLUSTERED INDEX IX_BatchLogs_BatchDate ON dbo.BatchLogs
(
BatchDate
) WITH (PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --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 AuditIndex
GO
CREATE NONCLUSTERED INDEX IX_BatchLogs_LoadStatus ON dbo.BatchLogs
(
LoadStatus
) WITH (PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --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 AuditIndex
GO
--constraints
ALTER TABLE dbo.BatchLogs
ADD CONSTRAINT DF_BatchLogs_BatchRecordCount
DEFAULT 0
FOR BatchRecordCount
GO
ALTER TABLE dbo.BatchLogs
ADD CONSTRAINT DF_BatchLogs_SourceBatchNumber
DEFAULT 0
FOR SourceBatchNumber
GO
ALTER TABLE dbo.BatchLogs
ADD CONSTRAINT DF_BatchLogs_BadBatch
DEFAULT 0
FOR BadBatch
GO
--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Table name for the batch load raw table',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', N'TableName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'If data is loaded from external file, note the filename and path (last known location)',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', N'FilePathName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date of the batch submission, this will be a critical date of when certain records were touched',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', N'BatchDate'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Possible values--EXTPROCESSING, EXTCOMPLETE, INTPROCESSING, INTCOMPLETE',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', 'LoadStatus'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date the batch load was completed',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', 'LoadCompleteDate'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Number of records loaded into the table for the batch',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', 'BatchRecordCount'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'The batch ID being used by an authoritative source; if zero, that means there is/was no batch ID in source to use',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', N'SourceBatchNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'true/false field to identify if a batch is bad',
N'SCHEMA', N'dbo',
N'TABLE', N'BatchLogs',
N'COLUMN', 'BadBatch'
GO