AdventureWorks database documentation

AdventureWorks database summary

AdventureWorks Sample OLTP Database

Database server:
Database size: 178.75 MB

70 tables
- 11 triggers
17 views
9 stored procedures
11 user defined functions
6 user defined data types
7 XML schemas
0 assemblies

Table dependencies matrix

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 )

Generators

Users

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

Roles

Role name	Is app role	Users with this role	Role description
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner		dbo, TestUser
db_securityadmin
public

Table: dbo.AWBuildVersion

Current version number of the AdventureWorks sample database.

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	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	Sort direction	Is 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.

Columns (8)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	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	Sort direction	Is 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.

Columns (9)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	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	Sort direction	Is unique	Index type
PK_ErrorLog_ErrorLogID	ErrorLogID	ASC	Yes	CLUSTERED

Objects that depend on dbo.ErrorLog:

▪ dbo.uspLogError (SP)

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 (dbdesc unregistered)

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

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	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.

Indexes

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

Objects that depend on HumanResources.Department (dbdesc unregistered):

▪ HumanResources.vEmployeeDepartment (VIEW)
▪ HumanResources.vEmployeeDepartmentHistory (VIEW)

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 (dbdesc unregistered)

Employee information such as salary, department, and title.

Columns (16)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	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	IX	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.

Indexes

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

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)

Check constraints:

Check name	Column name	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 (Created: 26 Apr 2006)
Trigger type: INSTEAD OF DELETE
Trigger active: YesTrigger 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 (dbdesc unregistered):

▪ dbo.uspGetEmployeeManagers (SP)
▪ dbo.uspGetManagerEmployees (SP)
▪ HumanResources.uspUpdateEmployeeHireInfo (SP)
▪ HumanResources.uspUpdateEmployeeLogin (SP)
▪ HumanResources.uspUpdateEmployeePersonalInfo (SP)
▪ HumanResources.vEmployee (VIEW)
▪ HumanResources.vEmployeeDepartment (VIEW)
▪ HumanResources.vEmployeeDepartmentHistory (VIEW)
▪ Sales.vSalesPerson (VIEW)
▪ Sales.vSalesPersonSalesByFiscalYears (VIEW)

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 (dbdesc unregistered)

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

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	EmployeeID	int				Primary key. Foreign key to Employee.EmployeeID.
PK	FK	IX	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.

Indexes

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

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)

Objects that depend on HumanResources.EmployeeAddress (dbdesc unregistered):

▪ HumanResources.vEmployee (VIEW)
▪ Sales.vSalesPerson (VIEW)

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 (dbdesc unregistered)

Employee department transfers.

Columns (6)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	EmployeeID	int				Employee identification number. Foreign key to Employee.EmployeeID.
PK	FK	IX	DepartmentID	smallint				Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
PK	FK	IX	ShiftID	tinyint				Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
PK		IX	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.

Indexes

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

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)

Check constraints:

Check name	Column name	Expresion
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 (dbdesc unregistered):

▪ HumanResources.vEmployeeDepartment (VIEW)
▪ HumanResources.vEmployeeDepartmentHistory (VIEW)

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 (dbdesc unregistered)

Employee pay history.

Columns (5)

			Name	Data type	Length	Is nullable	Default	Description
PK	FK	IX	EmployeeID	int				Employee identification number. Foreign key to Employee.EmployeeID.
PK		IX	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.

Indexes

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

Foreign keys dependencies:

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

Check constraints:

Check name	Column name	Expresion
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 (dbdesc unregistered):

▪ HumanResources.uspUpdateEmployeeHireInfo (SP)

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 (dbdesc unregistered)

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

Columns (4)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	JobCandidateID	int				Primary key for JobCandidate records.
	FK	IX	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.

Indexes

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

Foreign keys dependencies:

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

Objects that depend on HumanResources.JobCandidate (dbdesc unregistered):

▪ HumanResources.vJobCandidate (VIEW)
▪ HumanResources.vJobCandidateEducation (VIEW)
▪ HumanResources.vJobCandidateEmployment (VIEW)

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 (dbdesc unregistered)

Work shift lookup table.

Columns (5)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	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.

Indexes

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

Objects that depend on HumanResources.Shift (dbdesc unregistered):

▪ HumanResources.vEmployeeDepartmentHistory (VIEW)

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 (dbdesc unregistered)

Street address information for customers, employees, and vendors.

Columns (8)

			Name	Data type	Length	Is nullable	Default	Description
PK		IX	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	IX	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.

Indexes

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

Foreign keys dependencies:

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

Objects that depend on Person.Address (dbdesc unregistered):

▪ HumanResources.vEmployee (VIEW)
▪ Purchasing.vVendor (VIEW)
▪ Sales.vIndividualCustomer (VIEW)
▪ Sales.vSalesPerson (VIEW)
▪ Sales.vStoreWithDemographics (VIEW)

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 (dbdesc unregistered)

Types of addresses stored in the Address table.

Columns (4)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	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.

Indexes

Index name	Column name	Sort direction	Is 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 (dbdesc unregistered):

▪ Sales.vIndividualCustomer (VIEW)
▪ Sales.vStoreWithDemographics (VIEW)

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 (dbdesc unregistered)

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

Columns (15)

		Name	Data type	Length	Is nullable	Default	Description
PK	IX	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.

Indexes

Index name	Column name	Sort direction	Is 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	Expresion
CK_Contact_Ema