-- This script builds the Northgale database by inflating the data in Northwind.
-- Use Northwind.sql to build Northwind.
-- Depending on where you run the script, different things may happen:
-- On SQL Server on-prem: The script drops any existing database called Northgale
--    creates a new one in your default folders. The scripts creates two log files of 
--    which one is dropped at the end. There must be a database Northwind. The version of
--    SQL Server must be SQL 2008 or later. If these conditions are not fulfilled, the
--    scripts attempts to abort.
-- Azure SQL Managed Instance: In difference to on-prem, any existing database is not 
--    dropped (since Single User is not supported), but all Northgale tables are dropped
--    and recreated. Other tables are not touched. The must be a Northwind database on
--    the instance.
-- Azure SQL Database: You must first run Northwind.sql in the database. This script
--    will then create a schema Northwind and move all known tables there. If the 
--    Northwind schema already exists, the script will assume that the tables in the
--    dbo schema are Northgale and drop and create these.
--    Note that when running in Azure SQL Database you will get errors for the USE
--    statements in the script. Please ignore these.
-- Synapse, Fabric Datawarehouse etc - not supported, you are on your own.

-- Good SET commands.
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET DATEFORMAT mdy
SET XACT_ABORT ON
go
-- This statement will cause an error on Azure SQL Database. Please just ignore.
USE tempdb
go
-- Check the SQL Server version.
DECLARE @version nvarchar(250)
SELECT @version = convert(nvarchar(250), serverproperty('ProductVersion'))
IF @version IS NULL OR convert(int, substring(@version, 1, charindex('.', @version) - 1)) < 10
BEGIN 
   RAISERROR('This script requires SQL 2008 or higher and will attempt to abort.', 16, 127)
   RAISERROR('Trying to abort the connection now.', 20, 1) WITH LOG
END
GO
-- Unless we are on Azure SQL Database, try to error out if Northwind is not there.
IF convert(int, serverproperty('EngineEdition')) <> 5 AND db_id('Northwind') IS NULL
BEGIN
   RAISERROR('Cannot find any Northwind database.', 16, 127)
   RAISERROR('Trying to abort the connection now.', 20, 1) WITH LOG
END
go
-- Drop any existing database if we are on-prem.
IF convert(int, serverproperty('EngineEdition')) <= 4 AND db_id('Northgale') IS NOT NULL
BEGIN
   ALTER DATABASE Northgale SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   DROP DATABASE Northgale
END
go
-- On MI Create database plainly, if it not does exist.
IF convert(int, serverproperty('EngineEdition')) = 8 AND db_id('Northgale') IS NULL
   CREATE DATABASE Northgale
