Sales.CurrencyRate Table

Description

Currency exchange rates.

Properties

Creation Date	17/07/2014 16:11
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	13532
Data Space Used	768.00 KB
Index Space Used	440.00 KB

Columns

Column Name	Description	Datatype	Length	Default
CurrencyRateID	Primary key for CurrencyRate records.	Integer	4
CurrencyRateDate	Date and time the exchange rate was obtained.	DBTimeStamp	4
FromCurrencyCode	Exchange rate was converted from this currency code.	WChar	3
ToCurrencyCode	Exchange rate was converted to this currency code.	WChar	3
AverageRate	Average exchange rate for the day.	Currency	8
EndOfDayRate	Final exchange rate for the day.	Currency	8
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode	Unique nonclustered index.
PK_CurrencyRate_CurrencyRateID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_CurrencyRate_Currency_FromCurrencyCode	Foreign key constraint referencing Currency.FromCurrencyCode.
FK_CurrencyRate_Currency_ToCurrencyCode	Foreign key constraint referencing Currency.ToCurrencyCode.
FK_SalesOrderHeader_CurrencyRate_CurrencyRateID	Foreign key constraint referencing CurrencyRate.CurrencyRateID.

Objects that depend on Sales.CurrencyRate

Database Object	Object Type	Description	Dep Level
iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	3
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	2
Sales.SalesOrderHeader	Table	General sales order information.	1
Sales.SalesOrderHeaderSalesReason	Table	Cross-reference table mapping sales orders to sales reason codes.	2
uSalesOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.	2
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	2

Objects that Sales.CurrencyRate depends on

	Database Object	Object Type	Description	Dep Level
	Sales.Currency	Table	Lookup table containing standard ISO currencies.	1
	dbo.Name	User Defined Data Type		2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[CurrencyRate](
    [CurrencyRateID] [int] IDENTITY(1,1) NOT NULL,
    [CurrencyRateDate] [datetime] NOT NULL,
    [FromCurrencyCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [ToCurrencyCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [AverageRate] [money] NOT NULL,
    [EndOfDayRate] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_CurrencyRate_CurrencyRateID] PRIMARY KEY CLUSTERED 
(
    [CurrencyRateID] 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 [Sales].[CurrencyRate] ADD  CONSTRAINT [DF_CurrencyRate_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[CurrencyRate]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_Currency_FromCurrencyCode] FOREIGN KEY([FromCurrencyCode])
REFERENCES [Sales].[Currency] ([CurrencyCode])
ALTER TABLE [Sales].[CurrencyRate] CHECK CONSTRAINT [FK_CurrencyRate_Currency_FromCurrencyCode]
ALTER TABLE [Sales].[CurrencyRate]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_Currency_ToCurrencyCode] FOREIGN KEY([ToCurrencyCode])
REFERENCES [Sales].[Currency] ([CurrencyCode])
ALTER TABLE [Sales].[CurrencyRate] CHECK CONSTRAINT [FK_CurrencyRate_Currency_ToCurrencyCode]

Related Objects

Sales Schema
AdventureWorks Database