SQL Create Table

You create a table using the CREATE TABLE command.

SQL syntax

CREATE TABLE table_name
(column_name_1 datatype,
column_name_2 datatype,
...
);

Example

CREATE TABLE Individual
(IndividualId int,
FirstName Varchar(255),
LastName Varchar(255),
UserName Char(10)
);

Result

This results in an empty table. You can now use an INSERT statement to add data to the table.

IndividualIdFirstNameLastNameUserName
    

Data Types

You'll notice we explicitly stated the data type in our CREATE TABLE statement. This is because, when you create a column, you need to tell the database what type of data it can hold.

The exact data types and how they are expressed differs with each database system and vendor, but you'll find that generally, there will be support for fixed length strings (eg, char), variable length strings (eg varchar), date/time values (eg, datetime), numbers and integers (eg, bigint, int, smallint, tinyint, numeric).

The following base data types are available in SQL Server.

Exact Numerics

bigintnumeric
bitsmallint
decimalsmallmoney
inttinyint
money

Approximate Numerics

floatreal

Date and Time

datedatetimeoffset
datetime2smalldatetime
datetimetime

Character Strings

charvarchar
text

Unicode Character Strings

ncharnvarchar
ntext

Binary

binaryvarbinary
image

Other Data Types

cursortimestamp
hierarchyiduniqueidentifier
sql_variantxml
table Spatial Types

You may need to consult your database system's documentation if you're unsure of which data type to use or how it is expressed in that system.

Next, we learn how to create an index for our table with the CREATE INDEX command.