Site Loading

Back to basics: Create Table Syntax

This is the start of a series of posts aimed at the theme of getting back to basics, which is aimed at how to do various tasks for those who are new to software.  This post is how to create a table in SQL:

Syntax:

Create Table TableName (
ColumnName1 ColumnType NULL / NOT NULL IDENTITY(1,1),
ColumnName ColumnType NULL / NOT NULL,
PRIMARY KEY (ColumnName1)
)

The table can have an identity which means that whenever a row is inserted the ColumnName1 will have a unique int value to distinguish itself.  The keyword Identity means that it starts at one and increases by one each time.

Example:

Create Table Work(
Id INT IDENTITY (1, 1),
Name NVARCHAR(255) NOT NULL,
Date DATETIME,
PRIMARY KEY (Id)
)

In the example above, these are some of the common column types that can be used:

SQL Server

  • int, integer storage.
  • decimal, decimal storage.
  • money, money storage.
  • char(n), this is a fixed character string length.
  • varchar(n), this is a variable character string length.
  • text, variable character string length.
  • nchar(n), this is a fixed unicode character string.
  • nvarchar(n), this is variable unicode character string.
  • ntext, variable unicode character string length.
  • bit, this is a boolean value that allows nulls.
  • binary(n), fixed binary string storage.
  • varbinary(n), variable binary string storage.
  • image, variable binary string storage.
  • DateTime, Date and time storage.

MySql

  • int, integer storage.
  • decimal, decimal storage.
  • money, money storage.
  • char(n), this is a fixed character string length.
  • varchar(n), this is a variable character string length.
  • tinytext, string storage of a maximum length of 255 chars.
  • text, variable character string length.
  • blob, variable binary string length.
  • bit, this is a boolean value that allows nulls.
  • DateTime, Date and time storage.

 

Close