SQL Server – How to Add a Table Using SQL CREATE TABLE
- Blog
- SQL Server
- Post
Once you have created a user database, you can begin to create tables using the SQL CREATE TABLE statement. Databases typically have many different tables. If you haven’t yet created your first database in SQL Server, check out this article on Petri about how to create a SQL database using the SQL CREATE DATABASE statement.
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.
A 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 |
| Float | Stores 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 |
| Char | Stores 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 |
| Text | Stores variable width character strings with a maximum of 2,147,483,647 characters |
Creating an SQL Server table with the SQL 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 SQL 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; GO CREATE TABLE dbo.myLogins ( id INT NOT NULL PRIMARY KEY, name VARCHAR (20), login_date DATETIME ); GO
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.
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
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.
Summary
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.
Related Articles