Create Table - (SQL Server Database)

Creating tables is one of the core components of database design. Tables is where all the data is stored. In SQL Server, you can create tables using the CREATE TABLE statement. In this page, we will discuss how to create tables in SQL Server using the CREATE TABLE statement.

Syntax

Here is the basic syntax of the CREATE TABLE statement:

CREATE TABLE [database_name].[schema_name].[table_name] (
   [column_one] [data_type] [optional_parameters] [constraints],
   [column_two] [data_type] [optional_parameters] [constraints],
   ...
   [column_n] [data_type] [optional_parameters] [constraints]
);

Let's break down the syntax into its components:

• database_name: the name of the database where the table will be created. This is optional if you are creating the table in the current database.
• schema_name: the name of the schema where the table will be created. This is optional, and if not specified, the table will be created in the user's default schema.
• table_name: the name of the new table that you are creating.
• column_one, column_two, ... , column_n: the names and data types of the columns you want to add to the table.
• data_type: the data type of the column, such as int, varchar, date, etc.
• optional_parameters: the column can be assigned optional parameters like length and precision.
• constraints: the rules to the value the column can hold, such as NOT NULL, UNIQUE, CHECK, etc.

Example

Let us see an example of creating a customer table with 3 columns:

• id: integer type that is auto-incremented and acts as the primary key of the table.
• name: varchar type that is of length 100 to store customer name.
• email: varchar type that is of length 50 to store customer email address.

CREATE TABLE [dbo].[customer] (
   [id] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   [name] VARCHAR(100),
   [email] VARCHAR(50)
);

Output

Executing the above SQL statement will create a table named customer with 3 columns in the default schema of the current database.

Explanation

The CREATE TABLE statement creates a new table in a specified database whereby the table is created with one or more columns. The column definitions specify the name of the column, its data type, optional size and precision, and any additional not null constraints.

The primary key constraint is created using the PRIMARY KEY keyword in the column definition. Here the id column is assigned a constraint of primary key, and NOT NULL declaration which ensures that it doesn't allow null values.

Use

This statement is used to create tables to store data in a database in SQL Server.

Important Points

• A table must be unique within a database.
• A database can have multiple tables with the same name, provided they belong to different schemas within that database.
• Primary keys uniquely identify each row within a table.

Summary

In this page, we discussed how to create tables in SQL Server using the CREATE TABLE statement. We covered the syntax, example, output, explanation, use, and important points of creating tables in SQL Server. Tables are central to databases, and understanding this syntax is necessary for advanced database design and development.