DbSchema | SQL Server - How to Create a Table?

---

SQL Server • 13-Jun-2023

by Malik Umair Sajjad

---

Table of Contents:

1. Introduction
2. Prerequisites
3. SQL Server Data Types
4. Creating a Table in sqlcmd
5. Creating a Table in DbSchema
6. Conclusion
7. References

Introduction

Creating tables in a database is an essential task for any database administrator or developer. Tables are the fundamental building blocks where data is stored in relational databases. This article guides you through the process of creating tables in SQL Server using two different methods: using the sqlcmd command line utility and DbSchema graphical tool.

Prerequisites

Before you begin, ensure you have:

1. SQL Server installed and running on your machine.
2. sqlcmd utility installed (usually comes with SQL Server installation).
3. DbSchema installed on your machine.
4. Basic understanding of SQL (Structured Query Language).

For installation and establishing connection you can read our article SQL Server-How to create a database?

SQL Server Data Types

SQL Server supports several data types that allow it to store different kinds of data. They are categorized as follows:

_Category_	Description	_Examples_
Exact Numerics	Store numbers exactly as they are specified	int, bigint, decimal
Approximate Numerics	Store floating point numbers, which can be an approximation	float, real
Date and Time	Store date and time values	datetime, date, time
Character Strings	Store non-Unicode character data	char, varchar, text
Unicode Character Strings	Store Unicode data, which supports characters of almost all languages	nchar, nvarchar, ntext
Binary Strings	Store binary data	binary, varbinary, image

Creating a Table in sqlcmd

sqlcmd is a command line utility provided by SQL Server. Here’s how you can create a table using sqlcmd:

1. Open Command Prompt: Navigate to the start menu and open Command Prompt.

2. Connect to SQL Server: Enter the following command to connect to SQL Server:

   sqlcmd -S <server_name> -U <username> -P <password>

   Replace username with your username and password with your password.

3. Create a Database (if not exists): You can create a new database using the following command:

   CREATE DATABASE <database_name>;

   Replace database_name with the name of your database.

4. Use the Database: Enter the following command to switch to your database:

   USE <database_name>;

   Replace database_name with the name of your database.

To know more about creating a database you can read our article SQL Server-How to create a database?

5. Create Table: The general syntax for creating a table is:

   CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

   For example, to create a table named Students, enter the following command:

   CREATE TABLE Students (StudentId int, StudentName nvarchar(50), StudentAge int);

• CREATE TABLE: This is a SQL keyword that is used to create a new table.
• Students: This is the name of the new table.
• (StudentId int, StudentName nvarchar(50), StudentAge int): These are the columns of the table, each with a specified data type.

Let’s go through each column:

• StudentId int: This creates a column named StudentId that can store integers (int).
• StudentName nvarchar(50): This creates a column named StudentName that can store Unicode string data. The (50) indicates that this string can be up to 50 characters long.
• StudentAge int: This creates a column named StudentAge that can store integer data.

6. Exit: Use QUIT; to exit sqlcmd.

Creating a Table in DbSchema

DbSchema is a graphical tool for designing and managing databases. Here’s how you can create a table in DbSchema:

1. Launch DbSchema: Open the DbSchema tool on your machine.

2. Connect to SQL Server: Click on Connect and fill in the details like server name, username, password, and the database name.

3. Create a New Table: Navigate to Create on the top toolbar, and then Table.

4. Define Table Structure: A new window opens where you can define the table name, columns, and their data types.

   For example, to create a table named Students, you would add columns like StudentId with type int, StudentName with type nvarchar(50), and StudentAge with type int.

5. Save the Table: Click OK to save the table to the database.

6. Save and Apply the Changes: Click on Apply Changes to save your table structure and create the table in the database.

7. Verify Table Creation: To ensure the table has been created successfully, navigate to the Database menu at the top, select Refresh, and then Refresh All. The new students table should now be visible in your Schema Layout.

Conclusion

We have covered how to create a table in SQL Server using sqlcmd and DbSchema. Understanding these processes and the different data types SQL Server supports will be beneficial when setting up your database. However, it’s important to note that the methods we used here are just two of the many ways available to create tables in SQL Server.

References

1. Microsoft SQL Server Documentation
2. sqlcmd Utility
3. DbSchema Documentation
4. SQL Server Data Types