AdventureWorks database

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

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

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

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

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;

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE [Production.BillOfMaterials] (
	[BillOfMaterialsID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductAssemblyID] [int] NULL ,
	[ComponentID] [int] NOT NULL ,
	[StartDate] [datetime] NOT NULL CONSTRAINT [DF_BillOfMaterials_StartDate] DEFAULT (getdate()),
	[EndDate] [datetime] NULL ,
	[UnitMeasureCode] [nchar] (3) COLLATE Latin1_General_CS_AS NOT NULL ,
	[BOMLevel] [smallint] NOT NULL ,
	[PerAssemblyQty] [decimal](8, 2) NOT NULL CONSTRAINT [DF_BillOfMaterials_PerAssemblyQty] DEFAULT ((1.00)),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_BillOfMaterials_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_BillOfMaterials_BillOfMaterialsID] PRIMARY KEY  NONCLUSTERED 
	(
		[BillOfMaterialsID]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_BillOfMaterials_Product_ComponentID] FOREIGN KEY 
	(
		[ComponentID]
	) REFERENCES [Product] (
		[ProductID]
	),
	CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID] FOREIGN KEY 
	(
		[ProductAssemblyID]
	) REFERENCES [Product] (
		[ProductID]
	),
	CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode] FOREIGN KEY 
	(
		[UnitMeasureCode]
	) REFERENCES [UnitMeasure] (
		[UnitMeasureCode]
	),
	CONSTRAINT [CK_BillOfMaterials_BOMLevel] CHECK ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)),
	CONSTRAINT [CK_BillOfMaterials_EndDate] CHECK ([EndDate]>[StartDate] OR [EndDate] IS NULL),
	CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty] CHECK ([PerAssemblyQty]>=(1.00)),
	CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID] CHECK ([ProductAssemblyID]<>[ComponentID])
) ON [PRIMARY]
GO

CREATE TABLE [Production.Culture] (
	[CultureID] [nchar] (6) COLLATE Latin1_General_CS_AS NOT NULL ,
	[Name] [Name] NOT NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Culture_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Culture_CultureID] PRIMARY KEY  CLUSTERED 
	(
		[CultureID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [Production.Document] (
	[DocumentID] [int] IDENTITY (1, 1) NOT NULL ,
	[Title] [nvarchar] (50) COLLATE Latin1_General_CS_AS NOT NULL ,
	[FileName] [nvarchar] (400) COLLATE Latin1_General_CS_AS NOT NULL ,
	[FileExtension] [nvarchar] (8) COLLATE Latin1_General_CS_AS NOT NULL ,
	[Revision] [nchar] (5) COLLATE Latin1_General_CS_AS NOT NULL ,
	[ChangeNumber] [int] NOT NULL CONSTRAINT [DF_Document_ChangeNumber] DEFAULT ((0)),
	[Status] [tinyint] NOT NULL ,
	[DocumentSummary] [nvarchar] (0) COLLATE Latin1_General_CS_AS NULL ,
	[Document] [varbinary] (-1) NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Document_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Document_DocumentID] PRIMARY KEY  CLUSTERED 
	(
		[DocumentID]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_Document_Status] CHECK ([Status]>=(1) AND [Status]<=(3))
) ON [PRIMARY]
GO

CREATE TABLE [Production.Illustration] (
	[IllustrationID] [int] IDENTITY (1, 1) NOT NULL ,
	[Diagram] [xml] NULL ,
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Illustration_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Illustration_IllustrationID] PRIMARY KEY  CLUSTERED 
	(
		[IllustrationID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [Production.Location] (
	[LocationID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Name] [Name] NOT NULL ,
	[CostRate] [smallmoney] NOT NULL CONSTRAINT [DF_Location_CostRate] DEFAULT ((0.00)),
	[Availability] [decimal](8, 2) NOT NULL CONSTRAINT [DF_Location_Availability] DEFAULT ((0.00)),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Location_ModifiedDate] DEFAULT (getdate()),
	CONSTRAINT [PK_Location_LocationID] PRIMARY KEY  CLUSTERED 
	(
		[LocationID]
	)  ON [PRIMARY] ,
	CONSTRAINT [CK_Location_Availability] CHECK ([Availability]>=(0.00)),
	CONSTRAINT [CK_Location_CostRate] CHECK ([CostRate]>=(0.00))
) ON [PRIMARY]
GO