Table: [AdventureWorks2016CTP3].[Production].[WorkOrderRouting]

CollapseAll image

Collapse image Table properties


Name  Value 
 Schema  [Production]
 Owner  [dbo]
 Table is replicated   
 Creation date  23.10.2015
 ID  1246627484
 Located on  PRIMARY
 Data size KB  5600 KB
 Index size KB  1648 KB
 Rows  67131
 ChangeTrackingEnabled   
 FileStreamFileGroup   
 FileStreamPartitionScheme   
 LockEscalation  TABLE
 TrackColumnsUpdatedEnabled   
 Table is filetable   
 Filetable directory   
 Filetable collate   
 Filetable primary key  [PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence]
 Filetable streamid unique   
 Filetable fullpath unique   
 Filetable is enabled   
 Is memory optimized   
 Durability  SCHEMA_AND_DATA

Collapse image Creation options


Name  Value 
QUOTED_IDENTIFIER ON
ANSI_NULLS ON
ANSI_PADDING OFF

Collapse image Columns


    Name  Description  Data type  Max length  Nullable  Default IsGUID  SPARSE Computed value
Primary key Foreign key WorkOrderID     int 4        
Primary key   ProductID     int 4        
Primary key   OperationSequence     smallint 2        
  Foreign key LocationID     smallint 2        
    ScheduledStartDate     datetime 8        
    ScheduledEndDate     datetime 8        
    ActualStartDate     datetime 8      
    ActualEndDate     datetime 8      
    ActualResourceHrs     decimal 5      
    PlannedCost     money 8        
    ActualCost     money 8      
    ModifiedDate     datetime 8   (getdate())    
Total: 12 column(s)

Collapse image Indexes


Index  Description  Primary  Unique  Size KB 
[PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence] WorkOrderID   7248 0
[IX_WorkOrderRouting_ProductID] ProductID       1352 0
Total: 2 Index/indexes

Collapse image Columnstore indexes


No columnstore indexes exist

Collapse image Check constraints


