Table: [AdventureWorks2016CTP3].[Production].[BillOfMaterials]

CollapseAll image

Collapse image Table properties


Name  Value 
 Schema  [Production]
 Owner  [dbo]
 Table is replicated   
 Creation date  23.10.2015
 ID  821577965
 Located on  PRIMARY
 Data size KB  160 KB
 Index size KB  200 KB
 Rows  2679
 ChangeTrackingEnabled   
 FileStreamFileGroup   
 FileStreamPartitionScheme   
 LockEscalation  TABLE
 TrackColumnsUpdatedEnabled   
 Table is filetable   
 Filetable directory   
 Filetable collate   
 Filetable primary key  [PK_BillOfMaterials_BillOfMaterialsID]
 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 ON

Collapse image Columns


      Name  Description  Data type  Max length  Nullable  Default IsGUID  SPARSE Computed value
Primary key Identity   BillOfMaterialsID     int 4        
    Foreign key ProductAssemblyID     int 4      
    Foreign key ComponentID     int 4        
      StartDate     datetime 8   (getdate())    
      EndDate     datetime 8      
    Foreign key UnitMeasureCode     nchar 3        
      BOMLevel     smallint 2        
      PerAssemblyQty     decimal 5   ((1.00))    
      ModifiedDate     datetime 8   (getdate())    
Total: 9 column(s)

Collapse image Indexes


Index  Description  Primary  Unique  Size KB 
[PK_BillOfMaterials_BillOfMaterialsID] BillOfMaterialsID   88 0
[AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate] ProductAssemblyID     360 0
[IX_BillOfMaterials_UnitMeasureCode] UnitMeasureCode       96 0
Total: 3 Index/indexes

Collapse image Columnstore indexes


No columnstore indexes exist

Collapse image Check constraints


Name  Expression 
CK_BillOfMaterials_BOMLevel ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
CK_BillOfMaterials_EndDate ([EndDate]>[StartDate] OR [EndDate] IS NULL)
CK_BillOfMaterials_PerAssemblyQty ([PerAssemblyQty]>=(1.00))
CK_BillOfMaterials_ProductAssemblyID ([ProductAssemblyID]<>[ComponentID])

Collapse image Identity column


  Name  Seed  Increment  Not for replication 
 BillOfMaterialsID   1   1   

Collapse image Referenced tables


Table  Primary key or unique constraint  Foreign key 
[Production].[Product] PK_Product_ProductID FK_BillOfMaterials_Product_ComponentID
[Production].[Product] PK_Product_ProductID FK_BillOfMaterials_Product_ProductAssemblyID
[Production].[UnitMeasure] PK_UnitMeasure_UnitMeasureCode FK_BillOfMaterials_UnitMeasure_UnitMeasureCode
Total: 3 table(s)

Collapse image Objects that depend on [Production].[BillOfMaterials]


Object name Object type Dep level
[dbo].[uspGetBillOfMaterials] Procedure 1
[dbo].[uspGetWhereUsedProductID] Procedure 1
Total 2 object(s)

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


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

Collapse image Extended properties


Name  Value 
MS_Description Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

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 ON
GO
CREATE TABLE [Production].[BillOfMaterials] (
        [BillOfMaterialsID]     [int] IDENTITY(1, 1) NOT NULL,
        [ProductAssemblyID]     [int] NULL,
        [ComponentID]           [int] NOT NULL,
        [StartDate]             [datetime] NOT NULL,
        [EndDate]               [datetime] NULL,
        [UnitMeasureCode]       [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [BOMLevel]              [smallint] NOT NULL,
        [PerAssemblyQty]        [decimal](8, 2) NOT NULL,
        [ModifiedDate]          [datetime] NOT NULL,
        CONSTRAINT [PK_BillOfMaterials_BillOfMaterialsID]
        PRIMARY KEY
        NONCLUSTERED
        ([BillOfMaterialsID])
    ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key (clustered) constraint', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'PK_BillOfMaterials_BillOfMaterialsID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Nonclustered index created by a primary key constraint.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'INDEX', N'PK_BillOfMaterials_BillOfMaterialsID'
GO
ALTER TABLE [Production].[BillOfMaterials]
    ADD
    CONSTRAINT [CK_BillOfMaterials_BOMLevel]
    CHECK
    ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1)', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'CK_BillOfMaterials_BOMLevel'
GO
ALTER TABLE [Production].[BillOfMaterials]
CHECK CONSTRAINT [CK_BillOfMaterials_BOMLevel]
GO
ALTER TABLE [Production].[BillOfMaterials]
    ADD
    CONSTRAINT [CK_BillOfMaterials_EndDate]
    CHECK
    ([EndDate]>[StartDate] OR [EndDate] IS NULL)
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'CK_BillOfMaterials_EndDate'
GO
ALTER TABLE [Production].[BillOfMaterials]
CHECK CONSTRAINT [CK_BillOfMaterials_EndDate]
GO
ALTER TABLE [Production].[BillOfMaterials]
    ADD
    CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty]
    CHECK
    ([PerAssemblyQty]>=(1.00))
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [PerAssemblyQty] >= (1.00)', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'CK_BillOfMaterials_PerAssemblyQty'
GO
ALTER TABLE [Production].[BillOfMaterials]
CHECK CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty]
GO
ALTER TABLE [Production].[BillOfMaterials]
    ADD
    CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID]
    CHECK
    ([ProductAssemblyID]<>[ComponentID])
