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).
Here is a list of commonly used Datatypes
- 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.