Create a Table in SQL Server 2017
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
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
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:
- This part isn't actually part of the
CREATE TABLEstatement. 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 TABLEstatement. 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 NULLmeans that the field cannot contain null values, and
PRIMARY KEYsets 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
ArtistNameand 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 NULLso that it cannot contain null entries.
- The last column is called
ActiveFromand we set it to accept a data type of
- We then use
)to close the definition, and
;to end the statement (the semi-colon is a statement terminator).
- 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
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: