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
?--leaving a bunch of fields in, showing how can expand to have stations (parent stations) and CBOCs/clinics
CREATE TABLE dbo.Stations
(
--fields
StationID [smallint] IDENTITY(1,1) NOT NULL,
ParentStationID [smallint],
RegionID [tinyint],
CCNRegionID [tinyint],
VisnID [tinyint],
StateID [tinyint],
Station3N [varchar](10),
Station6A [varchar](10),
Moniker [varchar](5), --in CDW, this is called SiteCode
StationName [varchar](200) NOT NULL,
ParentStationName [varchar](200),
DisplayName [varchar](100),
Address1 NVARCHAR(100),
Address2 NVARCHAR(100),
Address3 NVARCHAR(100),
City [varchar](100),
ZipCode [varchar](10),
ZipCode4 [varchar](10),
Longitude [numeric](18,10),
Latitude [numeric](18,10),
ParentStation [bit] NOT NULL,
TryParseStation6A [bit] NOT NULL,
Inactive [bit] NOT NULL,
DateInactive [datetime2](7),
--primary key
CONSTRAINT PK_Stations PRIMARY KEY CLUSTERED
(
StationID 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.Stations
ADD CONSTRAINT FK_Stations_RegionID
FOREIGN KEY (RegionID)
REFERENCES dbo.Regions (RegionID)
GO
ALTER TABLE dbo.Stations
ADD CONSTRAINT FK_Stations_CCNRegionID
FOREIGN KEY (CCNRegionID)
REFERENCES dbo.Regions (RegionID)
GO
ALTER TABLE dbo.Stations
ADD CONSTRAINT FK_Stations_Visns
FOREIGN KEY (VisnID)
REFERENCES dbo.Visns (VisnID)
GO
ALTER TABLE dbo.Stations
ADD CONSTRAINT FK_Stations_ParentStationID
FOREIGN KEY (ParentStationID)
REFERENCES dbo.Stations (StationID)
GO
ALTER TABLE dbo.Stations
ADD CONSTRAINT FK_Stations_States
FOREIGN KEY (StateID)
REFERENCES dbo.States (StateID)
GO
--indexes
CREATE NONCLUSTERED INDEX IX_Stations_ParentStationID ON dbo.Stations
(
ParentStationID
) 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
CREATE NONCLUSTERED INDEX IX_Stations_RegionID ON dbo.Stations
(
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
CREATE NONCLUSTERED INDEX IX_Stations_CCNRegionID ON dbo.Stations
(
CCNRegionID
) 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
CREATE NONCLUSTERED INDEX IX_Stations_VisnID ON dbo.Stations
(
VisnID
) 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
CREATE NONCLUSTERED INDEX IX_Stations_StateID ON dbo.Stations
(
StateID
) 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
ALTER TABLE dbo.Stations
ADD CONSTRAINT DF_Stations_ParentStation
DEFAULT 0
FOR ParentStation
GO
ALTER TABLE dbo.Stations
ADD CONSTRAINT DF_Stations_IncludeTryParse
DEFAULT 0
FOR TryParseStation6A
GO
ALTER TABLE dbo.Stations
ADD CONSTRAINT DF_Stations_Inactive
DEFAULT 0
FOR Inactive
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'Stations',
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, self-referential key',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'ParentStationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Regions table, VHA Region',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'RegionID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Regions table, CCN Region',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'CCNRegionID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Visns table',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'VisnID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to States table',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'StateID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'3-digit numeric code for the Station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'Station3N'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'6-digit code for the Station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'Station6A'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'3-digit alpha code for the Station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'Moniker'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Name for the Station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'StationName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Name for the parent Station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'ParentStationName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Shorter name for the Station, primarily meant to be used in web controls',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'DisplayName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Main address for the station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'Address1'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Secondary address for the station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'Address2'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Tertiary address for the station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'Address3'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'City where the station is located',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'City'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'5-digit zip code',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'ZipCode'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'4-digit detailed zip code',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'ZipCode4'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Longitude coordinates of the station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'Longitude'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Latitude coordinates of the station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'Latitude'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False that identifies if the Station is a parent Station',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'ParentStation'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False that identifies if the Station6A should be used to try and slice data down from Sta3N to Sta6A level (e.g., 528, 589, etc.)',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
N'COLUMN', N'TryParseStation6A'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date record was inactivated',
N'SCHEMA', N'dbo',
N'TABLE', N'Stations',
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'Stations',
N'COLUMN', N'Inactive'
GO