How to Create Table in SQL Server by SQL Query?

Introduction

Data or information in the SQL server database is stored in tables. Each table has a unique name that is made up of rows and columns. The columns in a table are specified but it could have plenty of rows. Each column has a specific name and data type for the column.

Further, Rows contain the records or data for the columns. Here, in this blog, we will discuss how to create a table in SQL server by SQL query. Once you are sure how to create a table with the right syntax and how to set the parameters, you will immediately understand how to add data in tables.

Read: Comparative Study of SQL and NoSQL Databases

Before we dive deep into the topic, this is necessary to learn different data types and how to put them into an SQL table. In SQL, there are 40 different data types but we will focus on seven important ones that might be frequently used by database developers in 2020.

• Integer – This data type defines the whole number without any fractional part.
• Decimal – Here, you can define the number with fractions as well.
• Boolean – It will define the binary value either True or False.
• Date – This data type speaks itself, you just have to choose the format.
• Time – It will define the time, you have to select the format for this data type.
• Timestamp – If date and time need to define together then use the Timestamp data type.
• Text – This is another common data type but it focused on alphabets only or a mix of letters or numbers or any other characters.

You must be wondering why is this important to learn data types before we create a table in SQL. This is because the data is stored in rows and columns within tables. For each column, this is necessary to define the data types and once they are defined, stick to them.For example, if a column is defined with an integer data type then you can include only numeric values, not the characters. So, this is necessary that you should be conscious of data types also have a deeper knowledge of them before you actually create them.

Request for a demo class now.

How to create a table using SQL Query?

Creating a table in the SQL involves the name of a table, the definition of columns and its associated data types too. The CREATE TABLE Query in SQL is used to create a new table. The basic syntax for creating a new table in SQL is given below –

CREATE TABLE new_table_name ( first_column first_column_data_type, second_column second_column_data_type, third_column third_column_data_type, - last_column last_column_data_type ); 

Create Table keyword is used at the beginning of the statement and it will tell the database system that you are interested in designing a new table. The name of the table should be unique based on the data you are planning to store on a table. Name of the table should be clear and transparent. However, SQL is not case-sensitive for table names.

Read: Advanced SQL Server Interview Questions and Answers

Immediately after the statement, you can see the brackets defining different columns and their associated data types too. If you wanted to create the copy of an existing table then you should use the Create Table and Select commands together. Other than data types, you are free to add extra parameters too as per the requirements. However, the list of parameters is not limited but we will focus on three major categories that are frequently used by the database developers.

• Not Null – if you will add this constraint then you could not enter null values in the column.
• Unique – if you add this constraint then you cannot add duplicate values in the tables. This is especially important when you wanted to store unique values in the table.
• Primary Key – Practically, this is a combination of Not Null and Unique constraints but it has many more technical advantages too. To know more about what is a primary key in SQL, you should check our blog and get an in-depth understanding of the topic to use it effectively.

How to create a table in SQL Server by Management Studio?

In this section, we will discuss the creation of a table in SQL Server 2012, if you are using any another version then follow the steps accordingly.

Read: What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)

Design your table

Before we design the actual table, take a pen and paper then sketch all data elements and their relationships based on business needs. Think of the solid design before you actually start creating a table. Further, don’t forget to incorporate the database normalization to guide your work.

Initialize the Management Studio

Once you have decided on the design of a table, it's time to start the SQL server management studio to begin the actual implementation. Move ahead, open SSMS and connect with the server that hosts the database where you would like to create a new table.

Navigate the Right Folder

There is a folder called “databases” on the left-hand side, expand the folder and see the corresponding files hosted on the server. Expand the folder corresponding to the location you are interested in creating a new table. Check again and make sure that the table reflects in the database structure. You have to understand that you are going to create any duplicate table otherwise it will result in many fundamental problems that are difficult to correct.

Read: What is Complex SQL Queries? Explain Complex SQL Queries with Examples

Start with the table creation

First of all, right click on the Tables Folder from the pop-up menu. It will open the new pane within SSMS and you are ready to create a new database table.

Add columns in the table

The design interface allows you to add table properties too. For each attribute you wanted to include in the table, you could specify there. It includes the column name, data types, and other important table constraints like Not Null, Unique, or Primary key as discussed earlier.

Read: How To Learn SQL in 2023?

Assign the Primary key

Choosing the right primary key for the table is an important step that affects the overall database performance and usability. To define the primary key in SQL server, first select the column, you wanted to use as the primary key then click on the key icon from the taskbar to set the primary key. Here, you could define different attributes for primary key based on requirements.

Save the Table

Once you have defined the primary key, use the disk icon to save the table on the server. You will be asked to give a proper name to the table before you save it. Make sure that name is decided wisely that could help you in understanding the purpose of the table. That’s all for the day! Congratulations, you have learned how to create a table in SQL Server 2012 by management studio!

How to create temp tables in SQL?

As the name suggests, these are the tables that exist temporarily on the database servers. It will store the subset of data for a certain time period from the normal table. Temp tables are extremely helpful when you have voluminous records and need to deal with a set of records almost daily. Instead of filtering data again, and again, it is stored in the temporary table and you can execute queries on temporary tables based on requirements. Here, we will discuss how to create temp data in a simple scenario. The best idea of creating a temp table by using INTO statement within the Select statement. Here is the example of syntax that will help you in understanding the temp tables in SQL.

USE schooldb; SELECT name, age, gender INTO #maleStudents FROM student Where Gender = ‘Male’ 

Look at the given example carefully, Here, one temp table is created with the name Male Students that stores the name, age, and gender of male students from the student table. To create a temp table, always use INTO statement after the Select statement as shown in the example. The name of a temp table would always start with the # keyword.

When you will check the detail in the server then one unique identifier is also added along with the table name. Don’t get confused here because this identifier is added automatically by the server. Now you are ready to perform operations on the temp table as it is done in the normal table.

Read: Different Type of SQL Joins

Keep in mind that temp table is accessible through the channel from which it was created. It is not possible to access the temp table from any other connection. If you wanted to make it accessible through app open connections then you should use the global temporary tables here. The name of global temporary tables starts with the double hash symbol as shown in the example below.

USE schooldb; SELECT name, age, gender INTO ##femaleStudents FROM student Where Gender = ‘female’

Next Steps:

With this discussion, we have enough information now on the Create Table keyword. Also, you must be sure of how to create a table in SQL Server 2012 by management studio. Now you are ready to work on the database and start creating a table to store the data and assign important constraints too wherever it is necessary. All the best with JanBask Training and feel free to ask queries by our expert mentors for SQL certification program!

Read: Most Popular SQL Server Performance Tuning Tips

FaceBook Twitter Google+ LinkedIn Pinterest Email