LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 1018 min read

Creating Tables

Learn CREATE TABLE syntax in SQL Server, how to define columns and their data types, and the difference between NULL and NOT NULL.

Introduction

With your database created and data types understood, it's time to build actual tables — the structures that will hold your rows of data. This lesson covers CREATE TABLE from the ground up.

CREATE TABLE Syntax

A CREATE TABLE statement names the table and lists its columns, each with a data type and optional constraints, separated by commas inside parentheses.

CREATE TABLE TableName (
    Column1 DataType constraints,
    Column2 DataType constraints,
    Column3 DataType constraints
);

Column Definitions

Each column definition names the column, states its data type, and optionally adds constraints — rules that restrict what values are allowed. You'll cover constraints like PRIMARY KEY and CHECK in detail in a later lesson; here, focus on the shape of a basic column list.

NULL vs. NOT NULL

By default, SQL Server allows a column to hold NULL — meaning 'no value' — unless you explicitly mark it NOT NULL. NULL is not the same as zero or an empty string; it represents the absence of a known value. Deciding which columns must always have a value is one of the most important design choices you'll make for a table.

DeclarationMeaning
Email NVARCHAR(100) NOT NULLEvery row must have an email value; INSERTs without one fail
MiddleName NVARCHAR(50) NULLMiddleName may be left unspecified (defaults to allowing NULL)

A Complete Example

Here's a realistic Students table, using the USE statement to make sure it lands in the right database.

USE SchoolDB;
GO


CREATE TABLE Students (
    StudentID INT NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NULL,
    DateOfBirth DATE NULL,
    EnrollmentDate DATE NOT NULL,
    IsActive BIT NOT NULL
);
Messages

Click Run to see what this code prints.

This table has no PRIMARY KEY yet — you'll add proper keys and other constraints in the Constraints lesson. For now, focus on getting comfortable with column lists and NULL/NOT NULL.

Viewing Your Table

Refresh the Tables folder for SchoolDB in Object Explorer and you'll see dbo.Students appear. You can also inspect its structure with T-SQL.

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Students';
Result

Click Run to see what this code prints.

The dbo Prefix

Notice the table shows up as dbo.Students in Object Explorer. dbo (database owner) is the default schema every table belongs to unless you specify otherwise — think of a schema as a namespace grouping related objects.

Common Mistakes

Avoid These Mistakes
  • Forgetting a comma between column definitions, or leaving a trailing comma after the last one — both cause syntax errors.
  • Marking every column NOT NULL 'to be safe' even when a value is genuinely optional, forcing awkward placeholder data later.
  • Running CREATE TABLE without a preceding USE, landing the table in the wrong database.
  • Choosing a table name that's also a T-SQL reserved word (like Order or User) without realizing it requires bracket-escaping everywhere afterward.

Best Practices

  • Decide NULL vs. NOT NULL deliberately for every column based on whether the value is truly required.
  • Use singular or plural table names consistently across your whole schema (this course uses plural, e.g. Students).
  • Keep column names in PascalCase or another single consistent convention throughout a project.
  • Review INFORMATION_SCHEMA.COLUMNS after creating a table to confirm types and nullability landed as intended.

Frequently Asked Questions

SQL Server rejects the entire INSERT statement and raises an error stating the column does not allow nulls, so no partial row is written.

Yes, as shown in this lesson's example. It's valid T-SQL, though in real projects you'll almost always add a PRIMARY KEY, covered in the Constraints lesson.

dbo stands for 'database owner' and is the default schema SQL Server assigns to new objects unless you specify another one. Most simple applications keep everything in dbo.

Technically yes, if wrapped in square brackets (e.g., [First Name]), but this is strongly discouraged — it forces bracket-escaping in every query. Use PascalCase or underscores instead.

Key Takeaways

  • CREATE TABLE defines a table's columns, each with a data type and optional constraints.
  • NOT NULL requires every row to have a value in that column; NULL (the default) allows it to be omitted.
  • New tables land in the dbo schema by default unless specified otherwise.
  • INFORMATION_SCHEMA.COLUMNS is a quick way to confirm a table's structure after creating it.

Summary

You've created your first real table with typed, nullable and non-nullable columns. Tables aren't set in stone once created, though — the next lesson covers ALTER TABLE, for adding, modifying, and removing columns after the fact.

Next Lesson →

Altering Tables