Aug 30, 2018 
893.2k
23
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
- SQL Server Blogs -
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: MSBI Tutorial Guide for Beginners in 2024
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.
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.
SQL Server Training & Certification
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: SQL Server Indexes-All You Need to Know
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.
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: All About SQL Joins and Subqueries
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.
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.
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
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.
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: Know Everything About a SQL Developer Salary for Freshers & Experienced Professionals in 2021!
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.
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!
Learn SQL Server in the Easiest Way
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: Future Growth of a SQL Server Developer
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’
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: SQL Fiddle: The Best Resource to Practice SQL online
FaceBook
Twitter
LinkedIn
Pinterest
Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Aug 30, 2024
791.8k
791.8k