SQL Server With ASP.NET Core: Creating the Database

Updated on September 1, 2026

The ASP.NET Core Web API we build across this series reads and writes two SQL Server tables: Owner, holding a person, and Account, holding the accounts that person owns. One owner has many accounts, and that single relationship is the whole data model.

This part creates that database. We can run one script against a local SQL Server instance, or draw the schema in a designer and let it generate the same script for us. Both routes end at the same two tables, and the rest of the series starts from there.

If you want to see all the basic instructions and complete navigation for this tutorial, please click on the following link: Introduction page for this tutorial.

What Database Does the ASP.NET Core Web API Series Use?

The series uses a SQL Server database named AccountOwner with two tables and one relationship between them.

Owner holds a person: an id, a name, a date of birth, and an address. Account holds a bank account: an id, the date it was created, its type, and the id of the owner it belongs to.

The relationship is one-to-many. One owner can hold several accounts, so Account carries the foreign key, not Owner.

Both primary keys are UNIQUEIDENTIFIER, which is how SQL Server stores a GUID, and the reason for that choice gets a section of its own further down.

The foreign key updates in step with its parent and refuses to leave an orphan. Renaming an owner’s id cascades to that owner’s accounts; deleting an owner that still has accounts is rejected until the accounts go first.

Everything the API does across the remaining five parts is a read or a write against these two tables.

How Do We Create the SQL Server Database?

We run one script. The repository ships a T-SQL script that creates the AccountOwner database, both tables, and the foreign key between them.

On Windows the host is usually LocalDB, which Visual Studio installs with its data storage and processing workload. Where it is present, sqlcmd pointed at (localdb)\MSSQLLocalDB runs the script against an instance that is already there.

Everywhere else the fastest route is a container. One docker run line starts a SQL Server instance, and the same script runs against it from the command line or from any client that can execute a file.

Either way the script is the source of truth. It drops the tables before it creates them, so running it a second time rebuilds the schema rather than failing on names that already exist.

Drawing the schema in a designer is the other route, and the section below covers it. A designer does not create the database directly, it generates this same script and runs it for us.

On Windows, that is one command:

sqlcmd -S "(localdb)\MSSQLLocalDB" -i init.sql

Everywhere else, we start a container first and then point the same command at it:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStrong!Passw0rd" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest
sqlcmd -S localhost -U sa -P "YourStrong!Passw0rd" -C -i init.sql

The password has to satisfy SQL Server’s complexity rule, which is at least eight characters drawn from three of the four categories: uppercase letters, lowercase letters, digits, and non-alphanumeric characters. The -C flag tells sqlcmd to trust the container’s self-signed certificate, which the newer client builds require and LocalDB does not. The repository also ships a docker-compose.yml beside the script, so docker compose up -d starts the same instance if we prefer a file to a command line.

The script itself creates the database, then the two tables and the foreign key between them:

CREATE TABLE [dbo].[Owner]
(
    [OwnerId]     UNIQUEIDENTIFIER NOT NULL,
    [Name]        NVARCHAR(60)     NOT NULL,
    [DateOfBirth] DATE             NOT NULL,
    [Address]     NVARCHAR(100)    NOT NULL,
    CONSTRAINT [PK_Owner] PRIMARY KEY CLUSTERED ([OwnerId])
);
GO

CREATE TABLE [dbo].[Account]
(
    [AccountId]   UNIQUEIDENTIFIER NOT NULL,
    [DateCreated] DATE             NOT NULL,
    [AccountType] NVARCHAR(45)     NOT NULL,
    [OwnerId]     UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ([AccountId]),
    CONSTRAINT [FK_Account_Owner] FOREIGN KEY ([OwnerId])
        REFERENCES [dbo].[Owner] ([OwnerId])
        ON UPDATE CASCADE
        ON DELETE NO ACTION
);
GO

