Database: AdventureWorks

Properties

Name: AdventureWorks 
Owner: WIN2003\Administrator 
Create for Attach: 07/20/2008 
Compatibility Level: 90 
Recovery Model: Simple recovery model 
Status: Database is available for query 
Full Text Search Enabled:  
Primary File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf 
Version:  
Database size, MB: 178.75 MB 
Allocated Space, KB: 165120 KB 
Data Space in Use and Reserved for Use, KB: 83672 KB 
Index Space Usage, KB: 76608 KB 
Allocated and Unused Space, KB: 4840 KB 
Description: AdventureWorks Sample OLTP Database 

Data Files

File Name Location File Group Allocated Space, MB Autogrows Grows Maximum File Size, MB
AdventureWorks_Data  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Data.mdf  PRIMARY  176    16 MB  Unrestricted 
Total: 1 data file(s)

Log Files

File Name Location Allocated Space, MB Autogrows Grows Maximum File Size, MB
AdventureWorks_Log  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Log.ldf  2    16 MB  2097152 
Total: 1 log file(s)

Tables

Name Owner Data Size KB Index Size KB Creation Date Cols Rows Description
  Address  Person  2440  2224  04/26/2006  8  19614  Street address information for customers, employees, and vendors. 
  AddressType  Person  40  8  04/26/2006  4  6  Types of addresses stored in the Address table.  
  AWBuildVersion  dbo  8  8  04/26/2006  6  1  Current version number of the AdventureWorks sample database.  
  BillOfMaterials  Production  200  160  04/26/2006  9  2679  Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components. 
  Contact  Person  1896  4536  04/26/2006  15  19972  Names of each employee, customer contact, and vendor contact. 
  ContactCreditCard  Sales  16  480  04/26/2006  3  19118  Cross-reference table mapping customers in the Contact table to their credit card information in the CreditCard table.  
  ContactType  Person  24  8  04/26/2006  3  20  Lookup table containing the types of contacts stored in Contact. 
  CountryRegion  Person  32  16  04/26/2006  3  238  Lookup table containing the ISO standard codes for countries and regions. 
  CountryRegionCurrency  Sales  24  8  04/26/2006  3  109  Cross-reference table mapping ISO currency codes to a country or region. 
  CreditCard  Sales  776  1496  04/26/2006  6  19118  Customer credit card information. 
  Culture  Production  24  8  04/26/2006  3  8  Lookup table containing the languages in which some AdventureWorks data is stored. 
  Currency  Sales  24  8  04/26/2006  3  105  Lookup table containing standard ISO currencies. 
  CurrencyRate  Sales  400  768  04/26/2006  7  13532  Currency exchange rates. 
  Customer  Sales  1176  824  04/26/2006  6  19185  Current customer information. Also see the Individual and Store tables. 
  CustomerAddress  Sales  552  864  04/26/2006  5  19220  Cross-reference table mapping customers to their address(es). 
  DatabaseLog  dbo  24  1408  04/26/2006  8  389  Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog. 
  Department  HumanResources  24  8  04/26/2006  4  16  Lookup table containing the departments within the Adventure Works Cycles company. 
  Document  Production  24  344  04/26/2006  10  9  Product maintenance documents. 
  Employee  HumanResources  120  56  04/26/2006  16  290  Employee information such as salary, department, and title. 
  EmployeeAddress  HumanResources  32  16  04/26/2006  4  290  Cross-reference table mapping employees to their address(es). 
  EmployeeDepartmentHistory  HumanResources  48  16  04/26/2006  6  296  Employee department transfers. 
  EmployeePayHistory  HumanResources  16  16  04/26/2006  5  316  Employee pay history. 
  ErrorLog  dbo  0  0  04/26/2006  9  0  Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct. 
  Illustration  Production  8  184  04/26/2006  3  5  Bicycle assembly diagrams. 
  Individual  Sales  56  24648  04/26/2006  4  18484  Demographic data about customers that purchase Adventure Works products online. 
  JobCandidate  HumanResources  32  128  04/26/2006  4  13  Résumés submitted to Human Resources by job applicants. 
  Location  Production  24  8  04/26/2006  5  14  Product inventory and manufacturing locations. 
  Product  Production  120  104  04/26/2006  25  504  Products sold or used in the manfacturing of sold products. 
  ProductCategory  Production  40  8  04/26/2006  4  4  High-level product categorization. 
  ProductCostHistory  Production  16  24  04/26/2006  5  395  Changes in the cost of a product over time. 
  ProductDescription  Production  56  144  04/26/2006  4  762  Product descriptions in several languages. 
  ProductDocument  Production  8  8  04/26/2006  3  32  Cross-reference table mapping products to related product documents. 
  ProductInventory  Production  16  56  04/26/2006  7  1069  Product inventory information. 
  ProductListPriceHistory  Production  16  24  04/26/2006  5  395  Changes in the list price of a product over time. 
  ProductModel  Production  48  144  04/26/2006  6  128  Product model classification. 
  ProductModelIllustration  Production  8  8  04/26/2006  3  7  Cross-reference table mapping product models and illustrations. 
  ProductModelProductDescriptionCulture  Production  16  32  04/26/2006  4  762  Cross-reference table mapping product descriptions and the language the description is written in. 
  ProductPhoto  Production  16  2240  04/26/2006  6  101  Product images. 
  ProductProductPhoto  Production  40  16  04/26/2006  4  504  Cross-reference table mapping products and product photos. 
  ProductReview  Production  56  16  04/26/2006  8  4  Customer reviews of products they have purchased. 
  ProductSubcategory  Production  40  8  04/26/2006  5  37  Product subcategories. See ProductCategory table. 
  ProductVendor  Purchasing  48  40  04/26/2006  11  460  Cross-reference table mapping vendors with the products they supply. 
  PurchaseOrderDetail  Purchasing  168  512  04/26/2006  11  8845  Individual products associated with a specific purchase order. See PurchaseOrderHeader. 
  PurchaseOrderHeader  Purchasing  144  336  04/26/2006  13  4012  General purchase order information. See PurchaseOrderDetail. 
  SalesOrderDetail  Sales  5144  9864  04/26/2006  11  121317  Individual products associated with a specific sales order. See SalesOrderHeader. 
  SalesOrderHeader  Sales  2384  5592  04/26/2006  27  31465  General sales order information. 
  SalesOrderHeaderSalesReason  Sales  16  688  04/26/2006  3  27647  Cross-reference table mapping sales orders to sales reason codes. 
  SalesPerson  Sales  24  8  04/26/2006  9  17  Sales representative current information. 
  SalesPersonQuotaHistory  Sales  32  16  04/26/2006  5  163  Sales performance tracking. 
  SalesReason  Sales  8  8  04/26/2006  4  10  Lookup table of customer purchase reasons. 
  SalesTaxRate  Sales  40  8  04/26/2006  7  29  Tax rate lookup table. 
  SalesTerritory  Sales  40  8  04/26/2006  10  10  Sales territory lookup table. 
  SalesTerritoryHistory  Sales  24  8  04/26/2006  6  17  Sales representative transfers to other sales territories. 
  ScrapReason  Production  24  8  04/26/2006  3  16  Manufacturing failure reasons lookup table. 
  Shift  HumanResources  40  8  04/26/2006  5  3  Work shift lookup table. 
  ShipMethod  Purchasing  40  8  04/26/2006  6  5  Shipping company lookup table. 
  ShoppingCartItem  Sales  24  8  04/26/2006  6  3  Contains online customer orders until the order is submitted or cancelled. 
  SpecialOffer  Sales  24  8  04/26/2006  11  16  Sale discounts lookup table. 
  SpecialOfferProduct  Sales  64  24  04/26/2006  4  538  Cross-reference table mapping products to special offer discounts. 
  StateProvince  Person  64  16  04/26/2006  8  181  State and province lookup table. 
  Store  Sales  80  808  04/26/2006  6  701  Customers (resellers) of Adventure Works products. 
  StoreContact  Sales  120  40  04/26/2006  5  753  Cross-reference table mapping stores and their employees. 
  TransactionHistory  Production  2992  6304  04/26/2006  9  113443  Record of each purchase order, sales order, or work order transaction year to date. 
  TransactionHistoryArchive  Production  2352  4960  04/26/2006  9  89253  Transactions for previous years. 
  UnitMeasure  Production  24  8  04/26/2006  3  38  Unit of measure lookup table. 
  Vendor  Purchasing  32  16  04/26/2006  8  104  Companies from whom Adventure Works Cycles purchases parts or other goods. 
  VendorAddress  Purchasing  24  8  04/26/2006  4  104  Cross-reference mapping vendors and addresses. 
  VendorContact  Purchasing  40  8  04/26/2006  4  156  Cross-reference table mapping vendors and their employees. 
  WorkOrder  Production  1704  4184  04/26/2006  10  72591  Manufacturing work orders. 
  WorkOrderRouting  Production  912  5544  04/26/2006  12  67131  Work order details. 
