• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server CREATE TABLE

SQL Server CREATE TABLE

Learning Objective

In this tutorial, you will learn how to create a new table in SQL Server using CREATE TABLE statement in SQL Server.

SQL Server CREATE TABLE Introduction

We all know the importance of tables in any database. The table holds the data for business. The name of the table is unique in a schema in the database. A database can hold multiple tables with the same name but in different schemas. Each table holds one or more columns and every column is associated with a data type that defines the kind of data it can store e.g. INT, char, strings, etc.

CREATE TABLE statement allows you to create a new table in SQL Server database.

SQL Server CREATE TABLE Syntax

Following is the syntax of CREATE TABLE syntax in SQL Server database.

CREATE TABLE [database_name.][schema_name.]table_name
(
   pk_column data_type PRIMARY KEY,
   column_1 data_type column_constraint,
   column_2 data_type,
   …..
   table_constraints
);

In this syntax,

  • CREATE TABLE – The keyword to create a new table in the SQL Server database.
  • database_name – Defines the name of the database where you want to create the table.
  • schema_name – Defines the schema name to which the new table belongs.
  • table_name – The name of the new table you want to create.
  • pk_column – Each table has one primary key which consists of one or more columns. Generally, primary key columns are listed first followed by other table columns. If your table contains only one column as the primary key, then you can specify it after mentioning the data_type of the column by appending the PRIMARY KEY keyword at the end. In case your table has a primary key consisting of more than one column, you have mentioned it as table_constraint at the end of the column definition.
  • column_constraint – A cloumn can have one or more constraints such as NOT NULL, IDENTITY, and UNIQUE.
  • table_constraint – A table can have one or more constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.

SQL Server CREATE TABLE Example

The following statement create a new table named employees under company schema.

CREATE TABLE company.employees
(
   emp_id INT PRIMARY KEY IDENTITY (1,1),
   first_name nVARCHAR(50) NOT NULL,
   last_name nVARCHAR(50) NOT NULL,
   dob DATE NOT NULL,
   join_date DATE NOT NULL,
   phone nVARCHAR(20),
   dept_id INT NOT NULL,
   FOREIGN KEY (dept_id) REFERENCES company.department (dept_id)
);

In the above example, we have not specified any database name. So in which database it will be created?

The table employees is created in the dbo database and we have mentioned the schema name explicitly, so it created in the company schema.

The table employees contains seven columns. Now let’s try to understand each column in great details.

  1. emp_id – The emp_id column is the primary key column of the table. The IDENTITY(1,1) defines a identity column, here this keyword instructs SQL Server to create values for the column
    automatically e.g. starting from the integer one and increment the value by one for each row.
  2. first_name & last_name – The column first_name and last_name defines with nVARCHAR(50) to hold the character values up to 50. Also NOT NULL in these instructs SQL Server not to accept any NULL values for these columns.
  3. dob & join_date – The column dob and join_date define with DATE datatype which holds the date of birth and joining date of the employee in DATE format.
  4. phone – The phone column is a varying character column that holds data up to 50 characters and can contain NULL.
  5. dept_id – The dept_id contains integer number, but can not holds NULL values.
  6. FOREIGN_KEY – In the end, we have defined one FOREIGN KEY table constraint. This foreign key constraint ensures that the dept_id value which inserts into the employees table must be present in the company.department table.

Summary

In this tutorial, you have learned how to create a new table in SQL Server using the CREATE TABLE statement in SQL Server.

Was this tutorial helpful?
YesNo
« Previous: SQL Server DROP SCHEMA
SQL Server RENAME TABLE :Next »

Reader Interactions

Trackbacks

  1. sql server create table syntax Info Online How To Sign Into Account - gobanklogin says:
    January 15, 2022 at 12:08 am

    […] SQL Server CREATE TABLE with Examples – SQL Server Tutorial […]

    Reply
  2. sql server create table example Info Online How To Sign Into Account - gobanklogin says:
    January 15, 2022 at 12:09 am

    […] SQL Server CREATE TABLE with Examples – SQL Server Tutorial […]

    Reply
  3. sql server create table Info Online How To Sign Into Account - gobanklogin says:
    January 15, 2022 at 12:10 am

    […] SQL Server CREATE TABLE with Examples – SQL Server Tutorial […]

    Reply
  4. Www create table sql server example Sign In Your Online Account - loginrecords.com says:
    February 2, 2022 at 7:05 pm

    […] SQL Server CREATE TABLE with Examples – SQL Server Tutorial […]

    Reply

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.