AdventureWorks 2008 Details
The following changes have been made in the design of AdventureWorks2008:
- The way people and businesses are represented has changed. Some tables have been added, renamed, and deleted. These changes enable better integration with the Microsoft ADO.NET Entity Framework.
- New data types and attributes are now demonstrated in the database including:
- date and time
- Integrated full-text support has been incorporated into the database.
The schema introduces the idea of a business entity. A business entity is a person or business which has zero or more addresses and zero or more people related to it. For example, the people related to a store would typically be purchasing agents of a retail sporting or bicycle store who buy bicycles wholesale from AdventureWorks Cycles. A person related to an employee might be the employee’s emergency contact. All business entities have a common key (the BusinessEntityID
). This allows us to build a conceptual model using the Entity Framework which demonstrates table per type inheritance. It also simplifies the schema by having a single table (Person.BusinessEntityAddress
) relate addresses to business entities instead of using specialized tables (for example CustomerAddress
The concept of a customer has also been broadened. In AdventureWorks for SQL Server 2005, a customer is either a store (wholesale) or an individual (retail). In AdventureWorks2008, a customer can be any person or a store. This enables employees (for example) to be customers without storing redundant information about them.
data type is used to organize employees and their managers into a tree, and organize production documents into a tree (much like a file system).
data types are used instead of the datetime data type when appropriate (date for columns like HireDate
in the HumanResources.Employee
table, and time for columns like StartTime
The integrated full-text feature of SQL Server 2008 is used to demonstrate full-text querying over resumes stored in the HumanResources.JobCandidate
Fictitious addresses stored in the Person.Address
table have been fictionally geocoded and the result stored in the SpatialLocation
column to demonstrate the new spatial features in SQL Server 2008.
For specific information about these changes an entity relational diagram showing the conceptual model of business entities, a relational schema diagram, a Transact-SQL script and the CSV data files necessary to build the database are included in the AdventureWorks2008Scripts.msi installer located in The Sample Databases at CodePlex
AdventureWorks 2008 Installation
To install AdventureWorks2008 perform the following steps:
1. Download and invoke the appropriate installer from The Sample Databases at CodePlex. The appropriate installer will have “2008” in the name and the name will match the machine architecture of the machine on which you will be installing the samples. For example AdventureWorks2008.msi
will work on x86 machines, and AdventureWorks2008_x64.msi
will work on x64 machines.
2. Follow the directions on the installer screens.
3. A backup of the database and any schema diagrams will be installed by default into the systemdrive:\Program Files\Microsoft SQL Server\100\Tools\Samples
where systemdrive represents the disk drive letter of the disk where you installed windows.
4. If you have not already enabled the file stream feature, enable it based on the instructions in this blog entry
5. Use to restore the database or use a Transact-SQL command similar to the following in SQLCMD or SSMS (where systemdrive
is replaced with the appropriate drive letter):RESTORE DATABASE AdventureWorks2008FROM DISK = 'systemdrive:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008.bak';
Note: Full Text Index Bug
It's come to our attention that the design of our FTS implementation exposes a known bug (which has been fixed and will be available in the CTP-Refresh build). The FTS team has asked us to explain that all full-text search operations will fail (with the February CTP6 build) in the following two scenarios:
- The full-text index key is of type HierarchyID, or
- A full-text index has been defined on a key different than HierarchyID, but the corresponding table has a column of type HierarchyID and it is clustered. For this to happen we also need the crawl to be a full crawl.
We're investigating whether the same will be true for other CLR types (such as spatial).