Total: 70 table(s)

Views

Name Owner Schema bound Encrypted Creation Date Modification Date Description
  vAdditionalContactInfo  Person      04/26/2006  04/26/2006  Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person. 
  vEmployee  HumanResources      04/26/2006  07/20/2008  Employee names and addresses. 
  vEmployeeDepartment  HumanResources      04/26/2006  04/26/2006  Returns employee name, title, and current department. 
  vEmployeeDepartmentHistory  HumanResources      04/26/2006  04/26/2006  Returns employee name and current and previous departments. 
  View_1  dbo      09/11/2008  09/11/2008   
  vIndividualCustomer  Sales      04/26/2006  04/26/2006  Individual customers (names and addresses) that purchase Adventure Works Cycles products online. 
  vIndividualDemographics  Sales      04/26/2006  04/26/2006  Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table. 
  vJobCandidate  HumanResources      04/26/2006  04/26/2006  Job candidate names and resumes. 
  vJobCandidateEducation  HumanResources      04/26/2006  04/26/2006  Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed. 
  vJobCandidateEmployment  HumanResources      04/26/2006  04/26/2006  Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed. 
  vProductAndDescription  Production      04/26/2006  04/26/2006  Product names and descriptions. Product descriptions are provided in multiple languages. 
  vProductModelCatalogDescription  Production      04/26/2006  04/26/2006  Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data. 
  vProductModelInstructions  Production      04/26/2006  04/26/2006  Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions. 
  vSalesPerson  Sales      04/26/2006  04/26/2006  Sales representiatives (names and addresses) and their sales-related information. 
  vSalesPersonSalesByFiscalYears  Sales      04/26/2006  04/26/2006  Uses PIVOT to return aggregated sales information for each sales representative. 
  vStateProvinceCountryRegion  Person      04/26/2006  04/26/2006  Joins StateProvince table with CountryRegion table. 
  vStoreWithDemographics  Sales      04/26/2006  04/26/2006  Stores (names and addresses) that sell Adventure Works Cycles products to consumers. 
  vVendor  Purchasing      04/26/2006  04/26/2006  Vendor (company) names and addresses and the names of vendor employees to contact. 
