Tutorial Gateway

SQL Create Table

by

SQL Server use Tables to store and Manage Data. SQL Table is a combination of Rows and Columns. In order to create table in SQL Server we have to use SQL Create Table Statement

SQL Create Table Syntax

The basic syntax to create table in SQL Server is as shown below:

-- SQL Server Create Table Syntax 
CREATE TABLE [Table Name]
(
    Column_Name1 Data_Type(Size) [NULL | NOT NULL],
    Column_Name2 Data_Type(Size) [NULL | NOT NULL],
     …
    Column_NameN Data_Type(Size) [NULL | NOT NULL]
);

Let us see the individual items from the syntax:

  • Table Name: Please provide Unique table name here. If you write the already existing table name, it will throw an error
  • Column_Name: Please specify the Unique Column Names required for this table
  • Data Type: Please specify the valid Data type that the column will hold. For example, Int, Money, Varchar, Nvarchar and Date
  • Size: Data types like Varchar, Nvarchar, Char expects the size So, Please provide valid number here
  • NULL or NOT NULL: If you select NULL option then column will accept both normal values and NULL values otherwise, it will throw an error saying Column should not be empty

SQL Create Table Example

We are going to use the below shown code to create table called Customer inside the [SQL Tutorial] Database

-- SQL Server Create Table Example
USE [SQL Tutorial]
GO

CREATE TABLE [Customer]
(
  [CustomerKey] [int] IDENTITY(1,1) NOT NULL,
  [FirstName] [varchar](50) NULL,
  [LastName] [varchar](50) NULL,
  [BirthDate] [date] NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [Yearly Income] [money] NULL,
  [Profession] [nvarchar](100) NULL
)

NOTE: Before you start creating a TABLE, It is always advisable to check if a Table exists or not.

From the above code you can observe that, We declared 7 Columns:

  • Our first column is CustomerKey of Integer data type and it will not allow NULL values. We also defined this column as Identity starting with 1 and incremented by 1. (This is optional, If you want You can remove IDENTITY (1, 1) portion completely)
  • FirstName and LastName columns belongs to Varchar data type and it will allow NULL values. We also assigned the size to 50, it means both the columns will accept up to 50 characters
  • Fourth column is BirthDate of Date data type and it will allow NULL values. This will allow us to enter Date values only
  • EmailAddress and Profession columns belongs to NVarchar data type and it will allow NULL values.

From the below screenshot you can observe that, Command is executed successfully and you can see the Newly created table in our object explorer. Please expand the Columns folder to see the available columns

SQL Create Table 1

TIP: If you didn’t find the newly created table, Please click on the refresh button in object explorer

SQL Create Table Using GUI

Within the object explorer, Expand the Database folder in which you want to create table. Please select the Tables folder and Right click on it will open the context menu. Please select New option and then select the Table.. option as shown below

SQL Create Table 2

Once you select the Table.. option, following window will be opened to type the Column Name, Data Type and Check box to decide whether the column allows NULL values or Not

SQL Create Table 3

From the below screenshot you can observe that, We added 7 columns of different data types as shown below

SQL Create Table 4

Adding Identity using GUI

First, select the column and Go to the Column Properties tab. Please change the IsIdentity option from default No to Yes in Identity Specification property as shown below.

SQL Create Table 5

Once you finish creating the SQL table, Please click on the save button to save the table. Once you click on save button following window will be appeared to rename the table name.

SQL Create Table 6

Click OK to finish saving. Write the following Select statement to check whether the table holds all the column names or not.

SQL Create Table 7

TIP: You can use INSERT Statement to insert data into this newly created table

Thank You for Visiting Our Blog

Placed Under: SQL