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.Visns
(
--fields
VisnID [tinyint] NOT NULL, --set to non-identity seeding so we can set pre-populate the ID with the same value as the VISN code
--RegionID [tinyint],
--AltRegionID [tinyint],
VisnCode [varchar](10),
VisnName [varchar](100) NOT NULL,
[Description] [varchar](200),
DateInactive [datetime2](7),
Inactive [bit] NOT NULL,
--primary key
CONSTRAINT PK_Visns PRIMARY KEY CLUSTERED
(
VisnID ASC
)
)
ON DefinitionData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE) --less than 10,000 rows, don't need compression
GO
--foreign keys
--ALTER TABLE dbo.Visns
-- ADD CONSTRAINT FK_Visns_RegionID
-- FOREIGN KEY (RegionID)
-- REFERENCES dbo.Regions (RegionID)
--GO
--ALTER TABLE dbo.Visns
-- ADD CONSTRAINT FK_Visns_AltRegionID
-- FOREIGN KEY (AltRegionID)
-- REFERENCES dbo.Regions (RegionID)
--GO
--indexes
--CREATE NONCLUSTERED INDEX IX_Visns_RegionID ON dbo.Visns
-- (
-- RegionID
-- ) 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 = NONE --don't need, less than 10,000 rows
-- )
-- ON DefinitionIndex
--GO
--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'Visns',
N'COLUMN', N'VisnID'
GO
--EXECUTE sp_addextendedproperty
--N'MS_Description', N'Foreign Key, unique to EPRS, relates to Regions table, what most VA uses',
--N'SCHEMA', N'dbo',
--N'TABLE', N'Visns',
--N'COLUMN', N'RegionID'
--GO
--EXECUTE sp_addextendedproperty
--N'MS_Description', N'Foreign Key, unique to EPRS, relates to Regions table, alternate region',
--N'SCHEMA', N'dbo',
--N'TABLE', N'Visns',
--N'COLUMN', N'AltRegionID'
--GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Abbreviation for the Visn',
N'SCHEMA', N'dbo',
N'TABLE', N'Visns',
N'COLUMN', N'VisnCode'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Name of the Visn',
N'SCHEMA', N'dbo',
N'TABLE', N'Visns',
N'COLUMN', N'VisnName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Lengthier description of the Visn',
N'SCHEMA', N'dbo',
N'TABLE', N'Visns',
N'COLUMN', N'Description'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date record was inactivated',
N'SCHEMA', N'dbo',
N'TABLE', N'Visns',
N'COLUMN', N'DateInactive'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies if the record is inactive',
N'SCHEMA', N'dbo',
N'TABLE', N'Visns',
N'COLUMN', N'Inactive'
GO