How to Use CREATE DATABASE and CREATE TABLE in SQL Server

The SQL CREATE keyword defines a new database object. Two common starting points are CREATE DATABASE, which makes a database, and CREATE TABLE, which defines columns and constraints inside a database. SQL Server also supports other CREATE statements for schemas, views, indexes, procedures, and more.

The exact syntax varies between database systems. The examples in this guide use Transact-SQL for Microsoft SQL Server.

Create a database

The basic statement is:

CREATE DATABASE SchoolDb;

Creating a database requires suitable server-level permission. In SQL Server, the statement creates the database files and must run in autocommit mode rather than inside an explicit transaction. Production administrators may also specify file locations, sizes, growth settings, or a collation; use the CREATE DATABASE documentation for those options.

Switch the current connection to the new database before creating its tables:

USE SchoolDb;
GO

GO is a batch separator recognized by tools such as SSMS and sqlcmd; it is not a Transact-SQL statement sent to the Database Engine.

Create a table

A table definition normally includes a schema-qualified name, a data type for every column, and constraints that protect data quality:

CREATE TABLE dbo.Students
(
    StudentId  INT IDENTITY(1,1) NOT NULL,
    FullName   NVARCHAR(100) NOT NULL,
    Subject    NVARCHAR(100) NULL,
    BirthDate  DATE NULL,
    CreatedAt  DATETIME2(0) NOT NULL
        CONSTRAINT DF_Students_CreatedAt DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

This definition provides:

• StudentId: an automatically generated integer and the table’s primary key.
• FullName: required Unicode text with a maximum length of 100 characters.
• Subject and BirthDate: optional values, explicitly marked NULL.
• CreatedAt: a required timestamp with a default UTC value.

The SQL Server CREATE TABLE reference covers additional column, constraint, index, compression, and temporal-table options.

Choose data types deliberately

Data-type notation is not portable across every database. In SQL Server, write INT, not INT(3). The integer type has a fixed storage size and numeric range; a number in parentheses does not limit it to a certain count of digits. Microsoft’s integer type reference lists the supported ranges.

For strings, the number in NVARCHAR(100) sets the maximum character length. Use DATE for a calendar date, DATETIME2 for a date and time, and DECIMAL(p,s) when an exact number needs defined precision and scale. Avoid storing dates or numbers as text.

Insert and verify a row

INSERT INTO dbo.Students (FullName, Subject, BirthDate)
VALUES (N'Sam Rivera', N'Science', '2011-04-15');

SELECT StudentId, FullName, Subject, BirthDate, CreatedAt
FROM dbo.Students;

The N prefix marks Unicode string literals in SQL Server. Use parameters rather than concatenating user input into SQL in an application.

Common CREATE TABLE mistakes

• Missing keys: without a primary key, identifying and relating rows becomes harder.
• Overusing NULL: mark a column NOT NULL when every row must contain it.
• Wrong string size: choose a realistic length; do not default every field to MAX.
• Reserved or unclear names: use consistent, descriptive identifiers and qualify application tables with their schema.
• Skipping constraints: use CHECK, UNIQUE, and foreign keys when the database should enforce a rule.

To set up a practice environment, follow the SQL Server on Ubuntu guide. Once the table contains data, use LIKE, IN, BETWEEN, and IS NULL to filter its rows.