USE master
GO
if exists (select * from sysdatabases where name='Northgale')
      drop database Northgale
go

DECLARE @device_directory NVARCHAR(520)
SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1

EXECUTE (N'CREATE DATABASE Northgale
  ON PRIMARY (NAME = N''Northgale'', FILENAME = N''' + @device_directory + N'Northgale.mdf'', SIZE = 500 MB)
  LOG ON (NAME = N''Northgale_log'',  FILENAME = N''' + @device_directory + N'Northgale.ldf'', SIZE = 20 MB)')

DECLARE @logsize char(1)
SELECT @logsize = CASE WHEN convert(varchar, Serverproperty('ProductVersion')) LIKE '8%'
                       THEN '2'
                       ELSE '4'
                  END
EXECUTE(N'ALTER DATABASE Northgale
    ADD LOG FILE (NAME = N''Northgale_log2'',
                  FILENAME = N''' + @device_directory + N'Northgalelog2.ldf'', SIZE = ' + @logsize + ' GB)')


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" "datetime" NULL ,
   "HireDate" "datetime" 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" ON "dbo"."Employees"("LastName")
GO
 CREATE  INDEX "PostalCode" 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 "CategoryName" ON "dbo"."Categories"("CategoryName")
GO

CREATE TABLE "Customers" (
   "CustomerID" nchar (5) 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 ,
   CONSTRAINT "PK_Customers" PRIMARY KEY  CLUSTERED
   (
      "CustomerID"
   )
)
GO
 CREATE  INDEX "City" ON "dbo"."Customers"("City")
GO
 CREATE  INDEX "CompanyName" ON "dbo"."Customers"("CompanyName")
GO
 CREATE  INDEX "PostalCode" ON "dbo"."Customers"("PostalCode")
GO
 CREATE  INDEX "Region" ON "dbo"."Customers"("Region")
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" ON "dbo"."Suppliers"("CompanyName")
GO
 CREATE  INDEX "PostalCode" ON "dbo"."Suppliers"("PostalCode")
GO

CREATE TABLE "Orders" (
   "OrderID" "int" NOT NULL ,
   "CustomerID" nchar (5) NULL ,
   "EmployeeID" "int" NULL ,
   "OrderDate" "datetime" NULL ,
   "RequiredDate" "datetime" NULL ,
   "ShippedDate" "datetime" NULL ,
   "ShipVia" "int" NULL ,
   "Freight" "money" 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 ,
   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 "CustomerID" ON "dbo"."Orders"("CustomerID")
GO
 CREATE  INDEX "CustomersOrders" ON "dbo"."Orders"("CustomerID")
GO
 CREATE  INDEX "EmployeeID" ON "dbo"."Orders"("EmployeeID")
GO
 CREATE  INDEX "EmployeesOrders" ON "dbo"."Orders"("EmployeeID")
GO
 CREATE  INDEX "OrderDate" ON "dbo"."Orders"("OrderDate")
GO
 CREATE  INDEX "ShippedDate" ON "dbo"."Orders"("ShippedDate")
GO
 CREATE  INDEX "ShippersOrders" ON "dbo"."Orders"("ShipVia")
GO
 CREATE  INDEX "ShipPostalCode" ON "dbo"."Orders"("ShipPostalCode")
GO

CREATE TABLE "Products" (
   "ProductID" "int" NOT NULL ,
   "ProductName" nvarchar (40) NOT NULL ,
   "SupplierID" "int" NULL ,
   "CategoryID" "int" NULL ,
   "QuantityPerUnit" nvarchar (20) NULL ,
   "UnitPrice" "money" 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 "CategoriesProducts" ON "dbo"."Products"("CategoryID")
GO
 CREATE  INDEX "CategoryID" ON "dbo"."Products"("CategoryID")
GO
 CREATE  INDEX "ProductName" ON "dbo"."Products"("ProductName")
GO
 CREATE  INDEX "SupplierID" ON "dbo"."Products"("SupplierID")
GO
 CREATE  INDEX "SuppliersProducts" ON "dbo"."Products"("SupplierID")
GO

CREATE TABLE "Order Details" (
   "OrderID" "int" NOT NULL ,
   "ProductID" "int" NOT NULL ,
   "UnitPrice" "money" NOT NULL CONSTRAINT "DF_Order_Details_UnitPrice" DEFAULT (0),
   "Quantity" "smallint" NOT NULL CONSTRAINT "DF_Order_Details_Quantity" DEFAULT (1),
   "Discount" "real" NOT NULL CONSTRAINT "DF_Order_Details_Discount" DEFAULT (0),
   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_Discount" CHECK (Discount >= 0 and (Discount <= 1)),
   CONSTRAINT "CK_Quantity" CHECK (Quantity > 0),
   CONSTRAINT "CK_UnitPrice" CHECK (UnitPrice >= 0)
)
GO
 CREATE  INDEX "OrderID" ON "dbo"."Order Details"("OrderID")
GO
 CREATE  INDEX "OrdersOrder_Details" ON "dbo"."Order Details"("OrderID")
GO
 CREATE  INDEX "ProductID" ON "dbo"."Order Details"("ProductID")
GO
 CREATE  INDEX "ProductsOrder_Details" ON "dbo"."Order Details"("ProductID")
GO


/* The follwing adds tables to the Northgale database */


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]




--  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
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 (City, Country) AS (
    SELECT N'N''Djamena', N'Chad' UNION ALL SELECT N'Łódź', N'Poland' UNION ALL
    SELECT N'Praha', N'Czech Republic' UNION ALL SELECT N'Sighişoara', N'Romania' UNION ALL
    SELECT N'Cantavieja', N'Spain' UNION ALL SELECT N'Tōkyō', N'Japan' UNION ALL
    SELECT N'Donibane Garazi', N'France' UNION ALL SELECT N'Esquel', N'Argentina' UNION ALL
    SELECT N'Coimbra', N'Portugal' UNION ALL SELECT N'Călărași', N'Romania' UNION ALL
    SELECT N'Rovaniemi', N'Finland' UNION ALL SELECT N'Önnestad', N'Sweden' UNION ALL
    SELECT N'Vejle', N'Denmark' UNION ALL SELECT N'Celle', N'Germany' UNION ALL
    SELECT N'Győr', N'Hungary' UNION ALL SELECT N'Goma', N'DR Congo' UNION ALL
    SELECT N'Piacenza', N'Italy' UNION ALL SELECT N'Salzburg', N'Austria' UNION ALL
    SELECT N'Manuas', N'Brazil' UNION ALL SELECT N'Puerto Natales', N'Chile' UNION ALL
    SELECT N'Chernvitsi', N'Ukraine' UNION ALL SELECT N'Šiauliai', N'Lithuania'
    UNION ALL
    SELECT DISTINCT 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,
                 CompanyName,
                 ContactName, ContactTitle, Address, Region,
                 PostalCode, Phone, Fax,
                 City, Country)
   SELECT newcustid,
          substring( substring(a.CompanyName, 1, charindex(' ', a.CompanyName)) +
                     substring(b.CompanyName, charindex(' ', b.CompanyName), len(b.CompanyName)),
                    1, 40),
          substring( substring(a.ContactName, 1, charindex(' ', a.ContactName)) +
                     substring(b.ContactName, charindex(' ', b.ContactName), len(b.ContactName)),
                    1, 40),
          b.ContactTitle, a.Address, a.Region,
          b.PostalCode, a.Phone, b.Fax,
          CASE WHEN a.CustomerID = b.CustomerID THEN a.City
               ELSE (SELECT c.City FROM CitiesNumbered c WHERE x.rowno % c.cnt = c.rowno - 1)
          END,
          CASE WHEN a.CustomerID = b.CustomerID THEN a.Country
               ELSE (SELECT c.Country FROM CitiesNumbered c WHERE x.rowno % c.cnt = c.rowno - 1)
          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
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, a.LastName, b.FirstName, a.Title,
          b.TitleOfCourtesy, a.BirthDate, b.HireDate, a.Address, b.City,
          a.Region, b.PostalCode, a.Country, b.HomePhone, a.Extension,
          ((a.ReportsTo - 1) * 100) + b.ReportsTo
   FROM   Northwind..Employees a
   CROSS  JOIN Northwind..Employees b
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)) +
                     substring(b.ProductName, charindex(' ', b.ProductName), len(b.ProductName)),
                    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)
   SELECT (a.OrderID - (SELECT MIN(OrderID) FROM Northwind..Orders)) * 10000 + b.OrderID,
          substring(a.CustomerID, 1, 3) + substring(b.CustomerID, 4, 2),
          ((a.EmployeeID - 1) * 100) + b.EmployeeID,
          a.OrderDate, b.RequiredDate, a.ShippedDate,
          (a.ShipVia - 1) * 100 + b.ShipVia, a.Freight, b.ShipName,
          a.ShipAddress, b.ShipCity, a.ShipRegion, b.ShipPostalCode, a.ShipCountry
   FROM   Northwind..Orders a
   CROSS  JOIN Northwind..Orders b
   WHERE  a.OrderID < b.OrderID
go
INSERT [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
   SELECT (a.OrderID - (SELECT MIN(OrderID) FROM Northwind..Orders)) * 10000 + b.OrderID,
          ((a.ProductID - 1) * 100) + b.ProductID,
          a.UnitPrice, b.Quantity, a.Discount
   FROM   Northwind..[Order Details] a
   CROSS  JOIN Northwind..[Order Details] b
   WHERE  a.OrderID < b.OrderID
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
CREATE FUNCTION intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                       number  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 (number) 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 (number) VALUES(convert(int, @leftover))

   RETURN
END
go
-- Create a table type on SQL 2008 and higher.
DECLARE @version varchar(30)
SELECT @version = convert(varchar(30), serverproperty('ProductVersion'))
IF convert(int, substring(@version, 1, charindex('.', @version) - 1)) >= 10
   EXEC('CREATE TYPE intlist_tbltype AS TABLE (val int NOT NULL PRIMARY KEY)')
go
DBCC SHRINKFILE (Northgale_log2, EMPTYFILE )
go
ALTER DATABASE Northgale REMOVE FILE Northgale_log2