GO
EXEC sp_addextendedproperty N'MS_Description', N'Check constraint [ProductAssemblyID] <> [ComponentID]', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'CK_BillOfMaterials_ProductAssemblyID'
GO
ALTER TABLE [Production].[BillOfMaterials]
CHECK CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID]
GO
ALTER TABLE [Production].[BillOfMaterials]
    ADD
    CONSTRAINT [DF_BillOfMaterials_ModifiedDate]
    DEFAULT (getdate()) FOR [ModifiedDate]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of GETDATE()', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'DF_BillOfMaterials_ModifiedDate'
GO
ALTER TABLE [Production].[BillOfMaterials]
    ADD
    CONSTRAINT [DF_BillOfMaterials_PerAssemblyQty]
    DEFAULT ((1.00)) FOR [PerAssemblyQty]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of 1.0', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'DF_BillOfMaterials_PerAssemblyQty'
GO
ALTER TABLE [Production].[BillOfMaterials]
    ADD
    CONSTRAINT [DF_BillOfMaterials_StartDate]
    DEFAULT (getdate()) FOR [StartDate]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Default constraint value of GETDATE()', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'DF_BillOfMaterials_StartDate'
GO
ALTER TABLE [Production].[BillOfMaterials]
    WITH CHECK
    ADD CONSTRAINT [FK_BillOfMaterials_Product_ComponentID]
    FOREIGN KEY ([ComponentID]) REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[BillOfMaterials]
    CHECK CONSTRAINT [FK_BillOfMaterials_Product_ComponentID]

GO
EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing Product.ComponentID.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'FK_BillOfMaterials_Product_ComponentID'
GO
ALTER TABLE [Production].[BillOfMaterials]
    WITH CHECK
    ADD CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID]
    FOREIGN KEY ([ProductAssemblyID]) REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[BillOfMaterials]
    CHECK CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID]

GO
EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing Product.ProductAssemblyID.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'FK_BillOfMaterials_Product_ProductAssemblyID'
GO
ALTER TABLE [Production].[BillOfMaterials]
    WITH CHECK
    ADD CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode]
    FOREIGN KEY ([UnitMeasureCode]) REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ALTER TABLE [Production].[BillOfMaterials]
    CHECK CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode]

GO
EXEC sp_addextendedproperty N'MS_Description', N'Foreign key constraint referencing UnitMeasure.UnitMeasureCode.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'FK_BillOfMaterials_UnitMeasure_UnitMeasureCode'
GO
CREATE UNIQUE CLUSTERED INDEX [AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate]
    ON [Production].[BillOfMaterials] ([ProductAssemblyID], [ComponentID], [StartDate])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Clustered index.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'INDEX', N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate'
GO
CREATE NONCLUSTERED INDEX [IX_BillOfMaterials_UnitMeasureCode]
    ON [Production].[BillOfMaterials] ([UnitMeasureCode])
    ON [PRIMARY]
GO
EXEC sp_addextendedproperty N'MS_Description', N'Nonclustered index.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'INDEX', N'IX_BillOfMaterials_UnitMeasureCode'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Primary key for BillOfMaterials records.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'BillOfMaterialsID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Indicates the depth the component is from its parent (AssemblyID).', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'BOMLevel'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Component identification number. Foreign key to Product.ProductID.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'ComponentID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date the component stopped being used in the assembly item.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'EndDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date and time the record was last updated.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'ModifiedDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Quantity of the component needed to create the assembly.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'PerAssemblyQty'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Parent product identification number. Foreign key to Product.ProductID.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'ProductAssemblyID'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Date the component started being used in the assembly item.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'StartDate'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Standard code identifying the unit of measure for the quantity.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'COLUMN', N'UnitMeasureCode'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', NULL, NULL
GO

Collapse image See also


List of tables