AdventureWorks database 
(30/03/2009) 

Table: dbo.AWBuildVersion
Current version number of the AdventureWorks sample database.  

 	Field name	Data type	Nullable	Default	Comments	
    PK 	SystemInformationID	tinyint			Primary key for AWBuildVersion records.	
	Database Version	nvarchar  (25)			Version number of the database in 9.yy.mm.dd.00 format.	
	VersionDate	datetime			Date and time the record was last updated.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_AWBuildVersion_SystemInformationID	SystemInformationID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[AWBuildVersion]( 
         [SystemInformationID] [tinyint] IDENTITY(1,1) NOT NULL, 
         [Database Version] [nvarchar](25) COLLATE Latin1_General_CS_AS NOT NULL, 
         [VersionDate] [datetime] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.DatabaseLog
Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	DatabaseLogID	int			Primary key for DatabaseLog records.	
	PostTime	datetime			The date and time the DDL change occurred.	
	DatabaseUser	nvarchar  (128)			The user who implemented the DDL change.	
	Event	nvarchar  (128)			The type of DDL statement that was executed.	
	Schema	nvarchar  (128)	Yes		The schema to which the changed object belongs.	
	Object	nvarchar  (128)	Yes		The object that was changed by the DDL statment.	
	TSQL	nvarchar			The exact Transact-SQL statement that was executed.	
	XmlEvent	xml			The raw XML data generated by database trigger.	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_DatabaseLog_DatabaseLogID	DatabaseLogID	ASC	Yes	NONCLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[DatabaseLog]( 
         [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL, 
         [PostTime] [datetime] NOT NULL, 
         [DatabaseUser] [sysname] COLLATE Latin1_General_CS_AS NOT NULL, 
         [Event] [sysname] COLLATE Latin1_General_CS_AS NOT NULL, 
         [Schema] [sysname] COLLATE Latin1_General_CS_AS NULL, 
         [Object] [sysname] COLLATE Latin1_General_CS_AS NULL, 
         [TSQL] [nvarchar](max) COLLATE Latin1_General_CS_AS NOT NULL, 
         [XmlEvent] [xml] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: dbo.ErrorLog
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. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ErrorLogID	int			Primary key for ErrorLog records.	
	ErrorTime	datetime		(getdate())	The date and time at which the error occurred.	
	UserName	nvarchar  (128)			The user who executed the batch in which the error occurred.	
	ErrorNumber	int			The error number of the error that occurred.	
	ErrorSeverity	int	Yes		The severity of the error that occurred.	
	ErrorState	int	Yes		The state number of the error that occurred.	
	ErrorProcedure	nvarchar  (126)	Yes		The name of the stored procedure or trigger where the error occurred.	
	ErrorLine	int	Yes		The line number at which the error occurred.	
	ErrorMessage	nvarchar  (4000)			The message text of the error that occurred.	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ErrorLog_ErrorLogID	ErrorLogID	ASC	Yes	CLUSTERED	

 Objects that depend on dbo.ErrorLog: 

dbo.uspLogError 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [dbo].[ErrorLog]( 
         [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, 
         [ErrorTime] [datetime] NOT NULL, 
         [UserName] [sysname] COLLATE Latin1_General_CS_AS NOT NULL, 
         [ErrorNumber] [int] NOT NULL, 
         [ErrorSeverity] [int] NULL, 
         [ErrorState] [int] NULL, 
         [ErrorProcedure] [nvarchar](126) COLLATE Latin1_General_CS_AS NULL, 
         [ErrorLine] [int] NULL, 
         [ErrorMessage] [nvarchar](4000) COLLATE Latin1_General_CS_AS NOT NULL 
     ) ON [PRIMARY] 
      
     Table: HumanResources.Department
Lookup table containing the departments within the Adventure Works Cycles company. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	DepartmentID	smallint			Primary key for Department records.	
IX 	Name	Name			Name of the department.	
	GroupName	Name			Name of the group to which the department belongs.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
HumanResources.EmployeeDepartmentHistory	FK_EmployeeDepartmentHistory_Department_DepartmentID	PK_Department_DepartmentID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Department_Name	Name	ASC	Yes	NONCLUSTERED	
PK_Department_DepartmentID	DepartmentID	ASC	Yes	CLUSTERED	

 Objects that depend on HumanResources.Department: 

HumanResources.vEmployeeDepartment 
HumanResources.vEmployeeDepartmentHistory 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [HumanResources].[Department]( 
         [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [GroupName] [dbo].[Name] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: HumanResources.Employee
Employee information such as salary, department, and title. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	EmployeeID	int			Primary key for Employee records.	
IX 	NationalIDNumber	nvarchar  (15)			Unique national identification number such as a social security number.	
FK 	ContactID	int			Identifies the employee in the Contact table. Foreign key to Contact.ContactID.	
IX 	LoginID	nvarchar  (256)			Network login.	
FK 	ManagerID	int	Yes		Manager to whom the employee is assigned. Foreign Key to Employee.M	
	Title	nvarchar  (50)			Work title such as Buyer or Sales Representative.	
	BirthDate	datetime			Date of birth.	
	MaritalStatus	nchar  (1)			M = Married, S = Single	
	Gender	nchar  (1)			M = Male, F = Female	
	HireDate	datetime			Employee hired on this date.	
	SalariedFlag	Flag		((1))	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.	
	VacationHours	smallint		((0))	Number of available vacation hours.	
	SickLeaveHours	smallint		((0))	Number of available sick leave hours.	
	CurrentFlag	Flag		((1))	0 = Inactive, 1 = Active	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_Employee_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID    (Person.Contact)   
FK_Employee_Employee_ManagerID: ManagerID relies upon remote PK_Employee_EmployeeID    (HumanResources.Employee)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
HumanResources.JobCandidate	FK_JobCandidate_Employee_EmployeeID	PK_Employee_EmployeeID	
Purchasing.PurchaseOrderHeader	FK_PurchaseOrderHeader_Employee_EmployeeID	PK_Employee_EmployeeID	
Sales.SalesPerson	FK_SalesPerson_Employee_SalesPersonID	PK_Employee_EmployeeID	
HumanResources.Employee	FK_Employee_Employee_ManagerID	PK_Employee_EmployeeID	
HumanResources.EmployeeDepartmentHistory	FK_EmployeeDepartmentHistory_Employee_EmployeeID	PK_Employee_EmployeeID	
HumanResources.EmployeeAddress	FK_EmployeeAddress_Employee_EmployeeID	PK_Employee_EmployeeID	
HumanResources.EmployeePayHistory	FK_EmployeePayHistory_Employee_EmployeeID	PK_Employee_EmployeeID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Employee_LoginID	LoginID	ASC	Yes	NONCLUSTERED	
AK_Employee_NationalIDNumber	NationalIDNumber	ASC	Yes	NONCLUSTERED	
AK_Employee_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
IX_Employee_ManagerID	ManagerID	ASC		NONCLUSTERED	
PK_Employee_EmployeeID	EmployeeID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Employee_BirthDate	BirthDate	([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))	
CK_Employee_MaritalStatus	MaritalStatus	(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')	
CK_Employee_HireDate	HireDate	([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))	
CK_Employee_Gender	Gender	(upper([Gender])='F' OR upper([Gender])='M')	
CK_Employee_VacationHours	VacationHours	([VacationHours]>=(-40) AND [VacationHours]<=(240))	
CK_Employee_SickLeaveHours	SickLeaveHours	([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))	

Triggers: 

Trigger name: HumanResources.dEmployee
Trigger type: INSTEAD OF DELETE
Trigger active: Yes
Trigger description: INSTEAD OF DELETE trigger which keeps Employees from being deleted.
      Trigger definition:       

     CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee]  
     INSTEAD OF DELETE NOT FOR REPLICATION AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN 
             RAISERROR 
                 (N'Employees cannot be deleted. They can only be marked as not current.', -- Message 
                 10, -- Severity. 
                 1); -- State. 
      
             -- Rollback any active or uncommittable transactions 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
         END; 
     END; 

 Objects that depend on HumanResources.Employee: 

dbo.uspGetEmployeeManagers 
dbo.uspGetManagerEmployees 
HumanResources.uspUpdateEmployeeHireInfo 
HumanResources.uspUpdateEmployeeLogin 
HumanResources.uspUpdateEmployeePersonalInfo 
HumanResources.vEmployee 
HumanResources.vEmployeeDepartment 
HumanResources.vEmployeeDepartmentHistory 
Sales.vSalesPerson 
Sales.vSalesPersonSalesByFiscalYears 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [HumanResources].[Employee]( 
         [EmployeeID] [int] IDENTITY(1,1) NOT NULL, 
         [NationalIDNumber] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL, 
         [ContactID] [int] NOT NULL, 
         [LoginID] [nvarchar](256) COLLATE Latin1_General_CS_AS NOT NULL, 
         [ManagerID] [int] NULL, 
         [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL, 
         [BirthDate] [datetime] NOT NULL, 
         [MaritalStatus] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Gender] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL, 
         [HireDate] [datetime] NOT NULL, 
         [SalariedFlag] [dbo].[Flag] NOT NULL, 
         [VacationHours] [smallint] NOT NULL, 
         [SickLeaveHours] [smallint] NOT NULL, 
         [CurrentFlag] [dbo].[Flag] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: HumanResources.EmployeeAddress
Cross-reference table mapping employees to their address(es). 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	EmployeeID	int			Primary key. Foreign key to Employee.EmployeeID.	
    PK FK	AddressID	int			Primary key. Foreign key to Address.AddressID.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_EmployeeAddress_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID    (HumanResources.Employee)   
FK_EmployeeAddress_Address_AddressID: AddressID relies upon remote PK_Address_AddressID    (Person.Address)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_EmployeeAddress_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_EmployeeAddress_EmployeeID_AddressID	EmployeeID	ASC	Yes	CLUSTERED	
PK_EmployeeAddress_EmployeeID_AddressID	AddressID	ASC	Yes	CLUSTERED	

 Objects that depend on HumanResources.EmployeeAddress: 

HumanResources.vEmployee 
Sales.vSalesPerson 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [HumanResources].[EmployeeAddress]( 
         [EmployeeID] [int] NOT NULL, 
         [AddressID] [int] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: HumanResources.EmployeeDepartmentHistory
Employee department transfers. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	EmployeeID	int			Employee identification number. Foreign key to Employee.EmployeeID.	
    PK FK	DepartmentID	smallint			Department in which the employee worked including currently. Foreign key to Department.DepartmentID.	
    PK FK	ShiftID	tinyint			Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.	
    PK 	StartDate	datetime			Date the employee started work in the department.	
	EndDate	datetime	Yes		Date the employee left the department. NULL = Current department.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_EmployeeDepartmentHistory_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID    (HumanResources.Employee)   
FK_EmployeeDepartmentHistory_Department_DepartmentID: DepartmentID relies upon remote PK_Department_DepartmentID    (HumanResources.Department)   
FK_EmployeeDepartmentHistory_Shift_ShiftID: ShiftID relies upon remote PK_Shift_ShiftID    (HumanResources.Shift)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_EmployeeDepartmentHistory_DepartmentID	DepartmentID	ASC		NONCLUSTERED	
IX_EmployeeDepartmentHistory_ShiftID	ShiftID	ASC		NONCLUSTERED	
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	EmployeeID	ASC	Yes	CLUSTERED	
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	DepartmentID	ASC	Yes	CLUSTERED	
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	ShiftID	ASC	Yes	CLUSTERED	
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	StartDate	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_EmployeeDepartmentHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	
CK_EmployeeDepartmentHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	

 Objects that depend on HumanResources.EmployeeDepartmentHistory: 

HumanResources.vEmployeeDepartment 
HumanResources.vEmployeeDepartmentHistory 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [HumanResources].[EmployeeDepartmentHistory]( 
         [EmployeeID] [int] NOT NULL, 
         [DepartmentID] [smallint] NOT NULL, 
         [ShiftID] [tinyint] NOT NULL, 
         [StartDate] [datetime] NOT NULL, 
         [EndDate] [datetime] NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: HumanResources.EmployeePayHistory
Employee pay history. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	EmployeeID	int			Employee identification number. Foreign key to Employee.EmployeeID.	
    PK 	RateChangeDate	datetime			Date the change in pay is effective	
	Rate	money			Salary hourly rate.	
	PayFrequency	tinyint			1 = Salary received monthly, 2 = Salary received biweekly	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_EmployeePayHistory_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID    (HumanResources.Employee)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_EmployeePayHistory_EmployeeID_RateChangeDate	EmployeeID	ASC	Yes	CLUSTERED	
PK_EmployeePayHistory_EmployeeID_RateChangeDate	RateChangeDate	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_EmployeePayHistory_PayFrequency	PayFrequency	([PayFrequency]=(2) OR [PayFrequency]=(1))	
CK_EmployeePayHistory_Rate	Rate	([Rate]>=(6.50) AND [Rate]<=(200.00))	

 Objects that depend on HumanResources.EmployeePayHistory: 

HumanResources.uspUpdateEmployeeHireInfo 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [HumanResources].[EmployeePayHistory]( 
         [EmployeeID] [int] NOT NULL, 
         [RateChangeDate] [datetime] NOT NULL, 
         [Rate] [money] NOT NULL, 
         [PayFrequency] [tinyint] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: HumanResources.JobCandidate
Résumés submitted to Human Resources by job applicants. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	JobCandidateID	int			Primary key for JobCandidate records.	
FK 	EmployeeID	int	Yes		Employee identification number if applicant was hired. Foreign key to Employee.EmployeeID.	
	Resume	xml	Yes		Résumé in XML format.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_JobCandidate_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID    (HumanResources.Employee)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_JobCandidate_EmployeeID	EmployeeID	ASC		NONCLUSTERED	
PK_JobCandidate_JobCandidateID	JobCandidateID	ASC	Yes	CLUSTERED	

 Objects that depend on HumanResources.JobCandidate: 

HumanResources.vJobCandidate 
HumanResources.vJobCandidateEducation 
HumanResources.vJobCandidateEmployment 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [HumanResources].[JobCandidate]( 
         [JobCandidateID] [int] IDENTITY(1,1) NOT NULL, 
         [EmployeeID] [int] NULL, 
         [Resume] [xml](CONTENT [HumanResources].[HRResumeSchemaCollection]) NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: HumanResources.Shift
Work shift lookup table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ShiftID	tinyint			Primary key for Shift records.	
IX 	Name	Name			Shift description.	
IX 	StartTime	datetime			Shift start time.	
IX 	EndTime	datetime			Shift end time.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
HumanResources.EmployeeDepartmentHistory	FK_EmployeeDepartmentHistory_Shift_ShiftID	PK_Shift_ShiftID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Shift_Name	Name	ASC	Yes	NONCLUSTERED	
AK_Shift_StartTime_EndTime	StartTime	ASC	Yes	NONCLUSTERED	
AK_Shift_StartTime_EndTime	EndTime	ASC	Yes	NONCLUSTERED	
PK_Shift_ShiftID	ShiftID	ASC	Yes	CLUSTERED	

 Objects that depend on HumanResources.Shift: 

HumanResources.vEmployeeDepartmentHistory 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [HumanResources].[Shift]( 
         [ShiftID] [tinyint] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [StartTime] [datetime] NOT NULL, 
         [EndTime] [datetime] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Person.Address
Street address information for customers, employees, and vendors. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	AddressID	int			Primary key for Address records.	
IX 	AddressLine1	nvarchar  (60)			First street address line.	
IX 	AddressLine2	nvarchar  (60)	Yes		Second street address line.	
IX 	City	nvarchar  (30)			Name of the city.	
FK 	StateProvinceID	int			Unique identification number for the state or province. Foreign key to StateProvince table.	
IX 	PostalCode	nvarchar  (15)			Postal code for the street address.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_Address_StateProvince_StateProvinceID: StateProvinceID relies upon remote PK_StateProvince_StateProvinceID    (Person.StateProvince)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderHeader	FK_SalesOrderHeader_Address_BillToAddressID	PK_Address_AddressID	
Purchasing.VendorAddress	FK_VendorAddress_Address_AddressID	PK_Address_AddressID	
Sales.CustomerAddress	FK_CustomerAddress_Address_AddressID	PK_Address_AddressID	
HumanResources.EmployeeAddress	FK_EmployeeAddress_Address_AddressID	PK_Address_AddressID	
Sales.SalesOrderHeader	FK_SalesOrderHeader_Address_ShipToAddressID	PK_Address_AddressID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Address_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	AddressLine1	ASC	Yes	NONCLUSTERED	
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	AddressLine2	ASC	Yes	NONCLUSTERED	
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	City	ASC	Yes	NONCLUSTERED	
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	StateProvinceID	ASC	Yes	NONCLUSTERED	
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	PostalCode	ASC	Yes	NONCLUSTERED	
IX_Address_StateProvinceID	StateProvinceID	ASC		NONCLUSTERED	
PK_Address_AddressID	AddressID	ASC	Yes	CLUSTERED	

 Objects that depend on Person.Address: 

HumanResources.vEmployee 
Purchasing.vVendor 
Sales.vIndividualCustomer 
Sales.vSalesPerson 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Person].[Address]( 
         [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 
         [AddressLine1] [nvarchar](60) COLLATE Latin1_General_CS_AS NOT NULL, 
         [AddressLine2] [nvarchar](60) COLLATE Latin1_General_CS_AS NULL, 
         [City] [nvarchar](30) COLLATE Latin1_General_CS_AS NOT NULL, 
         [StateProvinceID] [int] NOT NULL, 
         [PostalCode] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Person.AddressType
Types of addresses stored in the Address table.  

 	Field name	Data type	Nullable	Default	Comments	
    PK 	AddressTypeID	int			Primary key for AddressType records.	
IX 	Name	Name			Address type description. For example, Billing, Home, or Shipping.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.CustomerAddress	FK_CustomerAddress_AddressType_AddressTypeID	PK_AddressType_AddressTypeID	
Purchasing.VendorAddress	FK_VendorAddress_AddressType_AddressTypeID	PK_AddressType_AddressTypeID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_AddressType_Name	Name	ASC	Yes	NONCLUSTERED	
AK_AddressType_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_AddressType_AddressTypeID	AddressTypeID	ASC	Yes	CLUSTERED	

 Objects that depend on Person.AddressType: 

Sales.vIndividualCustomer 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Person].[AddressType]( 
         [AddressTypeID] [int] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Person.Contact
Names of each employee, customer contact, and vendor contact. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ContactID	int			Primary key for Contact records.	
	NameStyle	NameStyle		((0))	0 = The data in FirstName and LastName are stored in western style (first name, last name) order.  1 = Eastern style (last name, first name) order.	
	Title	nvarchar  (8)	Yes		A courtesy title. For example, Mr. or Ms.	
	FirstName	Name			First name of the person.	
	MiddleName	Name	Yes		Middle name or middle initial of the person.	
	LastName	Name			Last name of the person.	
	Suffix	nvarchar  (10)	Yes		Surname suffix. For example, Sr. or Jr.	
IX 	EmailAddress	nvarchar  (50)	Yes		E-mail address for the person.	
	EmailPromotion	int		((0))	0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners. 	
	Phone	Phone	Yes		Phone number associated with the person.	
	PasswordHash	varchar  (128)			Password for the e-mail account.	
	PasswordSalt	varchar  (10)			Random value concatenated with the password string before the password is hashed.	
IX 	AdditionalContactInfo	xml	Yes		Additional contact information about the person stored in xml format. 	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderHeader	FK_SalesOrderHeader_Contact_ContactID	PK_Contact_ContactID	
Sales.StoreContact	FK_StoreContact_Contact_ContactID	PK_Contact_ContactID	
Purchasing.VendorContact	FK_VendorContact_Contact_ContactID	PK_Contact_ContactID	
Sales.ContactCreditCard	FK_ContactCreditCard_Contact_ContactID	PK_Contact_ContactID	
HumanResources.Employee	FK_Employee_Contact_ContactID	PK_Contact_ContactID	
Sales.Individual	FK_Individual_Contact_ContactID	PK_Contact_ContactID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Contact_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
IX_Contact_EmailAddress	EmailAddress	ASC		NONCLUSTERED	
PK_Contact_ContactID	ContactID	ASC	Yes	CLUSTERED	
PXML_Contact_AddContact	AdditionalContactInfo	ASC		XML	

Check constraints: 

Check name	Column name	Expression	
CK_Contact_EmailPromotion	EmailPromotion	([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))	

 Objects that depend on Person.Contact: 

dbo.uspGetEmployeeManagers 
dbo.uspGetManagerEmployees 
HumanResources.vEmployee 
HumanResources.vEmployeeDepartment 
HumanResources.vEmployeeDepartmentHistory 
Person.vAdditionalContactInfo 
Purchasing.vVendor 
Sales.vIndividualCustomer 
Sales.vSalesPerson 
Sales.vSalesPersonSalesByFiscalYears 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Person].[Contact]( 
         [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 
         [NameStyle] [dbo].[NameStyle] NOT NULL, 
         [Title] [nvarchar](8) COLLATE Latin1_General_CS_AS NULL, 
         [FirstName] [dbo].[Name] NOT NULL, 
         [MiddleName] [dbo].[Name] NULL, 
         [LastName] [dbo].[Name] NOT NULL, 
         [Suffix] [nvarchar](10) COLLATE Latin1_General_CS_AS NULL, 
         [EmailAddress] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL, 
         [EmailPromotion] [int] NOT NULL, 
         [Phone] [dbo].[Phone] NULL, 
         [PasswordHash] [varchar](128) COLLATE Latin1_General_CS_AS NOT NULL, 
         [PasswordSalt] [varchar](10) COLLATE Latin1_General_CS_AS NOT NULL, 
         [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Person.ContactType
Lookup table containing the types of contacts stored in Contact. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ContactTypeID	int			Primary key for ContactType records.	
IX 	Name	Name			Contact type description.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Purchasing.VendorContact	FK_VendorContact_ContactType_ContactTypeID	PK_ContactType_ContactTypeID	
Sales.StoreContact	FK_StoreContact_ContactType_ContactTypeID	PK_ContactType_ContactTypeID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_ContactType_Name	Name	ASC	Yes	NONCLUSTERED	
PK_ContactType_ContactTypeID	ContactTypeID	ASC	Yes	CLUSTERED	

 Objects that depend on Person.ContactType: 

Purchasing.vVendor 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Person].[ContactType]( 
         [ContactTypeID] [int] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Person.CountryRegion
Lookup table containing the ISO standard codes for countries and regions. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CountryRegionCode	nvarchar  (3)			ISO standard code for countries and regions.	
IX 	Name	Name			Country or region name.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Person.StateProvince	FK_StateProvince_CountryRegion_CountryRegionCode	PK_CountryRegion_CountryRegionCode	
Sales.CountryRegionCurrency	FK_CountryRegionCurrency_CountryRegion_CountryRegionCode	PK_CountryRegion_CountryRegionCode	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_CountryRegion_Name	Name	ASC	Yes	NONCLUSTERED	
PK_CountryRegion_CountryRegionCode	CountryRegionCode	ASC	Yes	CLUSTERED	

 Objects that depend on Person.CountryRegion: 

HumanResources.vEmployee 
Person.vStateProvinceCountryRegion 
Purchasing.vVendor 
Sales.vIndividualCustomer 
Sales.vSalesPerson 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Person].[CountryRegion]( 
         [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Person.StateProvince
State and province lookup table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	StateProvinceID	int			Primary key for StateProvince records.	
IX 	StateProvinceCode	nchar  (3)			ISO standard state or province code.	
FK 	CountryRegionCode	nvarchar  (3)			ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. 	
	IsOnlyStateProvinceFlag	Flag		((1))	0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.	
IX 	Name	Name			State or province description.	
FK 	TerritoryID	int			ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_StateProvince_CountryRegion_CountryRegionCode: CountryRegionCode relies upon remote PK_CountryRegion_CountryRegionCode    (Person.CountryRegion)   
FK_StateProvince_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID    (Sales.SalesTerritory)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesTaxRate	FK_SalesTaxRate_StateProvince_StateProvinceID	PK_StateProvince_StateProvinceID	
Person.Address	FK_Address_StateProvince_StateProvinceID	PK_StateProvince_StateProvinceID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_StateProvince_Name	Name	ASC	Yes	NONCLUSTERED	
AK_StateProvince_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
AK_StateProvince_StateProvinceCode_CountryRegionCode	StateProvinceCode	ASC	Yes	NONCLUSTERED	
AK_StateProvince_StateProvinceCode_CountryRegionCode	CountryRegionCode	ASC	Yes	NONCLUSTERED	
PK_StateProvince_StateProvinceID	StateProvinceID	ASC	Yes	CLUSTERED	

 Objects that depend on Person.StateProvince: 

HumanResources.vEmployee 
Person.vStateProvinceCountryRegion 
Purchasing.vVendor 
Sales.vIndividualCustomer 
Sales.vSalesPerson 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Person].[StateProvince]( 
         [StateProvinceID] [int] IDENTITY(1,1) NOT NULL, 
         [StateProvinceCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [IsOnlyStateProvinceFlag] [dbo].[Flag] NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [TerritoryID] [int] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.BillOfMaterials
Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	BillOfMaterialsID	int			Primary key for BillOfMaterials records.	
FK 	ProductAssemblyID	int	Yes		Parent product identification number. Foreign key to Product.ProductID.	
FK 	ComponentID	int			Component identification number. Foreign key to Product.ProductID.	
IX 	StartDate	datetime		(getdate())	Date the component started being used in the assembly item.	
	EndDate	datetime	Yes		Date the component stopped being used in the assembly item.	
FK 	UnitMeasureCode	nchar  (3)			Standard code identifying the unit of measure for the quantity.	
	BOMLevel	smallint			Indicates the depth the component is from its parent (AssemblyID).	
	PerAssemblyQty	decimal  (8.2)		((1.00))	Quantity of the component needed to create the assembly.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_BillOfMaterials_Product_ProductAssemblyID: ProductAssemblyID relies upon remote PK_Product_ProductID    (Production.Product)   
FK_BillOfMaterials_Product_ComponentID: ComponentID relies upon remote PK_Product_ProductID    (Production.Product)   
FK_BillOfMaterials_UnitMeasure_UnitMeasureCode: UnitMeasureCode relies upon remote PK_UnitMeasure_UnitMeasureCode    (Production.UnitMeasure)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	ProductAssemblyID	ASC	Yes	CLUSTERED	
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	ComponentID	ASC	Yes	CLUSTERED	
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	StartDate	ASC	Yes	CLUSTERED	
IX_BillOfMaterials_UnitMeasureCode	UnitMeasureCode	ASC		NONCLUSTERED	
PK_BillOfMaterials_BillOfMaterialsID	BillOfMaterialsID	ASC	Yes	NONCLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_BillOfMaterials_EndDate	StartDate	([EndDate]>[StartDate] OR [EndDate] IS NULL)	
CK_BillOfMaterials_EndDate	EndDate	([EndDate]>[StartDate] OR [EndDate] IS NULL)	
CK_BillOfMaterials_ProductAssemblyID	ProductAssemblyID	([ProductAssemblyID]<>[ComponentID])	
CK_BillOfMaterials_ProductAssemblyID	ComponentID	([ProductAssemblyID]<>[ComponentID])	
CK_BillOfMaterials_BOMLevel	ProductAssemblyID	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))	
CK_BillOfMaterials_BOMLevel	BOMLevel	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))	
CK_BillOfMaterials_BOMLevel	PerAssemblyQty	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))	
CK_BillOfMaterials_PerAssemblyQty	PerAssemblyQty	([PerAssemblyQty]>=(1.00))	

 Objects that depend on Production.BillOfMaterials: 

dbo.uspGetBillOfMaterials 
dbo.uspGetWhereUsedProductID 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[BillOfMaterials]( 
         [BillOfMaterialsID] [int] IDENTITY(1,1) NOT NULL, 
         [ProductAssemblyID] [int] NULL, 
         [ComponentID] [int] NOT NULL, 
         [StartDate] [datetime] NOT NULL, 
         [EndDate] [datetime] NULL, 
         [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [BOMLevel] [smallint] NOT NULL, 
         [PerAssemblyQty] [decimal](8, 2) NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.Culture
Lookup table containing the languages in which some AdventureWorks data is stored. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CultureID	nchar  (6)			Primary key for Culture records.	
IX 	Name	Name			Culture description.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.ProductModelProductDescriptionCulture	FK_ProductModelProductDescriptionCulture_Culture_CultureID	PK_Culture_CultureID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Culture_Name	Name	ASC	Yes	NONCLUSTERED	
PK_Culture_CultureID	CultureID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[Culture]( 
         [CultureID] [nchar](6) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.Document
Product maintenance documents. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	DocumentID	int			Primary key for Document records.	
	Title	nvarchar  (50)			Title of the document.	
IX 	FileName	nvarchar  (400)			Directory path and file name of the document	
	FileExtension	nvarchar  (8)			File extension indicating the document type. For example, .doc or .txt.	
IX 	Revision	nchar  (5)			Revision number of the document. 	
	ChangeNumber	int		((0))	Engineering change approval number.	
	Status	tinyint			1 = Pending approval, 2 = Approved, 3 = Obsolete	
	DocumentSummary	nvarchar	Yes		Document abstract.	
	Document	varbinary	Yes		Complete document.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.ProductDocument	FK_ProductDocument_Document_DocumentID	PK_Document_DocumentID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Document_FileName_Revision	FileName	ASC	Yes	NONCLUSTERED	
AK_Document_FileName_Revision	Revision	ASC	Yes	NONCLUSTERED	
PK_Document_DocumentID	DocumentID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Document_Status	Status	([Status]>=(1) AND [Status]<=(3))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[Document]( 
         [DocumentID] [int] IDENTITY(1,1) NOT NULL, 
         [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL, 
         [FileName] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL, 
         [FileExtension] [nvarchar](8) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Revision] [nchar](5) COLLATE Latin1_General_CS_AS NOT NULL, 
         [ChangeNumber] [int] NOT NULL, 
         [Status] [tinyint] NOT NULL, 
         [DocumentSummary] [nvarchar](max) COLLATE Latin1_General_CS_AS NULL, 
         [Document] [varbinary](max) NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.Illustration
Bicycle assembly diagrams. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	IllustrationID	int			Primary key for Illustration records.	
	Diagram	xml	Yes		Illustrations used in manufacturing instructions. Stored as XML.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.ProductModelIllustration	FK_ProductModelIllustration_Illustration_IllustrationID	PK_Illustration_IllustrationID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_Illustration_IllustrationID	IllustrationID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[Illustration]( 
         [IllustrationID] [int] IDENTITY(1,1) NOT NULL, 
         [Diagram] [xml] NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.Location
Product inventory and manufacturing locations. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	LocationID	smallint			Primary key for Location records.	
IX 	Name	Name			Location description.	
	CostRate	smallmoney		((0.00))	Standard hourly cost of the manufacturing location.	
	Availability	decimal  (8.2)		((0.00))	Work capacity (in hours) of the manufacturing location.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.ProductInventory	FK_ProductInventory_Location_LocationID	PK_Location_LocationID	
Production.WorkOrderRouting	FK_WorkOrderRouting_Location_LocationID	PK_Location_LocationID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Location_Name	Name	ASC	Yes	NONCLUSTERED	
PK_Location_LocationID	LocationID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Location_CostRate	CostRate	([CostRate]>=(0.00))	
CK_Location_Availability	Availability	([Availability]>=(0.00))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[Location]( 
         [LocationID] [smallint] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [CostRate] [smallmoney] NOT NULL, 
         [Availability] [decimal](8, 2) NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.Product
Products sold or used in the manfacturing of sold products. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ProductID	int			Primary key for Product records.	
IX 	Name	Name			Name of the product.	
IX 	ProductNumber	nvarchar  (25)			Unique product identification number.	
	MakeFlag	Flag		((1))	0 = Product is purchased, 1 = Product is manufactured in-house.	
	FinishedGoodsFlag	Flag		((1))	0 = Product is not a salable item. 1 = Product is salable.	
	Color	nvarchar  (15)	Yes		Product color.	
	SafetyStockLevel	smallint			Minimum inventory quantity. 	
	ReorderPoint	smallint			Inventory level that triggers a purchase order or work order. 	
	StandardCost	money			Standard cost of the product.	
	ListPrice	money			Selling price.	
	Size	nvarchar  (5)	Yes		Product size.	
FK 	SizeUnitMeasureCode	nchar  (3)	Yes		Unit of measure for Size column.	
FK 	WeightUnitMeasureCode	nchar  (3)	Yes		Unit of measure for Weight column.	
	Weight	decimal  (8.2)	Yes		Product weight.	
	DaysToManufacture	int			Number of days required to manufacture the product.	
	ProductLine	nchar  (2)	Yes		R = Road, M = Mountain, T = Touring, S = Standard	
	Class	nchar  (2)	Yes		H = High, M = Medium, L = Low	
	Style	nchar  (2)	Yes		W = Womens, M = Mens, U = Universal	
FK 	ProductSubcategoryID	int	Yes		Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID. 	
FK 	ProductModelID	int	Yes		Product is a member of this product model. Foreign key to ProductModel.ProductModelID.	
	SellStartDate	datetime			Date the product was available for sale.	
	SellEndDate	datetime	Yes		Date the product was no longer available for sale.	
	DiscontinuedDate	datetime	Yes		Date the product was discontinued.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_Product_UnitMeasure_SizeUnitMeasureCode: SizeUnitMeasureCode relies upon remote PK_UnitMeasure_UnitMeasureCode    (Production.UnitMeasure)   
FK_Product_UnitMeasure_WeightUnitMeasureCode: WeightUnitMeasureCode relies upon remote PK_UnitMeasure_UnitMeasureCode    (Production.UnitMeasure)   
FK_Product_ProductSubcategory_ProductSubcategoryID: ProductSubcategoryID relies upon remote PK_ProductSubcategory_ProductSubcategoryID    (Production.ProductSubcategory)   
FK_Product_ProductModel_ProductModelID: ProductModelID relies upon remote PK_ProductModel_ProductModelID    (Production.ProductModel)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.ProductCostHistory	FK_ProductCostHistory_Product_ProductID	PK_Product_ProductID	
Production.ProductDocument	FK_ProductDocument_Product_ProductID	PK_Product_ProductID	
Production.ProductInventory	FK_ProductInventory_Product_ProductID	PK_Product_ProductID	
Production.ProductListPriceHistory	FK_ProductListPriceHistory_Product_ProductID	PK_Product_ProductID	
Production.ProductProductPhoto	FK_ProductProductPhoto_Product_ProductID	PK_Product_ProductID	
Production.ProductReview	FK_ProductReview_Product_ProductID	PK_Product_ProductID	
Purchasing.ProductVendor	FK_ProductVendor_Product_ProductID	PK_Product_ProductID	
Sales.SpecialOfferProduct	FK_SpecialOfferProduct_Product_ProductID	PK_Product_ProductID	
Production.TransactionHistory	FK_TransactionHistory_Product_ProductID	PK_Product_ProductID	
Production.WorkOrder	FK_WorkOrder_Product_ProductID	PK_Product_ProductID	
Production.BillOfMaterials	FK_BillOfMaterials_Product_ComponentID	PK_Product_ProductID	
Purchasing.PurchaseOrderDetail	FK_PurchaseOrderDetail_Product_ProductID	PK_Product_ProductID	
Sales.ShoppingCartItem	FK_ShoppingCartItem_Product_ProductID	PK_Product_ProductID	
Production.BillOfMaterials	FK_BillOfMaterials_Product_ProductAssemblyID	PK_Product_ProductID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Product_Name	Name	ASC	Yes	NONCLUSTERED	
AK_Product_ProductNumber	ProductNumber	ASC	Yes	NONCLUSTERED	
AK_Product_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_Product_ProductID	ProductID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Product_SafetyStockLevel	SafetyStockLevel	([SafetyStockLevel]>(0))	
CK_Product_ReorderPoint	ReorderPoint	([ReorderPoint]>(0))	
CK_Product_StandardCost	StandardCost	([StandardCost]>=(0.00))	
CK_Product_ListPrice	ListPrice	([ListPrice]>=(0.00))	
CK_Product_Weight	Weight	([Weight]>(0.00))	
CK_Product_DaysToManufacture	DaysToManufacture	([DaysToManufacture]>=(0))	
CK_Product_ProductLine	ProductLine	(upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)	
CK_Product_Class	Class	(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)	
CK_Product_Style	Style	(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)	
CK_Product_SellEndDate	SellStartDate	([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)	
CK_Product_SellEndDate	SellEndDate	([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)	

 Objects that depend on Production.Product: 

dbo.ufnGetProductDealerPrice 
dbo.ufnGetProductListPrice 
dbo.ufnGetProductStandardCost 
dbo.uspGetBillOfMaterials 
dbo.uspGetWhereUsedProductID 
Production.vProductAndDescription 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[Product]( 
         [ProductID] [int] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ProductNumber] [nvarchar](25) COLLATE Latin1_General_CS_AS NOT NULL, 
         [MakeFlag] [dbo].[Flag] NOT NULL, 
         [FinishedGoodsFlag] [dbo].[Flag] NOT NULL, 
         [Color] [nvarchar](15) COLLATE Latin1_General_CS_AS NULL, 
         [SafetyStockLevel] [smallint] NOT NULL, 
         [ReorderPoint] [smallint] NOT NULL, 
         [StandardCost] [money] NOT NULL, 
         [ListPrice] [money] NOT NULL, 
         [Size] [nvarchar](5) COLLATE Latin1_General_CS_AS NULL, 
         [SizeUnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NULL, 
         [WeightUnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NULL, 
         [Weight] [decimal](8, 2) NULL, 
         [DaysToManufacture] [int] NOT NULL, 
         [ProductLine] [nchar](2) COLLATE Latin1_General_CS_AS NULL, 
         [Class] [nchar](2) COLLATE Latin1_General_CS_AS NULL, 
         [Style] [nchar](2) COLLATE Latin1_General_CS_AS NULL, 
         [ProductSubcategoryID] [int] NULL, 
         [ProductModelID] [int] NULL, 
         [SellStartDate] [datetime] NOT NULL, 
         [SellEndDate] [datetime] NULL, 
         [DiscontinuedDate] [datetime] NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductCategory
High-level product categorization. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ProductCategoryID	int			Primary key for ProductCategory records.	
IX 	Name	Name			Category description.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.ProductSubcategory	FK_ProductSubcategory_ProductCategory_ProductCategoryID	PK_ProductCategory_ProductCategoryID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_ProductCategory_Name	Name	ASC	Yes	NONCLUSTERED	
AK_ProductCategory_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_ProductCategory_ProductCategoryID	ProductCategoryID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductCategory]( 
         [ProductCategoryID] [int] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductCostHistory
Changes in the cost of a product over time. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ProductID	int			Product identification number. Foreign key to Product.ProductID	
    PK 	StartDate	datetime			Product cost start date.	
	EndDate	datetime	Yes		Product cost end date.	
	StandardCost	money			Standard cost of the product.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductCostHistory_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ProductCostHistory_ProductID_StartDate	ProductID	ASC	Yes	CLUSTERED	
PK_ProductCostHistory_ProductID_StartDate	StartDate	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_ProductCostHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	
CK_ProductCostHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	
CK_ProductCostHistory_StandardCost	StandardCost	([StandardCost]>=(0.00))	

 Objects that depend on Production.ProductCostHistory: 

dbo.ufnGetProductStandardCost 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductCostHistory]( 
         [ProductID] [int] NOT NULL, 
         [StartDate] [datetime] NOT NULL, 
         [EndDate] [datetime] NULL, 
         [StandardCost] [money] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductDescription
Product descriptions in several languages. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ProductDescriptionID	int			Primary key for ProductDescription records.	
	Description	nvarchar  (400)			Description of the product.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.ProductModelProductDescriptionCulture	FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID	PK_ProductDescription_ProductDescriptionID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_ProductDescription_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_ProductDescription_ProductDescriptionID	ProductDescriptionID	ASC	Yes	CLUSTERED	

 Objects that depend on Production.ProductDescription: 

Production.vProductAndDescription 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductDescription]( 
         [ProductDescriptionID] [int] IDENTITY(1,1) NOT NULL, 
         [Description] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductDocument
Cross-reference table mapping products to related product documents. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
    PK FK	DocumentID	int			Document identification number. Foreign key to Document.DocumentID.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductDocument_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   
FK_ProductDocument_Document_DocumentID: DocumentID relies upon remote PK_Document_DocumentID    (Production.Document)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ProductDocument_ProductID_DocumentID	ProductID	ASC	Yes	CLUSTERED	
PK_ProductDocument_ProductID_DocumentID	DocumentID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductDocument]( 
         [ProductID] [int] NOT NULL, 
         [DocumentID] [int] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductInventory
Product inventory information. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
    PK FK	LocationID	smallint			Inventory location identification number. Foreign key to Location.LocationID. 	
	Shelf	nvarchar  (10)			Storage compartment within an inventory location.	
	Bin	tinyint			Storage container on a shelf in an inventory location.	
	Quantity	smallint		((0))	Quantity of products in the inventory location.	
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductInventory_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   
FK_ProductInventory_Location_LocationID: LocationID relies upon remote PK_Location_LocationID    (Production.Location)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ProductInventory_ProductID_LocationID	ProductID	ASC	Yes	CLUSTERED	
PK_ProductInventory_ProductID_LocationID	LocationID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_ProductInventory_Shelf	Shelf	([Shelf] like '[A-Za-z]' OR [Shelf]='N/A')	
CK_ProductInventory_Bin	Bin	([Bin]>=(0) AND [Bin]<=(100))	

 Objects that depend on Production.ProductInventory: 

dbo.ufnGetStock 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductInventory]( 
         [ProductID] [int] NOT NULL, 
         [LocationID] [smallint] NOT NULL, 
         [Shelf] [nvarchar](10) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Bin] [tinyint] NOT NULL, 
         [Quantity] [smallint] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductListPriceHistory
Changes in the list price of a product over time. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ProductID	int			Product identification number. Foreign key to Product.ProductID	
    PK 	StartDate	datetime			List price start date.	
	EndDate	datetime	Yes		List price end date	
	ListPrice	money			Product list price.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductListPriceHistory_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ProductListPriceHistory_ProductID_StartDate	ProductID	ASC	Yes	CLUSTERED	
PK_ProductListPriceHistory_ProductID_StartDate	StartDate	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_ProductListPriceHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	
CK_ProductListPriceHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	
CK_ProductListPriceHistory_ListPrice	ListPrice	([ListPrice]>(0.00))	

 Objects that depend on Production.ProductListPriceHistory: 

dbo.ufnGetProductDealerPrice 
dbo.ufnGetProductListPrice 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductListPriceHistory]( 
         [ProductID] [int] NOT NULL, 
         [StartDate] [datetime] NOT NULL, 
         [EndDate] [datetime] NULL, 
         [ListPrice] [money] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductModel
Product model classification. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ProductModelID	int			Primary key for ProductModel records.	
IX 	Name	Name			Product model description.	
IX 	CatalogDescription	xml	Yes		Detailed product catalog information in xml format.	
IX 	Instructions	xml	Yes		Manufacturing instructions in xml format.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.Product	FK_Product_ProductModel_ProductModelID	PK_ProductModel_ProductModelID	
Production.ProductModelIllustration	FK_ProductModelIllustration_ProductModel_ProductModelID	PK_ProductModel_ProductModelID	
Production.ProductModelProductDescriptionCulture	FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID	PK_ProductModel_ProductModelID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_ProductModel_Name	Name	ASC	Yes	NONCLUSTERED	
AK_ProductModel_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_ProductModel_ProductModelID	ProductModelID	ASC	Yes	CLUSTERED	
PXML_ProductModel_CatalogDescription	CatalogDescription	ASC		XML	
PXML_ProductModel_Instructions	Instructions	ASC		XML	

 Objects that depend on Production.ProductModel: 

Production.vProductAndDescription 
Production.vProductModelCatalogDescription 
Production.vProductModelInstructions 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductModel]( 
         [ProductModelID] [int] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [CatalogDescription] [xml](CONTENT [Production].[ProductDescriptionSchemaCollection]) NULL, 
         [Instructions] [xml](CONTENT [Production].[ManuInstructionsSchemaCollection]) NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductModelIllustration
Cross-reference table mapping product models and illustrations. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ProductModelID	int			Primary key. Foreign key to ProductModel.ProductModelID.	
    PK FK	IllustrationID	int			Primary key. Foreign key to Illustration.IllustrationID.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductModelIllustration_ProductModel_ProductModelID: ProductModelID relies upon remote PK_ProductModel_ProductModelID    (Production.ProductModel)   
FK_ProductModelIllustration_Illustration_IllustrationID: IllustrationID relies upon remote PK_Illustration_IllustrationID    (Production.Illustration)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ProductModelIllustration_ProductModelID_IllustrationID	ProductModelID	ASC	Yes	CLUSTERED	
PK_ProductModelIllustration_ProductModelID_IllustrationID	IllustrationID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductModelIllustration]( 
         [ProductModelID] [int] NOT NULL, 
         [IllustrationID] [int] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductModelProductDescriptionCulture
Cross-reference table mapping product descriptions and the language the description is written in. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ProductModelID	int			Primary key. Foreign key to ProductModel.ProductModelID.	
    PK FK	ProductDescriptionID	int			Primary key. Foreign key to ProductDescription.ProductDescriptionID.	
    PK FK	CultureID	nchar  (6)			Culture identification number. Foreign key to Culture.CultureID.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID: ProductModelID relies upon remote PK_ProductModel_ProductModelID    (Production.ProductModel)   
FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID: ProductDescriptionID relies upon remote PK_ProductDescription_ProductDescriptionID    (Production.ProductDescription)   
FK_ProductModelProductDescriptionCulture_Culture_CultureID: CultureID relies upon remote PK_Culture_CultureID    (Production.Culture)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	ProductModelID	ASC	Yes	CLUSTERED	
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	ProductDescriptionID	ASC	Yes	CLUSTERED	
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	CultureID	ASC	Yes	CLUSTERED	

 Objects that depend on Production.ProductModelProductDescriptionCulture: 

Production.vProductAndDescription 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductModelProductDescriptionCulture]( 
         [ProductModelID] [int] NOT NULL, 
         [ProductDescriptionID] [int] NOT NULL, 
         [CultureID] [nchar](6) COLLATE Latin1_General_CS_AS NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductPhoto
Product images. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ProductPhotoID	int			Primary key for ProductPhoto records.	
	ThumbNailPhoto	varbinary	Yes		Small image of the product.	
	ThumbnailPhotoFileName	nvarchar  (50)	Yes		Small image file name.	
	LargePhoto	varbinary	Yes		Large image of the product.	
	LargePhotoFileName	nvarchar  (50)	Yes		Large image file name.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.ProductProductPhoto	FK_ProductProductPhoto_ProductPhoto_ProductPhotoID	PK_ProductPhoto_ProductPhotoID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ProductPhoto_ProductPhotoID	ProductPhotoID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductPhoto]( 
         [ProductPhotoID] [int] IDENTITY(1,1) NOT NULL, 
         [ThumbNailPhoto] [varbinary](max) NULL, 
         [ThumbnailPhotoFileName] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL, 
         [LargePhoto] [varbinary](max) NULL, 
         [LargePhotoFileName] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductProductPhoto
Cross-reference table mapping products and product photos. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
    PK FK	ProductPhotoID	int			Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID.	
	Primary	Flag		((0))	0 = Photo is not the principal image. 1 = Photo is the principal image.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductProductPhoto_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   
FK_ProductProductPhoto_ProductPhoto_ProductPhotoID: ProductPhotoID relies upon remote PK_ProductPhoto_ProductPhotoID    (Production.ProductPhoto)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ProductProductPhoto_ProductID_ProductPhotoID	ProductID	ASC	Yes	NONCLUSTERED	
PK_ProductProductPhoto_ProductID_ProductPhotoID	ProductPhotoID	ASC	Yes	NONCLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductProductPhoto]( 
         [ProductID] [int] NOT NULL, 
         [ProductPhotoID] [int] NOT NULL, 
         [Primary] [dbo].[Flag] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductReview
Customer reviews of products they have purchased. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ProductReviewID	int			Primary key for ProductReview records.	
FK 	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
IX 	ReviewerName	Name			Name of the reviewer.	
	ReviewDate	datetime		(getdate())	Date review was submitted.	
	EmailAddress	nvarchar  (50)			Reviewer's e-mail address.	
	Rating	int			Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.	
	Comments	nvarchar  (3850)	Yes		Reviewer's comments	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductReview_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_ProductReview_ProductID_Name	ProductID	ASC		NONCLUSTERED	
IX_ProductReview_ProductID_Name	ReviewerName	ASC		NONCLUSTERED	
PK_ProductReview_ProductReviewID	ProductReviewID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_ProductReview_Rating	Rating	([Rating]>=(1) AND [Rating]<=(5))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductReview]( 
         [ProductReviewID] [int] IDENTITY(1,1) NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [ReviewerName] [dbo].[Name] NOT NULL, 
         [ReviewDate] [datetime] NOT NULL, 
         [EmailAddress] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Rating] [int] NOT NULL, 
         [Comments] [nvarchar](3850) COLLATE Latin1_General_CS_AS NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ProductSubcategory
Product subcategories. See ProductCategory table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ProductSubcategoryID	int			Primary key for ProductSubcategory records.	
FK 	ProductCategoryID	int			Product category identification number. Foreign key to ProductCategory.ProductCategoryID.	
IX 	Name	Name			Subcategory description.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductSubcategory_ProductCategory_ProductCategoryID: ProductCategoryID relies upon remote PK_ProductCategory_ProductCategoryID    (Production.ProductCategory)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.Product	FK_Product_ProductSubcategory_ProductSubcategoryID	PK_ProductSubcategory_ProductSubcategoryID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_ProductSubcategory_Name	Name	ASC	Yes	NONCLUSTERED	
AK_ProductSubcategory_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_ProductSubcategory_ProductSubcategoryID	ProductSubcategoryID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ProductSubcategory]( 
         [ProductSubcategoryID] [int] IDENTITY(1,1) NOT NULL, 
         [ProductCategoryID] [int] NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.ScrapReason
Manufacturing failure reasons lookup table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ScrapReasonID	smallint			Primary key for ScrapReason records.	
IX 	Name	Name			Failure description.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.WorkOrder	FK_WorkOrder_ScrapReason_ScrapReasonID	PK_ScrapReason_ScrapReasonID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_ScrapReason_Name	Name	ASC	Yes	NONCLUSTERED	
PK_ScrapReason_ScrapReasonID	ScrapReasonID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[ScrapReason]( 
         [ScrapReasonID] [smallint] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.TransactionHistory
Record of each purchase order, sales order, or work order transaction year to date. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	TransactionID	int			Primary key for TransactionHistory records.	
FK 	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
IX 	ReferenceOrderID	int			Purchase order, sales order, or work order identification number.	
IX 	ReferenceOrderLineID	int		((0))	Line number associated with the purchase order, sales order, or work order.	
	TransactionDate	datetime		(getdate())	Date and time of the transaction.	
	TransactionType	nchar  (1)			W = WorkOrder, S = SalesOrder, P = PurchaseOrder	
	Quantity	int			Product quantity.	
	ActualCost	money			Product cost.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_TransactionHistory_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_TransactionHistory_ProductID	ProductID	ASC		NONCLUSTERED	
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderID	ASC		NONCLUSTERED	
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderLineID	ASC		NONCLUSTERED	
PK_TransactionHistory_TransactionID	TransactionID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_TransactionHistory_TransactionType	TransactionType	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')	

 Objects that depend on Production.TransactionHistory: 

Production.iWorkOrder 
Production.uWorkOrder 
Purchasing.iPurchaseOrderDetail 
Purchasing.uPurchaseOrderDetail 
Sales.iduSalesOrderDetail 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[TransactionHistory]( 
         [TransactionID] [int] IDENTITY(100000,1) NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [ReferenceOrderID] [int] NOT NULL, 
         [ReferenceOrderLineID] [int] NOT NULL, 
         [TransactionDate] [datetime] NOT NULL, 
         [TransactionType] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Quantity] [int] NOT NULL, 
         [ActualCost] [money] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.TransactionHistoryArchive
Transactions for previous years. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	TransactionID	int			Primary key for TransactionHistoryArchive records.	
IX 	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
IX 	ReferenceOrderID	int			Purchase order, sales order, or work order identification number.	
IX 	ReferenceOrderLineID	int		((0))	Line number associated with the purchase order, sales order, or work order.	
	TransactionDate	datetime		(getdate())	Date and time of the transaction.	
	TransactionType	nchar  (1)			W = Work Order, S = Sales Order, P = Purchase Order	
	Quantity	int			Product quantity.	
	ActualCost	money			Product cost.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_TransactionHistoryArchive_ProductID	ProductID	ASC		NONCLUSTERED	
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderID	ASC		NONCLUSTERED	
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderLineID	ASC		NONCLUSTERED	
PK_TransactionHistoryArchive_TransactionID	TransactionID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_TransactionHistoryArchive_TransactionType	TransactionType	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[TransactionHistoryArchive]( 
         [TransactionID] [int] NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [ReferenceOrderID] [int] NOT NULL, 
         [ReferenceOrderLineID] [int] NOT NULL, 
         [TransactionDate] [datetime] NOT NULL, 
         [TransactionType] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Quantity] [int] NOT NULL, 
         [ActualCost] [money] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.UnitMeasure
Unit of measure lookup table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	UnitMeasureCode	nchar  (3)			Primary key.	
IX 	Name	Name			Unit of measure description.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.Product	FK_Product_UnitMeasure_SizeUnitMeasureCode	PK_UnitMeasure_UnitMeasureCode	
Production.Product	FK_Product_UnitMeasure_WeightUnitMeasureCode	PK_UnitMeasure_UnitMeasureCode	
Purchasing.ProductVendor	FK_ProductVendor_UnitMeasure_UnitMeasureCode	PK_UnitMeasure_UnitMeasureCode	
Production.BillOfMaterials	FK_BillOfMaterials_UnitMeasure_UnitMeasureCode	PK_UnitMeasure_UnitMeasureCode	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_UnitMeasure_Name	Name	ASC	Yes	NONCLUSTERED	
PK_UnitMeasure_UnitMeasureCode	UnitMeasureCode	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[UnitMeasure]( 
         [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.WorkOrder
Manufacturing work orders. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	WorkOrderID	int			Primary key for WorkOrder records.	
FK 	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
	OrderQty	int			Product quantity to build.	
	StockedQty	int			Quantity built and put in inventory.	
	ScrappedQty	smallint			Quantity that failed inspection.	
	StartDate	datetime			Work order start date.	
	EndDate	datetime	Yes		Work order end date.	
	DueDate	datetime			Work order due date.	
FK 	ScrapReasonID	smallint	Yes		Reason for inspection failure.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_WorkOrder_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   
FK_WorkOrder_ScrapReason_ScrapReasonID: ScrapReasonID relies upon remote PK_ScrapReason_ScrapReasonID    (Production.ScrapReason)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Production.WorkOrderRouting	FK_WorkOrderRouting_WorkOrder_WorkOrderID	PK_WorkOrder_WorkOrderID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_WorkOrder_ProductID	ProductID	ASC		NONCLUSTERED	
IX_WorkOrder_ScrapReasonID	ScrapReasonID	ASC		NONCLUSTERED	
PK_WorkOrder_WorkOrderID	WorkOrderID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_WorkOrder_OrderQty	OrderQty	([OrderQty]>(0))	
CK_WorkOrder_ScrappedQty	ScrappedQty	([ScrappedQty]>=(0))	
CK_WorkOrder_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	
CK_WorkOrder_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	

Triggers: 

Trigger name: Production.iWorkOrder
Trigger type: INSERT
Trigger active: Yes
Trigger description: AFTER INSERT trigger that inserts a row in the TransactionHistory table.
      Trigger definition:       

     CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder]  
     AFTER INSERT AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             INSERT INTO [Production].[TransactionHistory]( 
                 [ProductID] 
                 ,[ReferenceOrderID] 
                 ,[TransactionType] 
                 ,[TransactionDate] 
                 ,[Quantity] 
                 ,[ActualCost]) 
             SELECT  
                 inserted.[ProductID] 
                 ,inserted.[WorkOrderID] 
                 ,'W' 
                 ,GETDATE() 
                 ,inserted.[OrderQty] 
                 ,0 
             FROM inserted; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

Trigger name: Production.uWorkOrder
Trigger type: UPDATE
Trigger active: Yes
Trigger description: AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.
      Trigger definition:       

     CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder]  
     AFTER UPDATE AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             IF UPDATE([ProductID]) OR UPDATE([OrderQty]) 
             BEGIN 
                 INSERT INTO [Production].[TransactionHistory]( 
                     [ProductID] 
                     ,[ReferenceOrderID] 
                     ,[TransactionType] 
                     ,[TransactionDate] 
                     ,[Quantity]) 
                 SELECT  
                     inserted.[ProductID] 
                     ,inserted.[WorkOrderID] 
                     ,'W' 
                     ,GETDATE() 
                     ,inserted.[OrderQty] 
                 FROM inserted; 
             END; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

 Objects that depend on Production.WorkOrder: 

Production.uWorkOrder 
Production.WorkOrder 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[WorkOrder]( 
         [WorkOrderID] [int] IDENTITY(1,1) NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [OrderQty] [int] NOT NULL, 
         [StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))), 
         [ScrappedQty] [smallint] NOT NULL, 
         [StartDate] [datetime] NOT NULL, 
         [EndDate] [datetime] NULL, 
         [DueDate] [datetime] NOT NULL, 
         [ScrapReasonID] [smallint] NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Production.WorkOrderRouting
Work order details. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	WorkOrderID	int			Primary key. Foreign key to WorkOrder.WorkOrderID.	
    PK 	ProductID	int			Primary key. Foreign key to Product.ProductID.	
    PK 	OperationSequence	smallint			Primary key. Indicates the manufacturing process sequence.	
FK 	LocationID	smallint			Manufacturing location where the part is processed. Foreign key to Location.LocationID.	
	ScheduledStartDate	datetime			Planned manufacturing start date.	
	ScheduledEndDate	datetime			Planned manufacturing end date.	
	ActualStartDate	datetime	Yes		Actual start date.	
	ActualEndDate	datetime	Yes		Actual end date.	
	ActualResourceHrs	decimal  (9.4)	Yes		Number of manufacturing hours used.	
	PlannedCost	money			Estimated manufacturing cost.	
	ActualCost	money	Yes		Actual manufacturing cost.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_WorkOrderRouting_WorkOrder_WorkOrderID: WorkOrderID relies upon remote PK_WorkOrder_WorkOrderID    (Production.WorkOrder)   
FK_WorkOrderRouting_Location_LocationID: LocationID relies upon remote PK_Location_LocationID    (Production.Location)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_WorkOrderRouting_ProductID	ProductID	ASC		NONCLUSTERED	
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	WorkOrderID	ASC	Yes	CLUSTERED	
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	ProductID	ASC	Yes	CLUSTERED	
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	OperationSequence	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_WorkOrderRouting_ScheduledEndDate	ScheduledStartDate	([ScheduledEndDate]>=[ScheduledStartDate])	
CK_WorkOrderRouting_ScheduledEndDate	ScheduledEndDate	([ScheduledEndDate]>=[ScheduledStartDate])	
CK_WorkOrderRouting_ActualEndDate	ActualStartDate	([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)	
CK_WorkOrderRouting_ActualEndDate	ActualEndDate	([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)	
CK_WorkOrderRouting_ActualResourceHrs	ActualResourceHrs	([ActualResourceHrs]>=(0.0000))	
CK_WorkOrderRouting_PlannedCost	PlannedCost	([PlannedCost]>(0.00))	
CK_WorkOrderRouting_ActualCost	ActualCost	([ActualCost]>(0.00))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Production].[WorkOrderRouting]( 
         [WorkOrderID] [int] NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [OperationSequence] [smallint] NOT NULL, 
         [LocationID] [smallint] NOT NULL, 
         [ScheduledStartDate] [datetime] NOT NULL, 
         [ScheduledEndDate] [datetime] NOT NULL, 
         [ActualStartDate] [datetime] NULL, 
         [ActualEndDate] [datetime] NULL, 
         [ActualResourceHrs] [decimal](9, 4) NULL, 
         [PlannedCost] [money] NOT NULL, 
         [ActualCost] [money] NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Purchasing.ProductVendor
Cross-reference table mapping vendors with the products they supply. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ProductID	int			Primary key. Foreign key to Product.ProductID.	
    PK FK	VendorID	int			Primary key. Foreign key to Vendor.VendorID.	
	AverageLeadTime	int			The average span of time (in days) between placing an order with the vendor and receiving the purchased product.	
	StandardPrice	money			The vendor's usual selling price.	
	LastReceiptCost	money	Yes		The selling price when last purchased.	
	LastReceiptDate	datetime	Yes		Date the product was last received by the vendor.	
	MinOrderQty	int			The maximum quantity that should be ordered.	
	MaxOrderQty	int			The minimum quantity that should be ordered.	
	OnOrderQty	int	Yes		The quantity currently on order.	
FK 	UnitMeasureCode	nchar  (3)			The product's unit of measure.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ProductVendor_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   
FK_ProductVendor_Vendor_VendorID: VendorID relies upon remote PK_Vendor_VendorID    (Purchasing.Vendor)   
FK_ProductVendor_UnitMeasure_UnitMeasureCode: UnitMeasureCode relies upon remote PK_UnitMeasure_UnitMeasureCode    (Production.UnitMeasure)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_ProductVendor_UnitMeasureCode	UnitMeasureCode	ASC		NONCLUSTERED	
IX_ProductVendor_VendorID	VendorID	ASC		NONCLUSTERED	
PK_ProductVendor_ProductID_VendorID	ProductID	ASC	Yes	CLUSTERED	
PK_ProductVendor_ProductID_VendorID	VendorID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_ProductVendor_AverageLeadTime	AverageLeadTime	([AverageLeadTime]>=(1))	
CK_ProductVendor_StandardPrice	StandardPrice	([StandardPrice]>(0.00))	
CK_ProductVendor_LastReceiptCost	LastReceiptCost	([LastReceiptCost]>(0.00))	
CK_ProductVendor_MinOrderQty	MinOrderQty	([MinOrderQty]>=(1))	
CK_ProductVendor_MaxOrderQty	MaxOrderQty	([MaxOrderQty]>=(1))	
CK_ProductVendor_OnOrderQty	OnOrderQty	([OnOrderQty]>=(0))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Purchasing].[ProductVendor]( 
         [ProductID] [int] NOT NULL, 
         [VendorID] [int] NOT NULL, 
         [AverageLeadTime] [int] NOT NULL, 
         [StandardPrice] [money] NOT NULL, 
         [LastReceiptCost] [money] NULL, 
         [LastReceiptDate] [datetime] NULL, 
         [MinOrderQty] [int] NOT NULL, 
         [MaxOrderQty] [int] NOT NULL, 
         [OnOrderQty] [int] NULL, 
         [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Purchasing.PurchaseOrderDetail
Individual products associated with a specific purchase order. See PurchaseOrderHeader. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	PurchaseOrderID	int			Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.	
    PK 	PurchaseOrderDetailID	int			Primary key. One line number per purchased product.	
	DueDate	datetime			Date the product is expected to be received.	
	OrderQty	smallint			Quantity ordered.	
FK 	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
	UnitPrice	money			Vendor's selling price of a single product.	
	LineTotal	money			Per product subtotal. Computed as OrderQty * UnitPrice.	
	ReceivedQty	decimal  (8.2)			Quantity actually received from the vendor.	
	RejectedQty	decimal  (8.2)			Quantity rejected during inspection.	
	StockedQty	decimal  (9.2)			Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID: PurchaseOrderID relies upon remote PK_PurchaseOrderHeader_PurchaseOrderID    (Purchasing.PurchaseOrderHeader)   
FK_PurchaseOrderDetail_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_PurchaseOrderDetail_ProductID	ProductID	ASC		NONCLUSTERED	
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID	PurchaseOrderID	ASC	Yes	CLUSTERED	
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID	PurchaseOrderDetailID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_PurchaseOrderDetail_OrderQty	OrderQty	([OrderQty]>(0))	
CK_PurchaseOrderDetail_UnitPrice	UnitPrice	([UnitPrice]>=(0.00))	
CK_PurchaseOrderDetail_ReceivedQty	ReceivedQty	([ReceivedQty]>=(0.00))	
CK_PurchaseOrderDetail_RejectedQty	RejectedQty	([RejectedQty]>=(0.00))	

Triggers: 

Trigger name: Purchasing.iPurchaseOrderDetail
Trigger type: INSERT
Trigger active: Yes
Trigger description: AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.
      Trigger definition:       

     CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail]  
     AFTER INSERT AS 
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             INSERT INTO [Production].[TransactionHistory] 
                 ([ProductID] 
                 ,[ReferenceOrderID] 
                 ,[ReferenceOrderLineID] 
                 ,[TransactionType] 
                 ,[TransactionDate] 
                 ,[Quantity] 
                 ,[ActualCost]) 
             SELECT  
                 inserted.[ProductID] 
                 ,inserted.[PurchaseOrderID] 
                 ,inserted.[PurchaseOrderDetailID] 
                 ,'P' 
                 ,GETDATE() 
                 ,inserted.[OrderQty] 
                 ,inserted.[UnitPrice] 
             FROM inserted  
                 INNER JOIN [Purchasing].[PurchaseOrderHeader]  
                 ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]; 
      
             -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the  
             -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. 
             UPDATE [Purchasing].[PurchaseOrderHeader] 
             SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =  
                 (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) 
                     FROM [Purchasing].[PurchaseOrderDetail] 
                     WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) 
             WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

Trigger name: Purchasing.uPurchaseOrderDetail
Trigger type: UPDATE
Trigger active: Yes
Trigger description: AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.
      Trigger definition:       

     CREATE TRIGGER [Purchasing].[uPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail]  
     AFTER UPDATE AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) 
             -- Insert record into TransactionHistory  
             BEGIN 
                 INSERT INTO [Production].[TransactionHistory] 
                     ([ProductID] 
                     ,[ReferenceOrderID] 
                     ,[ReferenceOrderLineID] 
                     ,[TransactionType] 
                     ,[TransactionDate] 
                     ,[Quantity] 
                     ,[ActualCost]) 
                 SELECT  
                     inserted.[ProductID] 
                     ,inserted.[PurchaseOrderID] 
                     ,inserted.[PurchaseOrderDetailID] 
                     ,'P' 
                     ,GETDATE() 
                     ,inserted.[OrderQty] 
                     ,inserted.[UnitPrice] 
                 FROM inserted  
                     INNER JOIN [Purchasing].[PurchaseOrderDetail]  
                     ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]; 
      
                 -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the  
                 -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. 
                 UPDATE [Purchasing].[PurchaseOrderHeader] 
                 SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =  
                     (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) 
                         FROM [Purchasing].[PurchaseOrderDetail] 
                         WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]  
                             = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) 
                 WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]  
                     IN (SELECT inserted.[PurchaseOrderID] FROM inserted); 
      
                 UPDATE [Purchasing].[PurchaseOrderDetail] 
                 SET [Purchasing].[PurchaseOrderDetail].[ModifiedDate] = GETDATE() 
                 FROM inserted 
                 WHERE inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID] 
                     AND inserted.[PurchaseOrderDetailID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID]; 
             END; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

 Objects that depend on Purchasing.PurchaseOrderDetail: 

Purchasing.iPurchaseOrderDetail 
Purchasing.PurchaseOrderDetail 
Purchasing.uPurchaseOrderDetail 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Purchasing].[PurchaseOrderDetail]( 
         [PurchaseOrderID] [int] NOT NULL, 
         [PurchaseOrderDetailID] [int] IDENTITY(1,1) NOT NULL, 
         [DueDate] [datetime] NOT NULL, 
         [OrderQty] [smallint] NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [UnitPrice] [money] NOT NULL, 
         [LineTotal]  AS (isnull([OrderQty]*[UnitPrice],(0.00))), 
         [ReceivedQty] [decimal](8, 2) NOT NULL, 
         [RejectedQty] [decimal](8, 2) NOT NULL, 
         [StockedQty]  AS (isnull([ReceivedQty]-[RejectedQty],(0.00))), 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Purchasing.PurchaseOrderHeader
General purchase order information. See PurchaseOrderDetail. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	PurchaseOrderID	int			Primary key.	
	RevisionNumber	tinyint		((0))	Incremental number to track changes to the purchase order over time.	
	Status	tinyint		((1))	Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete	
FK 	EmployeeID	int			Employee who created the purchase order. Foreign key to Employee.EmployeeID.	
FK 	VendorID	int			Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.	
FK 	ShipMethodID	int			Shipping method. Foreign key to ShipMethod.ShipMethodID.	
	OrderDate	datetime		(getdate())	Purchase order creation date.	
	ShipDate	datetime	Yes		Estimated shipment date from the vendor.	
	SubTotal	money		((0.00))	Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.	
	TaxAmt	money		((0.00))	Tax amount.	
	Freight	money		((0.00))	Shipping cost.	
	TotalDue	money			Total due to vendor. Computed as Subtotal + TaxAmt + Freight.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_PurchaseOrderHeader_Employee_EmployeeID: EmployeeID relies upon remote PK_Employee_EmployeeID    (HumanResources.Employee)   
FK_PurchaseOrderHeader_Vendor_VendorID: VendorID relies upon remote PK_Vendor_VendorID    (Purchasing.Vendor)   
FK_PurchaseOrderHeader_ShipMethod_ShipMethodID: ShipMethodID relies upon remote PK_ShipMethod_ShipMethodID    (Purchasing.ShipMethod)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Purchasing.PurchaseOrderDetail	FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID	PK_PurchaseOrderHeader_PurchaseOrderID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_PurchaseOrderHeader_EmployeeID	EmployeeID	ASC		NONCLUSTERED	
IX_PurchaseOrderHeader_VendorID	VendorID	ASC		NONCLUSTERED	
PK_PurchaseOrderHeader_PurchaseOrderID	PurchaseOrderID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_PurchaseOrderHeader_Status	Status	([Status]>=(1) AND [Status]<=(4))	
CK_PurchaseOrderHeader_ShipDate	OrderDate	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)	
CK_PurchaseOrderHeader_ShipDate	ShipDate	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)	
CK_PurchaseOrderHeader_SubTotal	SubTotal	([SubTotal]>=(0.00))	
CK_PurchaseOrderHeader_TaxAmt	TaxAmt	([TaxAmt]>=(0.00))	
CK_PurchaseOrderHeader_Freight	Freight	([Freight]>=(0.00))	

Triggers: 

Trigger name: Purchasing.uPurchaseOrderHeader
Trigger type: UPDATE
Trigger active: Yes
Trigger description: AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.
      Trigger definition:       

     CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader]  
     AFTER UPDATE AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             -- Update RevisionNumber for modification of any field EXCEPT the Status. 
             IF NOT UPDATE([Status]) 
             BEGIN 
                 UPDATE [Purchasing].[PurchaseOrderHeader] 
                 SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] =  
                     [Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1 
                 WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN  
                     (SELECT inserted.[PurchaseOrderID] FROM inserted); 
             END; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

 Objects that depend on Purchasing.PurchaseOrderHeader: 

Purchasing.iPurchaseOrderDetail 
Purchasing.PurchaseOrderHeader 
Purchasing.uPurchaseOrderDetail 
Purchasing.uPurchaseOrderHeader 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Purchasing].[PurchaseOrderHeader]( 
         [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL, 
         [RevisionNumber] [tinyint] NOT NULL, 
         [Status] [tinyint] NOT NULL, 
         [EmployeeID] [int] NOT NULL, 
         [VendorID] [int] NOT NULL, 
         [ShipMethodID] [int] NOT NULL, 
         [OrderDate] [datetime] NOT NULL, 
         [ShipDate] [datetime] NULL, 
         [SubTotal] [money] NOT NULL, 
         [TaxAmt] [money] NOT NULL, 
         [Freight] [money] NOT NULL, 
         [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) PERSISTED NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Purchasing.ShipMethod
Shipping company lookup table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ShipMethodID	int			Primary key for ShipMethod records.	
IX 	Name	Name			Shipping company name.	
	ShipBase	money		((0.00))	Minimum shipping charge.	
	ShipRate	money		((0.00))	Shipping charge per pound.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderHeader	FK_SalesOrderHeader_ShipMethod_ShipMethodID	PK_ShipMethod_ShipMethodID	
Purchasing.PurchaseOrderHeader	FK_PurchaseOrderHeader_ShipMethod_ShipMethodID	PK_ShipMethod_ShipMethodID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_ShipMethod_Name	Name	ASC	Yes	NONCLUSTERED	
AK_ShipMethod_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_ShipMethod_ShipMethodID	ShipMethodID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_ShipMethod_ShipBase	ShipBase	([ShipBase]>(0.00))	
CK_ShipMethod_ShipRate	ShipRate	([ShipRate]>(0.00))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Purchasing].[ShipMethod]( 
         [ShipMethodID] [int] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ShipBase] [money] NOT NULL, 
         [ShipRate] [money] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Purchasing.Vendor
Companies from whom Adventure Works Cycles purchases parts or other goods. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	VendorID	int			Primary key for Vendor records.	
IX 	AccountNumber	AccountNumber			Vendor account (identification) number.	
	Name	Name			Company name.	
	CreditRating	tinyint			1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average	
	PreferredVendorStatus	Flag		((1))	0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.	
	ActiveFlag	Flag		((1))	0 = Vendor no longer used. 1 = Vendor is actively used.	
	PurchasingWebServiceURL	nvarchar  (1024)	Yes		Vendor URL.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Purchasing.ProductVendor	FK_ProductVendor_Vendor_VendorID	PK_Vendor_VendorID	
Purchasing.PurchaseOrderHeader	FK_PurchaseOrderHeader_Vendor_VendorID	PK_Vendor_VendorID	
Purchasing.VendorAddress	FK_VendorAddress_Vendor_VendorID	PK_Vendor_VendorID	
Purchasing.VendorContact	FK_VendorContact_Vendor_VendorID	PK_Vendor_VendorID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Vendor_AccountNumber	AccountNumber	ASC	Yes	NONCLUSTERED	
PK_Vendor_VendorID	VendorID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Vendor_CreditRating	CreditRating	([CreditRating]>=(1) AND [CreditRating]<=(5))	

Triggers: 

Trigger name: Purchasing.dVendor
Trigger type: INSTEAD OF DELETE
Trigger active: Yes
Trigger description: INSTEAD OF DELETE trigger which keeps Vendors from being deleted.
      Trigger definition:       

     CREATE TRIGGER [Purchasing].[dVendor] ON [Purchasing].[Vendor]  
     INSTEAD OF DELETE NOT FOR REPLICATION AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             DECLARE @DeleteCount int; 
      
             SELECT @DeleteCount = COUNT(*) FROM deleted; 
             IF @DeleteCount > 0  
             BEGIN 
                 RAISERROR 
                     (N'Vendors cannot be deleted. They can only be marked as not active.', -- Message 
                     10, -- Severity. 
                     1); -- State. 
      
             -- Rollback any active or uncommittable transactions 
                 IF @@TRANCOUNT > 0 
                 BEGIN 
                     ROLLBACK TRANSACTION; 
                 END 
             END; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

 Objects that depend on Purchasing.Vendor: 

Purchasing.vVendor 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Purchasing].[Vendor]( 
         [VendorID] [int] IDENTITY(1,1) NOT NULL, 
         [AccountNumber] [dbo].[AccountNumber] NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [CreditRating] [tinyint] NOT NULL, 
         [PreferredVendorStatus] [dbo].[Flag] NOT NULL, 
         [ActiveFlag] [dbo].[Flag] NOT NULL, 
         [PurchasingWebServiceURL] [nvarchar](1024) COLLATE Latin1_General_CS_AS NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Purchasing.VendorAddress
Cross-reference mapping vendors and addresses. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	VendorID	int			Primary key. Foreign key to Vendor.VendorID.	
    PK FK	AddressID	int			Primary key. Foreign key to Address.AddressID.	
FK 	AddressTypeID	int			Address type. Foreign key to AddressType.AddressTypeID.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_VendorAddress_Vendor_VendorID: VendorID relies upon remote PK_Vendor_VendorID    (Purchasing.Vendor)   
FK_VendorAddress_Address_AddressID: AddressID relies upon remote PK_Address_AddressID    (Person.Address)   
FK_VendorAddress_AddressType_AddressTypeID: AddressTypeID relies upon remote PK_AddressType_AddressTypeID    (Person.AddressType)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_VendorAddress_AddressID	AddressID	ASC		NONCLUSTERED	
PK_VendorAddress_VendorID_AddressID	VendorID	ASC	Yes	CLUSTERED	
PK_VendorAddress_VendorID_AddressID	AddressID	ASC	Yes	CLUSTERED	

 Objects that depend on Purchasing.VendorAddress: 

Purchasing.vVendor 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Purchasing].[VendorAddress]( 
         [VendorID] [int] NOT NULL, 
         [AddressID] [int] NOT NULL, 
         [AddressTypeID] [int] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Purchasing.VendorContact
Cross-reference table mapping vendors and their employees. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	VendorID	int			Primary key.	
    PK FK	ContactID	int			Contact (Vendor employee) identification number. Foreign key to Contact.ContactID.	
FK 	ContactTypeID	int			Contact type such as sales manager, or sales agent.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_VendorContact_Vendor_VendorID: VendorID relies upon remote PK_Vendor_VendorID    (Purchasing.Vendor)   
FK_VendorContact_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID    (Person.Contact)   
FK_VendorContact_ContactType_ContactTypeID: ContactTypeID relies upon remote PK_ContactType_ContactTypeID    (Person.ContactType)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_VendorContact_ContactID	ContactID	ASC		NONCLUSTERED	
IX_VendorContact_ContactTypeID	ContactTypeID	ASC		NONCLUSTERED	
PK_VendorContact_VendorID_ContactID	VendorID	ASC	Yes	CLUSTERED	
PK_VendorContact_VendorID_ContactID	ContactID	ASC	Yes	CLUSTERED	

 Objects that depend on Purchasing.VendorContact: 

Purchasing.vVendor 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Purchasing].[VendorContact]( 
         [VendorID] [int] NOT NULL, 
         [ContactID] [int] NOT NULL, 
         [ContactTypeID] [int] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.ContactCreditCard
Cross-reference table mapping customers in the Contact table to their credit card information in the CreditCard table.  

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	ContactID	int			Customer identification number. Foreign key to Contact.ContactID.	
    PK FK	CreditCardID	int			Credit card identification number. Foreign key to CreditCard.CreditCardID.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ContactCreditCard_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID    (Person.Contact)   
FK_ContactCreditCard_CreditCard_CreditCardID: CreditCardID relies upon remote PK_CreditCard_CreditCardID    (Sales.CreditCard)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_ContactCreditCard_ContactID_CreditCardID	ContactID	ASC	Yes	CLUSTERED	
PK_ContactCreditCard_ContactID_CreditCardID	CreditCardID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[ContactCreditCard]( 
         [ContactID] [int] NOT NULL, 
         [CreditCardID] [int] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.CountryRegionCurrency
Cross-reference table mapping ISO currency codes to a country or region. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	CountryRegionCode	nvarchar  (3)			ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode.	
    PK FK	CurrencyCode	nchar  (3)			ISO standard currency code. Foreign key to Currency.CurrencyCode.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_CountryRegionCurrency_CountryRegion_CountryRegionCode: CountryRegionCode relies upon remote PK_CountryRegion_CountryRegionCode    (Person.CountryRegion)   
FK_CountryRegionCurrency_Currency_CurrencyCode: CurrencyCode relies upon remote PK_Currency_CurrencyCode    (Sales.Currency)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_CountryRegionCurrency_CurrencyCode	CurrencyCode	ASC		NONCLUSTERED	
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode	CountryRegionCode	ASC	Yes	CLUSTERED	
PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode	CurrencyCode	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[CountryRegionCurrency]( 
         [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [CurrencyCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.CreditCard
Customer credit card information. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CreditCardID	int			Primary key for CreditCard records.	
	CardType	nvarchar  (50)			Credit card name.	
IX 	CardNumber	nvarchar  (25)			Credit card number.	
	ExpMonth	tinyint			Credit card expiration month.	
	ExpYear	smallint			Credit card expiration year.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderHeader	FK_SalesOrderHeader_CreditCard_CreditCardID	PK_CreditCard_CreditCardID	
Sales.ContactCreditCard	FK_ContactCreditCard_CreditCard_CreditCardID	PK_CreditCard_CreditCardID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_CreditCard_CardNumber	CardNumber	ASC	Yes	NONCLUSTERED	
PK_CreditCard_CreditCardID	CreditCardID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[CreditCard]( 
         [CreditCardID] [int] IDENTITY(1,1) NOT NULL, 
         [CardType] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL, 
         [CardNumber] [nvarchar](25) COLLATE Latin1_General_CS_AS NOT NULL, 
         [ExpMonth] [tinyint] NOT NULL, 
         [ExpYear] [smallint] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.Currency
Lookup table containing standard ISO currencies. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CurrencyCode	nchar  (3)			The ISO code for the Currency.	
IX 	Name	Name			Currency name.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.CountryRegionCurrency	FK_CountryRegionCurrency_Currency_CurrencyCode	PK_Currency_CurrencyCode	
Sales.CurrencyRate	FK_CurrencyRate_Currency_FromCurrencyCode	PK_Currency_CurrencyCode	
Sales.CurrencyRate	FK_CurrencyRate_Currency_ToCurrencyCode	PK_Currency_CurrencyCode	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Currency_Name	Name	ASC	Yes	NONCLUSTERED	
PK_Currency_CurrencyCode	CurrencyCode	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[Currency]( 
         [CurrencyCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.CurrencyRate
Currency exchange rates. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CurrencyRateID	int			Primary key for CurrencyRate records.	
IX 	CurrencyRateDate	datetime			Date and time the exchange rate was obtained.	
FK 	FromCurrencyCode	nchar  (3)			Exchange rate was converted from this currency code.	
FK 	ToCurrencyCode	nchar  (3)			Exchange rate was converted to this currency code.	
	AverageRate	money			Average exchange rate for the day.	
	EndOfDayRate	money			Final exchange rate for the day.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_CurrencyRate_Currency_FromCurrencyCode: FromCurrencyCode relies upon remote PK_Currency_CurrencyCode    (Sales.Currency)   
FK_CurrencyRate_Currency_ToCurrencyCode: ToCurrencyCode relies upon remote PK_Currency_CurrencyCode    (Sales.Currency)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderHeader	FK_SalesOrderHeader_CurrencyRate_CurrencyRateID	PK_CurrencyRate_CurrencyRateID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode	CurrencyRateDate	ASC	Yes	NONCLUSTERED	
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode	FromCurrencyCode	ASC	Yes	NONCLUSTERED	
AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode	ToCurrencyCode	ASC	Yes	NONCLUSTERED	
PK_CurrencyRate_CurrencyRateID	CurrencyRateID	ASC	Yes	CLUSTERED	

Table definition: 

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 Latin1_General_CS_AS NOT NULL, 
         [ToCurrencyCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [AverageRate] [money] NOT NULL, 
         [EndOfDayRate] [money] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.Customer
Current customer information. Also see the Individual and Store tables. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	CustomerID	int			Primary key for Customer records.	
FK 	TerritoryID	int	Yes		ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID.	
IX 	AccountNumber	varchar  (10)			Unique number identifying the customer assigned by the accounting system.	
	CustomerType	nchar  (1)			Customer type: I = Individual, S = Store	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_Customer_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID    (Sales.SalesTerritory)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.Store	FK_Store_Customer_CustomerID	PK_Customer_CustomerID	
Sales.Individual	FK_Individual_Customer_CustomerID	PK_Customer_CustomerID	
Sales.SalesOrderHeader	FK_SalesOrderHeader_Customer_CustomerID	PK_Customer_CustomerID	
Sales.CustomerAddress	FK_CustomerAddress_Customer_CustomerID	PK_Customer_CustomerID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Customer_AccountNumber	AccountNumber	ASC	Yes	NONCLUSTERED	
AK_Customer_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
IX_Customer_TerritoryID	TerritoryID	ASC		NONCLUSTERED	
PK_Customer_CustomerID	CustomerID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_Customer_CustomerType	CustomerType	(upper([CustomerType])='I' OR upper([CustomerType])='S')	

 Objects that depend on Sales.Customer: 

Sales.Customer 
Sales.vIndividualCustomer 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     SET ARITHABORT ON 
     CREATE TABLE [Sales].[Customer]( 
         [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 
         [TerritoryID] [int] NULL, 
         [AccountNumber]  AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')), 
         [CustomerType] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.CustomerAddress
Cross-reference table mapping customers to their address(es). 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	CustomerID	int			Primary key. Foreign key to Customer.CustomerID.	
    PK FK	AddressID	int			Primary key. Foreign key to Address.AddressID.	
FK 	AddressTypeID	int			Address type. Foreign key to AddressType.AddressTypeID.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_CustomerAddress_Customer_CustomerID: CustomerID relies upon remote PK_Customer_CustomerID    (Sales.Customer)   
FK_CustomerAddress_Address_AddressID: AddressID relies upon remote PK_Address_AddressID    (Person.Address)   
FK_CustomerAddress_AddressType_AddressTypeID: AddressTypeID relies upon remote PK_AddressType_AddressTypeID    (Person.AddressType)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_CustomerAddress_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_CustomerAddress_CustomerID_AddressID	CustomerID	ASC	Yes	CLUSTERED	
PK_CustomerAddress_CustomerID_AddressID	AddressID	ASC	Yes	CLUSTERED	

 Objects that depend on Sales.CustomerAddress: 

Sales.vIndividualCustomer 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[CustomerAddress]( 
         [CustomerID] [int] NOT NULL, 
         [AddressID] [int] NOT NULL, 
         [AddressTypeID] [int] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.Individual
Demographic data about customers that purchase Adventure Works products online. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	CustomerID	int			Unique customer identification number. Foreign key to Customer.CustomerID.	
FK 	ContactID	int			Identifies the customer in the Contact table. Foreign key to Contact.ContactID.	
IX 	Demographics	xml	Yes		Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_Individual_Customer_CustomerID: CustomerID relies upon remote PK_Customer_CustomerID    (Sales.Customer)   
FK_Individual_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID    (Person.Contact)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_Individual_CustomerID	CustomerID	ASC	Yes	CLUSTERED	
PXML_Individual_Demographics	Demographics	ASC		XML	
XMLPATH_Individual_Demographics	Demographics	ASC		XML	
XMLPROPERTY_Individual_Demographics	Demographics	ASC		XML	
XMLVALUE_Individual_Demographics	Demographics	ASC		XML	

Triggers: 

Trigger name: Sales.iuIndividual
Trigger type: INSERT
Trigger active: Yes
Trigger description: 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.
      Trigger definition:       

     CREATE TRIGGER [Sales].[iuIndividual] ON [Sales].[Individual]  
     AFTER INSERT, UPDATE NOT FOR REPLICATION AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         -- Only allow the Customer to be a Store OR Individual 
         IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store]  
             ON inserted.[CustomerID] = [Sales].[Store].[CustomerID])  
         BEGIN 
             -- Rollback any active or uncommittable transactions 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
         END; 
      
         IF UPDATE([CustomerID]) OR UPDATE([Demographics])  
         BEGIN 
             UPDATE [Sales].[Individual]  
             SET [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">  
                 <TotalPurchaseYTD>0.00</TotalPurchaseYTD>  
                 </IndividualSurvey>'  
             FROM inserted  
             WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]  
                 AND inserted.[Demographics] IS NULL; 
              
             UPDATE [Sales].[Individual]  
             SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                 insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD>  
                 as first  
                 into (/IndividualSurvey)[1]')  
             FROM inserted  
             WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]  
                 AND inserted.[Demographics] IS NOT NULL  
                 AND inserted.[Demographics].exist(N'declare default element namespace  
                     "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                     /IndividualSurvey/TotalPurchaseYTD') <> 1; 
         END; 
     END; 
      
     CREATE TRIGGER [Sales].[iuIndividual] ON [Sales].[Individual]  
     AFTER INSERT, UPDATE NOT FOR REPLICATION AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         -- Only allow the Customer to be a Store OR Individual 
         IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store]  
             ON inserted.[CustomerID] = [Sales].[Store].[CustomerID])  
         BEGIN 
             -- Rollback any active or uncommittable transactions 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
         END; 
      
         IF UPDATE([CustomerID]) OR UPDATE([Demographics])  
         BEGIN 
             UPDATE [Sales].[Individual]  
             SET [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">  
                 <TotalPurchaseYTD>0.00</TotalPurchaseYTD>  
                 </IndividualSurvey>'  
             FROM inserted  
             WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]  
                 AND inserted.[Demographics] IS NULL; 
              
             UPDATE [Sales].[Individual]  
             SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                 insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD>  
                 as first  
                 into (/IndividualSurvey)[1]')  
             FROM inserted  
             WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]  
                 AND inserted.[Demographics] IS NOT NULL  
                 AND inserted.[Demographics].exist(N'declare default element namespace  
                     "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                     /IndividualSurvey/TotalPurchaseYTD') <> 1; 
         END; 
     END; 

 Objects that depend on Sales.Individual: 

Sales.iduSalesOrderDetail 
Sales.iStore 
Sales.iuIndividual 
Sales.vIndividualCustomer 
Sales.vIndividualDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[Individual]( 
         [CustomerID] [int] NOT NULL, 
         [ContactID] [int] NOT NULL, 
         [Demographics] [xml](CONTENT [Sales].[IndividualSurveySchemaCollection]) NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesOrderDetail
Individual products associated with a specific sales order. See SalesOrderHeader. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	SalesOrderID	int			Primary key. Foreign key to SalesOrderHeader.SalesOrderID.	
    PK 	SalesOrderDetailID	int			Primary key. One incremental unique number per product sold.	
	CarrierTrackingNumber	nvarchar  (25)	Yes		Shipment tracking number supplied by the shipper.	
	OrderQty	smallint			Quantity ordered per product.	
FK 	ProductID	int			Product sold to customer. Foreign key to Product.ProductID.	
FK 	SpecialOfferID	int			Promotional code. Foreign key to SpecialOffer.SpecialOfferID.	
	UnitPrice	money			Selling price of a single product.	
	UnitPriceDiscount	money		((0.0))	Discount amount.	
	LineTotal	numeric  (38.6)			Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID: SalesOrderID relies upon remote PK_SalesOrderHeader_SalesOrderID    (Sales.SalesOrderHeader)   
FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID: ProductID relies upon remote PK_SpecialOfferProduct_SpecialOfferID_ProductID    (Sales.SpecialOfferProduct)   
FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID: SpecialOfferID relies upon remote PK_SpecialOfferProduct_SpecialOfferID_ProductID    (Sales.SpecialOfferProduct)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SalesOrderDetail_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
IX_SalesOrderDetail_ProductID	ProductID	ASC		NONCLUSTERED	
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID	SalesOrderID	ASC	Yes	CLUSTERED	
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID	SalesOrderDetailID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_SalesOrderDetail_OrderQty	OrderQty	([OrderQty]>(0))	
CK_SalesOrderDetail_UnitPrice	UnitPrice	([UnitPrice]>=(0.00))	
CK_SalesOrderDetail_UnitPriceDiscount	UnitPriceDiscount	([UnitPriceDiscount]>=(0.00))	

Triggers: 

Trigger name: Sales.iduSalesOrderDetail
Trigger type: INSERT
Trigger active: Yes
Trigger description: AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.
      Trigger definition:       

     CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail]  
     AFTER INSERT, DELETE, UPDATE AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             -- If inserting or updating these columns 
             IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])  
             -- Insert record into TransactionHistory 
             BEGIN 
                 INSERT INTO [Production].[TransactionHistory] 
                     ([ProductID] 
                     ,[ReferenceOrderID] 
                     ,[ReferenceOrderLineID] 
                     ,[TransactionType] 
                     ,[TransactionDate] 
                     ,[Quantity] 
                     ,[ActualCost]) 
                 SELECT  
                     inserted.[ProductID] 
                     ,inserted.[SalesOrderID] 
                     ,inserted.[SalesOrderDetailID] 
                     ,'S' 
                     ,GETDATE() 
                     ,inserted.[OrderQty] 
                     ,inserted.[UnitPrice] 
                 FROM inserted  
                     INNER JOIN [Sales].[SalesOrderHeader]  
                     ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]; 
      
                 UPDATE [Sales].[Individual]  
                 SET [Demographics].modify('declare default element namespace  
                     "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                     replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]  
                     with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')  
                 FROM inserted  
                     INNER JOIN [Sales].[SalesOrderHeader]  
                     ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]  
                 WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID]; 
             END; 
      
             -- Update SubTotal in SalesOrderHeader record. Note that this causes the  
             -- SalesOrderHeader trigger to fire which will update the RevisionNumber. 
             UPDATE [Sales].[SalesOrderHeader] 
             SET [Sales].[SalesOrderHeader].[SubTotal] =  
                 (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal]) 
                     FROM [Sales].[SalesOrderDetail] 
                     WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]) 
             WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted); 
      
             UPDATE [Sales].[Individual]  
             SET [Demographics].modify('declare default element namespace  
                 "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                 replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]  
                 with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")')  
             FROM deleted  
                 INNER JOIN [Sales].[SalesOrderHeader]  
                 ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]  
             WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID]; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 
      
     CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail]  
     AFTER INSERT, DELETE, UPDATE AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             -- If inserting or updating these columns 
             IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])  
             -- Insert record into TransactionHistory 
             BEGIN 
                 INSERT INTO [Production].[TransactionHistory] 
                     ([ProductID] 
                     ,[ReferenceOrderID] 
                     ,[ReferenceOrderLineID] 
                     ,[TransactionType] 
                     ,[TransactionDate] 
                     ,[Quantity] 
                     ,[ActualCost]) 
                 SELECT  
                     inserted.[ProductID] 
                     ,inserted.[SalesOrderID] 
                     ,inserted.[SalesOrderDetailID] 
                     ,'S' 
                     ,GETDATE() 
                     ,inserted.[OrderQty] 
                     ,inserted.[UnitPrice] 
                 FROM inserted  
                     INNER JOIN [Sales].[SalesOrderHeader]  
                     ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]; 
      
                 UPDATE [Sales].[Individual]  
                 SET [Demographics].modify('declare default element namespace  
                     "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                     replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]  
                     with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')  
                 FROM inserted  
                     INNER JOIN [Sales].[SalesOrderHeader]  
                     ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]  
                 WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID]; 
             END; 
      
             -- Update SubTotal in SalesOrderHeader record. Note that this causes the  
             -- SalesOrderHeader trigger to fire which will update the RevisionNumber. 
             UPDATE [Sales].[SalesOrderHeader] 
             SET [Sales].[SalesOrderHeader].[SubTotal] =  
                 (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal]) 
                     FROM [Sales].[SalesOrderDetail] 
                     WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]) 
             WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted); 
      
             UPDATE [Sales].[Individual]  
             SET [Demographics].modify('declare default element namespace  
                 "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                 replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]  
                 with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")')  
             FROM deleted  
                 INNER JOIN [Sales].[SalesOrderHeader]  
                 ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]  
             WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID]; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 
      
     CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail]  
     AFTER INSERT, DELETE, UPDATE AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             -- If inserting or updating these columns 
             IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])  
             -- Insert record into TransactionHistory 
             BEGIN 
                 INSERT INTO [Production].[TransactionHistory] 
                     ([ProductID] 
                     ,[ReferenceOrderID] 
                     ,[ReferenceOrderLineID] 
                     ,[TransactionType] 
                     ,[TransactionDate] 
                     ,[Quantity] 
                     ,[ActualCost]) 
                 SELECT  
                     inserted.[ProductID] 
                     ,inserted.[SalesOrderID] 
                     ,inserted.[SalesOrderDetailID] 
                     ,'S' 
                     ,GETDATE() 
                     ,inserted.[OrderQty] 
                     ,inserted.[UnitPrice] 
                 FROM inserted  
                     INNER JOIN [Sales].[SalesOrderHeader]  
                     ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]; 
      
                 UPDATE [Sales].[Individual]  
                 SET [Demographics].modify('declare default element namespace  
                     "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                     replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]  
                     with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')  
                 FROM inserted  
                     INNER JOIN [Sales].[SalesOrderHeader]  
                     ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]  
                 WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID]; 
             END; 
      
             -- Update SubTotal in SalesOrderHeader record. Note that this causes the  
             -- SalesOrderHeader trigger to fire which will update the RevisionNumber. 
             UPDATE [Sales].[SalesOrderHeader] 
             SET [Sales].[SalesOrderHeader].[SubTotal] =  
                 (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal]) 
                     FROM [Sales].[SalesOrderDetail] 
                     WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID]) 
             WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted); 
      
             UPDATE [Sales].[Individual]  
             SET [Demographics].modify('declare default element namespace  
                 "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
                 replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]  
                 with data(/IndividualSurvey/TotalPurchaseYTD)[1] - sql:column("deleted.LineTotal")')  
             FROM deleted  
                 INNER JOIN [Sales].[SalesOrderHeader]  
                 ON deleted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]  
             WHERE [Sales].[SalesOrderHeader].[CustomerID] = [Sales].[Individual].[CustomerID]; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

 Objects that depend on Sales.SalesOrderDetail: 

