The Sharp Factory
Esc
↑ ↓ to moveEnter to open
Getting Started / Sample Database

Sample Database

A small shop database to try The Sharp Factory with: a SQL Server to run it on, the script, and the connection strings.

On this page

What Is In It#

SharpFactoryShop is a small shop: customers in cities, products, and orders with their lines. Everything is in the Sales schema. The samples in this documentation and the walkthroughs use it, so you can run them and get the same results.

ObjectKindHolds
CityTable4 cities
CustomerTable12 customers: name and last name (a unique key), city, balance, status and created date
ProductTable5 products and their prices
OrderTable10 orders, each of one customer
LineTable14 order lines: product, quantity and unit price
OrderTotalViewThe total of each order
GetCustomersByCityStored procedureThe customers of a city, and the number of customers searched in an output parameter
CustomersInCityTable functionThe customers of a city
OrderTotalForScalar functionThe total of one order
InvoiceNumberSequenceNumbers from 1000

No SQL Server Yet?#

Any SQL Server works. These two are free and take a few minutes to set up.

Windows: LocalDB#

LocalDB is a small SQL Server for developers that starts when a program connects to it. It comes with Visual Studio (the Data storage and processing workload) and with the SQL Server Express installer (choose Download Media, then LocalDB). sqllocaldb info lists MSSQLLocalDB when it is installed. Its server name is (localdb)\MSSQLLocalDB, with Windows authentication.

macOS, Linux and Windows: Docker#

With Docker Desktop (or Docker on Linux), this command starts SQL Server 2022 Developer edition in a container; ACCEPT_EULA=Y accepts SQL Server's license terms. Choose your own password: at least 8 characters, with three of upper case letters, lower case letters, digits and symbols.

Command line
docker run -d --name sharpfactory-sql -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=Sharp-Factory-1 -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

Its server name is localhost, with the SQL Server user sa and that password: in the app, turn off Windows authentication and enter them. After a restart, docker start sharpfactory-sql starts it again. If SQL Server already uses port 1433 on your computer, map another one (-p 14333:1433) and use the server name localhost,14333. On a Mac with Apple silicon, turn on Use Rosetta for x86_64/amd64 emulation on Apple Silicon in Docker Desktop's settings first.

Create The Database#

