AdventureWorks

AdventureWorks Sample OLTP Database14/07/2008
178.75 MB

Summary

Tables

Table: dbo.AWBuildVersion

Current version number of the AdventureWorks sample database.

Fields

Field name Data type Nullable Default value Field description
SystemInformationIDtinyintPrimary key for AWBuildVersion records.
Database Versionnvarchar (25) Version number of the database in 9.yy.mm.dd.00 format.
VersionDatedatetimeDate and time the record was last updated.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Indices

Index nameColumn nameSort directionUniqueIndex type
PK_AWBuildVersion_SystemInformationIDSystemInformationIDASCYesCLUSTERED

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
DatabaseLogIDintPrimary key for DatabaseLog records.
PostTimedatetimeThe date and time the DDL change occurred.
DatabaseUsernvarchar (128) The user who implemented the DDL change.
Eventnvarchar (128) The type of DDL statement that was executed.
Schemanvarchar (128) YesThe schema to which the changed object belongs.
Objectnvarchar (128) YesThe object that was changed by the DDL statment.
TSQLnvarcharThe exact Transact-SQL statement that was executed.
XmlEventxmlThe raw XML data generated by database trigger.

Indices

Index nameColumn nameSort directionUniqueIndex type
PK_DatabaseLog_DatabaseLogIDDatabaseLogIDASCYesNONCLUSTERED

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
ErrorLogIDintPrimary key for ErrorLog records.
ErrorTimedatetime(getdate())The date and time at which the error occurred.
UserNamenvarchar (128) The user who executed the batch in which the error occurred.
ErrorNumberintThe error number of the error that occurred.
ErrorSeverityintYesThe severity of the error that occurred.
ErrorStateintYesThe state number of the error that occurred.
ErrorProcedurenvarchar (126) YesThe name of the stored procedure or trigger where the error occurred.
ErrorLineintYesThe line number at which the error occurred.
ErrorMessagenvarchar (4000) The message text of the error that occurred.

Indices

Index nameColumn nameSort directionUniqueIndex type
PK_ErrorLog_ErrorLogIDErrorLogIDASCYesCLUSTERED

Related objects

stored proceduredbo.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
DepartmentIDsmallintPrimary key for Department records.
NameNameName of the department.
GroupNameNameName of the group to which the department belongs.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
HumanResources.EmployeeDepartmentHistoryFK_EmployeeDepartmentHistory_Department_DepartmentIDPK_Department_DepartmentID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_Department_NameNameASCYesNONCLUSTERED
PK_Department_DepartmentIDDepartmentIDASCYesCLUSTERED

Related objects

