Create a Table in SQL Server 2017

• Create a Database
• Create a Relationship

Here we create three tables in SQL Server, then analyze the script that created them. We also run another script to verify that the table was created.

Previously we created a database from a script. However, the database is a blank database — it contains no tables or data.

Now we're going to create some tables for our database.

First, as a refresher, here's what we've done so far:

That created a blank database. If you haven't done so yet, run that script.

Now run the following script:

That script adds three tables to the database. The tables are called Artists, Genres, and Albums.

Here's the result in SQL Operations Studio/Azure Data Studio:

Any GUI tool will display the tables in pretty much the same way. Each table can be expanded to reveal its columns, as well any indexes, triggers, keys, etc. You can also expand the Columns node to reveal the list of columns and their data types, as well as any constraints that have been specified.

Explanation of the CREATE TABLE Statement

The script we just ran consists of three CREATE TABLE statements — each one creates a different table. The script also adds a foreign key constraint, but we'll look at that later.

For now, let's analyze the first CREATE TABLE statement in our script:

This SQL statement creates a table called Artists with three columns, called ArtistId, ArtistName, and ActiveFrom. Each column's definition begins with its name, followed by its data type and any constraints to be applied to that column.

Here's a more detailed "line by line" description:

USE Music;

This part isn't actually part of the CREATE TABLE statement. It's only there to switch over to the Music database. There could be many databases on the server and we want to be sure that we're creating the tables on the correct database. You don't need this line if you're already working in the correct database.

CREATE TABLE Artists (

This is the start of the CREATE TABLE statement. It is followed by the table name (in this case Artists), followed by the first of the parentheses that enclose the table definition.

ArtistId int IDENTITY(1,1) NOT NULL PRIMARY KEY,

We then define the first column. In this case we call it ArtistId, specify its data type as an integer (int), set it as an indentity column (this column will provide the unique ID for the table, and the value will increment with each row added), the (1,1) means that the value starts at 1 and increments by 1, NOT NULL means that the field cannot contain null values, and PRIMARY KEY sets this column as the primary key for the table. A primary key is a column that has been configured as the unique identifier field for the table.

ArtistName nvarchar(255) NOT NULL,

The next column is called ArtistName and its data type is nvarchar(255), which means that it accepts variable-length Unicode string data, with a maximum length of 255 characters. We also set this column to NOT NULL so that it cannot contain null entries.

ActiveFrom date

The last column is called ActiveFrom and we set it to accept a data type of date

);

We then use ) to close the definition, and ; to end the statement (the semi-colon is a statement terminator).

GO

Signals the end of a batch of Transact-SQL statements. This is not actually a Transact-SQL statement. It is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code Editor to signal the end of a batch of Transact-SQL statements.

How to Retrieve Table Information When using a Command Line Tool

When using a command line tool, you don't have the luxury of seeing your database tables sitting in the side panel waiting to be expanded with a single click. But that doesn't mean you can't view information about your tables or other database objects.

When using a command line tool, you can run the following statement to display information about the tables in the above database:

column_name  data_type  character_maximum_length  is_nullable
-----------  ---------  ------------------------  -----------
ArtistId     int        null                      NO         
ArtistName   nvarchar   255                       NO         
ActiveFrom   date       null                      YES        
GenreId      int        null                      NO         
Genre        nvarchar   50                        NO         
AlbumId      int        null                      NO         
AlbumName    nvarchar   255                       NO         
ReleaseDate  date       null                      NO         
ArtistId     int        null                      NO         
GenreId      int        null                      NO         

10 row(s) returned

Executed in 1 ms

The table in question is in the database called Music so that's why we switch over to it first.

You can also narrow it down to a specific table by using a WHERE clause:

column_name  data_type  character_maximum_length  is_nullable
-----------  ---------  ------------------------  -----------
ArtistId     int        null                      NO         
ArtistName   nvarchar   255                       NO         
ActiveFrom   date       null                      YES        

3 row(s) returned

Executed in 1 ms

There are many more fields that can be returned by information_schema.columns. You can use SELECT * to return all of them if you wish.

The command line method for retrieving table information also works when using a GUI tool (after all, they're just SQL statements). You can enter the same SQL statements to retrieve the same data whether you use sql-cli, sqlcmd, SSMS, Azure Data Studio, DBeaver, or any other SQL Server management tool:

• Create a Database
• Create a Relationship