You can imagine a TABLE in SQL as an Excel spreadsheet. It contains rows and columns with data in their fields.

Let us create a table for pupils. The table should contain information about the pupils unique Id, the Name and the Date of Birth.

The query for such a table will look like this:

CREATE TABLE Pupil
(
  Id int NOT NULL,
  Name varchar(50) NULL,
  DateBirth datetime NULL
);

The Query starts with the command “Create Table [Tablename] (” followed by “Fieldname Datatype Nullable“. A Fieldname should be self explanatory. Available Datatypes see chapter below. Nullable means, if the value must be set. A fields default value is null (kind of empty or let’s undefined).

Datatypes

Here is a list of commonly used Datatypes

Numeric

• int – number between -2.147.483.648 and 2.147.483.647
• bigint – number between -9.223.372.036.854.775.808 and 9.223.372.036.854.775.807
• smallint – number between -32.768 and 32.767
• tinyint – number between 0 and 255
• bit – 1 or 0, use it as boolean true and false
• decimal(precission, scale) – define the total length and the comma-length – e.g. decimal(5,2) 249,16 or decimal(8,3) 12345678,123

Date and Time

• date – date only – e.g. 16/09/1985
• time – time only – e.g. 11:15
• datetime – date and time part combined in one field – e.g. 16/09/1985 11:15

Characters and Strings

• char(size) – uses always the max size – e.g. char(15) = “Hello          “
• varchar(size) – uses only the exact size -e.g. varchar(15) = “Hello”
• text – long text, length up to 2 billion

For further information about SQL Server and available Datatypes see http://technet.microsoft.com/en-us/library/ms187752.aspx.

Introduction  << — >> Part 2