T-SQL CREATE TABLE with example

Syntax

The Transact-SQL CREATE TABLE statement allows you to create and define a table. Here is the generic syntax:

CREATE TABLE [database_name.][schema_name.]table_name (
    column_1 data_type,
    column_2 data_type,
    ...,
    [table_constraints]
);

Example

Let’s create a new table Course:

CREATE TABLE dbo.Course (
  CourseID int,
  Title nvarchar(100),
  Credits int,
  DepartmentID int,
  CONSTRAINT FK_Course_Department FOREIGN KEY (DepartmentID) REFERENCES dbo.Department(DepartmentID),
  PRIMARY KEY (CourseID)
);

The query above created table Course in the schema dbo with 4 columns:

The first column CourseID with int datatype
The second column Title with nvarchar datatype, maximum of 100 characters in length
The third column Credits with int datatype
The final column DepartmentID with int datatype

Two other objects were also added:

A primary key was defined on the column CourseID
A foreign key was defined on the column DepartmentID, referencing column DepartmentID from the table Department in the same schema dbo.

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.