C# SQL Database: A Deeper Look Through Tim Corey’s Lesson

Introduction to Database Design

At 1:00, Tim starts by reminding viewers that they will receive all scripts and files in the package, including the database scripts. He emphasizes that the goal is not to become a full SQL Server expert in one lesson, but to understand the design and how to think about structuring data. Tim then moves directly into the database diagram.

Understanding the Database Diagram

At 1:52, Tim highlights the team members table and explains that it doesn’t store a person’s first name or last name directly. Instead, it stores a Person ID that links back to the People table. This is an example of a one-to-many relationship in a relational database, where one person can belong to multiple teams.

Tim gives a practical example: a person might be part of more than one team if multiple tournaments happen simultaneously. This avoids duplication and shows how using a primary key helps you query data efficiently. Tim stresses that duplicate data is harmful because it leads to conflicting information, such as inconsistent email addresses.

He explains that the design approach is to use IDs rather than storing full duplicate data. This is the foundation of good SQL Server database design.

Avoiding Duplicate Columns

At 4:48, Tim talks about why you should avoid storing multiple email addresses in separate columns like Email2 or Email3. Instead, he suggests creating a separate email addresses table that includes Person ID and Email Address columns. This allows one person to have multiple email addresses without complicating the People table.

Tim explains that the database design is a balance between simplicity and flexibility. For this tutorial, he decides to store only one email and one phone number, keeping the design clean and practical.

Creating a Database in SQL Server Management Studio

At 6:44, Tim switches to SQL Server Management Studio and explains that he is using SQL Server 2016 Developer Edition, which is free and has the same features as Enterprise Edition. He notes that it cannot be used in production, but it is perfect for development on a local machine.

Tim explains two ways to create a SQL Server database: through the SSMS dialog or using a SQL query. He prefers the query method because it’s faster. The command he uses is:

create database tournaments;

He explains that the semicolon is important and should be used at the end of every SQL statement. After creating the database, Tim shows how to refresh the SSMS window to see the new database appear.

Viewing Tables and Table Design

At 12:06, Tim mentions that he has already created all tables using scripts. He then demonstrates how to open the table designer in SSMS. Tim explains that SQL scripts can be intimidating for C# developers, and the designer helps you visually edit tables without needing to memorize SQL syntax.

Tim uses the Prizes table as an example. He explains how to set the primary key, choose data types, and manage null values. He also notes that the primary key helps SQL optimize table storage and retrieval. Without a primary key, SQL creates a “heap,” which makes queries slower.

Auto-Incrementing IDs

At 15:36, Tim explains how to make the ID column auto-increment using Identity Specification. He sets the increment and seed to 1. Tim emphasizes that this gives each row a unique identifier and helps SQL sort data efficiently.

He points out that this approach gives you a highly optimized design with minimal effort, which is perfect for C# developers who want a solid SQL Server database without complex tuning.

Understanding Data Types

At 18:09, Tim explains the different SQL string types: varchar, nvarchar, and nvarchar(max). He describes:

• Char: stores characters

• Var: variable length (up to a maximum number)

• N: Unicode support for international characters

Tim explains that nvarchar uses more storage but supports global languages, while varchar is ideal for English-only text. He also explains that nvarchar(max) is used for very large strings like JSON, which might be stored and later retrieved by an application.

Money and Float Data Types

At 24:00, Tim compares SQL data types to C# types. He uses money for currency values (similar to decimal in C#) and float for percentages (similar to double). This helps C# developers match SQL types with their programming types.

Table Relationships

At 25:04, Tim demonstrates how to create relationships using foreign keys. He opens the Relationships editor and explains that the primary table contains the real ID, while the foreign table references that ID.

He also explains the idea of cascade delete, which automatically removes related records when the parent record is deleted. Tim cautions that IDs should be treated as immutable, meaning you should never change them once created.

Creating a New Table

At 29:30, Tim demonstrates creating a new table using the SSMS editor. He builds a test person table with columns like:

• First Name

• Last Name

• Email Address

• Phone Number

• Number of Kids

• Create Date

Tim shows how to set default values, like setting Number of Kids to 0, and using getdate() to set the current time. He explains that getutcdate() is better for applications spanning time zones.

Editing Data in the Table

At 39:39, Tim shows how to edit rows directly in SSMS. He adds sample records and explains that even failed inserts still consume ID values. He advises to let SQL continue the auto-increment sequence and not to worry about missing IDs.

Basic SQL Queries

At 44:23, Tim teaches basic query syntax:

• SELECT * FROM table

• WHERE

• LIKE

• ORDER BY

• Column alias using AS

He notes that SELECT * is fine for learning, but in real applications you should specify columns for performance and clarity.

Stored Procedures

At 50:33, Tim introduces stored procedures. He explains that stored procedures allow you to access database logic securely and prevent SQL injection attacks. Tim prefers stored procedures over direct table access or views because they provide better security.

He shows how to create a stored procedure that retrieves prizes by tournament ID. He explains parameters, BEGIN and END blocks, and the NOCOUNT ON statement.

Executing Stored Procedures

At 58:04, Tim creates a stored procedure to get test persons by last name and demonstrates how to execute it using:

EXEC dbo.SP_TestPerson_GetByLastName 'Corey'

He explains that the dbo schema is the default and that SQL Server is not case sensitive for procedure names.

Final Thoughts

Tim in his video, concludes that SQL database design may look complicated, but the core concepts are simple. He encourages viewers to practice and explore, and previews the next lesson where he will connect the database to C# code.