BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

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.

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.

Date and Time Data Types

The date and time data types are used to store dates and times. These two data types differ only in the accuracy of the information that can be held. You should choose the smalldatetime data type if the accuracy and date range is acceptable. If not, use the datetime type.

DateTime Data Type

The datetime data type is the more accurate of the two date and time data types. Columns of this type can hold dates between 1 January 1753 and 31 December 9999. The time element of the data type is accurate to one three-hundredth of a second. Columns of this type require four bytes of storage per row of data.

SmallDateTime Data Type

The smalldatetime is similar to datetime but uses only four bytes of storage per data row. Accordingly, the range of dates permitted is reduced to 1 January 1900 to 6 June 2079. The time element of the data type has a lower accuracy, only storing hours and minutes.

Miscellaneous Data Types

These data type are used to store information that cannot be classified as simply numeric or textual.

UniqueIdentifier Data Type

The uniqueidentifier data type is used to hold globally unique identifiers (GUIDs). A GUID is a large hexadecimal number that can be generated with a very small chance of ever being duplicated. Each GUID uses sixteen bytes of storage space.

Timestamp

A timestamp column simply holds an eight-byte number that is unique to the database. Timestamps change each time their containing row in the database is modified. This makes timestamps ideal for determining a version number for a row and determining if a row has changed between two points in time. It is important to understand that, despite the name, timestamps do not store a date or time.

5 July 2008