In this step by step SQL Tutorial I will show you how to use SQL CREATE TABLE statement. For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
Who is it for?
- People who would like to learn basics of using SQL CREATE TABLE
- Those who are preparing for Microsoft certifications and need to cover this topic.
CREATE TABLE as the name suggest is used to create a table with specified columns and properties, In this tutorial I will start with very simple examples of create table and expand it gradually.
I will create my tables in AdventureWorksDW2012 database and the table will appear in tables folder.
During this tutorial I will use SQL scripts only.
In the example below we will create the same table multiple time using different options.
First I will create Client table with 3 columns (attributes). ID, FirstName and Surname. The minimum information to create a table is table name, field names and data types.
Please see below my example.
I execute the script
In the Messages I can see that my query was run successfully.
I refreshed tables folder and I can see the new table.
Now I will specify if column should allows Null value or not. In other words NULL means optional and NOT NULL means mandatory.
It's important to understand NULL value. NULL means the value does NOT exist, this is important because "blank" fields that are also known as empty string have a value (empty string) and the value exists. For instance a client may have an email address but has not provided it so it can be recorded as NULL (value not provided) but some clients may not have an email address and this can be entered as blank (empty string).
NOTE: if you don't specify NULL/NOT NULL SQL Server will create column with NULL
See below our example where we say ID and FirstName is Mandatory.
Identity is used to increment row id value (integer), each time new row is added. When insert operation is performed IDENTITY automatically increments column for the next available value. For example if we insert one new row Column with Identity will appear as 1 if we added another row the ID will be 2 and so on.
A table can have only one IDENTITY column. IDENTITY column is often used as Primary Key.
Below is an example of CREATE TABLE with IDENTITY. You will notice two arguments (1,1) which I describe below the picture.
IDENTITY requires two arguments. First is identity seed which specifies starting number and second is Increment by number which specified what value should be added to the previous ID. In our case we start with 1 and increment value by 1 for each new row.
Primary key is a column or set of columns whose values uniquely Identify every row in a table.
There are several method to create Primary Key and below is one of them.
Default value is used to populate new record field value when value is not specified during Insert statement.
Important: If you perform insert for a column with default value and you explicitly put NULL then default value is not used because you specified the value. Remember default value works only when you don't specify the field during insert.
I have create table TestDefault with ID as integer and Test field with default value 1.
Below are two insert statement first one will NOT use default value and will insert NULL and second example WILL use default value so Test field that was not specified in insert statement will contain value 1 (default value).
INSERT INTO dbo.TestDefault (id, Test)
VALUES (1, null) -- This will NOT use default value
INSERT INTO dbo.TestDefault (id)
VALUES (2) -- This will use default value
Below is a DateTimeInserted column where I added DEFAULT GetDate(). So each time new row is inserted without DateTimeInserted specified the value will be added using GetDate() function.
I go to Object Explorer, right click on Client table and from drop down box select Edit Top 200 Rows and type two records but I omit DateTimeInserted.
When I select data from the table I can see that DateTimeInserted field displays current date and time.
I hope the examples above helped you to learn more about SQL CREATE TABLE Statement.