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.
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.
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.
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.
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.
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.
When we try to check the Allow Nulls option for a column that has been set as a primary key, SSMS returns an error.
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.
The key sign will be shown for these multiple columns.
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.
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.
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.
|
1 2 3 4 5 6 7 |
CREATE TABLE [Persons] ([Id] [INT] PRIMARY KEY, [Name] [VARCHAR](50) NOT NULL, [SurName] [VARCHAR](50) NOT NULL, [Age] [SMALLINT] NOT NULL ) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Persons') BEGIN DROP TABLE Persons END GO CREATE TABLE [Persons] ([Id] [INT] PRIMARY KEY, [Name] [VARCHAR](50) NOT NULL, [SurName] [VARCHAR](50) NOT NULL, [Age] [SMALLINT] NOT NULL ) GO SELECT * FROM Persons |
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.
|
1 2 3 4 |
SELECT * INTO CopyPersons FROM Persons WHERE 1=0 GO SELECT * FROM CopyPersons |
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.
After selecting the template, the query table will be automatically typed into the query window.
After modifying the required fields the template, the CREATE TABLE statement will be completed.
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023