Total: 18 view(s)

Database triggers

Name Creation Date Modify Date Disabled Description
  ddlDatabaseTriggerLog  04/26/2006  04/26/2006    Database trigger to audit all of the DDL changes made to the AdventureWorks database. 
Total: 1 trigger(s)

Triggers

Name Owner Disabled Instead Of Table/View Description
  dEmployee  HumanResources      HumanResources.Employee  INSTEAD OF DELETE trigger which keeps Employees from being deleted. 
  dVendor  Purchasing      Purchasing.Vendor  INSTEAD OF DELETE trigger which keeps Vendors from being deleted. 
  iduSalesOrderDetail  Sales      Sales.SalesOrderDetail  AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column. 
  iPurchaseOrderDetail  Purchasing      Purchasing.PurchaseOrderDetail  AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column. 
  iStore  Sales      Sales.Store  AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table. 
  iuIndividual  Sales      Sales.Individual  AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date. 
  iWorkOrder  Production      Production.WorkOrder  AFTER INSERT trigger that inserts a row in the TransactionHistory table. 
  uPurchaseOrderDetail  Purchasing      Purchasing.PurchaseOrderDetail  AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column. 
  uPurchaseOrderHeader  Purchasing      Purchasing.PurchaseOrderHeader  AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table. 
  uSalesOrderHeader  Sales      Sales.SalesOrderHeader  AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables. 
  uWorkOrder  Production      Production.WorkOrder  AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table. 
