USE tempdb
go
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
if exists (select * from sys.databases where name='Northgale')
BEGIN
   ALTER DATABASE Northgale SET SINGLE_USER WITH ROLLBACK IMMEDIATE   
   drop database Northgale
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 perormance
-- 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)

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 older builds, 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)


ALTER DATABASE Northgale SET RECOVERY SIMPLE
GO
set quoted_identifier on
GO
use tempdb  -- Precaution, in case database creation failes.
go
use Northgale
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'

-- 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 'Clinton' WHEN 2 THEN 'Sanders' WHEN 3 THEN 'Kasich' WHEN 4 THEN 'Trump'
               WHEN 5 THEN 'Cruz'    WHEN 6 THEN 'Chafee'  WHEN 7 THEN 'Walker' WHEN 8 THEN 'Christie'
               ELSE a.LastName
          END, 
          CASE b.EmployeeID
               WHEN 1 THEN 'Hillary' WHEN 2 THEN 'Bernie' WHEN 3 THEN 'John' WHEN 4 THEN 'Donald'
               WHEN 5 THEN 'Ted'    WHEN 6 THEN 'Lincoln'  WHEN 7 THEN 'Scott' WHEN 8 THEN 'Chris'
               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


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
INSERT Shippers(ShipperID, CompanyName, Phone)
   SELECT DISTINCT ((a.ShipperID - 1) * 100) + b.ShipperID,
          substring( substring(a.CompanyName, 1, charindex(' ', a.CompanyName)) +
                     substring(b.CompanyName, charindex(' ', b.CompanyName), len(b.CompanyName)),
                    1, 40), b.Phone
   FROM   Northwind..Shippers a
   CROSS  JOIN Northwind..Shippers b
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 abs(checksum(newid())) % 10 = 1 OR 
                    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,
          CASE row_number() OVER (ORDER BY (SELECT 1)) % 100000
               WHEN 1 THEN 900
               WHEN 2 THEN 901
               WHEN 3 THEN 902 
               WHEN 4 THEN 902
               ELSE ((a.EmployeeID - 1) * 100) + (b.EmployeeID - 1) *10 + a.OrderID % 9 + 1
          END 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.ShipVia - 1) * 100 + b.ShipVia 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 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-.
CREATE 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.
CREATE 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
CREATE TYPE intlist_tbltype AS TABLE (val int NOT NULL PRIMARY KEY)
go
-- This function is specific to the presentation "Don't use cursors".
CREATE 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
CHECKPOINT
go
CHECKPOINT
go
DBCC SHRINKFILE (Northgale_log2, EMPTYFILE)
go
CHECKPOINT
go
CHECKPOINT
go
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
go