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.StaffUsers
(
--authoritative source:
--SQL52.SPV.SStaff.SStaff

--fields
StaffUserID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
StationID smallint NOT NULL, --EPRS assigned (FK), references Stations
RecordStatusID tinyint NULL, --EPRS assigned (Fk), referencess RecordStatuses

CDWStaffUserSysNumer bigint NULL,
StaffUserIEN bigint NULL,
StaffName varchar(100) NULL,
LastName varchar(50) NULL,
FirstName varchar(50) NULL,
MiddleName varchar(50) NULL,
StaffNamePrefix varchar(50) NULL,
StaffNameSuffix varchar(50) NULL


--EXAMPLES:
/** (<StaffSID, int,>
,<StaffIEN, varchar(50),>
,<Sta3n, smallint,>
,<StaffName, varchar(100),>
,<LastName, varchar(50),>
,<FirstName, varchar(50),>
,<MiddleName, varchar(50),>
,<StaffNamePrefix, varchar(50),>
,<StaffNameSuffix, varchar(50),>
,<StaffSSN, varchar(50),>
,<NetworkUsername, varchar(100),>
,<DEA, varchar(50),>
,<VANumber, varchar(50),>
,<MailCode, varchar(50),>
,<StreetAddress1, varchar(50),>
,<StreetAddress2, varchar(50),>
,<StreetAddress3, varchar(50),>
,<City, varchar(50),>
,<StateName, varchar(50),>
,<ZipCode, varchar(50),>
,<TemporaryAddress1, varchar(50),>
,<TemporaryAddress2, varchar(50),>
,<TemporaryAddress3, varchar(50),>
,<TemporaryCity, varchar(50),>
,<TemporaryStateName, varchar(50),>
,<TemporaryZipCode, varchar(50),>
,<TemporaryAddressStartDate, date,>
,<TemporaryAddressEndDate, date,>
,<HomePhone, varchar(50),>
,<OfficePhone, varchar(50),>
,<Phone3, varchar(50),>
,<Phone4, varchar(50),>
,<CommercialPhone, varchar(50),>
,<FaxNumber, varchar(50),>
,<VoicePager, varchar(50),>
,<DigitalPager, varchar(50),>
,<Room, varchar(50),>
,<EmailAddress, varchar(100),>
,<NPIAuthorizedReleaseFlag, char(1),>
,<NPI, varchar(50),>
,<SignatureBlockName, varchar(50),>
,<SignatureBlockTitle, varchar(50),>
,<TaxID, varchar(50),>
,<DateOfBirth, date,>
,<Age, numeric(18,0),>
,<Gender, varchar(50),>
,<PAIDEmployeeIEN, varchar(50),>
,<TerminationDate, date,>
,<TerminationReason, varchar(50),>
,<PositionTitle, varchar(50),>
,<DeleteAllMailAccessFlag, char(1),>
,<DeleteKeysAtTerminationFlag, char(1),>
,<ProviderClassIEN, varchar(50),>
,<ProviderClassSID, int,>
,<ProviderClass, varchar(50),>
,<LastUsedTerminalTypeIEN, varchar(50),>
,<LastUsedTerminalType, varchar(50),>
,<ProviderScheduleType, varchar(50),>
,<Degree, varchar(50),>
,<PACFlag, char(1),>
,<HINQEmployeeNumberFlag, char(1),>
,<DelegateOfStaffIEN, varchar(50),>
,<DelegateOfStaffSID, int,>
,<DelegationDate, date,>
,<ServiceComputationDate, date,>
,<DelegationLevel, varchar(50),>
,<ElectronicSignatureCodeFlag, char(1),>
,<AccessCodeFlag, char(1),>
,<VerifyCodeFlag, char(1),>
,<VerifyCodeLastChangedDate, date,>
,<EnteredDate, date,>
,<CreatedByStaffIEN, varchar(50),>
,<CreatedByStaffSID, int,>
,<MedOrdersAuthorizedFlag, char(1),>
,<ServiceSectionIEN, varchar(50),>
,<ServiceSectionSID, int,>
,<ServiceSection, varchar(50),>
,<ServiceType, char(1),>
,<ServiceParentIEN, varchar(50),>
,<SocialWorkerFlag, char(1),>
,<SocialWorkerPositionTitle, varchar(50),>
,<SocialWorkerImmediateSupervisorStaffIEN, varchar(50),>
,<SocialWorkerImmediateSupervisorStaffSID, int,>
,<SupplyEmployee, varchar(50),>
,<LastEditedDateTime, smalldatetime,>
,<LastSignonDateTime, smalldatetime,>
,<InactivationDate, date,>
,<DisUserFlag, char(1),>
,<XUSActiveUserFlag, char(1),>
,<PrimaryMenuOptionIEN, varchar(50),>
,<PrimaryMenuOptionSID, int,>
,<RequiresCosignerFlag, char(1),>
,<UsualCosignerStaffIEN, varchar(50),>
,<UsualCosignerStaffSID, int,>
,<NonVAPrescriberFlag, char(1),>
,<ExclusionaryCheckFlag, char(1),>
,<ExclusionaryCheckDate, date,>
,<ExclusionaryFlag, char(1),>
,<StaffReviewFlag, char(1),>
,<StaffRemarks, varchar(100),>
,<ETLBatchID, int,>
,<VistaCreateDate, datetime,>
,<VistaEditDate, datetime,>
,<OpCode, char(1),>)

**/
--primary key
CONSTRAINT PK_StaffUserID PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
StaffUserID 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.StaffUsers
ADD CONSTRAINT FK_StaffUsers_StationID
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO

ALTER TABLE dbo.StaffUsers
ADD CONSTRAINT FK_StaffUsers_RecordStatusID
FOREIGN KEY (RecordStatusID)
REFERENCES dbo.RecordStatuses (RecordStatusID)
GO


--indexes
----Create clustered columnstore index that doesn't exist yet
--CREATE CLUSTERED COLUMNSTORE INDEX CCIX_StaffUsers ON dbo.StaffUsers
--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
-- , 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_StaffUsers_StaffUserID ON dbo.StaffUsers
(
StaffUserID
) 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_StaffUsers_StationID ON dbo.StaffUsers
(
StationID
) 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_StaffUsers_Lastname ON dbo.StaffUsers
(
Lastname
) 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
/**

INSERT INTO StaffUsers (StationID, CDWStaffUserSysNumer, StaffUserIEN)
VALUES

**/

--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key for the table, unique to EPRS; note the authoritative source table is SPV.SStaff.SStaff',
N'SCHEMA', N'dbo',
N'TABLE', N'StaffUsers',
N'COLUMN', N'StaffUserID'
GO


EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to RecordStatuses Table',
N'SCHEMA', N'dbo',
N'TABLE', N'StaffUsers',
N'COLUMN', N'RecordStatusID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Staff full name, Source table: Spv.Sstaff.Sstaff',
N'SCHEMA', N'dbo',
N'TABLE', N'StaffUsers',
N'COLUMN', N'StaffName'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Staff Last name, Source table: Spv.sstaff.sstaff',
N'SCHEMA', N'dbo',
N'TABLE', N'StaffUsers',
N'COLUMN', N'LastName'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Staff First name, Source table: Spv.sstaff.sstaff',
N'SCHEMA', N'dbo',
N'TABLE', N'StaffUsers',
N'COLUMN', N'FirstName'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Staff Middle name, Source table: Spv.sstaff.sstaff',
N'SCHEMA', N'dbo',
N'TABLE', N'StaffUsers',
N'COLUMN', N'MiddleName'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Staff name Prefix, Source table: Spv.sstaff.sstaff',
N'SCHEMA', N'dbo',
N'TABLE', N'StaffUsers',
N'COLUMN', N'StaffNamePrefix'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Staff name Suffix, Source table: Spv.sstaff.sstaff',
N'SCHEMA', N'dbo',
N'TABLE', N'StaffUsers',
N'COLUMN', N'StaffNameSuffix'
GO