viewHumanResources.vEmployeeDepartment
viewHumanResources.vEmployeeDepartmentHistory

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
EmployeeIDintPrimary key for Employee records.
NationalIDNumbernvarchar (15) Unique national identification number such as a social security number.
ContactIDintIdentifies the employee in the Contact table. Foreign key to Contact.ContactID.
LoginIDnvarchar (256) Network login.
ManagerIDintYesManager to whom the employee is assigned. Foreign Key to Employee.M
Titlenvarchar (50) Work title such as Buyer or Sales Representative.
BirthDatedatetimeDate of birth.
MaritalStatusnchar (1) M = Married, S = Single
Gendernchar (1) M = Male, F = Female
HireDatedatetimeEmployee hired on this date.
SalariedFlagFlag((1))Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHourssmallint((0))Number of available vacation hours.
SickLeaveHourssmallint((0))Number of available sick leave hours.
CurrentFlagFlag((1))0 = Inactive, 1 = Active
rowguiduniqueidentifier(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Foreign keys dependencies:

NameColumnReferenceForeign Key description
FK_Employee_Contact_ContactIDContactIDPK_Contact_ContactID (Person.Contact) Foreign key constraint referencing Contact.ContactID.
FK_Employee_Employee_ManagerIDManagerIDPK_Employee_EmployeeID (HumanResources.Employee) Foreign key constraint referencing Employee.ManagerID.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
HumanResources.JobCandidateFK_JobCandidate_Employee_EmployeeIDPK_Employee_EmployeeID
Purchasing.PurchaseOrderHeaderFK_PurchaseOrderHeader_Employee_EmployeeIDPK_Employee_EmployeeID
Sales.SalesPersonFK_SalesPerson_Employee_SalesPersonIDPK_Employee_EmployeeID
HumanResources.EmployeeFK_Employee_Employee_ManagerIDPK_Employee_EmployeeID
HumanResources.EmployeeAddressFK_EmployeeAddress_Employee_EmployeeIDPK_Employee_EmployeeID
HumanResources.EmployeeDepartmentHistoryFK_EmployeeDepartmentHistory_Employee_EmployeeIDPK_Employee_EmployeeID
HumanResources.EmployeePayHistoryFK_EmployeePayHistory_Employee_EmployeeIDPK_Employee_EmployeeID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_Employee_LoginIDLoginIDASCYesNONCLUSTERED
AK_Employee_NationalIDNumberNationalIDNumberASCYesNONCLUSTERED
AK_Employee_rowguidrowguidASCYesNONCLUSTERED
IX_Employee_ManagerIDManagerIDASCNONCLUSTERED
PK_Employee_EmployeeIDEmployeeIDASCYesCLUSTERED

Check constraints

Check nameColumn nameCheck expresion
CK_Employee_BirthDateBirthDate([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
CK_Employee_MaritalStatusMaritalStatus(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
CK_Employee_HireDateHireDate([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
CK_Employee_GenderGender(upper([Gender])='F' OR upper([Gender])='M')
CK_Employee_VacationHoursVacationHours([VacationHours]>=(-40) AND [VacationHours]<=(240))
CK_Employee_SickLeaveHoursSickLeaveHours([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;

Related objects

stored proceduredbo.uspGetEmployeeManagers
stored proceduredbo.uspGetManagerEmployees
stored procedureHumanResources.uspUpdateEmployeeHireInfo
stored procedureHumanResources.uspUpdateEmployeeLogin
stored procedureHumanResources.uspUpdateEmployeePersonalInfo
viewHumanResources.vEmployee
viewHumanResources.vEmployeeDepartment
viewHumanResources.vEmployeeDepartmentHistory
viewSales.vSalesPerson
viewSales.vSalesPersonSalesByFiscalYears

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
EmployeeIDintPrimary key. Foreign key to Employee.EmployeeID.
AddressIDintPrimary key. Foreign key to Address.AddressID.
rowguiduniqueidentifier(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Foreign keys dependencies:

NameColumnReferenceForeign Key description
FK_EmployeeAddress_Employee_EmployeeIDEmployeeIDPK_Employee_EmployeeID (HumanResources.Employee) Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeAddress_Address_AddressIDAddressIDPK_Address_AddressID (Person.Address) Foreign key constraint referencing Address.AddressID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_EmployeeAddress_rowguidrowguidASCYesNONCLUSTERED
PK_EmployeeAddress_EmployeeID_AddressIDEmployeeIDASCYesCLUSTERED
AddressIDASCYesCLUSTERED

Related objects

viewHumanResources.vEmployee
viewSales.vSalesPerson

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
EmployeeIDintEmployee identification number. Foreign key to Employee.EmployeeID.
DepartmentIDsmallintDepartment in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftIDtinyintIdentifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDatedatetimeDate the employee started work in the department.
EndDatedatetimeYesDate the employee left the department. NULL = Current department.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Foreign keys dependencies:

NameColumnReferenceForeign Key description
FK_EmployeeDepartmentHistory_Employee_EmployeeIDEmployeeIDPK_Employee_EmployeeID (HumanResources.Employee) Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeDepartmentHistory_Department_DepartmentIDDepartmentIDPK_Department_DepartmentID (HumanResources.Department) Foreign key constraint referencing Department.DepartmentID.
FK_EmployeeDepartmentHistory_Shift_ShiftIDShiftIDPK_Shift_ShiftID (HumanResources.Shift) Foreign key constraint referencing Shift.ShiftID

Indices

Index nameColumn nameSort directionUniqueIndex type
IX_EmployeeDepartmentHistory_DepartmentIDDepartmentIDASCNONCLUSTERED
IX_EmployeeDepartmentHistory_ShiftIDShiftIDASCNONCLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentIDEmployeeIDASCYesCLUSTERED
DepartmentIDASCYesCLUSTERED
ShiftIDASCYesCLUSTERED
StartDateASCYesCLUSTERED

Check constraints

Check nameColumn nameCheck expresion
CK_EmployeeDepartmentHistory_EndDateStartDate([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_EmployeeDepartmentHistory_EndDateEndDate([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Related objects

viewHumanResources.vEmployeeDepartment
viewHumanResources.vEmployeeDepartmentHistory

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
EmployeeIDintEmployee identification number. Foreign key to Employee.EmployeeID.
RateChangeDatedatetimeDate the change in pay is effective
RatemoneySalary hourly rate.
PayFrequencytinyint1 = Salary received monthly, 2 = Salary received biweekly
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Foreign keys dependencies:

NameColumnReferenceForeign Key description
FK_EmployeePayHistory_Employee_EmployeeIDEmployeeIDPK_Employee_EmployeeID (HumanResources.Employee) Foreign key constraint referencing Employee.EmployeeID.

Indices

Index nameColumn nameSort directionUniqueIndex type
PK_EmployeePayHistory_EmployeeID_RateChangeDateEmployeeIDASCYesCLUSTERED
RateChangeDateASCYesCLUSTERED

Check constraints

Check nameColumn nameCheck expresion
CK_EmployeePayHistory_PayFrequencyPayFrequency([PayFrequency]=(2) OR [PayFrequency]=(1))
CK_EmployeePayHistory_RateRate([Rate]>=(6.50) AND [Rate]<=(200.00))

Related objects

stored procedureHumanResources.uspUpdateEmployeeHireInfo

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
JobCandidateIDintPrimary key for JobCandidate records.
EmployeeIDintYesEmployee identification number if applicant was hired. Foreign key to Employee.EmployeeID.
ResumexmlYesRésumé in XML format.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Foreign keys dependencies:

NameColumnReferenceForeign Key description
FK_JobCandidate_Employee_EmployeeIDEmployeeIDPK_Employee_EmployeeID (HumanResources.Employee) Foreign key constraint referencing Employee.EmployeeID.

Indices

Index nameColumn nameSort directionUniqueIndex type
IX_JobCandidate_EmployeeIDEmployeeIDASCNONCLUSTERED
PK_JobCandidate_JobCandidateIDJobCandidateIDASCYesCLUSTERED

Related objects

viewHumanResources.vJobCandidate
viewHumanResources.vJobCandidateEducation
viewHumanResources.vJobCandidateEmployment

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
ShiftIDtinyintPrimary key for Shift records.
NameNameShift description.
StartTimedatetimeShift start time.
EndTimedatetimeShift end time.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
HumanResources.EmployeeDepartmentHistoryFK_EmployeeDepartmentHistory_Shift_ShiftIDPK_Shift_ShiftID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_Shift_NameNameASCYesNONCLUSTERED
AK_Shift_StartTime_EndTimeStartTimeASCYesNONCLUSTERED
EndTimeASCYesNONCLUSTERED
PK_Shift_ShiftIDShiftIDASCYesCLUSTERED

Related objects

viewHumanResources.vEmployeeDepartmentHistory

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
AddressIDintPrimary key for Address records.
AddressLine1nvarchar (60) First street address line.
AddressLine2nvarchar (60) YesSecond street address line.
Citynvarchar (30) Name of the city.
StateProvinceIDintUnique identification number for the state or province. Foreign key to StateProvince table.
PostalCodenvarchar (15) Postal code for the street address.
rowguiduniqueidentifier(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Foreign keys dependencies:

NameColumnReferenceForeign Key description
FK_Address_StateProvince_StateProvinceIDStateProvinceIDPK_StateProvince_StateProvinceID (Person.StateProvince) Foreign key constraint referencing StateProvince.StateProvinceID.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
Sales.SalesOrderHeaderFK_SalesOrderHeader_Address_BillToAddressIDPK_Address_AddressID
Sales.SalesOrderHeaderFK_SalesOrderHeader_Address_ShipToAddressIDPK_Address_AddressID
Purchasing.VendorAddressFK_VendorAddress_Address_AddressIDPK_Address_AddressID
Sales.CustomerAddressFK_CustomerAddress_Address_AddressIDPK_Address_AddressID
HumanResources.EmployeeAddressFK_EmployeeAddress_Address_AddressIDPK_Address_AddressID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_Address_rowguidrowguidASCYesNONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCodeAddressLine1ASCYesNONCLUSTERED
AddressLine2ASCYesNONCLUSTERED
CityASCYesNONCLUSTERED
StateProvinceIDASCYesNONCLUSTERED
PostalCodeASCYesNONCLUSTERED
IX_Address_StateProvinceIDStateProvinceIDASCNONCLUSTERED
PK_Address_AddressIDAddressIDASCYesCLUSTERED

Related objects

viewHumanResources.vEmployee
viewPurchasing.vVendor
viewSales.vIndividualCustomer
viewSales.vSalesPerson
viewSales.vStoreWithDemographics

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
AddressTypeIDintPrimary key for AddressType records.
NameNameAddress type description. For example, Billing, Home, or Shipping.
rowguiduniqueidentifier(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
Purchasing.VendorAddressFK_VendorAddress_AddressType_AddressTypeIDPK_AddressType_AddressTypeID
Sales.CustomerAddressFK_CustomerAddress_AddressType_AddressTypeIDPK_AddressType_AddressTypeID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_AddressType_NameNameASCYesNONCLUSTERED
AK_AddressType_rowguidrowguidASCYesNONCLUSTERED
PK_AddressType_AddressTypeIDAddressTypeIDASCYesCLUSTERED

Related objects

viewSales.vIndividualCustomer
viewSales.vStoreWithDemographics

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
ContactIDintPrimary key for Contact records.
NameStyleNameStyle((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.
Titlenvarchar (8) YesA courtesy title. For example, Mr. or Ms.
FirstNameNameFirst name of the person.
MiddleNameNameYesMiddle name or middle initial of the person.
LastNameNameLast name of the person.
Suffixnvarchar (10) YesSurname suffix. For example, Sr. or Jr.
EmailAddressnvarchar (50) YesE-mail address for the person.
EmailPromotionint((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.
PhonePhoneYesPhone number associated with the person.
PasswordHashvarchar (128) Password for the e-mail account.
PasswordSaltvarchar (10) Random value concatenated with the password string before the password is hashed.
AdditionalContactInfoxmlYesAdditional contact information about the person stored in xml format.
rowguiduniqueidentifier(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
Sales.IndividualFK_Individual_Contact_ContactIDPK_Contact_ContactID
Sales.SalesOrderHeaderFK_SalesOrderHeader_Contact_ContactIDPK_Contact_ContactID
Sales.StoreContactFK_StoreContact_Contact_ContactIDPK_Contact_ContactID
Purchasing.VendorContactFK_VendorContact_Contact_ContactIDPK_Contact_ContactID
Sales.ContactCreditCardFK_ContactCreditCard_Contact_ContactIDPK_Contact_ContactID
HumanResources.EmployeeFK_Employee_Contact_ContactIDPK_Contact_ContactID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_Contact_rowguidrowguidASCYesNONCLUSTERED
IX_Contact_EmailAddressEmailAddressASCNONCLUSTERED
PK_Contact_ContactIDContactIDASCYesCLUSTERED
PXML_Contact_AddContactAdditionalContactInfoASCXML

Check constraints

Check nameColumn nameCheck expresion
CK_Contact_EmailPromotionEmailPromotion([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))

Related objects

stored proceduredbo.uspGetEmployeeManagers
stored proceduredbo.uspGetManagerEmployees
viewHumanResources.vEmployee
viewHumanResources.vEmployeeDepartment
viewHumanResources.vEmployeeDepartmentHistory
viewPerson.vAdditionalContactInfo
viewPurchasing.vVendor
viewSales.vIndividualCustomer
viewSales.vSalesPerson
viewSales.vSalesPersonSalesByFiscalYears
viewSales.vStoreWithDemographics

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
ContactTypeIDintPrimary key for ContactType records.
NameNameContact type description.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
Sales.StoreContactFK_StoreContact_ContactType_ContactTypeIDPK_ContactType_ContactTypeID
Purchasing.VendorContactFK_VendorContact_ContactType_ContactTypeIDPK_ContactType_ContactTypeID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_ContactType_NameNameASCYesNONCLUSTERED
PK_ContactType_ContactTypeIDContactTypeIDASCYesCLUSTERED

Related objects

viewPurchasing.vVendor
viewSales.vStoreWithDemographics

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
CountryRegionCodenvarchar (3) ISO standard code for countries and regions.
NameNameCountry or region name.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
Person.StateProvinceFK_StateProvince_CountryRegion_CountryRegionCodePK_CountryRegion_CountryRegionCode
Sales.CountryRegionCurrencyFK_CountryRegionCurrency_CountryRegion_CountryRegionCodePK_CountryRegion_CountryRegionCode

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_CountryRegion_NameNameASCYesNONCLUSTERED
PK_CountryRegion_CountryRegionCodeCountryRegionCodeASCYesCLUSTERED

Related objects

viewHumanResources.vEmployee
viewPerson.vStateProvinceCountryRegion
viewPurchasing.vVendor
viewSales.vIndividualCustomer
viewSales.vSalesPerson
viewSales.vStoreWithDemographics

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
StateProvinceIDintPrimary key for StateProvince records.
StateProvinceCodenchar (3) ISO standard state or province code.
CountryRegionCodenvarchar (3) ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
IsOnlyStateProvinceFlagFlag((1))0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
NameNameState or province description.
TerritoryIDintID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
rowguiduniqueidentifier(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetime(getdate())Date and time the record was last updated.

Foreign keys dependencies:

NameColumnReferenceForeign Key description
FK_StateProvince_CountryRegion_CountryRegionCodeCountryRegionCodePK_CountryRegion_CountryRegionCode (Person.CountryRegion) Foreign key constraint referencing CountryRegion.CountryRegionCode.
FK_StateProvince_SalesTerritory_TerritoryIDTerritoryIDPK_SalesTerritory_TerritoryID (Sales.SalesTerritory) Foreign key constraint referencing SalesTerritory.TerritoryID.

Referencing tables

Table nameForeign keyPrimary key or unique constraint
Sales.SalesTaxRateFK_SalesTaxRate_StateProvince_StateProvinceIDPK_StateProvince_StateProvinceID
Person.AddressFK_Address_StateProvince_StateProvinceIDPK_StateProvince_StateProvinceID

Indices

Index nameColumn nameSort directionUniqueIndex type
AK_StateProvince_NameNameASCYesNONCLUSTERED
AK_StateProvince_rowguidrowguidASCYesNONCLUSTERED
AK_StateProvince_StateProvinceCode_CountryRegionCodeStateProvinceCodeASCYesNONCLUSTERED
CountryRegionCodeASCYesNONCLUSTERED
PK_StateProvince_StateProvinceIDStateProvinceIDASCYesCLUSTERED

Related objects

viewHumanResources.vEmployee
viewPerson.vStateProvinceCountryRegion
viewPurchasing.vVendor
viewSales.vIndividualCustomer
viewSales.vSalesPerson
viewSales.vStoreWithDemographics

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
BillOfMaterialsIDintPrimary key for BillOfMaterials records.
ProductAssemblyIDintYesParent product identification number. Foreign key to Product.ProductID.
ComponentIDintComponent identification number. Foreign key to Product.ProductID.
StartDatedatetime(getdate())Date the compo