Sales.iduSalesOrderDetail 
Sales.SalesOrderDetail 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SalesOrderDetail]( 
         [SalesOrderID] [int] NOT NULL, 
         [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL, 
         [CarrierTrackingNumber] [nvarchar](25) COLLATE Latin1_General_CS_AS NULL, 
         [OrderQty] [smallint] NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [SpecialOfferID] [int] NOT NULL, 
         [UnitPrice] [money] NOT NULL, 
         [UnitPriceDiscount] [money] NOT NULL, 
         [LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))), 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesOrderHeader
General sales order information. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	SalesOrderID	int			Primary key.	
	RevisionNumber	tinyint		((0))	Incremental number to track changes to the sales order over time.	
	OrderDate	datetime		(getdate())	Dates the sales order was created.	
	DueDate	datetime			Date the order is due to the customer.	
	ShipDate	datetime	Yes		Date the order was shipped to the customer.	
	Status	tinyint		((1))	Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled	
	OnlineOrderFlag	Flag		((1))	0 = Order placed by sales person. 1 = Order placed online by customer.	
IX 	SalesOrderNumber	nvarchar  (25)			Unique sales order identification number.	
	PurchaseOrderNumber	OrderNumber	Yes		Customer purchase order number reference. 	
	AccountNumber	AccountNumber	Yes		Financial accounting number reference.	