The DROP TABLE statements that precede this in the script drop Account before Owner. That order is not cosmetic: dropping the parent while the foreign key still exists fails with Msg 3726, so the child has to go first for the script to be re-runnable.

How Do We Populate the Tables With Data?

We insert the owners first and the accounts second. The foreign key makes the order mandatory: every row in Account names an owner, and SQL Server rejects a row whose owner does not exist yet.

The sample data is four owners and eight accounts, with fixed GUIDs written into the script rather than generated. Fixed ids matter more than they look, since the later parts of this series call endpoints with these exact ids, so a reader who generates fresh ones cannot follow along.

Each INSERT names its columns explicitly rather than relying on the order the table was created with. That is the safer form and it is what the script below does.

Backing the data up afterwards is one command against the server, and it is worth doing once before the API starts writing to these tables.

Here are both statements, in the order the script runs them:

INSERT INTO [dbo].[Owner] ([OwnerId], [Name], [DateOfBirth], [Address])
VALUES
    ('24fd81f8-d58a-4bcc-9f35-dc6cd5641906', N'John Keen',     '1980-12-05', N'61 Wellfield Road'),
    ('261e1685-cf26-494c-b17c-3546e65f5620', N'Anna Bosh',     '1974-11-14', N'27 Colored Row'),
    ('a3c1880c-674c-4d18-8f91-5d3608a2c937', N'Sam Query',     '1990-04-22', N'91 Western Roads'),
    ('f98e4d74-0f68-4aac-89fd-047f1aaca6b6', N'Martin Miller', '1983-05-21', N'3 Edgar Buildings');
GO

INSERT INTO [dbo].[Account] ([AccountId], [DateCreated], [AccountType], [OwnerId])
VALUES
    ('03e91478-5608-4132-a753-d494dafce00b', '2003-12-15', N'Domestic', 'f98e4d74-0f68-4aac-89fd-047f1aaca6b6'),
    ('356a5a9b-64bf-4de0-bc84-5395a1fdc9c4', '1996-02-15', N'Domestic', '261e1685-cf26-494c-b17c-3546e65f5620'),
    ('371b93f2-f8c5-4a32-894a-fc672741aa5b', '1999-05-04', N'Domestic', '24fd81f8-d58a-4bcc-9f35-dc6cd5641906'),
    ('670775db-ecc0-4b90-a9ab-37cd0d8e2801', '1999-12-21', N'Savings',  '24fd81f8-d58a-4bcc-9f35-dc6cd5641906'),
    ('a3fbad0b-7f48-4feb-8ac0-6d3bbc997bfc', '2010-05-28', N'Domestic', 'a3c1880c-674c-4d18-8f91-5d3608a2c937'),
    ('aa15f658-04bb-4f73-82af-82db49d0fbef', '1999-05-12', N'Foreign',  '24fd81f8-d58a-4bcc-9f35-dc6cd5641906'),
    ('c6066eb0-53ca-43e1-97aa-3c2169eec659', '1996-02-16', N'Foreign',  '261e1685-cf26-494c-b17c-3546e65f5620'),
    ('eccadf79-85fe-402f-893c-32d3f03ed9b1', '2010-06-20', N'Foreign',  'a3c1880c-674c-4d18-8f91-5d3608a2c937');
GO

Turning the order around is not a style preference. An Account row whose OwnerId names nobody fails with Msg 547, because foreign key checking is on by default and the script never switches it off.

Designing the Schema in a Database Designer

The database already exists at this point. This section shows how the schema was designed, which is worth reading if we want to change it, and skippable if we do not.

A designer is a drawing surface over the same DDL. SQL Server Management Studio’s database diagrams do this on Windows, and any modelling tool that targets SQL Server will do the same job. Nothing below is required to follow the rest of the series.

Creating Tables in the Model Diagram

We add two tables to the diagram and name them Owner and Account, then give each one its columns.

