SQL Server CREATE TABLE

A table enables the user to store data and display information in the database. It arranges the data in rows and columns. It can have several rows and columns, and each column has a data type associated with it that specifies the type of data it can store, such as numbers, strings, or temporal data. It is similar to worksheets in the spreadsheet application. Three items are needed for table creation:

• Name of the table
• Names of fields
• Definitions for each field

We can create a table in SQL Server mainly in two ways:

• Transact-SQL Command
• SQL Server Management Studio

Let us discuss them one by one in detail.

CREATE TABLE using T-SQL command

We can create a table in the selected database by using the CREATE TABLE command. We can use this statement only when we have ALTER permission on the schema where the table is created and CREATE TABLE permission in the database. The following is a generic syntax that enables the users to create a new table in SQL Server:

Parameter Explanation

The parameter descriptions of the above syntax are as follows:

database_name: It is the database name in which we are going to create a table. It must be existing in the defined database. Otherwise, it will assume the current database by default.

schema_name: It indicates the schema to which our newly created table belongs.

table_name: It is the name of the new table and must be unique in the selected database. It should be a maximum of 128 characters.

column_definition: It indicates the column names of the table along with data types for each column. These data types can be int, float, char, varchar, text, datetime, and Boolean. The columns in the table definition are separated by the comma operator.

table_constraints: It indicates the table constraints such as PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK, etc.

We should provide a primary key column for each table. It can have single or multiple columns. Usually, we use the first column as a primary key and then other columns. If we define only one primary key column in a table, we should use the PRIMARY KEY keywords. Otherwise, it is advised to define a PRIMARY KEY as a table constraint. A column can have single or multiple constraints, such as CHECK, UNIQUE, and NOT NULL.

Example

Let us understand how we can create a table in SQL Server with the help of an example. Here, we are going to create a table named "Student" using the following statement:

In the above statement, we have created a 'Student' table in the 'TestDB' database under the 'dbo' schema. This table contains five columns Id, Name, Gender, Age, and Marks. Here, we have defined the id column as a primary key. The IDENTITY data type generates integer numbers that automatically begin with one and increase by one for each new row.

After the successful execution of this statement, we will get the below output. Now, we can add records to this table by using the INSERT statement.

CREATE TABLE using SQL Server Management Studio

We can create a table in SSMS by using the Table Designer. The following steps explain the creation of a table using Table Designer:

Step 1: Open the SSMS in administrator mode to avoid any permission issue. We will see the below screen where we need to click on Connect button to continue.

Step 2: Once the connection becomes successful, the Object Explorer window will appear on the left-hand side of the screen. If the Database folder isn't visible, click the "+" icon to expand it.

Step 3: Next, expand the Databases folder by clicking on the plus (+) button. We will see all available databases in the current server instance.

Step 4: The next step is to select the desired database in which you want to create a table and expand it. It will display the sub-menu such as Database Diagrams, Tables, Views, and, as shown in the below screen.

Step 5: The next step is to select the Tables folder, right-click on it, we will get the pop menu. Clicking on the New option will display a drop-down list where we will choose the Table option. See the below image:

Step 6: Once we click the Table option, we will get the Table Designer window. This window will include the column name, data types, and Not Null constraint to select whether to allow nulls or not for each column. For example, we want to create a table named 'Person' that will store four columns:

• FirstName
• LastName
• Mobile
• City
• Age

We can create this table by filling these column names and their data types as shown in the below image:

Step 7: If you want to define more properties for any specific column like IDENTITY or COMPUTED column values, then clicking on the desired column will display the column properties tab. Here you can choose the appropriate properties.

If you want to define the column as a primary key, right-click the desired column and select Set Primary Key, as shown below:

If you want to define a foreign key relationship, check constraints, or indexes, you need to right-click anywhere in the Table Designer pane and select the desired option from the context menu as shown below:

Step 8: A table stored in the dbo schema by default. If you want to store the table in a different schema, you need to right-click anywhere in the Table Designer pane and select Properties. We will see the below drop-down list where we can select the appropriate schema.

Step 9: Once we have done, navigate to the File menu, and choose the Save table name.

When we click the save button, SQL Server will display a box to choose the name of a table. Write your desired name for the table and click OK. For example, we have given a Person name for our table:

Step 10: Once the table creation is successful, we can see them by expanding the Tables folder under the Object Explorer and press the F5 button on our keyboard to refresh the list of objects. Now, we can see the new table (Person) in the list of tables.

This article will explain the complete overview of creating a new table in SQL Server using the T-SQL statement and SQL Server Management Studio (SSMS).