Total: 11 trigger(s)

Stored procedures

Name Owner Description
  uspGetBillOfMaterials  dbo  Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID. 
  uspGetEmployeeManagers  dbo  Stored procedure using a recursive query to return the direct and indirect managers of the specified employee. 
  uspGetManagerEmployees  dbo  Stored procedure using a recursive query to return the direct and indirect employees of the specified manager. 
  uspGetWhereUsedProductID  dbo  Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. 
  uspLogError  dbo  Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information. 
  uspPrintError  dbo  Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information. 
  uspUpdateEmployeeHireInfo  HumanResources  Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters. 
  uspUpdateEmployeeLogin  HumanResources  Updates the Employee table with the values specified in the input parameters for the given EmployeeID. 
  uspUpdateEmployeePersonalInfo  HumanResources  Updates the Employee table with the values specified in the input parameters for the given EmployeeID. 
Total: 9 stored procedure(s)

Functions

Name Owner Type Description
  ufnGetAccountingEndDate  dbo  SQL scalar function  Scalar function used in the uSalesOrderHeader trigger to set the starting account date. 
  ufnGetAccountingStartDate  dbo  SQL scalar function  Scalar function used in the uSalesOrderHeader trigger to set the ending account date. 
  ufnGetContactInformation  dbo  SQL table-valued-function  Table value function returning the first name, last name, job title and contact type for a given contact. 
  ufnGetDocumentStatusText  dbo  SQL scalar function  Scalar function returning the text representation of the Status column in the Document table. 
  ufnGetProductDealerPrice  dbo  SQL scalar function  Scalar function returning the dealer price for a given product on a particular order date. 
  ufnGetProductListPrice  dbo  SQL scalar function  Scalar function returning the list price for a given product on a particular order date. 
  ufnGetProductStandardCost  dbo  SQL scalar function  Scalar function returning the standard cost for a given product on a particular order date. 
  ufnGetPurchaseOrderStatusText  dbo  SQL scalar function  Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table. 
  ufnGetSalesOrderStatusText  dbo  SQL scalar function  Scalar function returning the text representation of the Status column in the SalesOrderHeader table. 
  ufnGetStock  dbo  SQL scalar function  Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID. 
  ufnLeadingZeros  dbo  SQL scalar function  Scalar function used by the Sales.Customer table to help set the account number. 
Total: 11 function(s)

Datatypes

Name Owner
  AccountNumber  dbo 
  Flag  dbo 
  Name  dbo 
  NameStyle  dbo 
  OrderNumber  dbo 
  Phone  dbo 
Total: 6 datatype(s)

Database Options

Settings: Auto Close:  
Settings: Auto Shrink:  
Settings: Auto Create Statistics:  
Settings: Auto Update Statistics:  
Settings: Use Quoted Identifiers:  
Settings: Torn Page Detection:  
Settings: Recursive Triggers:  
Settings: ANSI NULL default:  
Access: Read Only:  
Access: Single User:  
Access: Members of db_owner, dbcreator or sysadmin:  
Truncate Log On Checkpoint:  
Select Into/Bulkcopy:  
Cursor Close On Commit:  
Cursors in a Batch are with Local Scope:  

See Also

Server overview