| Tables | SQL 2005 Adventure Works OLTP |
Sales representative current information.
Columns
Name Data type Nullable Description SalesPersonID int No Primary key for SalesPerson records. TerritoryID int Yes Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID. SalesQuota money Yes Projected yearly sales. Bonus money No Default value: 0.00
Bonus due if quota is met.CommissionPct smallmoney No Default value: 0.00
Commision percent received per sale.SalesYTD money No Default value: 0.00
Sales total year to date.SalesLastYear money No Default value: 0.00
Sales total of previous year.rowguid uniqueidentifier No Default value: NEWID()
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.
Constraints
Name Type Columns Description DF_SalesPerson_Bonus DEFAULT Bonus Value: 0.00
Default constraint value of 0.0DF_SalesPerson_CommissionPct DEFAULT CommissionPct Value: 0.00
Default constraint value of 0.0DF_SalesPerson_SalesYTD DEFAULT SalesYTD Value: 0.00
Default constraint value of 0.0DF_SalesPerson_SalesLastYear DEFAULT SalesLastYear Value: 0.00
Default constraint value of 0.0DF_SalesPerson_rowguid DEFAULT rowguid Value: NEWID()
Default constraint value of NEWID()DF_SalesPerson_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()CK_SalesPerson_SalesQuota CHECK N/A Value: ([SalesQuota] > 0.00)
Check constraint [SalesQuota]>(0.00)CK_SalesPerson_Bonus CHECK N/A Value: ([Bonus] >= 0.00)
Check constraint [Bonus]>=(0.00)CK_SalesPerson_CommissionPct CHECK N/A Value: ([CommissionPct] >= 0.00)
Check constraint [CommissionPct]>=(0.00)CK_SalesPerson_SalesYTD CHECK N/A Value: ([SalesYTD] >= 0.00)
Check constraint [SalesYTD]>=(0.00)CK_SalesPerson_SalesLastYear CHECK N/A Value: ([SalesLastYear] >= 0.00)
Check constraint [SalesLastYear]>=(0.00)PK_SalesPerson_SalesPersonID PRIMARY KEY SalesPersonID Clustered index created by a primary key constraint. FK_SalesPerson_Employee_SalesPersonID FOREIGN KEY SalesPersonID Reference table: HumanResources.Employee
Foreign key constraint referencing Employee.EmployeeID.FK_SalesPerson_SalesTerritory_TerritoryID FOREIGN KEY TerritoryID Reference table: Sales.SalesTerritory
Foreign key constraint referencing SalesTerritory.TerritoryID.
Indices
Name Description AK_SalesPerson_rowguid Unique nonclustered index.Used to support replication samples.
Triggers
Name Description Sales.uSalesPerson AFTER UPDATE trigger setting the ModifiedDate column in the SalesPerson table to the current date.
Linked tables
Name Description Sales.SalesOrderHeader General sales order information. Sales.SalesPersonQuotaHistory Sales performance tracking. Sales.SalesTerritoryHistory Sales representative transfers to other sales territories. Sales.Store Customers (resellers) of Adventure Works products.
Accessed by
Name Description Employee Sales Summary Adventure Works sales for an individual employee. This report includes Sales Comparison and Current Month Sales Comparison charts in addition to a Current Month Order Summary table with drillthrough to individual orders. This report illustrates the use of multiple datasets, charts, tables, drillthrough, and dynamic parameters. Product Line Sales Adventure Works top five sales people and stores. This report illustrates a dataset with queries containing the TOP clause. It also illustrates the use of tables, charts, parameters, calculated fields, and drillthrough links. Sales.vSalesPerson Sales representiatives (names and addresses) and their sales-related information. Sales.vSalesPersonSalesByFiscalYears Uses PIVOT to return aggregated sales information for each sales representative. Territory Sales Drilldown Adventure Works sales by territory. This report drills down through salesperson and order number with drillthrough to individual orders. This report illustrates the use of a table data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of drillthrough links and conditional formatting.
Modified by
Name Description Sales.uSalesOrderHeader AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables. Sales.uSalesPerson AFTER UPDATE trigger setting the ModifiedDate column in the SalesPerson table to the current date.
Applies to
Tables | User