The AdventureWorks Refresh

Renee Rinker is t

he (relatively new) Dev Lead for Samples on the Microsoft SQL Server team. As part of a new change policy process for AdventureWorks, she has been posting changes here to the MVPs.

Renee has collected all of the changes to AdventureWorks into a single posting RTM.  AdventureWorks updates will be part of SP1 and is also available on the web.  Incidentally, the samples appear only on the web.

Here are the December 2005 web refresh (and SP1) changes to the AdventureWorks OLTP data store.

-----------------------------

Issue: Date literals in AdventureWorks function definitions are not locale-independent, so dates did not compare correctly in some locales.
Fix: In stored procedures, used CONVERT(datetime, '99991231', 112) to get the date in the correct format.

Issue: ProductVendor rows and Purchase Order data missing for 54 products that are marked as "buy".
Fix: Data added.

Issue: Scalar value function AdventureWorks.dbo.ufnGetDocumentStatusText returns [nvarchar](15) value. When @Status = 1, the function returns 'Pending approva' which is truncated.
Fix: Changed to return [nvarchar](16)

Issue: Triggers should check if @@rowcount is 0 before doing anything (performance issue).
Fix: Added checks.

Issue: AdventureWorks should not use triggers to update the date in the ModifiedDate column in various tables since it is more efficient for whatever updates a row to update that column as well.
Fix: Removed the triggers that update the ModifiedDate columns.

In SP1 only:
---------------

Major changes for AdventureWorks AS Projects:

- Enterprise version: updated French translations

- Both versions: set Mime type for Value binding of Large Photo attribute in Product dimension

- Both versions: Enabled Birth Date and Base Rate attributes in Product dimension

- Both versions: Updated Trend expression definition for KPIs

- Both versions: Removed unneeded attribute relationship (Month->Fiscal Year) in Date dimension

- Both versions: Removed unneeded attribute relationship (Product->Product Line) in Product dimension

 Misc.: Several hierarchy levels were renamed to be more concise. This is the change that has the most potential to break other samples.

Issue: Scotland, England listed as a city
Fix: changed data in Address (OLTP database) and DimGeography (DW database) to update city and postal code to Liverpool

Issue: PasswordHash (OLTP database only) data encrypted with SHA1 instead of SHA256.
Fix: Changed data encryption to SHA256. Changed column size from 40 to 128 since SHA256 encryption needed 44 characters.

Build version changed: 

- In OLTP database, AWBuildVersion table data changed to version 9.06.01.05.00 (maps to date of change as before) with modified dates and times also changed.

- In DW database, in the AdventureWorksDWBuildVersion table the version changed from 3.0 to 9.06.01.05.00 and modified date changed (time is still 00:00:00.000).