go
-- On on-prem, we create the database in a more sophisticated way.
IF convert(int, serverproperty('EngineEdition')) <= 4
BEGIN
   DECLARE @datadirectory nvarchar(520), 
           @logdirectory  nvarchar(520)
   
   -- Try to get default directories for data and log files.
   SELECT @datadirectory = convert(nvarchar(520), serverproperty('InstanceDefaultDataPath')),
          @logdirectory  = convert(nvarchar(520), serverproperty('InstanceDefaultLogPath'))
   
   -- But these serverproperties is not available in SQL 2008, so we use the location
   -- of the master database as a fallback.
   IF @datadirectory IS NULL OR @logdirectory IS NULL
   BEGIN
      SELECT @datadirectory = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
      FROM   sys.master_files
      WHERE  database_id = 1 
        AND  file_id = 1
      SELECT @logdirectory = @datadirectory
   END

   DECLARE @sql nvarchar(MAX)
   SELECT @sql = N'CREATE DATABASE Northgale
                    ON PRIMARY (NAME = N''Northgale'', 
                                FILENAME = N' + quotename(@datadirectory + N'Northgale.mdf', '''') + 
                                ', SIZE = 1300 MB)
                    LOG ON (NAME = N''Northgale_log'', 
                            FILENAME = N'+ quotename(@logdirectory + N'Northgale.ldf', '''') + 
                            ', SIZE = 20 MB)'
   PRINT @sql
   EXEC(@sql)

   SELECT @sql = N'ALTER DATABASE Northgale
                   ADD LOG FILE (NAME = N''Northgale_log2'',
                                 FILENAME = N' + quotename(@logdirectory + N'Northgalelog2.ldf', '''') + 
                                 ', SIZE = 4 GB)'
   PRINT @sql
   EXEC(@sql)

   -- This need to be dynamic SQL, as it errors out in Azure at compile time.
   EXEC('ALTER DATABASE Northgale SET RECOVERY SIMPLE')
END
GO
-- If database does not exist at this point, try to leave town.
IF db_id('Northgale') IS NULL
BEGIN
   RAISERROR('Database creation failed.', 16, 127)
   RAISERROR('Trying to abort the connection now.', 20, 1) WITH LOG
END
go
-- Move to the database
USE Northgale
go
-- Set up a list of all tables we have.
IF object_id('tempdb..#tables') IS NOT NULL
   DROP TABLE #tables

CREATE TABLE #tables (name      sysname NOT NULL PRIMARY KEY,
                      Northwind bit     NOT NULL,
                      droporder tinyint NOT NULL UNIQUE)

INSERT #tables(name, Northwind, droporder)
  VALUES('Order Details', 1, 1),
        ('Orders', 1, 2), 
        ('HistoricOrderDetails', 0, 3),
        ('HistoricOrders', 0, 4),
        ('Products', 1, 5),
	     ('CustomerCustomerDemo', 1, 6),
        ('CustomerDemographics', 1, 7),
        ('EmployeeTerritories', 1, 8),
        ('Discounts', 0, 9),
        ('Customers', 1, 10),
        ('Employees', 1, 11),
        ('Shippers', 1, 12),
        ('Suppliers', 1, 13),
        ('Categories', 1, 14),
        ('Territories', 1, 15),
        ('Region', 1, 16)
GO
-- On MI and also SQL Database if we have the Northwind schema, drop all tables.
IF  convert(int, serverproperty('EngineEdition')) = 8 OR
    convert(int, serverproperty('EngineEdition')) = 5 AND schema_id('Northwind') IS NOT NULL
BEGIN
   DECLARE @sql nvarchar(MAX)

   -- Yes, we use FOR XML PATH, so that code compiles on SQL 2008.
   SELECT @sql = 
      (SELECT 'DROP TABLE IF EXISTS ' + quotename(name) + ' ' 
       FROM   #tables
       ORDER  BY droporder
       FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
   PRINT @sql
   EXEC(@sql)
END
GO
-- On Azure SQL Database, if we don't have the Northwind schema, move all tables there.
IF convert(int, serverproperty('EngineEdition')) = 5 AND schema_id('Northwind') IS NULL
BEGIN
   DECLARE @sql nvarchar(MAX)

   BEGIN TRANSACTION

   EXEC('CREATE SCHEMA Northwind')

   SET @sql = 
      (SELECT 'ALTER SCHEMA Northwind TRANSFER dbo.' + quotename(name) + ' ' 
       FROM   #tables
       WHERE  Northwind = 1
       FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
   PRINT @sql
   EXEC(@sql)

   COMMIT TRANSACTION
END
GO
-- On on-prem, set up synonyms in the Northwind schema, and also on MI if we don't
-- have it already.
IF  convert(int, serverproperty('EngineEdition')) <= 4 OR
    convert(int, serverproperty('EngineEdition')) = 8 AND schema_id('Northwind') IS NULL
BEGIN
   DECLARE @sql nvarchar(MAX)

   BEGIN TRANSACTION

   EXEC('CREATE SCHEMA Northwind')

   SET @sql = 
      (SELECT 'CREATE SYNONYM Northwind.' + quotename(name) + ' FOR Northwind.dbo.' + quotename(name) + ' '
       FROM   #tables
       WHERE  Northwind = 1
       FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
   PRINT @sql
   EXEC(@sql)

   COMMIT TRANSACTION
END
GO
-- Inflator temp table. Permits us to inflate database a little more.
-- Pick as many values as needed to a database size that shows performance
-- issue without static_search_1 being *too* slow.
IF object_id('tempdb..#inflator') IS NOT NULL
   DROP TABLE #inflator
CREATE TABLE #inflator(n tinyint NOT NULL PRIMARY KEY)
INSERT #inflator (n) VALUES (1), (2), (3)
go
CREATE TABLE Employees (
   EmployeeID int NOT NULL ,
   LastName nvarchar (20) NOT NULL ,
   FirstName nvarchar (10) NOT NULL ,
   Title nvarchar (30) NULL ,
   TitleOfCourtesy nvarchar (25) NULL ,
   BirthDate date NULL ,
   HireDate date NULL ,
   Address nvarchar (60) NULL ,
   City nvarchar (15) NULL ,
   Region nvarchar (15) NULL ,
   PostalCode nvarchar (10) NULL ,
   Country nvarchar (15) NULL ,
   HomePhone nvarchar (24) NULL ,
   Extension nvarchar (4) NULL ,
   Photo varbinary(MAX) NULL ,
   Notes nvarchar(MAX) NULL ,
   ReportsTo int NULL ,
   PhotoPath nvarchar (255) NULL ,
   CONSTRAINT PK_Employees PRIMARY KEY  CLUSTERED
   (
      EmployeeID
   ),
   CONSTRAINT FK_Employees_Employees FOREIGN KEY
   (
      ReportsTo
   ) REFERENCES dbo.Employees (
      EmployeeID
   ),
   CONSTRAINT CK_Birthdate CHECK (BirthDate < getdate())
)
GO
 CREATE  INDEX lastname_ix ON dbo.Employees(LastName)
GO
 CREATE  INDEX postcode_ix ON dbo.Employees(PostalCode)
GO

CREATE TABLE Categories (
   CategoryID int NOT NULL ,
   CategoryName nvarchar (15) NOT NULL ,
   Description nvarchar(MAX) NULL ,
   Picture varbinary(MAX) NULL ,
   CONSTRAINT PK_Categories PRIMARY KEY  CLUSTERED
   (
      CategoryID
   )
)
GO
 CREATE  INDEX catname_ix ON dbo.Categories(CategoryName)
GO

CREATE TABLE Customers (
   CustomerID nchar (5) NOT NULL ,
   VATno         varchar(15) NULL,
   CustomerName nvarchar (40) NOT NULL ,
   ContactName nvarchar (30) NOT NULL ,
   ContactTitle nvarchar (30) NULL ,
   Address nvarchar (60) NULL ,
   City nvarchar (25) NULL ,
   Region nvarchar (15) NULL ,
   PostalCode nvarchar (10) NULL ,
   Country nvarchar (15) NULL ,
   Phone nvarchar (24) NULL ,
   Fax nvarchar (24) NULL ,
   CONSTRAINT PK_Customers PRIMARY KEY  CLUSTERED
   (
      CustomerID
   )
)
GO
 CREATE  INDEX city_ix ON dbo.Customers(City)
GO
 CREATE  INDEX custname_ix ON dbo.Customers(CustomerName)
GO
 CREATE  INDEX postcode_ix ON dbo.Customers(PostalCode)
GO
 CREATE  INDEX region_ix ON dbo.Customers(Region)
GO
  CREATE UNIQUE INDEX VATno_ix ON dbo.Customers(VATno) 
     WHERE VATno IS NOT NULL
go
CREATE TABLE Shippers (
   ShipperID int NOT NULL ,
   CompanyName nvarchar (40) NOT NULL ,
   Phone nvarchar (24) NULL ,
   CONSTRAINT PK_Shippers PRIMARY KEY  CLUSTERED
   (
      ShipperID
   )
)
GO
CREATE TABLE Suppliers (
   SupplierID int NOT NULL ,
   CompanyName nvarchar (40) NOT NULL ,
   ContactName nvarchar (30) NULL ,
   ContactTitle nvarchar (30) NULL ,
   Address nvarchar (60) NULL ,
   City nvarchar (15) NULL ,
   Region nvarchar (15) NULL ,
   PostalCode nvarchar (10) NULL ,
   Country nvarchar (15) NULL ,
   Phone nvarchar (24) NULL ,
   Fax nvarchar (24) NULL ,
   HomePage nvarchar(MAX) NULL ,
   CONSTRAINT PK_Suppliers PRIMARY KEY  CLUSTERED
   (
      SupplierID
   )
)
GO
 CREATE  INDEX companyname_ix ON dbo.Suppliers(CompanyName)
GO
 CREATE  INDEX postcode_ix ON dbo.Suppliers(PostalCode)
GO

CREATE TABLE Orders (
   OrderID int NOT NULL ,
   CustomerID nchar (5) NOT NULL ,
   EmployeeID int NOT NULL ,
   OrderDate date NOT NULL ,
   RequiredDate date NOT NULL ,
   ShippedDate date NULL ,
   ShipVia int NULL ,
   Freight decimal(10,2) NOT NULL CONSTRAINT DF_Orders_Freight DEFAULT (0),
   ShipName nvarchar (40) NULL ,
   ShipAddress nvarchar (60) NULL ,
   ShipCity nvarchar (15) NULL ,
   ShipRegion nvarchar (15) NULL ,
   ShipPostalCode nvarchar (10) NULL ,
   ShipCountry nvarchar (15) NULL ,
   Discount      decimal(5, 2) NOT NULL CONSTRAINT DF_Orders_Discount DEFAULT 0,
   TotalAmount   decimal(10, 2) NULL,
   Status char(1) NOT NULL CONSTRAINT ckc_Orders_status CHECK (Status IN ('N', 'P', 'E', 'C'))
   CONSTRAINT PK_Orders PRIMARY KEY  CLUSTERED
   (
      OrderID
   ),
   CONSTRAINT FK_Orders_Customers FOREIGN KEY
   (
      CustomerID
   ) REFERENCES dbo.Customers (
      CustomerID
   ),
   CONSTRAINT FK_Orders_Employees FOREIGN KEY
   (
      EmployeeID
   ) REFERENCES dbo.Employees (
      EmployeeID
   ),
   CONSTRAINT FK_Orders_Shippers FOREIGN KEY
   (
      ShipVia
   ) REFERENCES dbo.Shippers (
      ShipperID
   )
)
GO
 CREATE  INDEX custid_ix ON dbo.Orders(CustomerID)
GO
 CREATE  INDEX empid_ix ON dbo.Orders(EmployeeID)
GO
 CREATE  INDEX orderdate_ix ON dbo.Orders(OrderDate)
GO
 CREATE  INDEX shipid_ix ON dbo.Orders(ShippedDate)
GO
 CREATE  INDEX shipvia_ix ON dbo.Orders(ShipVia)
GO
 CREATE  INDEX shippostcode_ix ON dbo.Orders(ShipPostalCode)
GO
 CREATE INDEX status_ix ON dbo.Orders(Status) WHERE Status <> 'C'
go


CREATE TABLE HistoricOrders (
   OrderID int NOT NULL ,
   CustomerID nchar (5) NOT NULL ,
   EmployeeID int NOT NULL ,
   OrderDate date NOT NULL ,
   RequiredDate date NOT NULL ,
   ShippedDate date NOT NULL ,
   ShipVia int NULL ,
   Freight decimal(10,2) NULL,
   ShipName nvarchar (40) NULL ,
   ShipAddress nvarchar (60) NULL ,
   ShipCity nvarchar (15) NULL ,
   ShipRegion nvarchar (15) NULL ,
   ShipPostalCode nvarchar (10) NULL ,
   ShipCountry nvarchar (15) NULL ,
   Status char(1) NOT NULL CONSTRAINT ckc_HistoricOrders_status CHECK (Status IN ('E', 'C')),
   Discount      decimal(5, 2) NOT NULL 
     CONSTRAINT DF_HistoricOrders_Discount DEFAULT 0,
   CONSTRAINT PK_HistoricOrders PRIMARY KEY  CLUSTERED
   (
      OrderID
   ),
   CONSTRAINT FK_HistoricOrders_Customers FOREIGN KEY
   (
      CustomerID
   ) REFERENCES dbo.Customers (
      CustomerID
   ),
   CONSTRAINT FK_HistoricOrders_Employees FOREIGN KEY
   (
      EmployeeID
   ) REFERENCES dbo.Employees (
      EmployeeID
   ),
   CONSTRAINT FK_HistoricOrders_Shippers FOREIGN KEY
   (
      ShipVia
   ) REFERENCES dbo.Shippers (
      ShipperID
   )
)
GO
 CREATE  INDEX custid_ix ON dbo.HistoricOrders(CustomerID)
GO
 CREATE  INDEX empid_ix ON dbo.HistoricOrders(EmployeeID)
GO
 CREATE  INDEX orderdate_ix ON dbo.HistoricOrders(OrderDate)
GO
 CREATE  INDEX shipdate_ix ON dbo.HistoricOrders(ShippedDate)
GO
 CREATE  INDEX shipvia_ix ON dbo.HistoricOrders(ShipVia)
GO
 CREATE  INDEX ship_postcode_ix ON dbo.HistoricOrders(ShipPostalCode)
GO
 CREATE INDEX status_ix ON dbo.HistoricOrders(Status) WHERE Status <> 'C'
go


CREATE TABLE Products (
   ProductID int NOT NULL ,
   ProductName nvarchar (40) NOT NULL ,
   SupplierID int NULL ,
   CategoryID int NULL ,
   QuantityPerUnit nvarchar (20) NULL ,
   UnitPrice decimal(10,2) NULL CONSTRAINT DF_Products_UnitPrice DEFAULT (0),
   UnitsInStock smallint NULL CONSTRAINT DF_Products_UnitsInStock DEFAULT (0),
   UnitsOnOrder smallint NULL CONSTRAINT DF_Products_UnitsOnOrder DEFAULT (0),
   ReorderLevel smallint NULL CONSTRAINT DF_Products_ReorderLevel DEFAULT (0),
   Discontinued bit NOT NULL CONSTRAINT DF_Products_Discontinued DEFAULT (0),
   CONSTRAINT PK_Products PRIMARY KEY  CLUSTERED
   (
      ProductID
   ),
   CONSTRAINT FK_Products_Categories FOREIGN KEY
   (
      CategoryID
   ) REFERENCES dbo.Categories (
      CategoryID
   ),
   CONSTRAINT FK_Products_Suppliers FOREIGN KEY
   (
      SupplierID
   ) REFERENCES dbo.Suppliers (
      SupplierID
   ),
   CONSTRAINT CK_Products_UnitPrice CHECK (UnitPrice >= 0),
   CONSTRAINT CK_ReorderLevel CHECK (ReorderLevel >= 0),
   CONSTRAINT CK_UnitsInStock CHECK (UnitsInStock >= 0),
   CONSTRAINT CK_UnitsOnOrder CHECK (UnitsOnOrder >= 0)
)
GO
 CREATE  INDEX catid_ix ON dbo.Products(CategoryID)
GO
 CREATE  INDEX prodname_ix ON dbo.Products(ProductName)
GO
 CREATE  INDEX supplid_ix ON dbo.Products(SupplierID)
GO

CREATE TABLE "Order Details" (
   OrderID int NOT NULL ,
   ProductID int NOT NULL ,
   UnitPrice decimal(10,2) NOT NULL CONSTRAINT DF_Order_Details_UnitPrice DEFAULT (0),
   Quantity smallint NOT NULL CONSTRAINT DF_Order_Details_Quantity DEFAULT (1),
   CONSTRAINT PK_Order_Details PRIMARY KEY  CLUSTERED
   (
      OrderID,
      ProductID
   ),
   CONSTRAINT FK_Order_Details_Orders FOREIGN KEY
   (
      OrderID
   ) REFERENCES dbo.Orders (
      OrderID
   ),
   CONSTRAINT FK_Order_Details_Products FOREIGN KEY
   (
      ProductID
   ) REFERENCES dbo.Products (
      ProductID
   ),
   CONSTRAINT CK_Quantity CHECK (Quantity > 0),
   CONSTRAINT CK_UnitPrice CHECK (UnitPrice >= 0)
)
GO
 CREATE  INDEX ProductID_ix ON dbo."Order Details"(ProductID)
GO


CREATE TABLE HistoricOrderDetails (
   OrderID int NOT NULL ,
   ProductID int NOT NULL ,
   UnitPrice decimal(10,2) NOT NULL,
   Quantity smallint NOT NULL,
   CONSTRAINT PK_HistoricOrderDetails PRIMARY KEY  CLUSTERED
   (
      OrderID,
      ProductID
   ),
   CONSTRAINT FK_HistoricOrderDetails_HistoricOrders FOREIGN KEY
   (
      OrderID
   ) REFERENCES dbo.HistoricOrders (
      OrderID
   ),
   CONSTRAINT FK_HistoricOrderDetails_Products FOREIGN KEY
   (
      ProductID
   ) REFERENCES dbo.Products (
      ProductID
   ),
   CONSTRAINT CK_HistoricOrderDetails_Quantity CHECK (Quantity > 0),
   CONSTRAINT CK_HistoricOrderDetails_UnitPrice CHECK (UnitPrice >= 0)
)
GO
 CREATE  INDEX prodid_ix ON dbo.HistoricOrderDetails(ProductID)
GO






CREATE TABLE dbo.CustomerCustomerDemo
   (CustomerID nchar (5) NOT NULL,
   CustomerTypeID nchar (10) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE dbo.CustomerDemographics
   (CustomerTypeID nchar (10) NOT NULL ,
   CustomerDesc nvarchar(MAX) NULL
)  ON [PRIMARY]
GO

CREATE TABLE dbo.Region
   ( RegionID int NOT NULL ,
   RegionDescription nchar (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE dbo.Territories
   (TerritoryID nvarchar (20) NOT NULL ,
   TerritoryDescription nchar (50) NOT NULL ,
        RegionID int NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE dbo.EmployeeTerritories
   (EmployeeID int NOT NULL,
   TerritoryID nvarchar (20) NOT NULL
) ON [PRIMARY]


CREATE TABLE dbo.Discounts (
    DiscountID    int IDENTITY (1,1) NOT NULL,
    CustomerID    nchar(5)     NULL,
    City          nvarchar(25) NULL,
    Country       nvarchar(15) NULL,
    Discount      decimal(5,2) NOT NULL,
    CONSTRAINT pk_Discounts PRIMARY KEY (DiscountID),
    CONSTRAINT u_Discounts UNIQUE NONCLUSTERED (CustomerID, City, Country),
    CONSTRAINT ckt_Discounts CHECK (CASE WHEN CustomerID IS NOT NULL THEN 1 END +
                                    CASE WHEN City       IS NOT NULL THEN 1 END +
                                    CASE WHEN Country    IS NOT NULL THEN 1 END <= 1)
)


--  The following adds constraints to the Northgale database

ALTER TABLE CustomerCustomerDemo
   ADD CONSTRAINT PK_CustomerCustomerDemo PRIMARY KEY  NONCLUSTERED
   (
      CustomerID,
      CustomerTypeID
   ) ON [PRIMARY]
GO

ALTER TABLE CustomerDemographics
   ADD CONSTRAINT PK_CustomerDemographics PRIMARY KEY  NONCLUSTERED
   (
      CustomerTypeID
   ) ON [PRIMARY]
GO

ALTER TABLE CustomerCustomerDemo
   ADD CONSTRAINT FK_CustomerCustomerDemo FOREIGN KEY
   (
      CustomerTypeID
   ) REFERENCES dbo.CustomerDemographics (
      CustomerTypeID
   )
GO

ALTER TABLE CustomerCustomerDemo
   ADD CONSTRAINT FK_CustomerCustomerDemo_Customers FOREIGN KEY
   (
      CustomerID
   ) REFERENCES dbo.Customers (
      CustomerID
   )
GO

ALTER TABLE Region
   ADD CONSTRAINT PK_Region PRIMARY KEY  NONCLUSTERED
   (
      RegionID
   )  ON [PRIMARY]
GO

ALTER TABLE Territories
   ADD CONSTRAINT PK_Territories PRIMARY KEY  NONCLUSTERED
   (
      TerritoryID
   )  ON [PRIMARY]
GO

ALTER TABLE Territories
   ADD CONSTRAINT FK_Territories_Region FOREIGN KEY
   (
      RegionID
   ) REFERENCES dbo.Region (
      RegionID
   )
GO

ALTER TABLE EmployeeTerritories
   ADD CONSTRAINT PK_EmployeeTerritories PRIMARY KEY  NONCLUSTERED
   (
      EmployeeID,
      TerritoryID
   ) ON [PRIMARY]
GO

ALTER TABLE EmployeeTerritories
   ADD CONSTRAINT FK_EmployeeTerritories_Employees FOREIGN KEY
   (
      EmployeeID
   ) REFERENCES dbo.Employees (
      EmployeeID
   )
GO


ALTER TABLE EmployeeTerritories
   ADD CONSTRAINT FK_EmployeeTerritories_Territories FOREIGN KEY
   (
      TerritoryID
   ) REFERENCES dbo.Territories (
      TerritoryID
   )
GO
ALTER TABLE dbo.Discounts ADD CONSTRAINT fk_Discounts_Customers
    FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)

----------------------------------------- Data insertion from Northwind starts here.

INSERT Categories (CategoryID, CategoryName)
   SELECT DISTINCT ((a.CategoryID - 1) * 100) + b.CategoryID, a.CategoryName
   FROM   Northwind.Categories a
   CROSS  JOIN Northwind.Categories b
go
WITH Cities AS (
    SELECT City, Country
    FROM   (VALUES 
    (N'N''Djamena', N'Chad'), (N'Łódź', N'Poland'),
    (N'Praha', N'Czech Republic'), (N'Sighişoara', N'Romania'),
    (N'Cantavieja', N'Spain'), (N'Tōkyō', N'Japan'),
    (N'Donibane Garazi', N'France'), (N'Esquel', N'Argentina'),
    (N'Coimbra', N'Portugal'), (N'Călărași', N'Romania'),
    (N'Rovaniemi', N'Finland'), (N'Önnestad', N'Sweden'),
    (N'Vejle', N'Denmark'), (N'Parma', N'Italy'),
    (N'Győr', N'Hungary'), (N'Goma', N'DR Congo'),
    (N'Мелник', N'Bulgaria'), (N'Salzburg', N'Austria'),
    (N'Manuas', N'Brazil'), (N'Puerto Natales', N'Chile'),
    (N'Київ', N'Ukraine'), (N'Šiauliai', N'Lithuania'),
    (N'Göteborg', N'Sweden'), (N'Malmö', N'Sweden'), 
    (N'Xi''an', N'China'), (N'Кам''янець-Подільський', N'Ukraine'),
    (N'Oslo', N'Norway'), (N'Tromsø', N'Norway'), 
    (N'Львів', N'Ukraine'), (N'Coeur d''Alene', N'USA')) AS V(City, Country)
    UNION ALL
    SELECT City, Country 
    FROM   Northwind.Customers
), CitiesNumbered AS (
   SELECT City, Country, rowno = row_number() OVER(ORDER BY (SELECT City)), COUNT(*) OVER () AS cnt
   FROM   Cities
)
INSERT Customers(CustomerID, 
                 VATno,
                 CustomerName,
                 ContactName, ContactTitle, Address, Region,
                 PostalCode, Phone, Fax,
                 City, Country)
   SELECT newcustid,
          upper(substring(CASE WHEN a.CustomerID = x.newcustid THEN a.Country 
                               WHEN b.CustomerID = x.newcustid THEN b.Country 
                               ELSE cc.Country 
                         END, 1, 2)) + 
            substring(replace(
                      replace(
                      replace(
                      replace(
                      replace(a.Phone, ' ', ''), 
                                       '-', ''),
                                       '.', ''),
                                       ')', ''),
                                       '(', ''), 1, 4) +
            substring(replace(
                      replace(
                      replace(
                      replace(
                      replace(b.Fax,   ' ', ''), 
                                       '-', ''),
                                       '.', ''),
                                       ')', ''),
                                       '(', ''), 5, 4) +
            replace(str(x.rowno, 4), ' ', '0'),
          ltrim(substring( substring(a.CompanyName, 1, charindex(' ', a.CompanyName)) +
                     substring(b.CompanyName, charindex(' ', b.CompanyName) + 1, len(b.CompanyName)),
                    1, 40)),
          ltrim(substring( substring(a.ContactName, 1, charindex(' ', a.ContactName)) +
                     substring(b.ContactName, charindex(' ', b.ContactName) + 1, len(b.ContactName)),
                    1, 40)),
          b.ContactTitle, a.Address, a.Region,
          b.PostalCode, a.Phone, b.Fax,
          -- Retain city and country from Northwind for the same customer ID,
          -- else take random.
          CASE WHEN a.CustomerID = x.newcustid THEN a.City 
               WHEN b.CustomerID = x.newcustid THEN b.City 
               ELSE cc.City 
          END,
          CASE WHEN a.CustomerID = x.newcustid THEN a.Country 
               WHEN b.CustomerID = x.newcustid THEN b.Country 
               ELSE cc.Country 
          END
   FROM   (SELECT newcustid = substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2),
                  acustid = MIN(a.CustomerID), bcustid = MIN(b.CustomerID),
                  rowno = row_number() OVER(ORDER BY substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2))
           FROM   Northwind.Customers a
           CROSS  JOIN Northwind.Customers b
           GROUP  BY substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2)) AS x
   JOIN    Northwind.Customers a ON a.CustomerID = x.acustid
   JOIN    Northwind.Customers b ON b.CustomerID = x.bcustid
   OUTER   APPLY  (SELECT c.City, c.Country
                   FROM   CitiesNumbered c 
                   WHERE  x.rowno % c.cnt = c.rowno - 1) AS cc

-- Some post updates. Correct the name of this city, for the single-quote demo.
UPDATE Customers
SET    City = 'Reggio nell''Emilia'
WHERE  City = 'Reggio Emilia'

-- This city is mispelled in Northwind
UPDATE Customers
SET    City = 'København'
WHERE  City = 'Kobenhavn'

-- Create a skew in the City column.
; WITH Extra AS (
   SELECT TOP 3200 Country, City
   FROM   Customers
   WHERE  City <> 'London'
   ORDER  BY newid()
)
UPDATE Extra
SET    Country = 'United Kingdom',
       City    = 'London'

-- Make sure that this customer always is in Xi'an.
UPDATE Customers 
SET    City = 'Xi''an',
       Country = 'China'
WHERE  CustomerID = 'VICKI'

-- And this customer should always be in Kamyanets.
UPDATE Customers 
SET    City = N'Кам''янець-Подільський',
       Country = 'Ukraine'
WHERE  CustomerID = 'OTTAN'

-- And this customer moves to Chad and changes id.
UPDATE Customers
SET    CustomerID = 'TCHAD',
       City       = 'N''Djamena',
       Region     = NULL,
       Country    = 'Chad'
WHERE  CustomerID = 'OLDSR'


go
INSERT Employees (EmployeeID, LastName, FirstName, Title,
                  TitleOfCourtesy, BirthDate, HireDate, Address, City,
                  Region, PostalCode, Country, HomePhone, Extension, ReportsTo)
   SELECT DISTINCT ((a.EmployeeID - 1) * 100) + (b.EmployeeID - 1) * 10 + c.EmployeeID, 
          CASE c.EmployeeID 
               WHEN 1 THEN 'Pettersson' WHEN 2 THEN 'Lind' WHEN 3 THEN 'Smith' 
               WHEN 4 THEN 'Spadolini'  WHEN 5 THEN 'Cruz' WHEN 6 THEN 'Leblanc'  
               WHEN 7 THEN 'Walker'     WHEN 8 THEN 'Watanabe'
               ELSE a.LastName
          END, 
          CASE b.EmployeeID
               WHEN 1 THEN 'Stig'   WHEN 2 THEN 'Henri' WHEN 3 THEN 'John' 
               WHEN 4 THEN 'Roland' WHEN 5 THEN 'Ted'   WHEN 6 THEN 'Gerhard'  
               WHEN 7 THEN 'Scott'  WHEN 8 THEN 'Kazumi'
               ELSE c.FirstName
          END, 
          c.Title,
          a.TitleOfCourtesy, b.BirthDate, c.HireDate, a.Address, b.City,
          c.Region, c.PostalCode, a.Country, b.HomePhone, c.Extension,
          ((a.ReportsTo - 1) * 100) + 10 + b.ReportsTo
   FROM   Northwind.Employees a
   CROSS  JOIN Northwind.Employees b
   CROSS  JOIN Northwind.Employees c
   UNION ALL
   SELECT 900, 'Holgersson', 'Nils', 'Påg',
          NULL, '19000505', '19100404', NULL, 'Förslöv',
          'Skåne', '29028', NULL, NULL, NULL, 112
   UNION ALL
   SELECT 901, 'Friberg', 'Harry', 'Fotograf',
          NULL, '19141213', '19440501', NULL, NULL,
          NULL, NULL, NULL, NULL, NULL, 112
   UNION ALL
   SELECT 902, 'Suárez', 'Carmencita', 'Señorina',
          NULL, '19001105', '19220404', NULL, 'Samborobom',
          NULL, NULL, NULL, NULL, NULL, 115
   UNION ALL
   SELECT 903, 'Falk', 'Arvid', 'Skriftställare',
          NULL, '18550609', '18790505', NULL, 'Stockholm',
          NULL, NULL, NULL, NULL, NULL, 115


go
INSERT Suppliers (SupplierID,
                  CompanyName,
                  ContactName, ContactTitle, Address, City, Region,
                  PostalCode, Country, Phone, Fax)
   SELECT DISTINCT ((a.SupplierID - 1) * 1000) + b.SupplierID,
          substring( substring(a.CompanyName, 1, charindex(' ', a.CompanyName)) +
                     substring(b.CompanyName, charindex(' ', b.CompanyName), len(b.CompanyName)),
                    1, 40),
           b.ContactName, a.ContactTitle, b.Address, a.City, b.Region,
           a.PostalCode, b.Country, a.Phone, b.Fax
   FROM    Northwind.Suppliers a
   CROSS   JOIN Northwind.Suppliers b
go
IF convert(int, serverproperty('EngineEdition')) <= 4 
BEGIN
   INSERT Shippers(ShipperID, CompanyName, Phone)
      SELECT number + 1, 'Shipper ' + convert(varchar(10), number + 1), '+46890510'
      FROM   master.dbo.spt_values WHERE type = 'P'
END
ELSE
BEGIN
   INSERT Shippers(ShipperID, CompanyName, Phone)
      EXEC('SELECT value, ''Shipper '' + convert(varchar(10), value), ''+46890510''
            FROM   generate_series(1, 2048)')
END
go
INSERT Products (ProductID,
                 ProductName,
                 SupplierID,
                 CategoryID,
                 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
                 ReorderLevel, Discontinued)
   SELECT DISTINCT ((a.ProductID - 1) * 100) + b.ProductID,
          substring(substring(a.ProductName, 1, charindex(' ', a.ProductName)) +
                    CASE WHEN charindex(' ', b.ProductName) > 1
                         THEN substring(b.ProductName, charindex(' ', b.ProductName) + 1, len(b.ProductName))
                         ELSE b.ProductName
                    END, 1, 40),
          ((a.SupplierID - 1) * 1000) + b.SupplierID,
          ((a.CategoryID - 1) * 100) + b.CategoryID,
          a.QuantityPerUnit, b.UnitPrice, a.UnitsInStock, b.UnitsOnOrder,
          a.ReorderLevel, b.Discontinued
    FROM  Northwind.Products a
    CROSS JOIN  Northwind.Products b
go
INSERT Orders (OrderID,
               CustomerID,
               EmployeeID,
               OrderDate, RequiredDate, ShippedDate,
               ShipVia, Freight, ShipName,
               ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry,
               Status)
   SELECT row_number() OVER(ORDER BY n.n,
                                     CASE n%n % 2 WHEN 1 THEN a.OrderDate ELSE b.OrderDate END,
                                     a.OrderID, b.OrderID) AS OrderID,
          CASE WHEN substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2) = 'BOLSR' OR
                    substring(b.CustomerID, 1, 3) + substring(a.CustomerID, 4, 2) = 'BOLSR'
               THEN 'ERNTC'
               WHEN substring(b.CustomerID, 1, 3) + substring(a.CustomerID, 4, 2) = 'OLDSR' OR
                    substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2) = 'OLDSR' THEN 'TCHAD'
               WHEN n.n % 2 = 0 THEN substring(b.CustomerID, 1, 3) + substring(a.CustomerID, 4, 2)
               ELSE substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2)
          END AS CustomerID,
          ((a.EmployeeID - 1) * 100) + (b.EmployeeID - 1) *10 + a.OrderID % 9 + 1 AS EmployeeID,
          dateadd(DAY, d.range * (n.n-1), CASE n%n % 2 WHEN 1 THEN a.OrderDate ELSE b.OrderDate END) AS OrderDate, 
          dateadd(DAY, d.range * (n.n-1), CASE n%n % 2 WHEN 1 THEN a.RequiredDate ELSE b.RequiredDate END) AS RequiredDate, 
          dateadd(DAY, d.range * (n.n-1), CASE n%n % 2 WHEN 1 THEN a.ShippedDate ELSE b.ShippedDate END) AS ShippedDate,
          a.OrderID % 2048 + 1 AS ShipVia, a.Freight, b.ShipName,
          a.ShipAddress, b.ShipCity, a.ShipRegion, b.ShipPostalCode, a.ShipCountry,
          CASE WHEN a.OrderDate = '19980506' AND n.n = (SELECT MAX(n) FROM #inflator) THEN 'N'
               WHEN a.OrderDate = '19980505' AND n.n = (SELECT MAX(n) FROM #inflator) THEN 'P'
               WHEN abs(checksum(newid())) % 20000 = 1847 THEN 'E'
               ELSE 'C'
          END AS Status
   FROM   Northwind.Orders a
   CROSS  JOIN Northwind.Orders b
   CROSS  JOIN #inflator n
   CROSS  JOIN (SELECT datediff(DAY, MIN(OrderDate), MAX(OrderDate)) + 2 FROM Northwind.Orders) AS d(range)
   WHERE  a.OrderID < b.OrderID

UPDATE Orders
SET    CustomerID = 'ERNTC'
WHERE  OrderID % 10 = 5

UPDATE Orders 
SET    EmployeeID = CASE OrderID % 100000 
                         WHEN 11 THEN 900
                         WHEN 256 THEN 901
                         WHEN 3977 THEN 902 
                         WHEN 9766 THEN 902
                         WHEN 4755 THEN 903                  
                     END
WHERE  OrderID % 100000 IN (11, 256, 3977, 9766, 4755)


UPDATE TOP (1) Orders
SET    CustomerID = 'BOLSR'
WHERE  OrderDate = '19980811'
go
INSERT HistoricOrders (OrderID, CustomerID, EmployeeID,
                       OrderDate, RequiredDate, ShippedDate,
                       ShipVia, Freight, ShipName,
                       ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry,
                       Status)
   SELECT row_number() OVER (ORDER BY OrderID), CustomerID, EmployeeID,
          dateadd(YEAR, -2, OrderDate), dateadd(Year, -1, RequiredDate), dateadd(Year, -1, ShippedDate),
          ShipVia, Freight, ShipName,
          ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry,
          CASE Status WHEN 'E' THEN 'E' ELSE 'C' END
   FROM   (SELECT TOP 10248 *
           FROM   Orders
           WHERE  OrderDate <= '19970701'
           ORDER  BY OrderID DESC) AS O
go
INSERT [Order Details] (OrderID, ProductID, UnitPrice, Quantity)
   SELECT dense_rank() OVER(ORDER BY n.n,
                                     CASE n%n % 2 WHEN 1 THEN a1.OrderDate ELSE b1.OrderDate END,
                                     a.OrderID, b.OrderID),
          CASE WHEN n.n % 2 = 1 THEN ((a.ProductID - 1) * 100) + b.ProductID
               ELSE ((b.ProductID - 1) * 100) + a.ProductID
          END,
          a.UnitPrice, b.Quantity
   FROM   Northwind.[Order Details] a
   JOIN   Northwind.Orders a1 ON a.OrderID = a1.OrderID
   CROSS  JOIN Northwind.[Order Details] b
   JOIN   Northwind.Orders b1 ON b.OrderID = b1.OrderID
   CROSS  JOIN #inflator n
   WHERE  a.OrderID < b.OrderID

INSERT [Order Details] (OrderID, ProductID, UnitPrice, Quantity)
   SELECT O.OrderID, 43, 1.45, 14
   FROM   Orders O
   WHERE  NOT EXISTS (SELECT *
                      FROM   [Order Details] b
                      WHERE  O.OrderID = b.OrderID)
go
INSERT HistoricOrderDetails (OrderID, ProductID, UnitPrice, Quantity)
   SELECT O.historderid, OD.ProductID, OD.UnitPrice, OD.Quantity
   FROM   [Order Details] OD
   JOIN   (SELECT OrderID, row_number() OVER (ORDER BY newid()) AS historderid
           FROM   Orders
           WHERE  OrderDate <= '19970701') AS O ON OD.OrderID = O.OrderID
   WHERE  O.historderid <= 10248
go
-- Add discounts. This is a procedure, because it's used in a demo.
-- We don't use CREATE OR ALTER to support SQL 2008.
IF object_id ('setup_discounts') IS NULL EXEC('CREATE PROCEDURE setup_discounts AS RETURN 1')
go
ALTER PROCEDURE setup_discounts AS
   TRUNCATE TABLE Discounts

   INSERT Discounts(Country, City, CustomerID, Discount)
     VALUES (NULL, NULL, NULL, 2),
            ('Norway', NULL, NULL, 0),
            ('Sweden', NULL, NULL, 10),
            (NULL, 'Önnestad', NULL, 20),
            (NULL, NULL, 'ALFKI', 30)

   INSERT Discounts (City, Discount)
      SELECT TOP 20 City, abs(checksum(newid())) % 100 * 0.1
      FROM   (SELECT DISTINCT City
              FROM   Customers
              WHERE  Country NOT IN ('Norway', 'Sweden')) AS d
      ORDER BY newid()

   INSERT Discounts (CustomerID, Discount)
      SELECT TOP 1000 CustomerID, abs(checksum(newid())) % 100 * 0.15
      FROM   Customers C
      WHERE  NOT EXISTS (SELECT *
                         FROM   Discounts D
                         WHERE  D.CustomerID = C.CustomerID)
      ORDER  BY newid()
go
EXEC setup_discounts
go
UPDATE Orders
SET    Discount    = D.Discount,
       TotalAmount = round((OD.DetailSum * (1 - D.Discount/100)) + O.Freight, 2)
FROM   Orders O
JOIN   Customers C ON O.CustomerID = C.CustomerID
JOIN   (SELECT OrderID, SUM(Quantity * UnitPrice ) AS DetailSum
        FROM   [Order Details]
        GROUP  BY OrderID) OD ON O.OrderID = OD.OrderID
CROSS APPLY (SELECT TOP (1) D.Discount
             FROM   Discounts D
             WHERE  D.Country = C.Country AND D.City IS NULL    AND D.CustomerID IS NULL        OR
                    D.Country IS NULL     AND D.City = C.City   AND D.CustomerID IS NULL        OR
                    D.Country IS NULL     AND D.City IS NULL    AND D.CustomerID = C.CustomerID OR
                    D.City IS NULL        AND D.Country IS NULL AND D.CustomerID IS NULL
             ORDER  BY D.CustomerID DESC, D.City DESC, D.Country DESC) AS D               
go
ALTER INDEX ALL ON Categories REBUILD
ALTER INDEX ALL ON Suppliers REBUILD
ALTER INDEX ALL ON Employees REBUILD
ALTER INDEX ALL ON Shippers REBUILD
ALTER INDEX ALL ON Products REBUILD
ALTER INDEX ALL ON Customers REBUILD
ALTER INDEX ALL ON Orders REBUILD
ALTER INDEX ALL ON [Order Details] REBUILD
go
-- Utility functions etc used in various presentations.
IF object_id ('intlist_to_tbl') IS NULL 
    EXEC('CREATE FUNCTION intlist_to_tbl()  
            RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                                n  int NOT NULL) AS BEGIN RETURN END')
go
ALTER FUNCTION intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                       n  int NOT NULL) AS
BEGIN
   DECLARE @startpos int,
           @endpos   int,
           @textpos  int,
           @chunklen smallint,
           @str      nvarchar(4000),
           @tmpstr   nvarchar(4000),
           @leftover nvarchar(4000)

   SET @textpos = 1
   SET @leftover = ''
   WHILE @textpos <= datalength(@list) / 2
   BEGIN
      SET @chunklen = 4000 - datalength(@leftover) / 2
      SET @tmpstr = ltrim(@leftover +
                    substring(@list, @textpos, @chunklen))
      SET @textpos = @textpos + @chunklen

      SET @startpos = 0
      SET @endpos = charindex(',' COLLATE Slovenian_BIN2, @tmpstr)

      WHILE @endpos > 0
      BEGIN
         SET @str = substring(@tmpstr, @startpos + 1,
                              @endpos - @startpos - 1)
         IF @str <> ''
            INSERT @tbl (n) VALUES(convert(int, @str))
         SET @startpos = @endpos
         SET @endpos = charindex(',' COLLATE Slovenian_BIN2,
                                 @tmpstr, @startpos + 1)
      END

      SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   END

   IF ltrim(rtrim(@leftover)) <> ''
      INSERT @tbl (n) VALUES(convert(int, @leftover))

   RETURN
END
go
IF type_id('intlist_tbltype') IS NULL
   CREATE TYPE intlist_tbltype AS TABLE (val int NOT NULL PRIMARY KEY)
go
-- This function is specific to the presentation "Don't use cursors".
IF object_id('OrdersToModify') IS NULL
   EXEC('CREATE FUNCTION OrdersToModify() 
           RETURNS @orders TABLE (OrderID int NOT NULL PRIMARY KEY)
       BEGIN RETURN END')
go
ALTER FUNCTION OrdersToModify() 
   RETURNS @orders TABLE (OrderID int NOT NULL PRIMARY KEY)
BEGIN
   INSERT @orders(OrderID)
      SELECT TOP (30519) OrderID
      FROM   Orders
      WHERE  OrderDate >= '20011120'
        AND  EmployeeID % 3 = 2
   RETURN
END
go
-- For on-prem, get rid of that second log file.
IF  convert(int, serverproperty('EngineEdition')) <= 4
BEGIN
   CHECKPOINT
   WAITFOR DELAY '00:00:01'
   CHECKPOINT
   WAITFOR DELAY '00:00:01'
   DBCC SHRINKFILE (Northgale_log2, EMPTYFILE)
   WAITFOR DELAY '00:00:01'
   CHECKPOINT
   WAITFOR DELAY '00:00:01'
   CHECKPOINT
   BEGIN TRY
       -- It seems that this always fail the first time, but succeeds next time round.
      ALTER DATABASE Northgale REMOVE FILE Northgale_log2
   END TRY
   BEGIN CATCH
      ALTER DATABASE Northgale REMOVE FILE Northgale_log2
   END CATCH
END
go