Tables are essential database objects used to store data in SQL Server databases. While SQL Server Management Studio provides a graphical interface to create tables, T-SQL code offers a more flexible and efficient way to work with tables. In this article, we will explore how to create tables using T-SQL in SQL Server.

Prerequisites

Before we begin, make sure you have access to a SQL Server instance using SQL Server Management Studio (SSMS). If you don’t have SSMS installed, you can download it from the official website.

Create a Demo Database

Let’s start by creating a database named DemoDB for demonstration purposes. Open SSMS and right-click on the Databases folder. Select New Database and enter the desired name for your database. Alternatively, you can use an existing database.

To create the database using T-SQL, execute the following command:

CREATE DATABASE DemoDB

Create a Table

Now, let’s consider a scenario where we need to monitor and track Grade 3 students’ data. Instead of using spreadsheets in Excel, we can create a table in a SQL Server database to store and organize this information.

The basic syntax to create a table using T-SQL in SQL Server is as follows:

CREATE TABLE database_name.schema_name.table_name
(
col1 datatype [NULL | NOT NULL],
col2 datatype [NULL | NOT NULL],
...
)

Here, we use the CREATE TABLE statement to create a new table with a specified database name, schema name, and table name. We list the column names in the column definition along with their data types and indicate if the columns allow null values or not.

For example, let’s create a table named Grade3Students in the DemoDB database to store basic student information:

USE DemoDB
GO

DROP TABLE IF EXISTS Grade3Students

CREATE TABLE Grade3Students
(
StudentId    int          NOT NULL,
FirstName    varchar(20)  NOT NULL, 
LastName     varchar(20)  NOT NULL,
DateOfBirth  date         NOT NULL,
Address      varchar(30)  NULL,
PhoneNumber  nvarchar(10) NULL,
DepartmentId int          NOT NULL
)

After executing the above query, the Grade3Students table will be created in the DemoDB database. You can refresh the Databases folder in SSMS to see the newly created table.

Conclusion

In this article, we have learned how to create tables in SQL Server using T-SQL. By using T-SQL code, we have more control and flexibility in creating tables and defining their properties. This allows us to efficiently organize and store data in SQL Server databases.