FK 	CustomerID	int			Customer identification number. Foreign key to Customer.CustomerID.	
FK 	ContactID	int			Customer contact identification number. Foreign key to Contact.ContactID.	
FK 	SalesPersonID	int	Yes		Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.	
FK 	TerritoryID	int	Yes		Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.	
FK 	BillToAddressID	int			Customer billing address. Foreign key to Address.AddressID.	
FK 	ShipToAddressID	int			Customer shipping address. Foreign key to Address.AddressID.	
FK 	ShipMethodID	int			Shipping method. Foreign key to ShipMethod.ShipMethodID.	
FK 	CreditCardID	int	Yes		Credit card identification number. Foreign key to CreditCard.CreditCardID.	
	CreditCardApprovalCode	varchar  (15)	Yes		Approval code provided by the credit card company.	
FK 	CurrencyRateID	int	Yes		Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.	
	SubTotal	money		((0.00))	Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.	
	TaxAmt	money		((0.00))	Tax amount.	
	Freight	money		((0.00))	Shipping cost.	
	TotalDue	money			Total due from customer. Computed as Subtotal + TaxAmt + Freight.	
	Comment	nvarchar  (128)	Yes		Sales representative comments.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_SalesOrderHeader_Customer_CustomerID: CustomerID relies upon remote PK_Customer_CustomerID    (Sales.Customer)   
FK_SalesOrderHeader_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID    (Person.Contact)   
FK_SalesOrderHeader_SalesPerson_SalesPersonID: SalesPersonID relies upon remote PK_SalesPerson_SalesPersonID    (Sales.SalesPerson)   
FK_SalesOrderHeader_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID    (Sales.SalesTerritory)   
FK_SalesOrderHeader_Address_BillToAddressID: BillToAddressID relies upon remote PK_Address_AddressID    (Person.Address)   
FK_SalesOrderHeader_Address_ShipToAddressID: ShipToAddressID relies upon remote PK_Address_AddressID    (Person.Address)   
FK_SalesOrderHeader_ShipMethod_ShipMethodID: ShipMethodID relies upon remote PK_ShipMethod_ShipMethodID    (Purchasing.ShipMethod)   
FK_SalesOrderHeader_CreditCard_CreditCardID: CreditCardID relies upon remote PK_CreditCard_CreditCardID    (Sales.CreditCard)   
FK_SalesOrderHeader_CurrencyRate_CurrencyRateID: CurrencyRateID relies upon remote PK_CurrencyRate_CurrencyRateID    (Sales.CurrencyRate)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderDetail	FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID	PK_SalesOrderHeader_SalesOrderID	
Sales.SalesOrderHeaderSalesReason	FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID	PK_SalesOrderHeader_SalesOrderID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SalesOrderHeader_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
AK_SalesOrderHeader_SalesOrderNumber	SalesOrderNumber	ASC	Yes	NONCLUSTERED	
IX_SalesOrderHeader_CustomerID	CustomerID	ASC		NONCLUSTERED	
IX_SalesOrderHeader_SalesPersonID	SalesPersonID	ASC		NONCLUSTERED	
PK_SalesOrderHeader_SalesOrderID	SalesOrderID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_SalesOrderHeader_Status	Status	([Status]>=(0) AND [Status]<=(8))	
CK_SalesOrderHeader_DueDate	OrderDate	([DueDate]>=[OrderDate])	
CK_SalesOrderHeader_DueDate	DueDate	([DueDate]>=[OrderDate])	
CK_SalesOrderHeader_ShipDate	OrderDate	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)	
CK_SalesOrderHeader_ShipDate	ShipDate	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)	
CK_SalesOrderHeader_SubTotal	SubTotal	([SubTotal]>=(0.00))	
CK_SalesOrderHeader_TaxAmt	TaxAmt	([TaxAmt]>=(0.00))	
CK_SalesOrderHeader_Freight	Freight	([Freight]>=(0.00))	

Triggers: 

Trigger name: Sales.uSalesOrderHeader
Trigger type: UPDATE
Trigger active: Yes
Trigger description: AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.
      Trigger definition:       

     CREATE TRIGGER [Sales].[uSalesOrderHeader] ON [Sales].[SalesOrderHeader]  
     AFTER UPDATE NOT FOR REPLICATION AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             -- Update RevisionNumber for modification of any field EXCEPT the Status. 
             IF NOT UPDATE([Status]) 
             BEGIN 
                 UPDATE [Sales].[SalesOrderHeader] 
                 SET [Sales].[SalesOrderHeader].[RevisionNumber] =  
                     [Sales].[SalesOrderHeader].[RevisionNumber] + 1 
                 WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN  
                     (SELECT inserted.[SalesOrderID] FROM inserted); 
             END; 
      
             -- Update the SalesPerson SalesYTD when SubTotal is updated 
             IF UPDATE([SubTotal]) 
             BEGIN 
                 DECLARE @StartDate datetime, 
                         @EndDate datetime 
      
                 SET @StartDate = [dbo].[ufnGetAccountingStartDate](); 
                 SET @EndDate = [dbo].[ufnGetAccountingEndDate](); 
      
                 UPDATE [Sales].[SalesPerson] 
                 SET [Sales].[SalesPerson].[SalesYTD] =  
                     (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal]) 
                     FROM [Sales].[SalesOrderHeader]  
                     WHERE [Sales].[SalesPerson].[SalesPersonID] = [Sales].[SalesOrderHeader].[SalesPersonID] 
                         AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped 
                         AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate) 
                 WHERE [Sales].[SalesPerson].[SalesPersonID]  
                     IN (SELECT DISTINCT inserted.[SalesPersonID] FROM inserted  
                         WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate); 
      
                 -- Update the SalesTerritory SalesYTD when SubTotal is updated 
                 UPDATE [Sales].[SalesTerritory] 
                 SET [Sales].[SalesTerritory].[SalesYTD] =  
                     (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal]) 
                     FROM [Sales].[SalesOrderHeader]  
                     WHERE [Sales].[SalesTerritory].[TerritoryID] = [Sales].[SalesOrderHeader].[TerritoryID] 
                         AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped 
                         AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate) 
                 WHERE [Sales].[SalesTerritory].[TerritoryID]  
                     IN (SELECT DISTINCT inserted.[TerritoryID] FROM inserted  
                         WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate); 
             END; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

 Objects that depend on Sales.SalesOrderHeader: 

Sales.iduSalesOrderDetail 
Sales.SalesOrderHeader 
Sales.uSalesOrderHeader 
Sales.vSalesPersonSalesByFiscalYears 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     SET ARITHABORT ON 
     CREATE TABLE [Sales].[SalesOrderHeader]( 
         [SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 
         [RevisionNumber] [tinyint] NOT NULL, 
         [OrderDate] [datetime] NOT NULL, 
         [DueDate] [datetime] NOT NULL, 
         [ShipDate] [datetime] NULL, 
         [Status] [tinyint] NOT NULL, 
         [OnlineOrderFlag] [dbo].[Flag] NOT NULL, 
         [SalesOrderNumber]  AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***')), 
         [PurchaseOrderNumber] [dbo].[OrderNumber] NULL, 
         [AccountNumber] [dbo].[AccountNumber] NULL, 
         [CustomerID] [int] NOT NULL, 
         [ContactID] [int] NOT NULL, 
         [SalesPersonID] [int] NULL, 
         [TerritoryID] [int] NULL, 
         [BillToAddressID] [int] NOT NULL, 
         [ShipToAddressID] [int] NOT NULL, 
         [ShipMethodID] [int] NOT NULL, 
         [CreditCardID] [int] NULL, 
         [CreditCardApprovalCode] [varchar](15) COLLATE Latin1_General_CS_AS NULL, 
         [CurrencyRateID] [int] NULL, 
         [SubTotal] [money] NOT NULL, 
         [TaxAmt] [money] NOT NULL, 
         [Freight] [money] NOT NULL, 
         [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))), 
         [Comment] [nvarchar](128) COLLATE Latin1_General_CS_AS NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesOrderHeaderSalesReason
Cross-reference table mapping sales orders to sales reason codes. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	SalesOrderID	int			Primary key. Foreign key to SalesOrderHeader.SalesOrderID.	
    PK FK	SalesReasonID	int			Primary key. Foreign key to SalesReason.SalesReasonID.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID: SalesOrderID relies upon remote PK_SalesOrderHeader_SalesOrderID    (Sales.SalesOrderHeader)   
FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID: SalesReasonID relies upon remote PK_SalesReason_SalesReasonID    (Sales.SalesReason)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID	SalesOrderID	ASC	Yes	CLUSTERED	
PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID	SalesReasonID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SalesOrderHeaderSalesReason]( 
         [SalesOrderID] [int] NOT NULL, 
         [SalesReasonID] [int] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesPerson
Sales representative current information. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	SalesPersonID	int			Primary key for SalesPerson records.	
FK 	TerritoryID	int	Yes		Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID.	
	SalesQuota	money	Yes		Projected yearly sales.	
	Bonus	money		((0.00))	Bonus due if quota is met.	
	CommissionPct	smallmoney		((0.00))	Commision percent received per sale.	
	SalesYTD	money		((0.00))	Sales total year to date.	
	SalesLastYear	money		((0.00))	Sales total of previous year.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_SalesPerson_Employee_SalesPersonID: SalesPersonID relies upon remote PK_Employee_EmployeeID    (HumanResources.Employee)   
FK_SalesPerson_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID    (Sales.SalesTerritory)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderHeader	FK_SalesOrderHeader_SalesPerson_SalesPersonID	PK_SalesPerson_SalesPersonID	
Sales.SalesTerritoryHistory	FK_SalesTerritoryHistory_SalesPerson_SalesPersonID	PK_SalesPerson_SalesPersonID	
Sales.Store	FK_Store_SalesPerson_SalesPersonID	PK_SalesPerson_SalesPersonID	
Sales.SalesPersonQuotaHistory	FK_SalesPersonQuotaHistory_SalesPerson_SalesPersonID	PK_SalesPerson_SalesPersonID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SalesPerson_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_SalesPerson_SalesPersonID	SalesPersonID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_SalesPerson_SalesQuota	SalesQuota	([SalesQuota]>(0.00))	
CK_SalesPerson_Bonus	Bonus	([Bonus]>=(0.00))	
CK_SalesPerson_CommissionPct	CommissionPct	([CommissionPct]>=(0.00))	
CK_SalesPerson_SalesYTD	SalesYTD	([SalesYTD]>=(0.00))	
CK_SalesPerson_SalesLastYear	SalesLastYear	([SalesLastYear]>=(0.00))	

 Objects that depend on Sales.SalesPerson: 

Sales.uSalesOrderHeader 
Sales.vSalesPerson 
Sales.vSalesPersonSalesByFiscalYears 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SalesPerson]( 
         [SalesPersonID] [int] NOT NULL, 
         [TerritoryID] [int] NULL, 
         [SalesQuota] [money] NULL, 
         [Bonus] [money] NOT NULL, 
         [CommissionPct] [smallmoney] NOT NULL, 
         [SalesYTD] [money] NOT NULL, 
         [SalesLastYear] [money] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesPersonQuotaHistory
Sales performance tracking. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	SalesPersonID	int			Sales person identification number. Foreign key to SalesPerson.SalesPersonID.	
    PK 	QuotaDate	datetime			Sales quota date.	
	SalesQuota	money			Sales quota amount.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_SalesPersonQuotaHistory_SalesPerson_SalesPersonID: SalesPersonID relies upon remote PK_SalesPerson_SalesPersonID    (Sales.SalesPerson)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SalesPersonQuotaHistory_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate	SalesPersonID	ASC	Yes	CLUSTERED	
PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate	QuotaDate	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_SalesPersonQuotaHistory_SalesQuota	SalesQuota	([SalesQuota]>(0.00))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SalesPersonQuotaHistory]( 
         [SalesPersonID] [int] NOT NULL, 
         [QuotaDate] [datetime] NOT NULL, 
         [SalesQuota] [money] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesReason
Lookup table of customer purchase reasons. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	SalesReasonID	int			Primary key for SalesReason records.	
	Name	Name			Sales reason description.	
	ReasonType	Name			Category the sales reason belongs to.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderHeaderSalesReason	FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID	PK_SalesReason_SalesReasonID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
PK_SalesReason_SalesReasonID	SalesReasonID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SalesReason]( 
         [SalesReasonID] [int] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [ReasonType] [dbo].[Name] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesTaxRate
Tax rate lookup table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	SalesTaxRateID	int			Primary key for SalesTaxRate records.	
FK 	StateProvinceID	int			State, province, or country/region the sales tax applies to.	
IX 	TaxType	tinyint			1 = Tax applied to retail transactions, 2 = Tax applied to wholesale transactions, 3 = Tax applied to all sales (retail and wholesale) transactions.	
	TaxRate	smallmoney		((0.00))	Tax rate amount.	
	Name	Name			Tax rate description.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_SalesTaxRate_StateProvince_StateProvinceID: StateProvinceID relies upon remote PK_StateProvince_StateProvinceID    (Person.StateProvince)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SalesTaxRate_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
AK_SalesTaxRate_StateProvinceID_TaxType	StateProvinceID	ASC	Yes	NONCLUSTERED	
AK_SalesTaxRate_StateProvinceID_TaxType	TaxType	ASC	Yes	NONCLUSTERED	
PK_SalesTaxRate_SalesTaxRateID	SalesTaxRateID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_SalesTaxRate_TaxType	TaxType	([TaxType]>=(1) AND [TaxType]<=(3))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SalesTaxRate]( 
         [SalesTaxRateID] [int] IDENTITY(1,1) NOT NULL, 
         [StateProvinceID] [int] NOT NULL, 
         [TaxType] [tinyint] NOT NULL, 
         [TaxRate] [smallmoney] NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesTerritory
Sales territory lookup table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	TerritoryID	int			Primary key for SalesTerritory records.	
IX 	Name	Name			Sales territory description	
	CountryRegionCode	nvarchar  (3)			ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. 	
	Group	nvarchar  (50)			Geographic area to which the sales territory belong.	
	SalesYTD	money		((0.00))	Sales in the territory year to date.	
	SalesLastYear	money		((0.00))	Sales in the territory the previous year.	
	CostYTD	money		((0.00))	Business costs in the territory year to date.	
	CostLastYear	money		((0.00))	Business costs in the territory the previous year.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderHeader	FK_SalesOrderHeader_SalesTerritory_TerritoryID	PK_SalesTerritory_TerritoryID	
Sales.SalesPerson	FK_SalesPerson_SalesTerritory_TerritoryID	PK_SalesTerritory_TerritoryID	
Sales.SalesTerritoryHistory	FK_SalesTerritoryHistory_SalesTerritory_TerritoryID	PK_SalesTerritory_TerritoryID	
Sales.Customer	FK_Customer_SalesTerritory_TerritoryID	PK_SalesTerritory_TerritoryID	
Person.StateProvince	FK_StateProvince_SalesTerritory_TerritoryID	PK_SalesTerritory_TerritoryID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SalesTerritory_Name	Name	ASC	Yes	NONCLUSTERED	
AK_SalesTerritory_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_SalesTerritory_TerritoryID	TerritoryID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_SalesTerritory_SalesYTD	SalesYTD	([SalesYTD]>=(0.00))	
CK_SalesTerritory_SalesLastYear	SalesLastYear	([SalesLastYear]>=(0.00))	
CK_SalesTerritory_CostYTD	CostYTD	([CostYTD]>=(0.00))	
CK_SalesTerritory_CostLastYear	CostLastYear	([CostLastYear]>=(0.00))	

 Objects that depend on Sales.SalesTerritory: 

Sales.uSalesOrderHeader 
Sales.vSalesPerson 
Sales.vSalesPersonSalesByFiscalYears 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SalesTerritory]( 
         [TerritoryID] [int] IDENTITY(1,1) NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Group] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL, 
         [SalesYTD] [money] NOT NULL, 
         [SalesLastYear] [money] NOT NULL, 
         [CostYTD] [money] NOT NULL, 
         [CostLastYear] [money] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SalesTerritoryHistory
Sales representative transfers to other sales territories. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	SalesPersonID	int			Primary key for SalesTerritoryHistory records.	
    PK FK	TerritoryID	int			Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID.	
    PK 	StartDate	datetime			Date the sales representive started work in the territory.	
	EndDate	datetime	Yes		Date the sales representative left work in the territory.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_SalesTerritoryHistory_SalesPerson_SalesPersonID: SalesPersonID relies upon remote PK_SalesPerson_SalesPersonID    (Sales.SalesPerson)   
FK_SalesTerritoryHistory_SalesTerritory_TerritoryID: TerritoryID relies upon remote PK_SalesTerritory_TerritoryID    (Sales.SalesTerritory)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SalesTerritoryHistory_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID	SalesPersonID	ASC	Yes	CLUSTERED	
PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID	TerritoryID	ASC	Yes	CLUSTERED	
PK_SalesTerritoryHistory_SalesPersonID_StartDate_TerritoryID	StartDate	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_SalesTerritoryHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	
CK_SalesTerritoryHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SalesTerritoryHistory]( 
         [SalesPersonID] [int] NOT NULL, 
         [TerritoryID] [int] NOT NULL, 
         [StartDate] [datetime] NOT NULL, 
         [EndDate] [datetime] NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.ShoppingCartItem
Contains online customer orders until the order is submitted or cancelled. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	ShoppingCartItemID	int			Primary key for ShoppingCartItem records.	
IX 	ShoppingCartID	nvarchar  (50)			Shopping cart identification number.	
	Quantity	int		((1))	Product quantity ordered.	
FK 	ProductID	int			Product ordered. Foreign key to Product.ProductID.	
	DateCreated	datetime		(getdate())	Date the time the record was created.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_ShoppingCartItem_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
IX_ShoppingCartItem_ShoppingCartID_ProductID	ShoppingCartID	ASC		NONCLUSTERED	
IX_ShoppingCartItem_ShoppingCartID_ProductID	ProductID	ASC		NONCLUSTERED	
PK_ShoppingCartItem_ShoppingCartItemID	ShoppingCartItemID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_ShoppingCartItem_Quantity	Quantity	([Quantity]>=(1))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[ShoppingCartItem]( 
         [ShoppingCartItemID] [int] IDENTITY(1,1) NOT NULL, 
         [ShoppingCartID] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Quantity] [int] NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [DateCreated] [datetime] NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SpecialOffer
Sale discounts lookup table. 

 	Field name	Data type	Nullable	Default	Comments	
    PK 	SpecialOfferID	int			Primary key for SpecialOffer records.	
	Description	nvarchar  (255)			Discount description.	
	DiscountPct	smallmoney		((0.00))	Discount precentage.	
	Type	nvarchar  (50)			Discount type category.	
	Category	nvarchar  (50)			Group the discount applies to such as Reseller or Customer.	
	StartDate	datetime			Discount start date.	
	EndDate	datetime			Discount end date.	
	MinQty	int		((0))	Minimum discount percent allowed.	
	MaxQty	int	Yes		Maximum discount percent allowed.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SpecialOfferProduct	FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID	PK_SpecialOffer_SpecialOfferID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SpecialOffer_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
PK_SpecialOffer_SpecialOfferID	SpecialOfferID	ASC	Yes	CLUSTERED	

Check constraints: 

Check name	Column name	Expression	
CK_SpecialOffer_EndDate	StartDate	([EndDate]>=[StartDate])	
CK_SpecialOffer_EndDate	EndDate	([EndDate]>=[StartDate])	
CK_SpecialOffer_DiscountPct	DiscountPct	([DiscountPct]>=(0.00))	
CK_SpecialOffer_MinQty	MinQty	([MinQty]>=(0))	
CK_SpecialOffer_MaxQty	MaxQty	([MaxQty]>=(0))	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SpecialOffer]( 
         [SpecialOfferID] [int] IDENTITY(1,1) NOT NULL, 
         [Description] [nvarchar](255) COLLATE Latin1_General_CS_AS NOT NULL, 
         [DiscountPct] [smallmoney] NOT NULL, 
         [Type] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL, 
         [Category] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL, 
         [StartDate] [datetime] NOT NULL, 
         [EndDate] [datetime] NOT NULL, 
         [MinQty] [int] NOT NULL, 
         [MaxQty] [int] NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.SpecialOfferProduct
Cross-reference table mapping products to special offer discounts. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	SpecialOfferID	int			Primary key for SpecialOfferProduct records.	
    PK FK	ProductID	int			Product identification number. Foreign key to Product.ProductID.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID: SpecialOfferID relies upon remote PK_SpecialOffer_SpecialOfferID    (Sales.SpecialOffer)   
FK_SpecialOfferProduct_Product_ProductID: ProductID relies upon remote PK_Product_ProductID    (Production.Product)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.SalesOrderDetail	FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID	PK_SpecialOfferProduct_SpecialOfferID_ProductID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_SpecialOfferProduct_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
IX_SpecialOfferProduct_ProductID	ProductID	ASC		NONCLUSTERED	
PK_SpecialOfferProduct_SpecialOfferID_ProductID	SpecialOfferID	ASC	Yes	CLUSTERED	
PK_SpecialOfferProduct_SpecialOfferID_ProductID	ProductID	ASC	Yes	CLUSTERED	

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[SpecialOfferProduct]( 
         [SpecialOfferID] [int] NOT NULL, 
         [ProductID] [int] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.Store
Customers (resellers) of Adventure Works products. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	CustomerID	int			Primary key. Foreign key to Customer.CustomerID.	
	Name	Name			Name of the store.	
FK 	SalesPersonID	int	Yes		ID of the sales person assigned to the customer. Foreign key to SalesPerson.SalesPersonID.	
IX 	Demographics	xml	Yes		Demographic informationg about the store such as the number of employees, annual sales and store type.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_Store_Customer_CustomerID: CustomerID relies upon remote PK_Customer_CustomerID    (Sales.Customer)   
FK_Store_SalesPerson_SalesPersonID: SalesPersonID relies upon remote PK_SalesPerson_SalesPersonID    (Sales.SalesPerson)   

Referencing tables: 

Table name	Foreign key	Primary key or unique constraint	
Sales.StoreContact	FK_StoreContact_Store_CustomerID	PK_Store_CustomerID	

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_Store_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
IX_Store_SalesPersonID	SalesPersonID	ASC		NONCLUSTERED	
PK_Store_CustomerID	CustomerID	ASC	Yes	CLUSTERED	
PXML_Store_Demographics	Demographics	ASC		XML	

Triggers: 

Trigger name: Sales.iStore
Trigger type: INSERT
Trigger active: Yes
Trigger description: AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table.
      Trigger definition:       

     CREATE TRIGGER [Sales].[iStore] ON [Sales].[Store]  
     AFTER INSERT AS  
     BEGIN 
         DECLARE @Count int; 
      
         SET @Count = @@ROWCOUNT; 
         IF @Count = 0  
             RETURN; 
      
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             -- Only allow the Customer to be a Store OR Individual 
             IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Individual]  
                 ON inserted.[CustomerID] = [Sales].[Individual].[CustomerID])  
             BEGIN 
                 -- Rollback any active or uncommittable transactions 
                 IF @@TRANCOUNT > 0 
                 BEGIN 
                     ROLLBACK TRANSACTION; 
                 END 
             END; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspPrintError]; 
      
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

 Objects that depend on Sales.Store: 

