AdventureWorks

AdventureWorks Sample OLTP Database14/07/2008
178.75 MB

Summary

Tables (70)
Views (17)
Stored procedures (9)
User-defined functions (11)
User-defined types (6)
XML Schemas (7)
Users (2)
- dbo
- TestUser
Roles (10)

Tables

Table: dbo.AWBuildVersion

Current version number of the AdventureWorks sample database.

Fields

Field name	Data type	Nullable	Default value	Field description
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.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_AWBuildVersion_SystemInformationID	SystemInformationID	ASC	Yes	CLUSTERED

Table definition

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 CONSTRAINT [DF_AWBuildVersion_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_AWBuildVersion_SystemInformationID] PRIMARY KEY  CLUSTERED 
	(
		[SystemInformationID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: dbo.DatabaseLog

Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.

Fields

Field name	Data type	Nullable	Default value	Field description
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.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_DatabaseLog_DatabaseLogID	DatabaseLogID	ASC	Yes	NONCLUSTERED

Table definition

CREATE TABLE [dbo.DatabaseLog] (
	[DatabaseLogID] [int] IDENTITY (1, 1) NOT NULL ,
	[PostTime] [datetime] NOT NULL ,
	[DatabaseUser] [sysname] NOT NULL ,
	[Event] [sysname] NOT NULL ,
	[Schema] [sysname] NULL ,
	[Object] [sysname] NULL ,
	[TSQL] [nvarchar] (0) COLLATE Latin1_General_CS_AS NOT NULL ,
	[XmlEvent] [xml] NOT NULL ,
	CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY  NONCLUSTERED 
	(
		[DatabaseLogID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

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.

Fields

Field name	Data type	Nullable	Default value	Field description
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.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ErrorLog_ErrorLogID	ErrorLogID	ASC	Yes	CLUSTERED

Related objects

stored procedure dbo.uspLogError

Table definition

CREATE TABLE [dbo.ErrorLog] (
	[ErrorLogID] [int] IDENTITY (1, 1) NOT NULL ,
	[ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (getdate()),
	[UserName] [sysname] 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 ,
	CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY  CLUSTERED 
	(
		[ErrorLogID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: HumanResources.Department (go to dbdesc.com to register)

Lookup table containing the departments within the Adventure Works Cycles company.

Fields

Field name	Data type	Nullable	Default value	Field description
DepartmentID	smallint			Primary key for Department records.
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

Indices

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

Table definition

CREATE TABLE [HumanResources.Department] (
	[DepartmentID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Name] [Name] NOT NULL ,
	[GroupName] [Name] NOT NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Department_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY  CLUSTERED 
	(
		[DepartmentID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: HumanResources.Employee (go to dbdesc.com to register)

Employee information such as salary, department, and title.

Fields

Field name	Data type	Nullable	Default value	Field description
EmployeeID	int			Primary key for Employee records.
NationalIDNumber	nvarchar (15)			Unique national identification number such as a social security number.
ContactID	int			Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginID	nvarchar (256)			Network login.
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
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:

Name	Column	Reference	Foreign Key description
FK_Employee_Contact_ContactID	ContactID	PK_Contact_ContactID (Person.Contact)	Foreign key constraint referencing Contact.ContactID.
FK_Employee_Employee_ManagerID	ManagerID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.ManagerID.

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.EmployeeAddress	FK_EmployeeAddress_Employee_EmployeeID	PK_Employee_EmployeeID
HumanResources.EmployeeDepartmentHistory	FK_EmployeeDepartmentHistory_Employee_EmployeeID	PK_Employee_EmployeeID
HumanResources.EmployeePayHistory	FK_EmployeePayHistory_Employee_EmployeeID	PK_Employee_EmployeeID

Indices

Index name	Column name	Sort 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	Check expresion
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
Description:	INSTEAD OF DELETE trigger which keeps Employees from being deleted.
Creation date:	26 Apr 2006
Trigger type:	INSTEAD OF DELETE
Trigger active:	Yes

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;

Table definition

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] [Flag] NOT NULL CONSTRAINT [DF_Employee_SalariedFlag] DEFAULT ((1)),
	[VacationHours] [smallint] NOT NULL CONSTRAINT [DF_Employee_VacationHours] DEFAULT ((0)),
	[SickLeaveHours] [smallint] NOT NULL CONSTRAINT [DF_Employee_SickLeaveHours] DEFAULT ((0)),
	[CurrentFlag] [Flag] NOT NULL CONSTRAINT [DF_Employee_CurrentFlag] DEFAULT ((1)),
	[rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Employee_rowguid] DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY  CLUSTERED 
	(
		[EmployeeID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Employee_Contact_ContactID] FOREIGN KEY 
	(
		[ContactID]
	) REFERENCES [Contact] (
		[ContactID]
	),
	CONSTRAINT [FK_Employee_Employee_ManagerID] FOREIGN KEY 
	(
		[ManagerID]
	) REFERENCES [Employee] (
		[EmployeeID]
	),
	CONSTRAINT [CK_Employee_BirthDate] CHECK ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())),
	CONSTRAINT [CK_Employee_Gender] CHECK (upper([Gender])='F' OR upper([Gender])='M'),
	CONSTRAINT [CK_Employee_HireDate] CHECK ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())),
	CONSTRAINT [CK_Employee_MaritalStatus] CHECK (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M'),
	CONSTRAINT [CK_Employee_SickLeaveHours] CHECK ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)),
	CONSTRAINT [CK_Employee_VacationHours] CHECK ([VacationHours]>=(-40) AND [VacationHours]<=(240))
) ON [PRIMARY]
GO

Table: HumanResources.EmployeeAddress (go to dbdesc.com to register)

Cross-reference table mapping employees to their address(es).

Fields

Field name	Data type	Nullable	Default value	Field description
EmployeeID	int			Primary key. Foreign key to Employee.EmployeeID.
AddressID	int			Primary key. Foreign key to Address.AddressID.
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:

Name	Column	Reference	Foreign Key description
FK_EmployeeAddress_Employee_EmployeeID	EmployeeID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeAddress_Address_AddressID	AddressID	PK_Address_AddressID (Person.Address)	Foreign key constraint referencing Address.AddressID

Indices

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

Table definition

CREATE TABLE [HumanResources.EmployeeAddress] (
	[EmployeeID] [int] NOT NULL ,
	[AddressID] [int] NOT NULL ,
	[rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_EmployeeAddress_rowguid] DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeeAddress_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_EmployeeAddress_EmployeeID_AddressID] PRIMARY KEY  CLUSTERED 
	(
		[EmployeeID],
		[AddressID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_EmployeeAddress_Address_AddressID] FOREIGN KEY 
	(
		[AddressID]
	) REFERENCES [Address] (
		[AddressID]
	),
	CONSTRAINT [FK_EmployeeAddress_Employee_EmployeeID] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [Employee] (
		[EmployeeID]
	)
) ON [PRIMARY]
GO

Table: HumanResources.EmployeeDepartmentHistory (go to dbdesc.com to register)

Employee department transfers.

Fields

Field name	Data type	Nullable	Default value	Field description
EmployeeID	int			Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentID	smallint			Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftID	tinyint			Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
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:

Name	Column	Reference	Foreign Key description
FK_EmployeeDepartmentHistory_Employee_EmployeeID	EmployeeID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeDepartmentHistory_Department_DepartmentID	DepartmentID	PK_Department_DepartmentID (HumanResources.Department)	Foreign key constraint referencing Department.DepartmentID.
FK_EmployeeDepartmentHistory_Shift_ShiftID	ShiftID	PK_Shift_ShiftID (HumanResources.Shift)	Foreign key constraint referencing Shift.ShiftID

Indices

Index name	Column name	Sort 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
	DepartmentID	ASC	Yes	CLUSTERED
	ShiftID	ASC	Yes	CLUSTERED
	StartDate	ASC	Yes	CLUSTERED

Check constraints

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

Table definition

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 CONSTRAINT [DF_EmployeeDepartmentHistory_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID] PRIMARY KEY  CLUSTERED 
	(
		[EmployeeID],
		[StartDate],
		[DepartmentID],
		[ShiftID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID] FOREIGN KEY 
	(
		[DepartmentID]
	) REFERENCES [Department] (
		[DepartmentID]
	),
	CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_EmployeeID] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [Employee] (
		[EmployeeID]
	),
	CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID] FOREIGN KEY 
	(
		[ShiftID]
	) REFERENCES [Shift] (
		[ShiftID]
	),
	CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
) ON [PRIMARY]
GO

Table: HumanResources.EmployeePayHistory (go to dbdesc.com to register)

Employee pay history.

Fields

Field name	Data type	Nullable	Default value	Field description
EmployeeID	int			Employee identification number. Foreign key to Employee.EmployeeID.
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:

Name	Column	Reference	Foreign Key description
FK_EmployeePayHistory_Employee_EmployeeID	EmployeeID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.EmployeeID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_EmployeePayHistory_EmployeeID_RateChangeDate	EmployeeID	ASC	Yes	CLUSTERED
	RateChangeDate	ASC	Yes	CLUSTERED

Check constraints

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

Table definition

CREATE TABLE [HumanResources.EmployeePayHistory] (
	[EmployeeID] [int] NOT NULL ,
	[RateChangeDate] [datetime] NOT NULL ,
	[Rate] [money] NOT NULL ,
	[PayFrequency] [tinyint] NOT NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeePayHistory_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_EmployeePayHistory_EmployeeID_RateChangeDate] PRIMARY KEY  CLUSTERED 
	(
		[EmployeeID],
		[RateChangeDate]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_EmployeePayHistory_Employee_EmployeeID] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [Employee] (
		[EmployeeID]
	),
	CONSTRAINT [CK_EmployeePayHistory_PayFrequency] CHECK ([PayFrequency]=(2) OR [PayFrequency]=(1)),
	CONSTRAINT [CK_EmployeePayHistory_Rate] CHECK ([Rate]>=(6.50) AND [Rate]<=(200.00))
) ON [PRIMARY]
GO

Table: HumanResources.JobCandidate (go to dbdesc.com to register)

Résumés submitted to Human Resources by job applicants.

Fields

Field name	Data type	Nullable	Default value	Field description
JobCandidateID	int			Primary key for JobCandidate records.
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:

Name	Column	Reference	Foreign Key description
FK_JobCandidate_Employee_EmployeeID	EmployeeID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.EmployeeID.

Indices

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

Table definition

CREATE TABLE [HumanResources.JobCandidate] (
	[JobCandidateID] [int] IDENTITY (1, 1) NOT NULL ,
	[EmployeeID] [int] NULL ,
	[Resume] [xml] NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_JobCandidate_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_JobCandidate_JobCandidateID] PRIMARY KEY  CLUSTERED 
	(
		[JobCandidateID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_JobCandidate_Employee_EmployeeID] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [Employee] (
		[EmployeeID]
	)
) ON [PRIMARY]
GO

Table: HumanResources.Shift (go to dbdesc.com to register)

Work shift lookup table.

Fields

Field name	Data type	Nullable	Default value	Field description
ShiftID	tinyint			Primary key for Shift records.
Name	Name			Shift description.
StartTime	datetime			Shift start time.
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

Indices

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

Table definition

CREATE TABLE [HumanResources.Shift] (
	[ShiftID] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[Name] [Name] NOT NULL ,
	[StartTime] [datetime] NOT NULL ,
	[EndTime] [datetime] NOT NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Shift_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Shift_ShiftID] PRIMARY KEY  CLUSTERED 
	(
		[ShiftID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: Person.Address (go to dbdesc.com to register)

Street address information for customers, employees, and vendors.

Fields

Field name	Data type	Nullable	Default value	Field description
AddressID	int			Primary key for Address records.
AddressLine1	nvarchar (60)			First street address line.
AddressLine2	nvarchar (60)	Yes		Second street address line.
City	nvarchar (30)			Name of the city.
StateProvinceID	int			Unique identification number for the state or province. Foreign key to StateProvince table.
PostalCode	nvarchar (15)			Postal code for the street address.
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:

Name	Column	Reference	Foreign Key description
FK_Address_StateProvince_StateProvinceID	StateProvinceID	PK_StateProvince_StateProvinceID (Person.StateProvince)	Foreign key constraint referencing StateProvince.StateProvinceID.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Sales.SalesOrderHeader	FK_SalesOrderHeader_Address_BillToAddressID	PK_Address_AddressID
Sales.SalesOrderHeader	FK_SalesOrderHeader_Address_ShipToAddressID	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

Indices

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

Table definition

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 CONSTRAINT [DF_Address_rowguid] DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Address_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Address_AddressID] PRIMARY KEY  CLUSTERED 
	(
		[AddressID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_Address_StateProvince_StateProvinceID] FOREIGN KEY 
	(
		[StateProvinceID]
	) REFERENCES [StateProvince] (
		[StateProvinceID]
	)
) ON [PRIMARY]
GO

Table: Person.AddressType (go to dbdesc.com to register)

Types of addresses stored in the Address table.

Fields

Field name	Data type	Nullable	Default value	Field description
AddressTypeID	int			Primary key for AddressType records.
Name	Name			Address type description. For example, Billing, Home, or Shipping.
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
Purchasing.VendorAddress	FK_VendorAddress_AddressType_AddressTypeID	PK_AddressType_AddressTypeID
Sales.CustomerAddress	FK_CustomerAddress_AddressType_AddressTypeID	PK_AddressType_AddressTypeID

Indices

Index name	Column name	Sort 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

Table definition

CREATE TABLE [Person.AddressType] (
	[AddressTypeID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [Name] NOT NULL ,
	[rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_AddressType_rowguid] DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_AddressType_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY  CLUSTERED 
	(
		[AddressTypeID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: Person.Contact (go to dbdesc.com to register)

Names of each employee, customer contact, and vendor contact.

Fields

Field name	Data type	Nullable	Default value	Field description
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.
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.
AdditionalContactInfo	xml	Yes		Additional contact information about the person stored in xml format.
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.Individual	FK_Individual_Contact_ContactID	PK_Contact_ContactID
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

Indices

Index name	Column name	Sort 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	Check expresion
CK_Contact_EmailPromotion	EmailPromotion	([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))

Table definition

CREATE TABLE [Person.Contact] (
	[ContactID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[NameStyle] [NameStyle] NOT NULL CONSTRAINT [DF_Contact_NameStyle] DEFAULT ((0)),
	[Title] [nvarchar] (8) COLLATE Latin1_General_CS_AS NULL ,
	[FirstName] [Name] NOT NULL ,
	[MiddleName] [Name] NULL ,
	[LastName] [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 CONSTRAINT [DF_Contact_EmailPromotion] DEFAULT ((0)),
	[Phone] [Phone] NULL ,
	[PasswordHash] [varchar] (128) COLLATE Latin1_General_CS_AS NOT NULL ,
	[PasswordSalt] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
	[AdditionalContactInfo] [xml] NULL ,
	[rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Contact_rowguid] DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Contact_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Contact_ContactID] PRIMARY KEY  CLUSTERED 
	(
		[ContactID]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_Contact_EmailPromotion] CHECK ([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
) ON [PRIMARY]
GO

Table: Person.ContactType (go to dbdesc.com to register)

Lookup table containing the types of contacts stored in Contact.

Fields

Field name	Data type	Nullable	Default value	Field description
ContactTypeID	int			Primary key for ContactType records.
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
Sales.StoreContact	FK_StoreContact_ContactType_ContactTypeID	PK_ContactType_ContactTypeID
Purchasing.VendorContact	FK_VendorContact_ContactType_ContactTypeID	PK_ContactType_ContactTypeID

Indices

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

Table definition

CREATE TABLE [Person.ContactType] (
	[ContactTypeID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [Name] NOT NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_ContactType_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_ContactType_ContactTypeID] PRIMARY KEY  CLUSTERED 
	(
		[ContactTypeID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: Person.CountryRegion (go to dbdesc.com to register)

Lookup table containing the ISO standard codes for countries and regions.

Fields

Field name	Data type	Nullable	Default value	Field description
CountryRegionCode	nvarchar (3)			ISO standard code for countries and regions.
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

Indices

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

Table definition

CREATE TABLE [Person.CountryRegion] (
	[CountryRegionCode] [nvarchar] (3) COLLATE Latin1_General_CS_AS NOT NULL ,
	[Name] [Name] NOT NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_CountryRegion_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_CountryRegion_CountryRegionCode] PRIMARY KEY  CLUSTERED 
	(
		[CountryRegionCode]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Table: Person.StateProvince (go to dbdesc.com to register)

State and province lookup table.

Fields

Field name	Data type	Nullable	Default value	Field description
StateProvinceID	int			Primary key for StateProvince records.
StateProvinceCode	nchar (3)			ISO standard state or province code.
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.
Name	Name			State or province description.
TerritoryID	int			ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
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:

Name	Column	Reference	Foreign Key description
FK_StateProvince_CountryRegion_CountryRegionCode	CountryRegionCode	PK_CountryRegion_CountryRegionCode (Person.CountryRegion)	Foreign key constraint referencing CountryRegion.CountryRegionCode.
FK_StateProvince_SalesTerritory_TerritoryID	TerritoryID	PK_SalesTerritory_TerritoryID (Sales.SalesTerritory)	Foreign key constraint referencing SalesTerritory.TerritoryID.

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

Indices

Index name	Column name	Sort 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
	CountryRegionCode	ASC	Yes	NONCLUSTERED
PK_StateProvince_StateProvinceID	StateProvinceID	ASC	Yes	CLUSTERED

Table definition

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] [Flag] NOT NULL CONSTRAINT [DF_StateProvince_IsOnlyStateProvinceFlag] DEFAULT ((1)),
	[Name] [Name] NOT NULL ,
	[TerritoryID] [int] NOT NULL ,
	[rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_StateProvince_rowguid] DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_StateProvince_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_StateProvince_StateProvinceID] PRIMARY KEY  CLUSTERED 
	(
		[StateProvinceID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_StateProvince_CountryRegion_CountryRegionCode] FOREIGN KEY 
	(
		[CountryRegionCode]
	) REFERENCES [CountryRegion] (
		[CountryRegionCode]
	),
	CONSTRAINT [FK_StateProvince_SalesTerritory_TerritoryID] FOREIGN KEY 
	(
		[TerritoryID]
	) REFERENCES [SalesTerritory] (
		[TerritoryID]
	)
) ON [PRIMARY]
GO

Table: Production.BillOfMaterials (go to dbdesc.com to register)

Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Fields

Field name	Data type	Nullable	Default value	Field description
BillOfMaterialsID	int			Primary key for BillOfMaterials records.
ProductAssemblyID	int	Yes		Parent product identification number. Foreign key to Product.ProductID.
ComponentID	int			Component identification number. Foreign key to Product.ProductID.
StartDate	datetime		(getdate())	Date the compo