Production.TransactionHistoryArchive Table

Description

Transactions for previous years.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	89253
Data Space Used	4,992.00 KB
Index Space Used	3,144.00 KB

Columns

Column Name	Description	Datatype	Length	Default
TransactionID	Primary key for TransactionHistoryArchive records.	Integer	4
ProductID	Product identification number. Foreign key to Product.ProductID.	Integer	4
ReferenceOrderID	Purchase order, sales order, or work order identification number.	Integer	4
ReferenceOrderLineID	Line number associated with the purchase order, sales order, or work order.	Integer	4	((0))
TransactionDate	Date and time of the transaction.	DBTimeStamp	4	(getdate())
TransactionType	W = Work Order, S = Sales Order, P = Purchase Order	WChar	1
Quantity	Product quantity.	Integer	4
ActualCost	Product cost.	Currency	8
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
IX_TransactionHistoryArchive_ProductID	Nonclustered index.
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	Nonclustered index.
PK_TransactionHistoryArchive_TransactionID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_TransactionHistoryArchive_TransactionType	Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W'	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistoryArchive](
    [TransactionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineID] [int] NOT NULL,
    [TransactionDate] [datetime] NOT NULL,
    [TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY CLUSTERED 
(
    [TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [DF_TransactionHistoryArchive_ReferenceOrderLineID]  DEFAULT ((0)) FOR [ReferenceOrderLineID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [DF_TransactionHistoryArchive_TransactionDate]  DEFAULT (getdate()) FOR [TransactionDate]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [DF_TransactionHistoryArchive_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[TransactionHistoryArchive]  WITH CHECK ADD  CONSTRAINT [CK_TransactionHistoryArchive_TransactionType] CHECK  ((upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W'))
ALTER TABLE [Production].[TransactionHistoryArchive] CHECK CONSTRAINT [CK_TransactionHistoryArchive_TransactionType]

Related Objects