Sales.iuIndividual 
Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[Store]( 
         [CustomerID] [int] NOT NULL, 
         [Name] [dbo].[Name] NOT NULL, 
         [SalesPersonID] [int] NULL, 
         [Demographics] [xml](CONTENT [Sales].[StoreSurveySchemaCollection]) NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 
      
     Table: Sales.StoreContact
Cross-reference table mapping stores and their employees. 

 	Field name	Data type	Nullable	Default	Comments	
    PK FK	CustomerID	int			Store identification number. Foreign key to Customer.CustomerID.	
    PK FK	ContactID	int			Contact (store employee) identification number. Foreign key to Contact.ContactID.	
FK 	ContactTypeID	int			Contact type such as owner or purchasing agent. Foreign key to ContactType.ContactTypeID.	
IX 	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.	

Foreign keys dependencies: 

FK_StoreContact_Store_CustomerID: CustomerID relies upon remote PK_Store_CustomerID    (Sales.Store)   
FK_StoreContact_Contact_ContactID: ContactID relies upon remote PK_Contact_ContactID    (Person.Contact)   
FK_StoreContact_ContactType_ContactTypeID: ContactTypeID relies upon remote PK_ContactType_ContactTypeID    (Person.ContactType)   

Indexes: 

Index name	Column name	Direction	Unique	Index type	
AK_StoreContact_rowguid	rowguid	ASC	Yes	NONCLUSTERED	
IX_StoreContact_ContactID	ContactID	ASC		NONCLUSTERED	
IX_StoreContact_ContactTypeID	ContactTypeID	ASC		NONCLUSTERED	
PK_StoreContact_CustomerID_ContactID	CustomerID	ASC	Yes	CLUSTERED	
PK_StoreContact_CustomerID_ContactID	ContactID	ASC	Yes	CLUSTERED	

 Objects that depend on Sales.StoreContact: 

Sales.vStoreWithDemographics 

Table definition: 

SET ANSI_NULLS ON 
     SET QUOTED_IDENTIFIER ON 
     CREATE TABLE [Sales].[StoreContact]( 
         [CustomerID] [int] NOT NULL, 
         [ContactID] [int] NOT NULL, 
         [ContactTypeID] [int] NOT NULL, 
         [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, 
         [ModifiedDate] [datetime] NOT NULL 
     ) ON [PRIMARY] 

View: HumanResources.vEmployee  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Employee names and addresses. 

 	Source	Field name	Data type	Nullable	Comments	
	HumanResources.Employee	EmployeeID	int			
	Person.Contact	Title	nvarchar  (8)	Yes		
	Person.Contact	FirstName	Name			
	Person.Contact	MiddleName	Name	Yes		
	Person.Contact	LastName	Name			
	Person.Contact	Suffix	nvarchar  (10)	Yes		
		JobTitle	nvarchar  (50)			
	Person.Contact	Phone	Phone	Yes		
	Person.Contact	EmailAddress	nvarchar  (50)	Yes		
	Person.Contact	EmailPromotion	int			
	Person.Address	AddressLine1	nvarchar  (60)			
	Person.Address	AddressLine2	nvarchar  (60)	Yes		
	Person.Address	City	nvarchar  (30)			
		StateProvinceName	Name			
	Person.Address	PostalCode	nvarchar  (15)			
		CountryRegionName	Name			
	Person.Contact	AdditionalContactInfo	xml	Yes		

 
     CREATE VIEW [HumanResources].[vEmployee]  
     AS  
     SELECT  
         e.[EmployeeID] 
         ,c.[Title] 
         ,c.[FirstName] 
         ,c.[MiddleName] 
         ,c.[LastName] 
         ,c.[Suffix] 
         ,e.[Title] AS [JobTitle]  
         ,c.[Phone] 
         ,c.[EmailAddress] 
         ,c.[EmailPromotion] 
         ,a.[AddressLine1] 
         ,a.[AddressLine2] 
         ,a.[City] 
         ,sp.[Name] AS [StateProvinceName]  
         ,a.[PostalCode] 
         ,cr.[Name] AS [CountryRegionName]  
         ,c.[AdditionalContactInfo] 
     FROM [HumanResources].[Employee] e 
         INNER JOIN [Person].[Contact] c  
         ON c.[ContactID] = e.[ContactID] 
         INNER JOIN [HumanResources].[EmployeeAddress] ea  
         ON e.[EmployeeID] = ea.[EmployeeID]  
         INNER JOIN [Person].[Address] a  
         ON ea.[AddressID] = a.[AddressID] 
         INNER JOIN [Person].[StateProvince] sp  
         ON sp.[StateProvinceID] = a.[StateProvinceID] 
         INNER JOIN [Person].[CountryRegion] cr  
         ON cr.[CountryRegionCode] = sp.[CountryRegionCode]; 

Related objects:  Person.Address Person.StateProvince HumanResources.Employee HumanResources.EmployeeAddress Person.Contact Person.CountryRegion 

View: HumanResources.vEmployeeDepartment  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Returns employee name, title, and current department. 

 	Source	Field name	Data type	Nullable	Comments	
	HumanResources.Employee	EmployeeID	int			
	Person.Contact	Title	nvarchar  (8)	Yes		
	Person.Contact	FirstName	Name			
	Person.Contact	MiddleName	Name	Yes		
	Person.Contact	LastName	Name			
	Person.Contact	Suffix	nvarchar  (10)	Yes		
		JobTitle	nvarchar  (50)			
		Department	Name			
	HumanResources.Department	GroupName	Name			
	HumanResources.EmployeeDepartmentHistory	StartDate	datetime			

 
     CREATE VIEW [HumanResources].[vEmployeeDepartment]  
     AS  
     SELECT  
         e.[EmployeeID]  
         ,c.[Title]  
         ,c.[FirstName]  
         ,c.[MiddleName]  
         ,c.[LastName]  
         ,c.[Suffix]  
         ,e.[Title] AS [JobTitle]  
         ,d.[Name] AS [Department]  
         ,d.[GroupName]  
         ,edh.[StartDate]  
     FROM [HumanResources].[Employee] e 
         INNER JOIN [Person].[Contact] c  
         ON c.[ContactID] = e.[ContactID] 
         INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh  
         ON e.[EmployeeID] = edh.[EmployeeID]  
         INNER JOIN [HumanResources].[Department] d  
         ON edh.[DepartmentID] = d.[DepartmentID]  
     WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE()); 

Related objects:  Person.Contact HumanResources.EmployeeDepartmentHistory HumanResources.Department HumanResources.Employee 

View: HumanResources.vEmployeeDepartmentHistory  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Returns employee name and current and previous departments. 

 	Source	Field name	Data type	Nullable	Comments	
	HumanResources.Employee	EmployeeID	int			
	Person.Contact	Title	nvarchar  (8)	Yes		
	Person.Contact	FirstName	Name			
	Person.Contact	MiddleName	Name	Yes		
	Person.Contact	LastName	Name			
	Person.Contact	Suffix	nvarchar  (10)	Yes		
		Shift	Name			
		Department	Name			
	HumanResources.Department	GroupName	Name			
	HumanResources.EmployeeDepartmentHistory	StartDate	datetime			
	HumanResources.EmployeeDepartmentHistory	EndDate	datetime	Yes		

 
     CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory]  
     AS  
     SELECT  
         e.[EmployeeID]  
         ,c.[Title]  
         ,c.[FirstName]  
         ,c.[MiddleName]  
         ,c.[LastName]  
         ,c.[Suffix]  
         ,s.[Name] AS [Shift] 
         ,d.[Name] AS [Department]  
         ,d.[GroupName]  
         ,edh.[StartDate]  
         ,edh.[EndDate] 
     FROM [HumanResources].[Employee] e 
         INNER JOIN [Person].[Contact] c  
         ON c.[ContactID] = e.[ContactID] 
         INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh  
         ON e.[EmployeeID] = edh.[EmployeeID]  
         INNER JOIN [HumanResources].[Department] d  
         ON edh.[DepartmentID] = d.[DepartmentID]  
         INNER JOIN [HumanResources].[Shift] s 
         ON s.[ShiftID] = edh.[ShiftID]; 

Related objects:  Person.Contact HumanResources.EmployeeDepartmentHistory HumanResources.Shift HumanResources.Department HumanResources.Employee 

View: HumanResources.vJobCandidate  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Job candidate names and resumes. 

 	Source	Field name	Data type	Nullable	Comments	
	HumanResources.JobCandidate	JobCandidateID	int			
	HumanResources.JobCandidate	EmployeeID	int	Yes		
		Name.Prefix	nvarchar  (30)	Yes		
		Name.First	nvarchar  (30)	Yes		
		Name.Middle	nvarchar  (30)	Yes		
		Name.Last	nvarchar  (30)	Yes		
		Name.Suffix	nvarchar  (30)	Yes		
		Skills	nvarchar	Yes		
		Addr.Type	nvarchar  (30)	Yes		
		Addr.Loc.CountryRegion	nvarchar  (100)	Yes		
		Addr.Loc.State	nvarchar  (100)	Yes		
		Addr.Loc.City	nvarchar  (100)	Yes		
		Addr.PostalCode	nvarchar  (20)	Yes		
		EMail	nvarchar	Yes		
		WebSite	nvarchar	Yes		
	HumanResources.JobCandidate	ModifiedDate	datetime			

 
     CREATE VIEW [HumanResources].[vJobCandidate]  
     AS  
     SELECT  
         jc.[JobCandidateID]  
         ,jc.[EmployeeID]  
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (/Resume/Name/Name.Prefix)[1]', 'nvarchar(30)') AS [Name.Prefix]  
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"; 
             (/Resume/Name/Name.First)[1]', 'nvarchar(30)') AS [Name.First]  
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (/Resume/Name/Name.Middle)[1]', 'nvarchar(30)') AS [Name.Middle]  
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (/Resume/Name/Name.Last)[1]', 'nvarchar(30)') AS [Name.Last]  
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (/Resume/Name/Name.Suffix)[1]', 'nvarchar(30)') AS [Name.Suffix]  
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (/Resume/Skills)[1]', 'nvarchar(max)') AS [Skills]  
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Address/Addr.Type)[1]', 'nvarchar(30)') AS [Addr.Type] 
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Address/Addr.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Addr.Loc.CountryRegion] 
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Address/Addr.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Addr.Loc.State] 
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Address/Addr.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Addr.Loc.City] 
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Address/Addr.PostalCode)[1]', 'nvarchar(20)') AS [Addr.PostalCode] 
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (/Resume/EMail)[1]', 'nvarchar(max)') AS [EMail]  
         ,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (/Resume/WebSite)[1]', 'nvarchar(max)') AS [WebSite]  
         ,jc.[ModifiedDate]  
     FROM [HumanResources].[JobCandidate] jc  
     CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
         /Resume') AS Resume(ref); 

Related objects:  HumanResources.JobCandidate 

View: HumanResources.vJobCandidateEducation  ( Created: 26 Apr 2006 Last altered: 26 Apr 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. 

 	Source	Field name	Data type	Nullable	Comments	
	HumanResources.JobCandidate	JobCandidateID	int			
		Edu.Level	nvarchar	Yes		
		Edu.StartDate	datetime	Yes		
		Edu.EndDate	datetime	Yes		
		Edu.Degree	nvarchar  (50)	Yes		
		Edu.Major	nvarchar  (50)	Yes		
		Edu.Minor	nvarchar  (50)	Yes		
		Edu.GPA	nvarchar  (5)	Yes		
		Edu.GPAScale	nvarchar  (5)	Yes		
		Edu.School	nvarchar  (100)	Yes		
		Edu.Loc.CountryRegion	nvarchar  (100)	Yes		
		Edu.Loc.State	nvarchar  (100)	Yes		
		Edu.Loc.City	nvarchar  (100)	Yes		

 
     CREATE VIEW [HumanResources].[vJobCandidateEducation]  
     AS  
     SELECT  
         jc.[JobCandidateID]  
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.Level)[1]', 'nvarchar(max)') AS [Edu.Level] 
         ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.StartDate]  
         ,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.EndDate]  
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.Degree)[1]', 'nvarchar(50)') AS [Edu.Degree] 
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.Major)[1]', 'nvarchar(50)') AS [Edu.Major] 
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.Minor)[1]', 'nvarchar(50)') AS [Edu.Minor] 
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.GPA)[1]', 'nvarchar(5)') AS [Edu.GPA] 
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.GPAScale)[1]', 'nvarchar(5)') AS [Edu.GPAScale] 
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.School)[1]', 'nvarchar(100)') AS [Edu.School] 
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Edu.Loc.CountryRegion] 
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Edu.Loc.State] 
         ,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Edu.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Edu.Loc.City] 
     FROM [HumanResources].[JobCandidate] jc  
     CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
         /Resume/Education') AS [Education](ref); 

Related objects:  HumanResources.JobCandidate 

View: HumanResources.vJobCandidateEmployment  ( Created: 26 Apr 2006 Last altered: 26 Apr 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. 

 	Source	Field name	Data type	Nullable	Comments	
	HumanResources.JobCandidate	JobCandidateID	int			
		Emp.StartDate	datetime	Yes		
		Emp.EndDate	datetime	Yes		
		Emp.OrgName	nvarchar  (100)	Yes		
		Emp.JobTitle	nvarchar  (100)	Yes		
		Emp.Responsibility	nvarchar	Yes		
		Emp.FunctionCategory	nvarchar	Yes		
		Emp.IndustryCategory	nvarchar	Yes		
		Emp.Loc.CountryRegion	nvarchar	Yes		
		Emp.Loc.State	nvarchar	Yes		
		Emp.Loc.City	nvarchar	Yes		

 
     CREATE VIEW [HumanResources].[vJobCandidateEmployment]  
     AS  
     SELECT  
         jc.[JobCandidateID]  
         ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.StartDate]  
         ,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.EndDate]  
         ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.OrgName)[1]', 'nvarchar(100)') AS [Emp.OrgName] 
         ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.JobTitle)[1]', 'nvarchar(100)') AS [Emp.JobTitle] 
         ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.Responsibility)[1]', 'nvarchar(max)') AS [Emp.Responsibility] 
         ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.FunctionCategory)[1]', 'nvarchar(max)') AS [Emp.FunctionCategory] 
         ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.IndustryCategory)[1]', 'nvarchar(max)') AS [Emp.IndustryCategory] 
         ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(max)') AS [Emp.Loc.CountryRegion] 
         ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.Location/Location/Loc.State)[1]', 'nvarchar(max)') AS [Emp.Loc.State] 
         ,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
             (Emp.Location/Location/Loc.City)[1]', 'nvarchar(max)') AS [Emp.Loc.City] 
     FROM [HumanResources].[JobCandidate] jc  
     CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";  
         /Resume/Employment') AS Employment(ref); 

Related objects:  HumanResources.JobCandidate 

View: Person.vAdditionalContactInfo  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person. 

 	Source	Field name	Data type	Nullable	Comments	
	Person.Contact	ContactID	int			
	Person.Contact	FirstName	Name			
	Person.Contact	MiddleName	Name	Yes		
	Person.Contact	LastName	Name			
		TelephoneNumber	nvarchar  (50)	Yes		
		TelephoneSpecialInstructions	nvarchar	Yes		
		Street	nvarchar  (50)	Yes		
		City	nvarchar  (50)	Yes		
		StateProvince	nvarchar  (50)	Yes		
		PostalCode	nvarchar  (50)	Yes		
		CountryRegion	nvarchar  (50)	Yes		
		HomeAddressSpecialInstructions	nvarchar	Yes		
		EMailAddress	nvarchar  (128)	Yes		
		EMailSpecialInstructions	nvarchar	Yes		
		EMailTelephoneNumber	nvarchar  (50)	Yes		
	Person.Contact	rowguid	uniqueidentifier			
	Person.Contact	ModifiedDate	datetime			

 
     CREATE VIEW [Person].[vAdditionalContactInfo]  
     AS  
     SELECT  
         [ContactID]  
         ,[FirstName] 
         ,[MiddleName] 
         ,[LastName] 
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber]  
         ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions]  
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"; 
             (act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street]  
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City]  
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince]  
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') AS [PostalCode]  
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion]  
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions]  
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress]  
         ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions]  
         ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
             declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
             (act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber]  
         ,[rowguid]  
         ,[ModifiedDate] 
     FROM [Person].[Contact] 
     OUTER APPLY [AdditionalContactInfo].nodes( 
         'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
         /ci:AdditionalContactInfo') AS ContactInfo(ref)  
     WHERE [AdditionalContactInfo] IS NOT NULL; 

Related objects:  Person.Contact 

View: Person.vStateProvinceCountryRegion  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Joins StateProvince table with CountryRegion table. 

 	Source	Field name	Data type	Nullable	Comments	
	Person.StateProvince	StateProvinceID	int		Clustered index on the view vStateProvinceCountryRegion.	
	Person.StateProvince	StateProvinceCode	nchar  (3)			
	Person.StateProvince	IsOnlyStateProvinceFlag	Flag			
		StateProvinceName	Name			
	Person.StateProvince	TerritoryID	int			
	Person.CountryRegion	CountryRegionCode	nvarchar  (3)			
		CountryRegionName	Name			

 
     CREATE VIEW [Person].[vStateProvinceCountryRegion]  
     WITH SCHEMABINDING  
     AS  
     SELECT  
         sp.[StateProvinceID]  
         ,sp.[StateProvinceCode]  
         ,sp.[IsOnlyStateProvinceFlag]  
         ,sp.[Name] AS [StateProvinceName]  
         ,sp.[TerritoryID]  
         ,cr.[CountryRegionCode]  
         ,cr.[Name] AS [CountryRegionName] 
     FROM [Person].[StateProvince] sp  
         INNER JOIN [Person].[CountryRegion] cr  
         ON sp.[CountryRegionCode] = cr.[CountryRegionCode]; 

Related objects:  Person.CountryRegion Person.StateProvince 

View: Production.vProductAndDescription  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Product names and descriptions. Product descriptions are provided in multiple languages. 

 	Source	Field name	Data type	Nullable	Comments	
	Production.Product	ProductID	int		Clustered index on the view vProductAndDescription.	
	Production.Product	Name	Name			
		ProductModel	Name			
	Production.ProductModelProductDescriptionCulture	CultureID	nchar  (6)			
	Production.ProductDescription	Description	nvarchar  (400)			

 
     CREATE VIEW [Production].[vProductAndDescription]  
     WITH SCHEMABINDING  
     AS  
     -- View (indexed or standard) to display products and product descriptions by language. 
     SELECT  
         p.[ProductID]  
         ,p.[Name]  
         ,pm.[Name] AS [ProductModel]  
         ,pmx.[CultureID]  
         ,pd.[Description]  
     FROM [Production].[Product] p  
         INNER JOIN [Production].[ProductModel] pm  
         ON p.[ProductModelID] = pm.[ProductModelID]  
         INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx  
         ON pm.[ProductModelID] = pmx.[ProductModelID]  
         INNER JOIN [Production].[ProductDescription] pd  
         ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]; 

Related objects:  Production.Product Production.ProductDescription Production.ProductModelProductDescriptionCulture Production.ProductModel 

View: Production.vProductModelCatalogDescription  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data. 

 	Source	Field name	Data type	Nullable	Comments	
	Production.ProductModel	ProductModelID	int			
	Production.ProductModel	Name	Name			
		Summary	nvarchar	Yes		
		Manufacturer	nvarchar	Yes		
		Copyright	nvarchar  (30)	Yes		
		ProductURL	nvarchar  (256)	Yes		
		WarrantyPeriod	nvarchar  (256)	Yes		
		WarrantyDescription	nvarchar  (256)	Yes		
		NoOfYears	nvarchar  (256)	Yes		
		MaintenanceDescription	nvarchar  (256)	Yes		
		Wheel	nvarchar  (256)	Yes		
		Saddle	nvarchar  (256)	Yes		
		Pedal	nvarchar  (256)	Yes		
		BikeFrame	nvarchar	Yes		
		Crankset	nvarchar  (256)	Yes		
		PictureAngle	nvarchar  (256)	Yes		
		PictureSize	nvarchar  (256)	Yes		
		ProductPhotoID	nvarchar  (256)	Yes		
		Material	nvarchar  (256)	Yes		
		Color	nvarchar  (256)	Yes		
		ProductLine	nvarchar  (256)	Yes		
		Style	nvarchar  (256)	Yes		
		RiderExperience	nvarchar  (1024)	Yes		
	Production.ProductModel	rowguid	uniqueidentifier			
	Production.ProductModel	ModifiedDate	datetime			

 
     CREATE VIEW [Production].[vProductModelCatalogDescription]  
     AS  
     SELECT  
         [ProductModelID]  
         ,[Name]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace html="http://www.w3.org/1999/xhtml";  
             (/p1:ProductDescription/p1:Summary/html:p)[1]', 'nvarchar(max)') AS [Summary]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Manufacturer/p1:Name)[1]', 'nvarchar(max)') AS [Manufacturer]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Manufacturer/p1:Copyright)[1]', 'nvarchar(30)') AS [Copyright]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Manufacturer/p1:ProductURL)[1]', 'nvarchar(256)') AS [ProductURL]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";  
             (/p1:ProductDescription/p1:Features/wm:Warranty/wm:WarrantyPeriod)[1]', 'nvarchar(256)') AS [WarrantyPeriod]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";  
             (/p1:ProductDescription/p1:Features/wm:Warranty/wm:Description)[1]', 'nvarchar(256)') AS [WarrantyDescription]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";  
             (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:NoOfYears)[1]', 'nvarchar(256)') AS [NoOfYears]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";  
             (/p1:ProductDescription/p1:Features/wm:Maintenance/wm:Description)[1]', 'nvarchar(256)') AS [MaintenanceDescription]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";  
             (/p1:ProductDescription/p1:Features/wf:wheel)[1]', 'nvarchar(256)') AS [Wheel]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";  
             (/p1:ProductDescription/p1:Features/wf:saddle)[1]', 'nvarchar(256)') AS [Saddle]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";  
             (/p1:ProductDescription/p1:Features/wf:pedal)[1]', 'nvarchar(256)') AS [Pedal]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";  
             (/p1:ProductDescription/p1:Features/wf:BikeFrame)[1]', 'nvarchar(max)') AS [BikeFrame]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";  
             (/p1:ProductDescription/p1:Features/wf:crankset)[1]', 'nvarchar(256)') AS [Crankset]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Picture/p1:Angle)[1]', 'nvarchar(256)') AS [PictureAngle]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Picture/p1:Size)[1]', 'nvarchar(256)') AS [PictureSize]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Picture/p1:ProductPhotoID)[1]', 'nvarchar(256)') AS [ProductPhotoID]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Specifications/Material)[1]', 'nvarchar(256)') AS [Material]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Specifications/Color)[1]', 'nvarchar(256)') AS [Color]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Specifications/ProductLine)[1]', 'nvarchar(256)') AS [ProductLine]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Specifications/Style)[1]', 'nvarchar(256)') AS [Style]  
         ,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
             (/p1:ProductDescription/p1:Specifications/RiderExperience)[1]', 'nvarchar(1024)') AS [RiderExperience]  
         ,[rowguid]  
         ,[ModifiedDate] 
     FROM [Production].[ProductModel]  
     WHERE [CatalogDescription] IS NOT NULL; 

Related objects:  Production.ProductModel 

View: Production.vProductModelInstructions  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions. 

 	Source	Field name	Data type	Nullable	Comments	
	Production.ProductModel	ProductModelID	int			
	Production.ProductModel	Name	Name			
	Production.ProductModel	Instructions	nvarchar	Yes		
		LocationID	int	Yes		
		SetupHours	decimal  (9.4)	Yes		
		MachineHours	decimal  (9.4)	Yes		
		LaborHours	decimal  (9.4)	Yes		
		LotSize	int	Yes		
		Step	nvarchar  (1024)	Yes		
	Production.ProductModel	rowguid	uniqueidentifier			
	Production.ProductModel	ModifiedDate	datetime			

 
     CREATE VIEW [Production].[vProductModelInstructions]  
     AS  
     SELECT  
         [ProductModelID]  
         ,[Name]  
         ,[Instructions].value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
             (/root/text())[1]', 'nvarchar(max)') AS [Instructions]  
         ,[MfgInstructions].ref.value('@LocationID[1]', 'int') AS [LocationID]  
         ,[MfgInstructions].ref.value('@SetupHours[1]', 'decimal(9, 4)') AS [SetupHours]  
         ,[MfgInstructions].ref.value('@MachineHours[1]', 'decimal(9, 4)') AS [MachineHours]  
         ,[MfgInstructions].ref.value('@LaborHours[1]', 'decimal(9, 4)') AS [LaborHours]  
         ,[MfgInstructions].ref.value('@LotSize[1]', 'int') AS [LotSize]  
         ,[Steps].ref.value('string(.)[1]', 'nvarchar(1024)') AS [Step]  
         ,[rowguid]  
         ,[ModifiedDate] 
     FROM [Production].[ProductModel]  
     CROSS APPLY [Instructions].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
         /root/Location') MfgInstructions(ref) 
     CROSS APPLY [MfgInstructions].ref.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
         step') Steps(ref); 

Related objects:  Production.ProductModel 

