How to Create a Table in SQL Server
Published: Aug 19, 2013
There are a couple of ways of achieving this, the first is using SQL Server Management Studio as a “point and click” tool, and the other is using T-SQL code.

Personally I only ever use T-SQL and therefore this article will focus on that approach. Personally I find that T-SQL offers much clearer control and power than solely using the GUI.

A table is the component in a database in which you find your data. This is an organised structure usually used to contain logical groups of information. It is created using the syntax:

create table [schema_name].[table_name]
(
    [column_name] [datatype] (not) null,
    [column_name] [datatype] (not) null
)


As an example, you may want to store information about people in a database. Therefore we will create a very basic table called “Person”…

Firstly consider the information you wish to hold… first name, last name, age, hair colour.

Based on this we can already construct a portion of our table:

Note: In respect of [schema_name] I am going to use the “dbo” schema. If you wish to know more about schemas then I will be writing about schemas in the very near future.

create table dbo.Person
(
    firstName [datatype] (not) null,
    lastName [datatype] (not) null,
    age [datatype] (not) null,
    hairColour [datatype] (not) null
)


First thing to consider is whether or not we will allow null (blank) data to be entered. In this case we will insist that everyone we enter has a first and last name. They will also have an age. However, if someone is bald then we will allow null for the hairColour. Based on this we can say that firstname, lastname and age are “not null”, ie. They are not allowed to be empty. Hair colour, however, can be null…

create table dbo.Person
(
    firstName [datatype] not null,
    lastName [datatype] not null,
    age [datatype] not null,
    hairColour [datatype] null
)


We now have to make some decisions about datatypes. If you’re not already familiar with these then please read my article here.

In our example the firstname, lastname, and hairColour columns will be textual, or varying length, and therefore best suit a “varchar”. As for the max length, I have chosen numbers that I believe should be capable of holding the data we require but without being excessive (in order to keep the size of the table and therefore database to a minimum)…

create table dbo.Person
(
    firstName varchar(20) not null,
    lastName varchar(30) not null,
    age [datatype] not null,
    hairColour varchar(10) null
)


This simply leaves us with “age”. The obvious thought would be an int. However, this is a 4 byte datatype and therefore, for example, after adding 100 rows into our table, will be taking up 400 bytes of space. If we consider that someone will never have a negative age and will most likely not have an age about 255, then a tinyint would actually be more appropriate. This only requires 1 byte of storage and therefore the 100 rows would take up 100 bytes. Just a quarter of that used by the int.

This doesn’t sound like much space to argue about but when you consider that some databases are 100GB+ in size, and can contain tables with well over 100 million rows, suddenly the storage savings you can make by simply using the appropriate datatype become apparent.

There we have an executable table creation script (but don’t run it yet)…

create table dbo.Person
(
    firstName varchar(20) not null,
    lastName varchar(30) not null,
    age tinyint not null,
    hairColour varchar(10) null
)


Lastly, it is good practice for every table to have a primary key (this is not essential, but it’s VERY rare that there is good reason not to have one).

For a full description of keys and constraints, please refer to my article here.

A primary key is a column who’s value can be used to uniquely identify the row in which it sits.

In this example we have no guaranteed unique key present so we will add one ourselves. In this case I’ll add an integer column called “id” which HAS to be “not null” in order to qualify as a primary key. I have also chosen int as we could have well in excess of 33,000 people in our table and therefore it will be required…

create table dbo.Person
(
    id int not null,
    firstName varchar(20) not null,
    lastName varchar(30) not null,
    age tinyint not null,
    hairColour varchar(10) null
)


We now make that column the primary key of the table. This can be done during table creation or as an additional step. The additional step would be executed after the creation of the table and looks like this:

alter table [schema_name].[table_name] add constraint [constraint_name] primary key ([column_name])


There we would use it as follows:

alter table dbo.person add constraint pk_person primary key (id)


As mentioned above, this can also be done during the table creation script:

create table dbo.Person
(
    id int not null,
    firstName varchar(20) not null,
    lastName varchar(30) not null,
    age tinyint not null,
    hairColour varchar(10) null
    constraint pk_personprimary key(id)
)


As a very last note, if you wish to auto increment your primary key so that you don’t have to determine the next unique value, then you can request that SQL Server does this for you by using “identity (a, b)” where “a” is the starting value and “b” the size of increment. For example, identity (1, 1) will start at 1 and increase by 1 each time a new record is entered into the table. Identity(10, 2) will start at 10 and increase the value by 2 on each new insert…

create table dbo.Person
(
    id int identity(1, 1) not null,
    firstName varchar(20) not null,
    lastName varchar(30) not null,
    age tinyint not null,
    hairColour varchar(10) null
    constraint pk_person primary key(id)
)


And we’re done. We now have a very comprehensive new table for our database with an auto increasing primary key, appropriate datatypes and rules surrounding compulsory and nullable data.

All that’s left is to insert some data
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron