- slide 1 of 2
What are SQL Server Tables?
This article assumes that you have access to a SQL Server database and have installed SQL Server Management Studio, which is the official Microsoft tool for managing SQL Server. Within SQL Server and most other relational databases, a table is the standard way of defining the structure of similar data elements. At an elementary level, a database table is built using one or more columns, with each column requiring a specific data type. These data types could be strings, integers, decimals, dates, etc. and define the type of element that fits within that column. Each data entry within that table is then encapsulated as a unique row spanning the columns, while conforming to the defined data structure.
There are a number of other requirements or specifications that can be added to the schema of a table when it is created, such as if specific columns are required or may be omitted, if specific columns should be automatically generated based on a formula, or if columns define a relationship against other tables. Since the best way to learn is by example, let's jump right in with a sample script. Note that there is also a GUI method of creating tables, which is fairly self explanatory.
- slide 2 of 2
Example: Creating a Table in SQL Server
For the purpose of this example, let's create a basic table that will store blog posts. We will use five simple columns, or elements of data, to describe each unique blog post. Those are: a unique identifier for the blog post, the title of the post, the content of the post, when the post was created, and who created it. The simple command to create a table which has these columns is:
CREATE TABLE [Posts](
[PostID] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Title] varchar(100) NOT NULL,
[Content] varchar(4000) NOT NULL,
[Created] datetime NOT NULL,
[AuthorID] int NOT NULL)
This will then be graphically depicted in SQL Server Management Studio as the image to the right. Let's break down what we just did.
The first line of the script denotes that we are going to create a new table called "Posts." Every line of the script after that (between the parentheses) defines a specific column within that table. Moving from left to right, we first name the column, e.g. [PostID]. In this example I've placed the column names within brackets, which is a best practice, but not technically required unless the column name contains spaces or special characters.
Next, we define the data type of the column, such as an int or integer for the PostID. You can view the full list of SQL Server data types here. The column name and the data type are the only two required fields to describe a column, but in our example, we chose to add several option attributes as well. To start, we marked each column as "NOT NULL", which means that they are required in any data inserted into the table. We also set the PostID as the Primary Key, which adds various restrictions to ensure that it uniquely identifies each record in the table. Finally, we set the PostID to automatically generate itself using the Identity function.