SQL 2005+

Creating SQL Server Tables Part 1 (2)

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.

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

Real Data Type

The real data type is functionally equivalent to float(24), ie. a float data type with 24 bits for the storage of the mantissa. A real value uses four bytes of data storage.

Money Data Type

The money data type is used to hold currency values. The money data type uses eight bytes of storage to hold a fixed-point value with four decimal places. The value may be between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.

SmallMoney Data Type

The smallmoney data type is similar to the money type but uses only four bytes of storage. The range of values that it may hold is from -214,748.3648 to 214,748.3647.

Character Data Types

Character data types are used to store textual information. Depending upon the type selected, the data is of fixed or variable length. Fixed length columns hold a specific number of characters. If the string stored in the column is larger than that available, the text is truncated. If the text is shorter than the fixed size, the data is padded. Variable length data types have a maximum size defined and truncate strings that are oversized.

Character data types can be used to store either Unicode or non-Unicode information. Unicode strings use two bytes per character and allow letters, numbers and symbols from many international languages to be stored. Non-Unicode data types cannot store multilingual character sets but generally only use a single byte per character.

The selection of textual data type is not at simple as choosing a numeric type. Firstly, consider whether the data to be stored will contain information in multiple languages. If it will, select a Unicode type. If not, select the smaller non-Unicode equivalent. If the size of the information stored in a character-based column will generally be approximately the same size for all rows in a table, select a fixed size. If the text will vary in length considerably, select a variable size column type.

Char Data Type

The char data type is used to hold a fixed length, non-Unicode text string of up to 8,000 characters. A size is specified that determines the number of bytes that will be used to hold the text. For English collations, the number represents the number of characters that may be held. If the collation specified for the database or the column uses double-byte characters, the maximum length of the string will be less.

NChar Data Type

The nchar, or national character, data type stores fixed length Unicode text. The size of the data type is specified in characters and may be between one and four thousand. The storage size for an nchar column is always double the maximum number of characters.

VarChar Data Type

The varchar data type is similar to char but stores variable length text. A maximum size for varchar columns is specified in bytes and may be between one and eight thousand. The amount of storage space used by a varchar is dependant upon the length of the string stored, not the maximum length specified.

NVarChar Data Type

The nvarchar data type is the Unicode equivalent of the varchar type. The maximum size of the column is specified in characters and the storage space used is double the length of the data actually stored. The maximum size may be between one and four thousand characters.