Owner gets OwnerId, Name, DateOfBirth, and Address. Account gets AccountId, DateCreated, AccountType, and OwnerId. Both id columns are UNIQUEIDENTIFIER and both are the table’s primary key.

The two text columns on Owner are NVARCHAR(60) and NVARCHAR(100), and AccountType is NVARCHAR(45). Those lengths are the same ones the entity classes validate against later in the series, so changing them here means changing them there too.

Adding Table Relations

One owner can hold several accounts, so the relationship is one-to-many and the foreign key belongs on Account. We draw it from Account.OwnerId to Owner.OwnerId.

The two referential actions are worth setting deliberately. ON UPDATE CASCADE means changing an owner’s id updates the matching rows in Account instead of breaking them. ON DELETE NO ACTION means deleting an owner that still has accounts is refused, which is what preserves referential integrity here. NO ACTION is also SQL Server’s default when no delete rule is given, so leaving that dropdown alone produces the same script.

If we want the wider picture of how a model expresses this, our article on one-to-many and the other EF Core relationships covers the same shape from the entity side.

Exporting the Schema to a Script File

The designer’s export produces a CREATE script, and that script is the artifact we keep. Ask it to generate DROP statements before each CREATE, so the file can be run again without failing on tables that already exist.

Two details decide whether the exported file is re-runnable. The DROP statements have to name Account before Owner, and the whole thing has to run in one pass against an empty server as well as a populated one. The script in the repository does both, which is why it is the file the article points at rather than a screenshot of the designer.

Why Do the Primary Keys Use UNIQUEIDENTIFIER?

UNIQUEIDENTIFIER is SQL Server’s native type for a GUID. It stores the value as sixteen bytes, not the thirty-six characters a GUID takes when written out as text.

We use GUIDs rather than auto-incrementing integers because the API generates ids before the row exists. A client can create an owner and its accounts in one round trip without waiting for the database to hand back a number.

The cost is not size, it is order. A random GUID lands anywhere in a clustered index, so inserts scatter across pages instead of appending to the end, and the index fragments as the table grows.

SQL Server’s NEWSEQUENTIALID() avoids that by generating GUIDs that increase, which keeps inserts at the end of the index. It applies only to values the database generates, so it does not help ids the API creates.

We stay with client-generated GUIDs here because the API owns identity in this design, and at the scale this series reaches the fragmentation costs nothing worth measuring.

The trade-off is measurable rather than theoretical. We ran twenty thousand single-row inserts into three tables carrying the same 200-byte payload, on SQL Server Express LocalDB 13.0.4001.0 (the build named in the tested-with line) in August 2026. The random-GUID clustered key finished at 99.17% fragmentation and 66% page fullness, against 0.52% and 97% behind a sequential one.

Microsoft’s NEWSEQUENTIALID reference states that it “can only be used with DEFAULT constraints on table columns of type uniqueidentifier”, so it is not an option for an id the API generates before the row exists.

It is also worth knowing that the schema here is hand-written, so if we would rather have the model generate it, our guide on generating a schema from your model instead, with migrations takes the other route.

Conclusion

We now have a SQL Server database named AccountOwner, two tables with a one-to-many relationship between them, and eight accounts spread across four owners. That is everything the rest of this series reads and writes.

From here, the API needs to reach it. Part 4 wires the connection up, and if we want to read ahead, our articles on how to set the connection string this database needs and how EF Core maps entities to these tables cover both halves.

Thank you for reading, and next up is Service Configuration in ASP.NET Core Web API With Extension Methods, where we start diving into the .NET world. The series introduction page lists every part if you would rather jump around.

This series builds the API from the database up. The Ultimate ASP.NET Core Web API course takes the same project much further, through validation, authentication, versioning, caching, and testing, with the finished source at every step.

Tested with SQL Server 2022 (mcr.microsoft.com/mssql/server:2022-latest), the image the series’ compose file and its integration tests use, and with SQL Server Express LocalDB 13.0.4001.0.