Esat Erkec

An introduction to SQL tables

July 17, 2020 by

In this article, we will learn the concept of SQL tables and then work on how we can create tables with different techniques in SQL Server.

A relational database model is one of the most used data models to store and process the data. Tables are the essential elements of a database. In this context, tables are the database objects that hold the data in the relational databases. A database contains one or more tables and these tables can be modeled as relational. The tables come into existence from the columns and every column must have a name and a data type. These columns store data according to the defined data types and these data records are called rows. The following illustration shows an example of a table structure.

Anatomy of a SQL table

As we can see, the Persons table consists of Id, Name, Surname, and Age columns. These columns hold either numeric or text data according to defined data types. Now, we will learn how to create tables with different techniques.

Create a table using SQL Server Management Studio (SSMS)

SQL Server Management Studio is an IDE (integrated development environment) that helps to manage SQL Server and building T-SQL queries. So, it is very easy to create a table through the SSMS. After connecting to the database, we right-click on the Tables folder and select the New on the pop-up menu and click the Table option.

Create a table using SSMS

A New Table customized window will be shown to create a table quickly. At the same time, we can find the properties of the selected column at the bottom of the same screen.

 Property of a column in SSMS

On the New Table window, we can define the columns with names and data types. Each column must have a data type and a unique name.

Designing a table on SSMS

Tip: When we checked the Allow Nulls option enables us to store null values to the associated column.

When we right-click on any column, we can set some options for this column. The right-arrow indicates which column we are working on.

Changing the primary key setting of a column in SSMS

Set Primary Key: The primary key is a value or combination of values that help to uniquely identify each row on the table. For this table, we will set the Id column as a primary key. After setting a column as a primary key, a key sign will be appeared on the column.

Set a column as a primary key

When we try to check the Allow Nulls option for a column that has been set as a primary key, SSMS returns an error.

Primary key columns do not allow to null values

As we stated, SQL tables allow us to specify composite primary keys. Only we need to select more than one column with the help of the shift key and click the Set Primary Key option.

Set a column as primary key

The key sign will be shown for these multiple columns.

How to create a composite primary key in SQL Server

Relationships: This option provides to define a foreign key relationship with the other tables.

Indexes/Keys: Through this option, we can create indexes or set unique constraints for the columns.

Check Constraints: Check constraints are used to control according to specified rule the data that will be stored by the columns. This option provides to create this type of rules (constraints).

Properties: When we select this option for any column, we can reach table property windows.

Giving a name to the table before to create a SQL table

On this screen, we can change the table name and other properties of the table. As a final step, we will click the save button or the CTRL+S key combination. The created table will be shown under the Tables folder.

Find created table under the table folder in SSMS

Create a table using T-SQL

The CREATE TABLE statement is used to create a new table in SQL Server. The query below creates the Persons table. After the CREATE TABLE statement, we define the table name. In the brackets, we specify the column names and data types. Additionally, we set the Id column as a primary key.

Error  message

We get an error after executing the query because a table with the same name exists under the same schema. For this reason, before creating a table, we need to check the existence of the table thus we can avoid this type of errors. The DROP TABLE condition will be executed if the Persons table already exists on the database.

Create a SQL table through the query

Create a table from an existing table

We can create a new table from the existing table. SELECT INTO statement creates a new table and inserts the result set of the SELECT query to the new table. However, if we want to create an empty copy of a table we can use the following method. This method uses a WHERE condition that causes an empty result set to be returned from the query.

How to create a table from an existing one

The disadvantage of this method is that it does not copy the indexes and constraints from source table to destination (new) table. Such as, we know that the Id column is the primary key for the Person table but this attribute does not transfer to the CopyPerson table.

Create a table using Azure Data Studio

Azure Data Studio is a new lightweight tool that enables us to execute queries on-premises or cloud databases and it also helps to manage databases. Code Snippets is one of the main advantages of the Azure Data Studio, these templates assist us in typing queries to generate proper syntax easily. After opening a new query window, we type “CREATE TABLE” to create a SQL table and select the sqlCreateTable snippet.

How to create a table with Azure Data Studio

After selecting the template, the query table will be automatically typed into the query window.

Using code snippets to create a new SQL table

After modifying the required fields the template, the CREATE TABLE statement will be completed.

Create a table on Azure Data Studio

Conclusion

In this article, we learned the fundamentals of the SQL tables in the relational databases and then learned different techniques used to create tables. We can use the method that is easiest and convenient for us.

Esat Erkec
Latest posts by Esat Erkec (see all)
679 Views