View: Purchasing.vVendor  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Vendor (company) names and addresses and the names of vendor employees to contact. 

 	Source	Field name	Data type	Nullable	Comments	
	Purchasing.Vendor	VendorID	int			
	Purchasing.Vendor	Name	Name			
		ContactType	Name			
	Person.Contact	Title	nvarchar  (8)	Yes		
	Person.Contact	FirstName	Name			
	Person.Contact	MiddleName	Name	Yes		
	Person.Contact	LastName	Name			
	Person.Contact	Suffix	nvarchar  (10)	Yes		
	Person.Contact	Phone	Phone	Yes		
	Person.Contact	EmailAddress	nvarchar  (50)	Yes		
	Person.Contact	EmailPromotion	int			
	Person.Address	AddressLine1	nvarchar  (60)			
	Person.Address	AddressLine2	nvarchar  (60)	Yes		
	Person.Address	City	nvarchar  (30)			
		StateProvinceName	Name			
	Person.Address	PostalCode	nvarchar  (15)			
		CountryRegionName	Name			

 
     CREATE VIEW [Purchasing].[vVendor] AS  
     SELECT  
         v.[VendorID] 
         ,v.[Name] 
         ,ct.[Name] AS [ContactType] 
         ,c.[Title] 
         ,c.[FirstName] 
         ,c.[MiddleName] 
         ,c.[LastName] 
         ,c.[Suffix] 
         ,c.[Phone] 
         ,c.[EmailAddress] 
         ,c.[EmailPromotion] 
         ,a.[AddressLine1] 
         ,a.[AddressLine2] 
         ,a.[City] 
         ,[StateProvinceName] = sp.[Name] 
         ,a.[PostalCode] 
         ,[CountryRegionName] = cr.[Name] 
     FROM [Purchasing].[Vendor] v 
         INNER JOIN [Purchasing].[VendorContact] vc  
         ON vc.[VendorID] = v.[VendorID] 
         INNER JOIN [Person].[Contact] c  
         ON c.[ContactID] = vc.[ContactID] 
         INNER JOIN [Person].[ContactType] ct  
         ON vc.[ContactTypeID] = ct.[ContactTypeID] 
         INNER JOIN [Purchasing].[VendorAddress] va  
         ON va.[VendorID] = v.[VendorID] 
         INNER JOIN [Person].[Address] a  
         ON a.[AddressID] = va.[AddressID] 
         INNER JOIN [Person].[StateProvince] sp  
         ON sp.[StateProvinceID] = a.[StateProvinceID] 
         INNER JOIN [Person].[CountryRegion] cr  
         ON cr.[CountryRegionCode] = sp.[CountryRegionCode]; 

Related objects:  Person.Address Person.CountryRegion Person.StateProvince Purchasing.VendorAddress Purchasing.VendorContact Person.ContactType Person.Contact Purchasing.Vendor 

View: Sales.vIndividualCustomer  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Individual customers (names and addresses) that purchase Adventure Works Cycles products online. 

 	Source	Field name	Data type	Nullable	Comments	
	Sales.Customer	CustomerID	int			
	Person.Contact	Title	nvarchar  (8)	Yes		
	Person.Contact	FirstName	Name			
	Person.Contact	MiddleName	Name	Yes		
	Person.Contact	LastName	Name			
	Person.Contact	Suffix	nvarchar  (10)	Yes		
	Person.Contact	Phone	Phone	Yes		
	Person.Contact	EmailAddress	nvarchar  (50)	Yes		
	Person.Contact	EmailPromotion	int			
		AddressType	Name			
	Person.Address	AddressLine1	nvarchar  (60)			
	Person.Address	AddressLine2	nvarchar  (60)	Yes		
	Person.Address	City	nvarchar  (30)			
		StateProvinceName	Name			
	Person.Address	PostalCode	nvarchar  (15)			
		CountryRegionName	Name			
	Sales.Individual	Demographics	xml	Yes		

 
     CREATE VIEW [Sales].[vIndividualCustomer]  
     AS  
     SELECT  
         i.[CustomerID] 
         ,c.[Title] 
         ,c.[FirstName] 
         ,c.[MiddleName] 
         ,c.[LastName] 
         ,c.[Suffix] 
         ,c.[Phone] 
         ,c.[EmailAddress] 
         ,c.[EmailPromotion] 
         ,at.[Name] AS [AddressType] 
         ,a.[AddressLine1] 
         ,a.[AddressLine2] 
         ,a.[City] 
         ,[StateProvinceName] = sp.[Name] 
         ,a.[PostalCode] 
         ,[CountryRegionName] = cr.[Name] 
         ,i.[Demographics] 
     FROM [Sales].[Individual] i 
         INNER JOIN [Person].[Contact] c  
         ON c.[ContactID] = i.[ContactID] 
         INNER JOIN [Sales].[CustomerAddress] ca  
         ON ca.[CustomerID] = i.[CustomerID] 
         INNER JOIN [Person].[Address] a  
         ON a.[AddressID] = ca.[AddressID] 
         INNER JOIN [Person].[StateProvince] sp  
         ON sp.[StateProvinceID] = a.[StateProvinceID] 
         INNER JOIN [Person].[CountryRegion] cr  
         ON cr.[CountryRegionCode] = sp.[CountryRegionCode] 
         INNER JOIN [Person].[AddressType] at  
         ON ca.[AddressTypeID] = at.[AddressTypeID] 
     WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]  
         FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I'); 

Related objects:  Person.Address Sales.Individual Person.StateProvince Person.CountryRegion Sales.Customer Sales.CustomerAddress Person.Contact Person.AddressType 

View: Sales.vIndividualDemographics  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table. 

 	Source	Field name	Data type	Nullable	Comments	
	Sales.Individual	CustomerID	int			
		TotalPurchaseYTD	money	Yes		
		DateFirstPurchase	datetime	Yes		
		BirthDate	datetime	Yes		
		MaritalStatus	nvarchar  (1)	Yes		
		YearlyIncome	nvarchar  (30)	Yes		
		Gender	nvarchar  (1)	Yes		
		TotalChildren	int	Yes		
		NumberChildrenAtHome	int	Yes		
		Education	nvarchar  (30)	Yes		
		Occupation	nvarchar  (30)	Yes		
		HomeOwnerFlag	bit	Yes		
		NumberCarsOwned	int	Yes		

 
     CREATE VIEW [Sales].[vIndividualDemographics]  
     AS  
     SELECT  
         i.[CustomerID]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             TotalPurchaseYTD[1]', 'money') AS [TotalPurchaseYTD]  
         ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             DateFirstPurchase[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [DateFirstPurchase]  
         ,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             BirthDate[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [BirthDate]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             MaritalStatus[1]', 'nvarchar(1)') AS [MaritalStatus]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             YearlyIncome[1]', 'nvarchar(30)') AS [YearlyIncome]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             Gender[1]', 'nvarchar(1)') AS [Gender]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             TotalChildren[1]', 'integer') AS [TotalChildren]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             NumberChildrenAtHome[1]', 'integer') AS [NumberChildrenAtHome]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             Education[1]', 'nvarchar(30)') AS [Education]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             Occupation[1]', 'nvarchar(30)') AS [Occupation]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             HomeOwnerFlag[1]', 'bit') AS [HomeOwnerFlag]  
         ,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
             NumberCarsOwned[1]', 'integer') AS [NumberCarsOwned]  
     FROM [Sales].[Individual] i  
     CROSS APPLY i.[Demographics].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";  
         /IndividualSurvey') AS [IndividualSurvey](ref)  
     WHERE [Demographics] IS NOT NULL; 

Related objects:  Sales.Individual 

View: Sales.vSalesPerson  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Sales representiatives (names and addresses) and their sales-related information. 

 	Source	Field name	Data type	Nullable	Comments	
	Sales.SalesPerson	SalesPersonID	int			
	Person.Contact	Title	nvarchar  (8)	Yes		
	Person.Contact	FirstName	Name			
	Person.Contact	MiddleName	Name	Yes		
	Person.Contact	LastName	Name			
	Person.Contact	Suffix	nvarchar  (10)	Yes		
		JobTitle	nvarchar  (50)			
	Person.Contact	Phone	Phone	Yes		
	Person.Contact	EmailAddress	nvarchar  (50)	Yes		
	Person.Contact	EmailPromotion	int			
	Person.Address	AddressLine1	nvarchar  (60)			
	Person.Address	AddressLine2	nvarchar  (60)	Yes		
	Person.Address	City	nvarchar  (30)			
		StateProvinceName	Name			
	Person.Address	PostalCode	nvarchar  (15)			
		CountryRegionName	Name			
		TerritoryName	Name	Yes		
		TerritoryGroup	nvarchar  (50)	Yes		
	Sales.SalesPerson	SalesQuota	money	Yes		
	Sales.SalesPerson	SalesYTD	money			
	Sales.SalesPerson	SalesLastYear	money			

 
     CREATE VIEW [Sales].[vSalesPerson]  
     AS  
     SELECT  
         s.[SalesPersonID] 
         ,c.[Title] 
         ,c.[FirstName] 
         ,c.[MiddleName] 
         ,c.[LastName] 
         ,c.[Suffix] 
         ,[JobTitle] = e.[Title] 
         ,c.[Phone] 
         ,c.[EmailAddress] 
         ,c.[EmailPromotion] 
         ,a.[AddressLine1] 
         ,a.[AddressLine2] 
         ,a.[City] 
         ,[StateProvinceName] = sp.[Name] 
         ,a.[PostalCode] 
         ,[CountryRegionName] = cr.[Name] 
         ,[TerritoryName] = st.[Name] 
         ,[TerritoryGroup] = st.[Group] 
         ,s.[SalesQuota] 
         ,s.[SalesYTD] 
         ,s.[SalesLastYear] 
     FROM [Sales].[SalesPerson] s 
         INNER JOIN [HumanResources].[Employee] e  
         ON e.[EmployeeID] = s.[SalesPersonID] 
         LEFT OUTER JOIN [Sales].[SalesTerritory] st  
         ON st.[TerritoryID] = s.[TerritoryID] 
         INNER JOIN [Person].[Contact] c  
         ON c.[ContactID] = e.[ContactID] 
         INNER JOIN [HumanResources].[EmployeeAddress] ea  
         ON e.[EmployeeID] = ea.[EmployeeID]  
         INNER JOIN [Person].[Address] a  
         ON ea.[AddressID] = a.[AddressID] 
         INNER JOIN [Person].[StateProvince] sp  
         ON sp.[StateProvinceID] = a.[StateProvinceID] 
         INNER JOIN [Person].[CountryRegion] cr  
         ON cr.[CountryRegionCode] = sp.[CountryRegionCode]; 

Related objects:  Person.Address Person.StateProvince Sales.SalesPerson HumanResources.EmployeeAddress Sales.SalesTerritory HumanResources.Employee Person.Contact Person.CountryRegion 

View: Sales.vSalesPersonSalesByFiscalYears  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Uses PIVOT to return aggregated sales information for each sales representative. 

 	Source	Field name	Data type	Nullable	Comments	
	Sales.SalesOrderHeader	SalesPersonID	int	Yes		
		FullName	nvarchar  (152)	Yes		
	HumanResources.Employee	Title	nvarchar  (50)			
		SalesTerritory	Name			
		2002	money	Yes		
		2003	money	Yes		
		2004	money	Yes		

 
     CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]  
     AS  
     SELECT  
         pvt.[SalesPersonID] 
         ,pvt.[FullName] 
         ,pvt.[Title] 
         ,pvt.[SalesTerritory] 
         ,pvt.[2002] 
         ,pvt.[2003] 
         ,pvt.[2004]  
     FROM (SELECT  
             soh.[SalesPersonID] 
             ,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName] 
             ,e.[Title] 
             ,st.[Name] AS [SalesTerritory] 
             ,soh.[SubTotal] 
             ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]  
         FROM [Sales].[SalesPerson] sp  
             INNER JOIN [Sales].[SalesOrderHeader] soh  
             ON sp.[SalesPersonID] = soh.[SalesPersonID] 
             INNER JOIN [Sales].[SalesTerritory] st  
             ON sp.[TerritoryID] = st.[TerritoryID]  
             INNER JOIN [HumanResources].[Employee] e  
             ON soh.[SalesPersonID] = e.[EmployeeID]  
             INNER JOIN [Person].[Contact] c  
             ON e.[ContactID] = c.ContactID  
         ) AS soh  
     PIVOT  
     ( 
         SUM([SubTotal])  
         FOR [FiscalYear]  
         IN ([2002], [2003], [2004]) 
     ) AS pvt; 

Related objects:  Person.Contact Sales.SalesOrderHeader Sales.SalesTerritory HumanResources.Employee Sales.SalesPerson 

View: Sales.vStoreWithDemographics  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Stores (names and addresses) that sell Adventure Works Cycles products to consumers. 

 	Source	Field name	Data type	Nullable	Comments	
	Sales.StoreContact	CustomerID	int			
	Person.AddressType	Name	Name			
		ContactType	Name			
	Person.Contact	Title	nvarchar  (8)	Yes		
	Person.Contact	FirstName	Name			
	Person.Contact	MiddleName	Name	Yes		
	Person.Contact	LastName	Name			
	Person.Contact	Suffix	nvarchar  (10)	Yes		
	Person.Contact	Phone	Phone	Yes		
	Person.Contact	EmailAddress	nvarchar  (50)	Yes		
	Person.Contact	EmailPromotion	int			
		AddressType	Name			
	Person.Address	AddressLine1	nvarchar  (60)			
	Person.Address	AddressLine2	nvarchar  (60)	Yes		
	Person.Address	City	nvarchar  (30)			
		StateProvinceName	Name			
	Person.Address	PostalCode	nvarchar  (15)			
		CountryRegionName	Name			
		AnnualSales	money	Yes		
		AnnualRevenue	money	Yes		
		BankName	nvarchar  (50)	Yes		
		BusinessType	nvarchar  (5)	Yes		
		YearOpened	int	Yes		
		Specialty	nvarchar  (50)	Yes		
		SquareFeet	int	Yes		
		Brands	nvarchar  (30)	Yes		
		Internet	nvarchar  (30)	Yes		
		NumberEmployees	int	Yes		

 
     CREATE VIEW [Sales].[vStoreWithDemographics] AS  
     SELECT  
         s.[CustomerID]  
         ,s.[Name]  
         ,ct.[Name] AS [ContactType]  
         ,c.[Title]  
         ,c.[FirstName]  
         ,c.[MiddleName]  
         ,c.[LastName]  
         ,c.[Suffix]  
         ,c.[Phone]  
         ,c.[EmailAddress]  
         ,c.[EmailPromotion]  
         ,at.[Name] AS [AddressType] 
         ,a.[AddressLine1]  
         ,a.[AddressLine2]  
         ,a.[City]  
         ,sp.[Name] AS [StateProvinceName]  
         ,a.[PostalCode]  
         ,cr.[Name] AS [CountryRegionName]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet]  
         ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";  
             (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees]  
     FROM [Sales].[Store] s 
         INNER JOIN [Sales].[StoreContact] sc  
         ON sc.[CustomerID] = s.[CustomerID] 
         INNER JOIN [Person].[Contact] c  
         ON c.[ContactID] = sc.[ContactID] 
         INNER JOIN [Person].[ContactType] ct  
         ON sc.[ContactTypeID] = ct.[ContactTypeID] 
         INNER JOIN [Sales].[CustomerAddress] ca  
         ON ca.[CustomerID] = s.[CustomerID] 
         INNER JOIN [Person].[Address] a  
         ON a.[AddressID] = ca.[AddressID] 
         INNER JOIN [Person].[StateProvince] sp  
         ON sp.[StateProvinceID] = a.[StateProvinceID] 
         INNER JOIN [Person].[CountryRegion] cr  
         ON cr.[CountryRegionCode] = sp.[CountryRegionCode] 
         INNER JOIN [Person].[AddressType] at  
         ON ca.[AddressTypeID] = at.[AddressTypeID] 
     WHERE s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]  
         FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S'); 

Related objects:  Sales.StoreContact Person.Address Person.StateProvince Sales.Store Sales.Customer Sales.CustomerAddress Person.Contact Person.ContactType Person.CountryRegion Person.AddressType 

Stored Procedure: dbo.uspGetBillOfMaterials  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@StartProductID	int	Input parameter for the stored procedure uspGetBillOfMaterials. Enter a valid ProductID from the Production.Product table.	
IN	@CheckDate	datetime	Input parameter for the stored procedure uspGetBillOfMaterials used to eliminate components not used after that date. Enter a valid date.	

 
     CREATE PROCEDURE [dbo].[uspGetBillOfMaterials] 
         @StartProductID [int], 
         @CheckDate [datetime] 
     AS 
     BEGIN 
         SET NOCOUNT ON; 
      
         -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1  
         -- components of a level 0 assembly, all level 2 components of a level 1 assembly) 
         -- The CheckDate eliminates any components that are no longer used in the product on this date. 
         WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns 
         AS ( 
             SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly 
             FROM [Production].[BillOfMaterials] b 
                 INNER JOIN [Production].[Product] p  
                 ON b.[ComponentID] = p.[ProductID]  
             WHERE b.[ProductAssemblyID] = @StartProductID  
                 AND @CheckDate >= b.[StartDate]  
                 AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) 
             UNION ALL 
             SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor 
             FROM [BOM_cte] cte 
                 INNER JOIN [Production].[BillOfMaterials] b  
                 ON b.[ProductAssemblyID] = cte.[ComponentID] 
                 INNER JOIN [Production].[Product] p  
                 ON b.[ComponentID] = p.[ProductID]  
             WHERE @CheckDate >= b.[StartDate]  
                 AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) 
             ) 
         -- Outer select from the CTE 
         SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel] 
         FROM [BOM_cte] b 
         GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice] 
         ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID] 
         OPTION (MAXRECURSION 25)  
     END; 

Related objects:  Production.BillOfMaterials Production.Product 

Stored Procedure: dbo.uspGetEmployeeManagers  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Stored procedure using a recursive query to return the direct and indirect managers of the specified employee. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@EmployeeID	int	Input parameter for the stored procedure uspGetEmployeeManagers. Enter a valid EmployeeID from the HumanResources.Employee table.	

 
     CREATE PROCEDURE [dbo].[uspGetEmployeeManagers] 
         @EmployeeID [int] 
     AS 
     BEGIN 
         SET NOCOUNT ON; 
      
         -- Use recursive query to list out all Employees required for a particular Manager 
         WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [Title], [RecursionLevel]) -- CTE name and columns 
         AS ( 
             SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee 
             FROM [HumanResources].[Employee] e  
                 INNER JOIN [Person].[Contact] c  
                 ON e.[ContactID] = c.[ContactID] 
             WHERE e.[EmployeeID] = @EmployeeID 
             UNION ALL 
             SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor 
             FROM [HumanResources].[Employee] e  
                 INNER JOIN [EMP_cte] 
                 ON e.[EmployeeID] = [EMP_cte].[ManagerID] 
                 INNER JOIN [Person].[Contact] c  
                 ON e.[ContactID] = c.[ContactID] 
         ) 
         -- Join back to Employee to return the manager name  
         SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName],  
             [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE 
         FROM [EMP_cte]  
             INNER JOIN [HumanResources].[Employee] e  
             ON [EMP_cte].[ManagerID] = e.[EmployeeID] 
             INNER JOIN [Person].[Contact] c  
             ON e.[ContactID] = c.[ContactID] 
         ORDER BY [RecursionLevel], [ManagerID], [EmployeeID] 
         OPTION (MAXRECURSION 25)  
     END; 

Related objects:  Person.Contact HumanResources.Employee 

Stored Procedure: dbo.uspGetManagerEmployees  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Stored procedure using a recursive query to return the direct and indirect employees of the specified manager. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@ManagerID	int	Input parameter for the stored procedure uspGetManagerEmployees. Enter a valid ManagerID from the HumanResources.Employee table.	

 
     CREATE PROCEDURE [dbo].[uspGetManagerEmployees] 
         @ManagerID [int] 
     AS 
     BEGIN 
         SET NOCOUNT ON; 
      
         -- Use recursive query to list out all Employees required for a particular Manager 
         WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns 
         AS ( 
             SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 -- Get the initial list of Employees for Manager n 
             FROM [HumanResources].[Employee] e  
                 INNER JOIN [Person].[Contact] c  
                 ON e.[ContactID] = c.[ContactID] 
             WHERE [ManagerID] = @ManagerID 
             UNION ALL 
             SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor 
             FROM [HumanResources].[Employee] e  
                 INNER JOIN [EMP_cte] 
                 ON e.[ManagerID] = [EMP_cte].[EmployeeID] 
                 INNER JOIN [Person].[Contact] c  
                 ON e.[ContactID] = c.[ContactID] 
             ) 
         -- Join back to Employee to return the manager name  
         SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName', 
             [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE 
         FROM [EMP_cte]  
             INNER JOIN [HumanResources].[Employee] e  
             ON [EMP_cte].[ManagerID] = e.[EmployeeID] 
             INNER JOIN [Person].[Contact] c  
             ON e.[ContactID] = c.[ContactID] 
         ORDER BY [RecursionLevel], [ManagerID], [EmployeeID] 
         OPTION (MAXRECURSION 25)  
     END; 

Related objects:  Person.Contact HumanResources.Employee 

Stored Procedure: dbo.uspGetWhereUsedProductID  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@StartProductID	int	Input parameter for the stored procedure uspGetWhereUsedProductID. Enter a valid ProductID from the Production.Product table.	
IN	@CheckDate	datetime	Input parameter for the stored procedure uspGetWhereUsedProductID used to eliminate components not used after that date. Enter a valid date.	

 
     CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID] 
         @StartProductID [int], 
         @CheckDate [datetime] 
     AS 
     BEGIN 
         SET NOCOUNT ON; 
      
         --Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly) 
         WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns 
         AS ( 
             SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly 
             FROM [Production].[BillOfMaterials] b 
                 INNER JOIN [Production].[Product] p  
                 ON b.[ProductAssemblyID] = p.[ProductID]  
             WHERE b.[ComponentID] = @StartProductID  
                 AND @CheckDate >= b.[StartDate]  
                 AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) 
             UNION ALL 
             SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor 
             FROM [BOM_cte] cte 
                 INNER JOIN [Production].[BillOfMaterials] b  
                 ON cte.[ProductAssemblyID] = b.[ComponentID] 
                 INNER JOIN [Production].[Product] p  
                 ON b.[ProductAssemblyID] = p.[ProductID]  
             WHERE @CheckDate >= b.[StartDate]  
                 AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) 
             ) 
         -- Outer select from the CTE 
         SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel] 
         FROM [BOM_cte] b 
         GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice] 
         ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID] 
         OPTION (MAXRECURSION 25)  
     END; 

Related objects:  Production.BillOfMaterials Production.Product 

Stored Procedure: dbo.uspLogError  ( Created: 26 Apr 2006 Last altered: 26 Oct 2008 )
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. 

Parameters: 

    Mode	Parameter name	Data type	Description	
INOUT	@ErrorLogID	int	Output parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.	

 
     -- uspLogError logs error information in the ErrorLog table about the  
     -- error that caused execution to jump to the CATCH block of a  
     -- TRY...CATCH construct. This should be executed from within the scope  
     -- of a CATCH block otherwise it will return without inserting error  
     -- information.  
     CREATE PROCEDURE [dbo].[uspLogError]  
         @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted 
     AS                               -- by uspLogError in the ErrorLog table 
     BEGIN 
         SET NOCOUNT ON; 
      
         -- Output parameter value of 0 indicates that error  
         -- information was not logged 
         SET @ErrorLogID = 0; 
      
         BEGIN TRY 
             -- Return if there is no error information to log 
             IF ERROR_NUMBER() IS NULL 
                 RETURN; 
      
             -- Return if inside an uncommittable transaction. 
             -- Data insertion/modification is not allowed when  
             -- a transaction is in an uncommittable state. 
             IF XACT_STATE() = -1 
             BEGIN 
                 PRINT 'Cannot log error since the current transaction is in an uncommittable state. '  
                     + 'Rollback the transaction before executing uspLogError in order to successfully log error information.'; 
                 RETURN; 
             END 
      
             INSERT [dbo].[ErrorLog]  
                 ( 
                 [UserName],  
                 [ErrorNumber],  
                 [ErrorSeverity],  
                 [ErrorState],  
                 [ErrorProcedure],  
                 [ErrorLine],  
                 [ErrorMessage] 
                 )  
             VALUES  
                 ( 
                 CONVERT(sysname, CURRENT_USER),  
                 ERROR_NUMBER(), 
                 ERROR_SEVERITY(), 
                 ERROR_STATE(), 
                 ERROR_PROCEDURE(), 
                 ERROR_LINE(), 
                 ERROR_MESSAGE() 
                 ); 
      
             -- Pass back the ErrorLogID of the row inserted 
             SET @ErrorLogID = @@IDENTITY; 
         END TRY 
         BEGIN CATCH 
             PRINT 'An error occurred in stored procedure uspLogError: '; 
             EXECUTE [dbo].[uspPrintError]; 
             RETURN -1; 
         END CATCH 
     END; 