Download SharpFactoryShop.sql and run it once: it creates the database SharpFactoryShop with everything in it. Open it in SQL Server Management Studio, or in VS Code with the mssql extension, and run it; or run it with sqlcmd from the folder you saved it in (sqlcmd comes with SQL Server's tools; on macOS, brew install sqlcmd):

LocalDB
sqlcmd -S "(localdb)\MSSQLLocalDB" -E -I -i SharpFactoryShop.sql
SQL Server on this computer
sqlcmd -S localhost -E -I -i SharpFactoryShop.sql
Docker
sqlcmd -S localhost -U sa -P Sharp-Factory-1 -C -I -i SharpFactoryShop.sql

-I runs the script with quoted identifiers on, and -C trusts the container's own certificate. The whole script, to read or copy:

SharpFactoryShop.sql
/* SharpFactoryShop: the sample database of The Sharp Factory's documentation and walkthroughs.   A small shop: cities, customers, products, orders and their lines, a view, a stored procedure with an output   parameter, a table function, a scalar function and a sequence, with sample rows.   Run it once, on SQL Server (LocalDB, Express, Developer, the Docker image or your own server):   it creates the database SharpFactoryShop and everything in it. For example, with sqlcmd:       sqlcmd -S localhost -E -I -i SharpFactoryShop.sql                (Windows authentication)       sqlcmd -S localhost -U sa -C -I -i SharpFactoryShop.sql          (a SQL Server user; asks for the password)   or open it in SQL Server Management Studio or VS Code (mssql extension) and run it.   To start over, drop the database SharpFactoryShop and run the script again.   https://www.thesharpfactory.com/Documentation/GettingStarted/SampleDatabase */IF DB_ID(N'SharpFactoryShop') IS NULL    CREATE DATABASE SharpFactoryShopGOUSE SharpFactoryShopGOCREATE SCHEMA SalesGOCREATE TABLE Sales.City (    Id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_City PRIMARY KEY,    Name nvarchar(50) NOT NULL)GOCREATE TABLE Sales.Customer (    Id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,    Name nvarchar(50) NOT NULL,    LastName nvarchar(50) NOT NULL,    CityId int NULL CONSTRAINT FK_Customer_City REFERENCES Sales.City (Id),    Balance decimal(18,2) NOT NULL CONSTRAINT DF_Customer_Balance DEFAULT (0),    Status nvarchar(20) NOT NULL CONSTRAINT DF_Customer_Status DEFAULT (N'Active'),    CreatedDate datetime2(0) NOT NULL CONSTRAINT DF_Customer_CreatedDate DEFAULT (SYSDATETIME()),    CONSTRAINT UK_Customer_Name UNIQUE (Name, LastName))GOCREATE TABLE Sales.Product (    Id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Product PRIMARY KEY,    Name nvarchar(100) NOT NULL,    Price decimal(18,2) NOT NULL)GOCREATE TABLE Sales.[Order] (    Id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Order PRIMARY KEY,    CustomerId int NOT NULL CONSTRAINT FK_Order_Customer REFERENCES Sales.Customer (Id),    OrderDate datetime2(0) NOT NULL CONSTRAINT DF_Order_OrderDate DEFAULT (SYSDATETIME()))GOCREATE TABLE Sales.Line (    Id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Line PRIMARY KEY,    OrderId int NOT NULL CONSTRAINT FK_Line_Order REFERENCES Sales.[Order] (Id),    ProductId int NOT NULL CONSTRAINT FK_Line_Product REFERENCES Sales.Product (Id),    Quantity int NOT NULL,    UnitPrice decimal(18,2) NOT NULL)GOCREATE VIEW Sales.OrderTotal AS    SELECT o.Id AS OrderId, o.CustomerId, o.OrderDate, SUM(l.Quantity * l.UnitPrice) AS Total    FROM Sales.[Order] o JOIN Sales.Line l ON l.OrderId = o.Id    GROUP BY o.Id, o.CustomerId, o.OrderDateGOCREATE PROCEDURE Sales.GetCustomersByCity @CityID int, @TotalCustomersSearched int OUTPUT ASBEGIN    SET NOCOUNT ON;    SELECT @TotalCustomersSearched = COUNT(*) FROM Sales.Customer;    SELECT Id, Name, LastName FROM Sales.Customer WHERE CityId = @CityID ORDER BY LastName;ENDGOCREATE FUNCTION Sales.CustomersInCity (@CityID int) RETURNS TABLE AS    RETURN (SELECT Id, Name, LastName, Balance FROM Sales.Customer WHERE CityId = @CityID)GOCREATE FUNCTION Sales.OrderTotalFor (@OrderID int) RETURNS decimal(18,2) ASBEGIN    RETURN (SELECT COALESCE(SUM(Quantity * UnitPrice), 0) FROM Sales.Line WHERE OrderId = @OrderID);ENDGOCREATE SEQUENCE Sales.InvoiceNumber AS int START WITH 1000 INCREMENT BY 1GO/* sample rows */INSERT INTO Sales.City (Name) VALUES (N'Seattle'), (N'Portland'), (N'Denver'), (N'Austin');INSERT INTO Sales.Customer (Name, LastName, CityId, Balance, Status, CreatedDate) VALUES    (N'Ana', N'Torres', 1, 250.00, N'Active', '2026-01-12'),    (N'Ben', N'Carter', 1, 0.00, N'Active', '2026-02-03'),    (N'Chloe', N'Nguyen', 2, 1240.50, N'Active', '2026-02-20'),    (N'David', N'Okafor', 3, 75.25, N'Active', '2026-03-08'),    (N'Elena', N'Rossi', 1, 0.00, N'Closed', '2026-03-15'),    (N'Farid', N'Haddad', 4, 980.00, N'Active', '2026-04-01'),    (N'Grace', N'Kim', 2, 15.00, N'Active', '2026-04-22'),    (N'Hugo', N'Lambert', 3, 0.00, N'Active', '2026-05-10'),    (N'Isla', N'Bennett', 1, 410.75, N'Active', '2026-06-02'),    (N'Jonas', N'Weber', 4, 0.00, N'Closed', '2026-06-18'),    (N'Keiko', N'Sato', 2, 330.00, N'Active', '2026-07-07'),    (N'Liam', N'Murphy', 3, 52.40, N'Active', '2026-08-14');INSERT INTO Sales.Product (Name, Price) VALUES    (N'Espresso beans 1 kg', 24.00), (N'Ceramic mug', 9.50), (N'Pour-over kettle', 45.00),    (N'Paper filters (100)', 6.00), (N'Burr grinder', 129.00);INSERT INTO Sales.[Order] (CustomerId, OrderDate) VALUES    (1, '2026-09-01'), (1, '2026-10-05'), (3, '2026-09-12'), (3, '2026-10-20'), (3, '2026-11-02'),    (4, '2026-10-01'), (6, '2026-09-25'), (7, '2026-10-11'), (9, '2026-11-04'), (11, '2026-10-29');INSERT INTO Sales.Line (OrderId, ProductId, Quantity, UnitPrice) VALUES    (1, 1, 2, 24.00), (1, 2, 2, 9.50), (2, 4, 3, 6.00), (3, 5, 1, 129.00), (3, 1, 1, 24.00),    (4, 3, 1, 45.00), (5, 1, 4, 24.00), (6, 2, 6, 9.50), (7, 5, 1, 129.00), (7, 3, 1, 45.00),    (8, 4, 2, 6.00), (9, 1, 1, 24.00), (9, 2, 1, 9.50), (10, 3, 1, 45.00);GO

Connection Strings#

In the app, the server name and sign-in above are all it needs. In your application's code, register one of these with Database.RegisterModelConnectionString (see Project Setup):

C#
// LocalDBvar localDb = "Server=(localdb)\\MSSQLLocalDB;Database=SharpFactoryShop;Integrated Security=true";// SQL Server on this computervar local = "Server=localhost;Database=SharpFactoryShop;Integrated Security=true;Encrypt=False";// the Docker containervar docker = "Server=localhost;Database=SharpFactoryShop;User ID=sa;Password=Sharp-Factory-1;Encrypt=False";

In C#, the backslash in (localdb)\MSSQLLocalDB is written twice, or the string starts with @. Encrypt=False lets your application connect to a server without a trusted certificate, like a new SQL Server or the container (see Troubleshooting).

Use It With The Samples#

When you create the model from it, name the model AppDb and use MyApp as the root namespace: the names in the samples (repo.AppDb.Sales.Customer, using MyApp.Query;) then match your generated code. Then write your first program.

Start Over#

The samples on the Create, Update and Delete pages change rows. To get the original rows back, drop the database (for example DROP DATABASE SharpFactoryShop in SQL Server Management Studio) and run the script again.

Something wrong or unclear on this page? Tell us on GitHub