Name  Expression 
CK_WorkOrderRouting_ActualCost ([ActualCost]>(0.00))
CK_WorkOrderRouting_ActualEndDate ([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)
CK_WorkOrderRouting_ActualResourceHrs ([ActualResourceHrs]>=(0.0000))
CK_WorkOrderRouting_PlannedCost ([PlannedCost]>(0.00))
CK_WorkOrderRouting_ScheduledEndDate ([ScheduledEndDate]>=[ScheduledStartDate])

Collapse image Referenced tables


Table  Primary key or unique constraint  Foreign key 
[Production].[Location] PK_Location_LocationID FK_WorkOrderRouting_Location_LocationID
[Production].[WorkOrder] PK_WorkOrder_WorkOrderID FK_WorkOrderRouting_WorkOrder_WorkOrderID
Total: 2 table(s)

Collapse image Objects that [Production].[WorkOrderRouting] depends on


Object name Object type Dep level
[Production] Schema 1
[Production].[Location] Table 1
[Production].[WorkOrder] Table 1
Total 3 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Work order details.

Collapse image Table options


Name  Value 
Pintable OFF
Table lock on bulk load OFF
Insert row lock OFF
Text in row 0
Large value types out of row OFF

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING OFF
GO
CREATE TABLE [Production].[WorkOrderRouting] (
        [WorkOrderID]            [int] NOT NULL,
        [ProductID]              [int] NOT NULL,
        [OperationSequence]      [smallint] NOT NULL,
        [LocationID]             [smallint] NOT NULL,
        [ScheduledStartDate]     [datetime] NOT NULL,
        [ScheduledEndDate]       [datetime] NOT NULL,
        [ActualStartDate]        [datetime] NULL,
        [ActualEndDate]          [datetime] NULL,
        [ActualResourceHrs]      [decimal](9, 4) NULL,
        [PlannedCost]            [money] NOT NULL,
        [ActualCost]             [money] NULL,
        [ModifiedDate]           [datetime] NOT NULL,
        CONSTRAINT [PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence]
        PRIMARY KEY
        CLUSTERED
        ([WorkOrderID], [ProductID], [OperationSequence])
    ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key (clustered) constraint', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Clustered index created by a primary key constraint.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'INDEX', N'PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence'
GO
ALTER TABLE [Production].[WorkOrderRouting]
    ADD
    CONSTRAINT [CK_WorkOrderRouting_ActualCost]
    CHECK
    ([ActualCost]>(0.00))
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [ActualCost] > (0.00)', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'CK_WorkOrderRouting_ActualCost'
GO
ALTER TABLE [Production].[WorkOrderRouting]
CHECK CONSTRAINT [CK_WorkOrderRouting_ActualCost]
GO
ALTER TABLE [Production].[WorkOrderRouting]
    ADD
    CONSTRAINT [CK_WorkOrderRouting_ActualEndDate]
    CHECK
    ([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [ActualEndDate] >= [ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'CK_WorkOrderRouting_ActualEndDate'
GO
ALTER TABLE [Production].[WorkOrderRouting]
CHECK CONSTRAINT [CK_WorkOrderRouting_ActualEndDate]
GO
ALTER TABLE [Production].[WorkOrderRouting]
    ADD
    CONSTRAINT [CK_WorkOrderRouting_ActualResourceHrs]
    CHECK
    ([ActualResourceHrs]>=(0.0000))
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [ActualResourceHrs] >= (0.0000)', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'CK_WorkOrderRouting_ActualResourceHrs'
GO
ALTER TABLE [Production].[WorkOrderRouting]
CHECK CONSTRAINT [CK_WorkOrderRouting_ActualResourceHrs]
GO
ALTER TABLE [Production].[WorkOrderRouting]
    ADD
    CONSTRAINT [CK_WorkOrderRouting_PlannedCost]
    CHECK
    ([PlannedCost]>(0.00))
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [PlannedCost] > (0.00)', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'CK_WorkOrderRouting_PlannedCost'
GO
ALTER TABLE [Production].[WorkOrderRouting]
CHECK CONSTRAINT [CK_WorkOrderRouting_PlannedCost]
GO
ALTER TABLE [Production].[WorkOrderRouting]
    ADD
    CONSTRAINT [CK_WorkOrderRouting_ScheduledEndDate]
    CHECK
    ([ScheduledEndDate]>=[ScheduledStartDate])
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [ScheduledEndDate] >= [ScheduledStartDate]', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'CK_WorkOrderRouting_ScheduledEndDate'
GO
ALTER TABLE [Production].[WorkOrderRouting]
CHECK CONSTRAINT [CK_WorkOrderRouting_ScheduledEndDate]
GO
ALTER TABLE [Production].[WorkOrderRouting]
    ADD
    CONSTRAINT [DF_WorkOrderRouting_ModifiedDate]
    DEFAULT (getdate()) FOR [ModifiedDate]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of GETDATE()', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'DF_WorkOrderRouting_ModifiedDate'
GO
ALTER TABLE [Production].[WorkOrderRouting]
    WITH CHECK
    ADD CONSTRAINT [FK_WorkOrderRouting_Location_LocationID]
    FOREIGN KEY ([LocationID]) REFERENCES [Production].[Location] ([LocationID])
ALTER TABLE [Production].[WorkOrderRouting]
    CHECK CONSTRAINT [FK_WorkOrderRouting_Location_LocationID]

GO
EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing Location.LocationID.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'FK_WorkOrderRouting_Location_LocationID'
GO
ALTER TABLE [Production].[WorkOrderRouting]
    WITH CHECK
    ADD CONSTRAINT [FK_WorkOrderRouting_WorkOrder_WorkOrderID]
    FOREIGN KEY ([WorkOrderID]) REFERENCES [Production].[WorkOrder] ([WorkOrderID])
ALTER TABLE [Production].[WorkOrderRouting]
    CHECK CONSTRAINT [FK_WorkOrderRouting_WorkOrder_WorkOrderID]

GO
EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing WorkOrder.WorkOrderID.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'CONSTRAINT', N'FK_WorkOrderRouting_WorkOrder_WorkOrderID'
GO
CREATE NONCLUSTERED INDEX [IX_WorkOrderRouting_ProductID]
    ON [Production].[WorkOrderRouting] ([ProductID])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Nonclustered index.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'INDEX', N'IX_WorkOrderRouting_ProductID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Actual manufacturing cost.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'ActualCost'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Actual end date.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'ActualEndDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Number of manufacturing hours used.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'ActualResourceHrs'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Actual start date.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'ActualStartDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Manufacturing location where the part is processed. Foreign key to Location.LocationID.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'LocationID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date and time the record was last updated.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'ModifiedDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key. Indicates the manufacturing process sequence.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'OperationSequence'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Estimated manufacturing cost.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'PlannedCost'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key. Foreign key to Product.ProductID.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'ProductID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Planned manufacturing end date.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'ScheduledEndDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Planned manufacturing start date.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'ScheduledStartDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key. Foreign key to WorkOrder.WorkOrderID.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', 'COLUMN', N'WorkOrderID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Work order details.', 'SCHEMA', N'Production', 'TABLE', N'WorkOrderRouting', NULL, NULL
GO

Collapse image See also


List of tables