Related objects:  dbo.uspPrintError dbo.ErrorLog HumanResources.uspUpdateEmployeeHireInfo HumanResources.uspUpdateEmployeeLogin HumanResources.uspUpdateEmployeePersonalInfo Production.iWorkOrder Production.uWorkOrder Purchasing.dVendor Purchasing.iPurchaseOrderDetail Purchasing.uPurchaseOrderDetail Purchasing.uPurchaseOrderHeader Sales.iduSalesOrderDetail Sales.iStore Sales.uSalesOrderHeader 

Stored Procedure: dbo.uspPrintError  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
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. 

     -- uspPrintError 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. 
     CREATE PROCEDURE [dbo].[uspPrintError]  
     AS 
     BEGIN 
         SET NOCOUNT ON; 
      
         -- Print error information.  
         PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + 
               ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) + 
               ', State ' + CONVERT(varchar(5), ERROR_STATE()) +  
               ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +  
               ', Line ' + CONVERT(varchar(5), ERROR_LINE()); 
         PRINT ERROR_MESSAGE(); 
     END; 

Related objects:  dbo.ufnGetContactInformation dbo.uspLogError Production.iWorkOrder Production.uWorkOrder Purchasing.dVendor Purchasing.iPurchaseOrderDetail Purchasing.uPurchaseOrderDetail Purchasing.uPurchaseOrderHeader Sales.iduSalesOrderDetail Sales.iStore Sales.uSalesOrderHeader 

Stored Procedure: HumanResources.uspUpdateEmployeeHireInfo  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@EmployeeID	int	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid EmployeeID from the Employee table.	
IN	@Title	nvarchar	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.	
IN	@HireDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.	
IN	@RateChangeDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.	
IN	@Rate	money	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.	
IN	@PayFrequency	tinyint	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.	
IN	@CurrentFlag	bit	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.	

 
     CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo] 
         @EmployeeID [int],  
         @Title [nvarchar](50),  
         @HireDate [datetime],  
         @RateChangeDate [datetime],  
         @Rate [money],  
         @PayFrequency [tinyint],  
         @CurrentFlag [dbo].[Flag]  
     WITH EXECUTE AS CALLER 
     AS 
     BEGIN 
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             BEGIN TRANSACTION; 
      
             UPDATE [HumanResources].[Employee]  
             SET [Title] = @Title  
                 ,[HireDate] = @HireDate  
                 ,[CurrentFlag] = @CurrentFlag  
             WHERE [EmployeeID] = @EmployeeID; 
      
             INSERT INTO [HumanResources].[EmployeePayHistory]  
                 ([EmployeeID] 
                 ,[RateChangeDate] 
                 ,[Rate] 
                 ,[PayFrequency])  
             VALUES (@EmployeeID, @RateChangeDate, @Rate, @PayFrequency); 
      
             COMMIT TRANSACTION; 
         END TRY 
         BEGIN CATCH 
             -- Rollback any active or uncommittable transactions before 
             -- inserting information in the ErrorLog 
             IF @@TRANCOUNT > 0 
             BEGIN 
                 ROLLBACK TRANSACTION; 
             END 
      
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

Related objects:  dbo.uspLogError HumanResources.Employee HumanResources.EmployeePayHistory 

Stored Procedure: HumanResources.uspUpdateEmployeeLogin  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Updates the Employee table with the values specified in the input parameters for the given EmployeeID. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@EmployeeID	int	Input parameter for the stored procedure uspUpdateEmployeeLogin. Enter a valid EmployeeID from the Employee table.	
IN	@ManagerID	int	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid ManagerID for the employee.	
IN	@LoginID	nvarchar	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid login for the employee.	
IN	@Title	nvarchar	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.	
IN	@HireDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.	
IN	@CurrentFlag	bit	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.	

 
     CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin] 
         @EmployeeID [int],  
         @ManagerID [int], 
         @LoginID [nvarchar](256), 
         @Title [nvarchar](50), 
         @HireDate [datetime], 
         @CurrentFlag [dbo].[Flag] 
     WITH EXECUTE AS CALLER 
     AS 
     BEGIN 
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             UPDATE [HumanResources].[Employee]  
             SET [ManagerID] = @ManagerID  
                 ,[LoginID] = @LoginID  
                 ,[Title] = @Title  
                 ,[HireDate] = @HireDate  
                 ,[CurrentFlag] = @CurrentFlag  
             WHERE [EmployeeID] = @EmployeeID; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

Related objects:  dbo.uspLogError HumanResources.Employee 

Stored Procedure: HumanResources.uspUpdateEmployeePersonalInfo  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Updates the Employee table with the values specified in the input parameters for the given EmployeeID. 

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@EmployeeID	int	Input parameter for the stored procedure uspUpdateEmployeePersonalInfo. Enter a valid EmployeeID from the HumanResources.Employee table.	
IN	@NationalIDNumber	nvarchar	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a national ID for the employee.	
IN	@BirthDate	datetime	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a birth date for the employee.	
IN	@MaritalStatus	nchar	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a marital status for the employee.	
IN	@Gender	nchar	Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a gender for the employee.	

 
     CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo] 
         @EmployeeID [int],  
         @NationalIDNumber [nvarchar](15),  
         @BirthDate [datetime],  
         @MaritalStatus [nchar](1),  
         @Gender [nchar](1) 
     WITH EXECUTE AS CALLER 
     AS 
     BEGIN 
         SET NOCOUNT ON; 
      
         BEGIN TRY 
             UPDATE [HumanResources].[Employee]  
             SET [NationalIDNumber] = @NationalIDNumber  
                 ,[BirthDate] = @BirthDate  
                 ,[MaritalStatus] = @MaritalStatus  
                 ,[Gender] = @Gender  
             WHERE [EmployeeID] = @EmployeeID; 
         END TRY 
         BEGIN CATCH 
             EXECUTE [dbo].[uspLogError]; 
         END CATCH; 
     END; 

Related objects:  dbo.uspLogError HumanResources.Employee 

UDF: dbo.ufnGetAccountingEndDate  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function used in the uSalesOrderHeader trigger to set the starting account date. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		datetime		

 
     CREATE FUNCTION [dbo].[ufnGetAccountingEndDate]() 
     RETURNS [datetime]  
     AS  
     BEGIN 
         RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112)); 
     END; 

UDF: dbo.ufnGetAccountingStartDate  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function used in the uSalesOrderHeader trigger to set the ending account date. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		datetime		

 
     CREATE FUNCTION [dbo].[ufnGetAccountingStartDate]() 
     RETURNS [datetime]  
     AS  
     BEGIN 
         RETURN CONVERT(datetime, '20030701', 112); 
     END; 

UDF: dbo.ufnGetContactInformation  ( Created: 26 Apr 2006 Last altered: 26 Oct 2008 )

Parameters: 

    Mode	Parameter name	Data type	Description	
IN	@ContactID	int	Input parameter for the table value function ufnGetContactInformation. Enter a valid ContactID from the Person.Contact table.	

 
     CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int) 
     RETURNS @retContactInformation TABLE  
     ( 
         -- Columns returned by the function 
         [ContactID] int PRIMARY KEY NOT NULL,  
         [FirstName] [nvarchar](50) NULL,  
         [LastName] [nvarchar](50) NULL,  
         [JobTitle] [nvarchar](50) NULL,  
         [ContactType] [nvarchar](50) NULL 
     ) 
     AS  
     -- Returns the first name, last name, job title and contact type for the specified contact. 
     BEGIN 
         DECLARE  
             @FirstName [nvarchar](50),  
             @LastName [nvarchar](50),  
             @JobTitle [nvarchar](50),  
             @ContactType [nvarchar](50); 
      
         -- Get common contact information 
         SELECT  
             @ContactID = ContactID,  
             @FirstName = FirstName,  
             @LastName = LastName 
         FROM [Person].[Contact]  
         WHERE [ContactID] = @ContactID; 
      
         SET @JobTitle =  
             CASE  
                 -- Check for employee 
                 WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e  
                     WHERE e.[ContactID] = @ContactID)  
                     THEN (SELECT [Title]  
                         FROM [HumanResources].[Employee]  
                         WHERE [ContactID] = @ContactID) 
      
                 -- Check for vendor 
                 WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc  
                         INNER JOIN [Person].[ContactType] ct  
                         ON vc.[ContactTypeID] = ct.[ContactTypeID]  
                     WHERE vc.[ContactID] = @ContactID)  
                     THEN (SELECT ct.[Name]  
                         FROM [Purchasing].[VendorContact] vc  
                             INNER JOIN [Person].[ContactType] ct  
                             ON vc.[ContactTypeID] = ct.[ContactTypeID]  
                         WHERE vc.[ContactID] = @ContactID) 
      
                 -- Check for store 
                 WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc  
                         INNER JOIN [Person].[ContactType] ct  
                         ON sc.[ContactTypeID] = ct.[ContactTypeID]  
                     WHERE sc.[ContactID] = @ContactID)  
                     THEN (SELECT ct.[Name]  
                         FROM [Sales].[StoreContact] sc  
                             INNER JOIN [Person].[ContactType] ct  
                             ON sc.[ContactTypeID] = ct.[ContactTypeID]  
                         WHERE [ContactID] = @ContactID) 
      
                 ELSE NULL  
             END; 
      
         SET @ContactType =  
             CASE  
                 -- Check for employee 
                 WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e  
                     WHERE e.[ContactID] = @ContactID)  
                     THEN 'Employee' 
      
                 -- Check for vendor 
                 WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc  
                         INNER JOIN [Person].[ContactType] ct  
                         ON vc.[ContactTypeID] = ct.[ContactTypeID]  
                     WHERE vc.[ContactID] = @ContactID)  
                     THEN 'Vendor Contact' 
      
                 -- Check for store 
                 WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc  
                         INNER JOIN [Person].[ContactType] ct  
                         ON sc.[ContactTypeID] = ct.[ContactTypeID]  
                     WHERE sc.[ContactID] = @ContactID)  
                     THEN 'Store Contact' 
      
                 -- Check for individual consumer 
                 WHEN EXISTS(SELECT * FROM [Sales].[Individual] i  
                     WHERE i.[ContactID] = @ContactID)  
                     THEN 'Consumer' 
             END; 
      
         -- Return the information to the caller 
         IF @ContactID IS NOT NULL  
         BEGIN 
     		EXECUTE [dbo].[uspPrintError]; 
             INSERT @retContactInformation 
             SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; 
         END; 
      
         RETURN; 
     END; 

Related objects:  dbo.uspPrintError Sales.StoreContact Person.Contact Purchasing.VendorContact Person.ContactType HumanResources.Employee Sales.Individual 

UDF: dbo.ufnGetDocumentStatusText  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function returning the text representation of the Status column in the Document table. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		nvarchar		
IN	@Status	tinyint	Input parameter for the scalar function ufnGetDocumentStatusText. Enter a valid integer.	

 
     CREATE FUNCTION [dbo].[ufnGetDocumentStatusText](@Status [tinyint]) 
     RETURNS [nvarchar](16)  
     AS  
     -- Returns the sales order status text representation for the status value. 
     BEGIN 
         DECLARE @ret [nvarchar](16); 
      
         SET @ret =  
             CASE @Status 
                 WHEN 1 THEN N'Pending approval' 
                 WHEN 2 THEN N'Approved' 
                 WHEN 3 THEN N'Obsolete' 
                 ELSE N'** Invalid **' 
             END; 
          
         RETURN @ret 
     END; 

UDF: dbo.ufnGetProductDealerPrice  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function returning the dealer price for a given product on a particular order date. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		money		
IN	@ProductID	int	Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.	
IN	@OrderDate	datetime	Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.	

 
     CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime]) 
     RETURNS [money]  
     AS  
     -- Returns the dealer price for the product on a specific date. 
     BEGIN 
         DECLARE @DealerPrice money; 
         DECLARE @DealerDiscount money; 
      
         SET @DealerDiscount = 0.60  -- 60% of list price 
      
         SELECT @DealerPrice = plph.[ListPrice] * @DealerDiscount  
         FROM [Production].[Product] p  
             INNER JOIN [Production].[ProductListPriceHistory] plph  
             ON p.[ProductID] = plph.[ProductID]  
                 AND p.[ProductID] = @ProductID  
                 AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices! 
      
         RETURN @DealerPrice; 
     END; 

Related objects:  Production.Product Production.ProductListPriceHistory 

UDF: dbo.ufnGetProductListPrice  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function returning the list price for a given product on a particular order date. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		money		
IN	@ProductID	int	Input parameter for the scalar function ufnGetProductListPrice. Enter a valid ProductID from the Production.Product table.	
IN	@OrderDate	datetime	Input parameter for the scalar function ufnGetProductListPrice. Enter a valid order date.	

 
     CREATE FUNCTION [dbo].[ufnGetProductListPrice](@ProductID [int], @OrderDate [datetime]) 
     RETURNS [money]  
     AS  
     BEGIN 
         DECLARE @ListPrice money; 
      
         SELECT @ListPrice = plph.[ListPrice]  
         FROM [Production].[Product] p  
             INNER JOIN [Production].[ProductListPriceHistory] plph  
             ON p.[ProductID] = plph.[ProductID]  
                 AND p.[ProductID] = @ProductID  
                 AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices! 
      
         RETURN @ListPrice; 
     END; 

Related objects:  Production.Product Production.ProductListPriceHistory 

UDF: dbo.ufnGetProductStandardCost  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function returning the standard cost for a given product on a particular order date. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		money		
IN	@ProductID	int	Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table.	
IN	@OrderDate	datetime	Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date.	

 
     CREATE FUNCTION [dbo].[ufnGetProductStandardCost](@ProductID [int], @OrderDate [datetime]) 
     RETURNS [money]  
     AS  
     -- Returns the standard cost for the product on a specific date. 
     BEGIN 
         DECLARE @StandardCost money; 
      
         SELECT @StandardCost = pch.[StandardCost]  
         FROM [Production].[Product] p  
             INNER JOIN [Production].[ProductCostHistory] pch  
             ON p.[ProductID] = pch.[ProductID]  
                 AND p.[ProductID] = @ProductID  
                 AND @OrderDate BETWEEN pch.[StartDate] AND COALESCE(pch.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices! 
      
         RETURN @StandardCost; 
     END; 

Related objects:  Production.Product Production.ProductCostHistory 

UDF: dbo.ufnGetPurchaseOrderStatusText  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		nvarchar		
IN	@Status	tinyint	Input parameter for the scalar function ufnGetPurchaseOrdertStatusText. Enter a valid integer.	

 
     CREATE FUNCTION [dbo].[ufnGetPurchaseOrderStatusText](@Status [tinyint]) 
     RETURNS [nvarchar](15)  
     AS  
     -- Returns the sales order status text representation for the status value. 
     BEGIN 
         DECLARE @ret [nvarchar](15); 
      
         SET @ret =  
             CASE @Status 
                 WHEN 1 THEN 'Pending' 
                 WHEN 2 THEN 'Approved' 
                 WHEN 3 THEN 'Rejected' 
                 WHEN 4 THEN 'Complete' 
                 ELSE '** Invalid **' 
             END; 
          
         RETURN @ret 
     END; 

UDF: dbo.ufnGetSalesOrderStatusText  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function returning the text representation of the Status column in the SalesOrderHeader table. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		nvarchar		
IN	@Status	tinyint	Input parameter for the scalar function ufnGetSalesOrderStatusText. Enter a valid integer.	

 
     CREATE FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status [tinyint]) 
     RETURNS [nvarchar](15)  
     AS  
     -- Returns the sales order status text representation for the status value. 
     BEGIN 
         DECLARE @ret [nvarchar](15); 
      
         SET @ret =  
             CASE @Status 
                 WHEN 1 THEN 'In process' 
                 WHEN 2 THEN 'Approved' 
                 WHEN 3 THEN 'Backordered' 
                 WHEN 4 THEN 'Rejected' 
                 WHEN 5 THEN 'Shipped' 
                 WHEN 6 THEN 'Cancelled' 
                 ELSE '** Invalid **' 
             END; 
          
         RETURN @ret 
     END; 

UDF: dbo.ufnGetStock  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		int		
IN	@ProductID	int	Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.	

 
     CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int]) 
     RETURNS [int]  
     AS  
     -- Returns the stock level for the product. This function is used internally only 
     BEGIN 
         DECLARE @ret int; 
          
         SELECT @ret = SUM(p.[Quantity])  
         FROM [Production].[ProductInventory] p  
         WHERE p.[ProductID] = @ProductID  
             AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage 
          
         IF (@ret IS NULL)  
             SET @ret = 0 
          
         RETURN @ret 
     END; 

Related objects:  Production.ProductInventory 

UDF: dbo.ufnLeadingZeros  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )
Scalar function used by the Sales.Customer table to help set the account number. 

Parameters: 

    Mode	Parameter name	Data type	Description	
OUT		varchar		
IN	@Value	int	Input parameter for the scalar function ufnLeadingZeros. Enter a valid integer.	

 
     CREATE FUNCTION [dbo].[ufnLeadingZeros]( 
         @Value int 
     )  
     RETURNS varchar(8)  
     WITH SCHEMABINDING  
     AS  
     BEGIN 
         DECLARE @ReturnValue varchar(8); 
      
         SET @ReturnValue = CONVERT(varchar(8), @Value); 
         SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue; 
      
         RETURN (@ReturnValue); 
     END; 

Related objects:  Sales.Customer 

User-defined data types 

 - AccountNumber (nvarchar (15), null )  
 - Flag (bit, not null )  
 - Name (nvarchar (50), null )  
 - NameStyle (bit, not null )  
 - OrderNumber (nvarchar (25), null )  
 - Phone (nvarchar (25), null )  

XML Schemas 

XML schema: HumanResources.HRResumeSchemaCollection  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )    
Collection of XML schemas for the Resume column in the HumanResources.JobCandidate table. 

Components: 

    Name	Type	Derivation	Namespace	
AddressType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
EducationType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
EmploymentType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
LocationType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
NameType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
ResumeType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
TelephoneType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Addr.Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Addr.OrgName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Addr.PostalCode	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Addr.Street	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Addr.Telephone	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Addr.Type	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Address	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
EMail	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.Degree	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.EndDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.GPA	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.GPAAlternate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.GPAScale	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.Level	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.Major	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.Minor	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.School	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Edu.StartDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Education	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Emp.EndDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Emp.FunctionCategory	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Emp.IndustryCategory	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Emp.JobTitle	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Emp.Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Emp.OrgName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Emp.Responsibility	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Emp.StartDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Employment	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Loc.City	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Loc.CountryRegion	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Loc.State	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Name	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Name.First	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Name.Last	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Name.Middle	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Name.Prefix	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Name.Suffix	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Resume	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Skills	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Tel.AreaCode	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Tel.Extension	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Tel.IntlCode	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Tel.Number	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Tel.Type	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
Telephone	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	
WebSite	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume	

XML schema: Person.AdditionalContactInfoSchemaCollection  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )    
Collection of XML schemas for the AdditionalContactInfo column in the Person.Contact table. 

Components: 

    Name	Type	Derivation	Namespace	
date	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord	
addressType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
eMailType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
phoneNumberType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
specialInstructionsType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
AdditionalContactInfo	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo	
City	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
ContactRecord	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord	
CountryRegion	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
PostalCode	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
SpecialInstructions	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
SpecialInstructions	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
SpecialInstructions	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
StateProvince	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
Street	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
eMail	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
eMailAddress	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
facsimileTelephoneNumber	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
homePostalAddress	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
internationaliSDNNumber	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
mobile	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
number	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
pager	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
physicalDeliveryOfficeName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
registeredAddress	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
telephoneNumber	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	
telexNumber	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes	

XML schema: Production.ManuInstructionsSchemaCollection  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )    
Collection of XML schemas for the Instructions column in the Production.ProductModel table. 

Components: 

    Name	Type	Derivation	Namespace	
LaborHours	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
LocationID	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
LotSize	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
MachineHours	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
SetupHours	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
StepType	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
Location	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
blueprint	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
diag	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
material	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
root	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
specs	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
step	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	
tool	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions	

XML schema: Production.ProductDescriptionSchemaCollection  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )    
Collection of XML schemas for the CatalogDescription column in the Production.ProductModel table. 

Components: 

    Name	Type	Derivation	Namespace	
ProductModelID	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
ProductModelName	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Category	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Features	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Manufacturer	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Picture	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
ProductDescription	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Specifications	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Summary	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Angle	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Category	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Code	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Copyright	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
CopyrightURL	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Description	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Description	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain	
Description	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain	
Features	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Maintenance	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain	
Manufacturer	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Name	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Name	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
NoOfYears	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain	
Picture	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
ProductDescription	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
ProductPhotoID	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
ProductURL	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Size	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Specifications	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Summary	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Taxonomy	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription	
Warranty	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain	
WarrantyPeriod	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain	

XML schema: Sales.IndividualSurveySchemaCollection  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )    
Collection of XML schemas for the Demographics column in the Sales.Individual table. 

Components: 

    Name	Type	Derivation	Namespace	
BirthDate	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
Comments	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
CommuteDistance	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
DateFirstPurchase	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
Education	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
Gender	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
Hobby	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
HomeOwnerFlag	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
IndividualSurvey	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
MaritalStatus	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
NumberCarsOwned	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
NumberChildrenAtHome	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
Occupation	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
TotalChildren	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
TotalPurchaseYTD	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
YearlyIncome	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
MileRangeType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	
SalaryType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey	

XML schema: Sales.StoreSurveySchemaCollection  ( Created: 26 Apr 2006 Last altered: 26 Apr 2006 )    
Collection of XML schemas for the Demographics column in the Sales.Store table. 

Components: 

    Name	Type	Derivation	Namespace	
AnnualRevenue	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
AnnualSales	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
BankName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
Brands	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
BusinessType	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
Comments	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
ContactName	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
Internet	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
JobTitle	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
NumberEmployees	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
Specialty	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
SquareFeet	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
StoreSurvey	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
YearOpened	ELEMENT	NONE	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
BrandType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
BusinessType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
InternetType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	
SpecialtyType	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey	

XML schema: sys.sys  ( Created: 14 Oct 2005 Last altered: 14 Oct 2005 )    

Components: 

    Name	Type	Derivation	Namespace	
anySimpleType	ANY_SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
anyType	ANY_TYPE	NONE	http://www.w3.org/2001/XMLSchema	
base	ATTRIBUTE	NONE	http://www.w3.org/XML/1998/namespace	
clrTypeName	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
lang	ATTRIBUTE	NONE	http://www.w3.org/XML/1998/namespace	
localeId	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
maxLength	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
precision	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
scale	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
space	ATTRIBUTE	NONE	http://www.w3.org/XML/1998/namespace	
sqlCollationVersion	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
sqlCompareOptions	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
sqlDbType	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
sqlSortId	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
sqlTypeAlias	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
xmlSchemaCollection	ATTRIBUTE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
xml	COMPLEX_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
ENTITIES	LIST_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
IDREFS	LIST_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
NMTOKENS	LIST_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
sqlCompareOptionsList	LIST_TYPE	NONE	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
NOTATION	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
QName	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
anyURI	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
base64Binary	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
boolean	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
date	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
dateTime	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
decimal	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
double	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
duration	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
float	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
gDay	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
gMonth	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
gMonthDay	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
gYear	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
gYearMonth	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
hexBinary	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
string	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
time	PRIMITIVE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
ENTITY	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
ID	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
IDREF	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
NCName	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
NMTOKEN	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
Name	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
bigint	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
binary	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
bit	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
byte	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
char	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
datetime	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
dbobject	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
decimal	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
float	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
image	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
int	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
int	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
integer	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
language	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
long	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
money	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
nchar	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
negativeInteger	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
nonNegativeInteger	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
nonPositiveInteger	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
normalizedString	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
ntext	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
numeric	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
nvarchar	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
positiveInteger	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
real	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
short	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
smalldatetime	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
smallint	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
smallmoney	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
sqlCompareOptionsEnum	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
sqlDbTypeEnum	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
text	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
timestamp	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
timestampNumeric	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
tinyint	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
token	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
uniqueidentifier	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
unsignedByte	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
unsignedInt	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
unsignedLong	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
unsignedShort	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/2001/XMLSchema	
varbinary	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
varchar	SIMPLE_TYPE	RESTRICTION	http://schemas.microsoft.com/sqlserver/2004/sqltypes	
xmlSpaceEnum	SIMPLE_TYPE	RESTRICTION	http://www.w3.org/XML/1998/namespace	

USERS 

User name	Login name	Created	Roles	
dbo	sa	08 Apr 2003	db_owner 
	
TestUser	panel	03 Jun 2008	db_owner 

ROLES 

Role name	App role	Users in this role	
db_accessadmin			
db_backupoperator			
db_datareader			
db_datawriter			
db_ddladmin			
db_denydatareader			
db_denydatawriter			
db_owner		dbo 
TestUser 
	
db_securityadmin			
public