SQLServer recipes

We’ll always be using AdventureWorks for these:

(and there’s a new-ish SQLServer 2016 here)

SQLADW01 SQLADW02

SQLADW03

SQLADW04

SQLADW05

SQLADW06

SQLADW07

Microsoft SQL Server 2014 – 12.0.2361.0 (X64)
May 20 2014 15:40:06
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Started – 2015-10-22 21:30:36.110

*** Dropping Database

*** Creating Database

*** Checking for AdventureWorks2014 Database

*** Creating DDL Trigger for Database

*** Creating Error Log objects
CREATE_TABLE – dbo.ErrorLog
ALTER_TABLE – dbo.ErrorLog
CREATE_PROCEDURE – dbo.uspPrintError
CREATE_PROCEDURE – dbo.uspLogError

*** Creating Data Types
CREATE_TYPE – dbo.AccountNumber
CREATE_TYPE – dbo.Flag
(snip)

*** Creating Pre-Table Database Functions
CREATE_FUNCTION – dbo.ufnLeadingZeros

*** Creating Database Schemas
CREATE_SCHEMA – HumanResources.HumanResources
CREATE_SCHEMA – Person.Person
CREATE_SCHEMA – Production.Production
CREATE_SCHEMA – Purchasing.Purchasing
CREATE_SCHEMA – Sales.Sales

*** Creating XML Schemas

Create AdditionalContactInfo schema
CREATE_XML_SCHEMA_COLLECTION – Person.AdditionalContactInfoSchemaCollection
ALTER_XML_SCHEMA_COLLECTION – Person.AdditionalContactInfoSchemaCollection
ALTER_XML_SCHEMA_COLLECTION – Person.AdditionalContactInfoSchemaCollection

Create Individual survey schema
CREATE_XML_SCHEMA_COLLECTION – Person.IndividualSurveySchemaCollection

Create Resume schema
CREATE_XML_SCHEMA_COLLECTION – HumanResources.HRResumeSchemaCollection

Create Product catalog description schema
CREATE_XML_SCHEMA_COLLECTION – Production.ProductDescriptionSchemaCollection
ALTER_XML_SCHEMA_COLLECTION – Production.ProductDescriptionSchemaCollection

Create Manufacturing instructions schema
CREATE_XML_SCHEMA_COLLECTION – Production.ManuInstructionsSchemaCollection

Create Store survey schema
CREATE_XML_SCHEMA_COLLECTION – Sales.StoreSurveySchemaCollection

*** Creating Tables
CREATE_TABLE – Person.Address
CREATE_TABLE – Person.AddressType
CREATE_TABLE – dbo.AWBuildVersion
(snip)
CREATE_TABLE – Production.WorkOrderRouting

*** Loading Data
Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
Loading [Person].[Address]
Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

(19614 row(s) affected)
Loading [Person].[AddressType]

(6 row(s) affected)
Loading [dbo].[AWBuildVersion]

((snip)

(72591 row(s) affected)
Loading [Production].[WorkOrderRouting]

(67131 row(s) affected)

*** Adding Primary Keys
ALTER_TABLE – Person.Address
ALTER_TABLE – Person.AddressType
ALTER_TABLE – dbo.AWBuildVersion
(snip)
ALTER_TABLE – Production.WorkOrderRouting

*** Adding Indexes
CREATE_INDEX – Person.AK_Address_rowguid
(snip)
CREATE_INDEX – Production.IX_WorkOrder_ProductID
CREATE_INDEX – Production.IX_WorkOrderRouting_ProductID

*** Creating XML index for each XML column
CREATE_XML_INDEX – Person.PXML_Person_AddContact
CREATE_XML_INDEX – Person.PXML_Person_Demographics
CREATE_XML_INDEX – Person.XMLPATH_Person_Demographics
CREATE_XML_INDEX – Person.XMLPROPERTY_Person_Demographics
CREATE_XML_INDEX – Person.XMLVALUE_Person_Demographics
CREATE_XML_INDEX – Sales.PXML_Store_Demographics
CREATE_XML_INDEX – Production.PXML_ProductModel_CatalogDescription
CREATE_XML_INDEX – Production.PXML_ProductModel_Instructions

*** Creating Full Text catalog and indexes

CREATE_FULLTEXT_INDEX – Production.ProductReview
CREATE_FULLTEXT_INDEX – HumanResources.JobCandidate
CREATE_FULLTEXT_INDEX – Production.Document

*** Creating Foreign Key Constraints
ALTER_TABLE – Person.Address
ALTER_TABLE – Production.BillOfMaterials
ALTER_TABLE – Person.BusinessEntityAddress
(snip)
ALTER_TABLE – Production.WorkOrderRouting

*** Creating Table Triggers
CREATE_TRIGGER – HumanResources.dEmployee
CREATE_TRIGGER – Person.iuPerson
CREATE_TRIGGER – Purchasing.iPurchaseOrderDetail
CREATE_TRIGGER – Purchasing.uPurchaseOrderDetail
CREATE_TRIGGER – Purchasing.uPurchaseOrderHeader
CREATE_TRIGGER – Sales.iduSalesOrderDetail
CREATE_TRIGGER – Sales.uSalesOrderHeader
CREATE_TRIGGER – Purchasing.dVendor
CREATE_TRIGGER – Production.iWorkOrder
CREATE_TRIGGER – Production.uWorkOrder

*** Creating Table Views
CREATE_VIEW – Person.vAdditionalContactInfo
(snip)
CREATE_VIEW – Purchasing.vVendorWithAddresses

*** Creating Database Functions
CREATE_FUNCTION – dbo.ufnGetAccountingStartDate
(snip)
CREATE_FUNCTION – dbo.ufnGetSalesOrderStatusText

*** Creating Stored Procedures
CREATE_PROCEDURE – dbo.uspGetBillOfMaterials
(snip)
CREATE_PROCEDURE – dbo.uspSearchCandidateResumes

*** Creating Extended Properties
Database
CREATE_EXTENDED_PROPERTY – .AdventureWorks2014
CREATE_EXTENDED_PROPERTY – .ddlDatabaseTriggerLog
Files and Filegroups
CREATE_EXTENDED_PROPERTY – .PRIMARY
CREATE_EXTENDED_PROPERTY – .AdventureWorks2014_Data
Schemas
CREATE_EXTENDED_PROPERTY – .HumanResources
CREATE_EXTENDED_PROPERTY – .Production
CREATE_EXTENDED_PROPERTY – .Purchasing
CREATE_EXTENDED_PROPERTY – .Sales
CREATE_EXTENDED_PROPERTY – .Person
Tables and Columns
CREATE_EXTENDED_PROPERTY – Person.Address
(snip)
CREATE_EXTENDED_PROPERTY – Production.ModifiedDate
Triggers
CREATE_EXTENDED_PROPERTY – HumanResources.dEmployee
(snip)
CREATE_EXTENDED_PROPERTY – Production.uWorkOrder
Views
CREATE_EXTENDED_PROPERTY – Person.vAdditionalContactInfo
(snip)

CREATE_EXTENDED_PROPERTY – Production.ProductDescriptionSchemaCollection
CREATE_EXTENDED_PROPERTY – Sales.StoreSurveySchemaCollection

*** Disabling DDL Trigger for Database

*** Changing File Growth Values for Database

*** Shrinking Database

(2 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Finished – 2015-10-22 21:32:26.763

AND WE’RE DONE!

SQLADW09

SQLADW10

SQLADW11

Documentation

Microsoft maintain a database diagram for AdventureWorks here.

AdWorksDiag01

, and there are docs on installation here – there are various docs – pick the ones that match your specific download:

AdWorksDiag02