Microsoft 365


Active Directory


Windows Server


Upcoming FREE Conference on Identity Management and Privileged Access Management


SQL Server

Essential SQL Server: Creating Databases and Tables

Michael Otey

Datacenter networking servers

SQL Server is Microsoft’s premier data platform. While its core is a relational database engine, it also includes many other subsystems and capabilities that go far beyond just a relational database. In this tutorial, I’ll explain how to create two of the most important relational database components: databases and tables.

While not everyone realizes this at first, SQL Server provides business intelligence using Analysis Services, reports from Reporting Services, data integration using Integration Services, data validation with Master Data Services, business continuity using Azure and Availability Groups, and more. These are all of the features that businesses require from an enterprise data platform.

That said, the core of all of these services is relational data. SQL Server’s relational database engine runs on both Windows and Linux.

Understanding SQL Server databases

Databases are the primary containers for your relational database objects. SQL Server lets you create your own user databases, and some of the main relational database objects include tables, schema, indexes, views, logins, and roles. In addition, SQL Server provides a number of built-in system databases including the master, model, msdb and tempdb databases.

Creating an SQL Server database

SQL Server databases and database objects like tables can be created in a variety of ways. SQL Server Management Studio’s (SSMS) Object Explorer has a menu option that enables you to create a table interactively.

However, the most basic way to create databases and other database objects is by executing Transact-SQL (T-SQL) commands. T-SQL commands are text-based, and they’re usually executed using either the SSMS Query Editor or Azure Data Studio.

Using the CREATE DATABASE statement

Creating a SQL Server database using T-SQL command is pretty straightforward. You can do it by using the CREATE DATABASE command like the one below:


This T-SQL command created a database named myDatabase. As you might guess, you can specify the number of additional parameters.

We've created an SQL Server database named myDatabase
We’ve created an SQL Server database named myDatabase (Image credit: Petri/Michael Otey)

Understanding SQL Server tables

Once you have created a user database, you can begin to create tables and other database objects in the database. Databases typically have many different tables.

Tables are structures that contain raw data. Table names are descriptive, and their data is organized by rows and columns. You can access this data using T-SQL queries.

Columns and data types in an SQL Server table

Each column in an SQL Server table has a specified column name and data type that represents the values that will be stored in the column. This is essentially the column definition.

data type is an attribute that specifies the type of data that the column can hold. Some of the most common data types include:

Int  Stores whole numbers
Bigint  Stores whole numbers larger than the standard integer
Smallint  Stores small integers to save storage space
Bit  Stores 0, 1, or NULL
Decimal  Stores fixed precision decimal numbers
Numeric  Stores fixed precision numbers
Money  Stores monetary data
FloatStores floating precision numbers  
Real  Stores larger floating precision numbers
Datetime  Stores date and time values from January 1, 1753 to December 31, 9999
Date  Stores only date from January 1, 0001 to December 31, 9999
Time  Stores only time only values with an accuracy of 100 nanoseconds
CharStores fixed width character strings with a maximum of 8,000 characters
Varchar  Stores variable width character strings with a maximum of 8,000 characters
Varchar (max)  Stores variable width character strings with a maximum of 1,073,741,824 characters
TextStores variable width character strings with a maximum of 2,147,483,647 characters
Table 1 – SQL Server data types

Creating an SQL Server table with the CREATE TABLE Statement

Just as there are different ways of creating SQL Server databases, there are also a number of ways to create tables. SQL Server Management Studio has an interactive Table Designer, but the most basic way to create tables is by executing the T-SQL CREATE TABLE command.

The CREATE TABLE command is typically executed using either the SSMS Query Editor or Azure Data Studio. There are a lot of options when it comes to creating tables. You can incorporate constraints, foreign keys, advanced data types like FILESTREAM and XML, and even make partitioned tables. However, the basic table syntax is fairly simple.

You can see a basic example of a T-SQL CREATE TABLE command below:

USE myDatabase;

CREATE TABLE dbo.myLogins
     name VARCHAR (20),
    login_date DATETIME

In this example, the USE T-SQL command specifies which SQL Server database will be the current database that will be used by any following commands. The semi-colon indicates the end of a T-SQL statement within a batch of commands. The GO command signals the end of a batch of commands.

Next, the CREATE TABLE statement is used to create a table named myLogins. You may notice the schema name of dbo, which is the default schema of every database for all users. An SQL Server schema is essentially a tag for database objects that allows you to group and secure related database objects into separate namespaces.

The three columns in our table are defined inside the parenthesis. This simple example uses three columns: id, name, and login_date. Each column has a defined data type, and they do not have a default value. The id column is an INT and it acts as an identifier for the row. The name column is a VARCHAR and login_date is a DATETIME data type.

The three columns in our table have a defined data type (Image credit: Petri/Michael Otey)

In case you were wondering, tables can have a maximum of 4096 columns – although in practice you would never want to use anywhere near that. You can learn more about the complete syntax of the command from the Microsoft documentation CREATE TABLE.

Creating a Primary Key and a clustered Index

One thing you may notice about the id column is that it uses the PRIMARY KEY keyword to specify that it is the primary key for the table. The primary key should contain unique values. The NOT NULL keyword means that the column must have a value. In other words, it cannot contain a null value.

A primary key is not required, but most tables use them. A table can have only one primary key constraint, and it can consist of single or multiple columns. The purpose of the primary key is to uniquely identify each row in a table. 

SQL Server automatically creates a clustered index when you create a primary key. The clustered index causes all of the rows that are stored in the table to be in the same order as the primary key. This typically improves access efficiency.

Querying tables using the T-SQL SELECT statement

Data gets stored in tables using the T-SQL INSERT statement, and it is usually retrieved using the T-SQL SELECT statement. Likewise, table data is updated using the UPDATE statement and it is deleted using the DELETE statement. 

Querying tables can be an involved topic as you have the ability to select the rows and columns that you want, as well as join multiple related tables together and retrieve the results. However, the basic SELECT statement is fairly simple.

The following SELECT statement will retrieve all of the rows and columns from my myLogins table:

SELECT id, name, login_date FROM myLogins
(Image credit: Petri/Michael Otey)

Creating an SQL Server table from another table using SELECT INTO

A variation of the SELECT statement can be used to create new tables that are a copy of an existing table. The SELECT INTO statement will create a new table and insert the resulting rows from the query into that table. You can see an example of the SELECT INTO statement in the following listing.

SELECT * INTO myLogins2 FROM myLogins;

This will create a new table named myLogins2. As a shortcut, you can use ‘SELECT *’ to retrieve all of the columns from a table.

This practice is fine for occasional ad hoc queries. However, this is not a good practice for your applications as you can wind up retrieving more data than you need, which would result in needlessly increasing the system’s overall workload.


In this article, I detailed the basics of using databases and tables, which are core components of the SQL Server relational database engine. Most tables are permanent structures but you can also create temporary tables. Primary key constraints can be used to uniquely identify and retrieve each row. In addition, you can use foreign key constraints to specify relationships between tables.

Article saved!

Access saved content from your profile page. View Saved