Creating SQL Server Tables Part 1
The fifth part of the SQL Server Programming Fundamentals tutorial looks at the creation of tables within a SQL Server database. Tables provide structured storage locations for all of the information stored within a relational database.
What is a Table?
The table is the most important part of a relational database. Tables are the storage location for all information within a database. Each table holds a single set of structured information. For example, you may have a database that stores customers, order headers and order lines. Three tables could be used in this situation, one for each key entity.
Columns and Rows
A table is organised into columns and rows. One named column is defined in a table for each individual piece of data that may be stored. In a table of customers, columns may be created for first name, last name, etc. The columns define a set of rules that restrict the information held in the table. Rows contain the actual information stored in the table. Each row includes a value for every column according to the rules that they enforce. In the case of customers, this would be one row per customer.
Types of Data
All columns have a data type. The data type enforces a rule that restricts the kinds of information that can be held in the table. Generally speaking, the data types can be classified into five groups: numeric, character, date and time, large objects and other miscellaneous types. The various data types are described in the following sections.
When choosing the type of data for numeric columns, the type that uses the minimum amount of storage space, whilst still allowing for the full range of possible values, should be selected. If in the future the requirements for a column change, it is possible to modify a column's data type to allow for a larger range.
Numeric Data Types
The numeric data types are used for columns that hold numeric data only. This includes integers, floating-point numbers and fixed-point values. There are eleven numeric data types.
Bit Data Type
The bit data type holds a Boolean value. Though technically this may not be considered a numeric data type, it is included in this section as often the values it contains are referred to as either zero or one.
Int Data Type
Int data is used to store 32-bit integers that can hold values between -2,147,483,648 and 2,147,483,647. Columns of this type require four bytes of storage per row of data.
BigInt Data Type
BigInt columns can store 64-bit integer values. They can hold values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. Columns of this type require eight bytes of storage per row of data.
SmallInt Data Type
A smallint is a 16-bit integer that can hold values between -32,768 and 32,767. Columns of this type require two bytes of storage per row.
TinyInt Data Type
The TinyInt data type is the smallest integer type. It holds an unsigned 8-bit integer value containing values between 0 and 255. Columns of this type require one byte of storage per row.
Decimal Data Type
The decimal data type is used to store fixed-point numbers with up to 38 digits. The limitations of the number are described using precision and scale values. The precision determines the total number of digits that can appear in the number, including digits to the left and right of the decimal point. The scale value determines how many of these digits appear to the right of the decimal point. For example, a precision of five and a scale of two, described as (5,2), allows values with up to three digits in the integer part and two digits in the fractional part, such as 123.45.
The amount of storage space used by decimal value varies according to the precision value specified:
|1 - 9||5|
|10 - 19||9|
|20 - 28||13|
|29 - 38||17|
Numeric Data Type
The numeric data type is functionally equivalent to the decimal data type.
Float Data Type
The float data type stores floating-point values. Unlike decimals and numerics, the float data type stores approximate values only and its use is subject to rounding errors.
The float data type requires the specification of a value that determines the accuracy of the stored value. The value can be between one and fifty-three and specifies the number of bits used to hold the mantissa of the floating-point number when described in scientific notation. The overall precision and the storage space required for floats are also determined by this value.
|Mantissa Bits||Precision||Storage Bytes|
|1 - 24||7 digits||4|
|25 - 53||15 digits||8|
5 July 2008