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.

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:

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.

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.

Binary Data Type

The binary data type is used to hold fixed length binary-formatted data. This is useful for storing information that must be encrypted, such as passwords. The size of binary columns is specified as between one and eight thousand bytes.

VarBinary Data Type

The varbinary data type is a variable length version of the binary type. The data size is between one and eight thousand bytes but the amount of storage space used is determined by the actual length of information stored.

SQL_Variant Data Type

The sql_variant data type can be used to store information of varying types in a single table column. An sql_variant can contain most other data types with the exception of large objects and user defined types.

Large Object Data Types

Large object data types are used to hold information that cannot fit in the limited size of the previously mentioned types. They permit the storage of binary data in addition to standard Unicode and non-Unicode text. The information in a large object is held separately to standard row data, potentially in a separate filegroup.

VarChar(MAX) Data Type

The varchar(MAX) data type is used to hold non-Unicode text and is similar to the standard varchar type. The MAX data size indicates that columns of this type may hold up to almost two gigabytes of information with an enforced maximum size of 2,147,483,647 bytes.

This data type was introduced in SQL Server 2005 and should be considered a replacement to the text data type. Text data is still permitted for legacy and compatibility purposes. It may be deprecated in a future release of SQL Server.

NVarChar(Max) Data Type

The nvarchar(MAX) data type is equivalent to varchar(MAX) except that it stores Unicode characters. This data type is a replacement for the ntext data type, which may be deprecated in future SQL Server versions.

Text Data Type

The text data type provides a method for storing large non-Unicode character strings of up to just less than two gigabytes. In SQL Server 2005, the text data type is provided for legacy and compatibility purposes only. The varchar(MAX) data type should be used instead for new databases.

NText Data Type

The ntext data type is the Unicode equivalent of text. For new database, use nvarchar(MAX) instead of ntext.

XML Data Type

The XML data type is used to hold XML documents and fragments. A fragment is an XML document that does not include a single top-level element. An XML schema may be associated with an XML data column, preventing information that does not confirm to the schema from being stored. This type of column is said to hold typed XML. The maximum size of XML data is 2,147,483,647 bytes.

VarBinary(MAX) Data Type

The varbinary(MAX) data type is similar to the varbinary data type. This large object version may hold up to 2,147,483,647 bytes of information in a single column. It is often used for holding graphical information or information that would normally be held in a file. The data type is a replacement for the image data type, which may be deprecated in the future.

Image Data Type

The final data type in SQL Server 2005 is the image. This is a legacy data type that is provided for compatibility with older versions of SQL Server. For new databases, the varbinary(MAX) data type should be used instead.

Nullable Columns

In addition to the type of data held in a column, all table columns can be marked as either nullable or not nullable. A column that is not nullable requires that a value is present in every row. Nullable columns can be assigned the special value of NULL. A null value in a row indicates that the value has not been set.

The null value gives an additional option to the range of values that a column can contain. This is beneficial when all of the possible values that a data type gives could potentially be used in a column but an additional, "undefined", option is required. For example, consider a database that holds the resultant data from a survey where interviewees answer yes or no to a series of questions. The answers would be stored in nullable bit columns with a 0 indicating that the user answered "no", a 1 for "false" and null to indicate that the question remains to be answered.

Creating Tables

In this tutorial we will consider two methods for creating database tables. The first is using the graphical user interface aspects of SQL Server Management Studio and is examined in this article. The second, using Transact-SQL (T-SQL) commands and scripts, will be described in the next tutorial instalment. We will use these technologies to create tables within the sample JoBS database. If you have not done so already, create the empty JoBS database using the techniques described in the earlier article, Creating SQL Server Databases Part 1.

Creating Tables Using SQL Server Management Studio

In this section we will using SQL Server Management Studio (SSMS) to create a new table in the JoBS database. This table will hold the list of customers that receive services from DisasterFix. The usual naming convention for database tables is a name that describes the table's contents and is specified in Pascal case. In this case, we will name the table "Customers".

To create the new table, expand the tree in the Object Explorer so that the Tables branch of the JoBS database is visible. Right-click this branch and select "New Table..." from the context-sensitive menu that appears. A new design window will be opened in the main area of SSMS and the Properties area will update to allow modification of the new table's options. At this point, the new table has not yet been created in the database.

Setting Table Properties

There are several properties that can be set for a new table. The key properties that are of interest to this article are as follows:

• Name. The name of the table. A new name for the table can be entered here to replace the default name. If the name is not changed, a new name will be requested when the table is saved.
• Description. A simple, human-readable description for the table.
• Schema. Tables may be grouped into schemas using this option. Schemas are beyond the scope of this article.
• Regular Data Space Specification. This setting can be expanded and, using the options within, you can set the filegroup for storage of the table's row data. This is the storage location for normal column data, excluding large objects.
• Text/Image Filegroup. If desired, large object data can be held in a different filegroup to normal row data. The target filegroup is specified using this property.

The only property that requires modification for this first table is the name. Change the name property of the table to "Customers".

Adding Columns

The Customers table will be used to hold various elements of data for each customer row. These are as follows:

• Customer Number. Each customer will have a unique reference number. This will be used to identify the customer and will appear on correspondence. A simple, non-nullable integer will suffice for this column. It will be named "CustomerNumber" to match the Pascal case standard. Note that the space has been removed from between the words in the column name. This is not strictly necessary but makes the column easier to work with.
• Contact Name. All customers will require a name. For business customers this will be the primary contact for the business. For homeowners this will be the name of the person who purchased a service or contract from DisasterFix. The name will be held in two columns named "FirstName" and "LastName". As these will be quite variable in size we will use a varchar column for each. The maximum size for each will be 25 characters.
• Business Name. For business customers, the name of the company will be held. This will be stored as a variable length character string with a maximum size of one hundred bytes. As homeowners will not have a business name, the "BusinessName" column will be nullable.
• Creation Date. The final column will hold the date of creation of the customer. This will be used for auditing purposes with only the date being required. We will use a non-nullable, smalldatetime column named "CreationDate".

To create the table's columns, you use the grid in the main area of SSMS. The first column in the grid determines the name of the new column. The second column in the grid permits the selection of the data type for the new table column. Where the data type requires a size to be specified, this is appended to the data type name within parentheses (). The last column shows a checkbox for each table column. If checked, the new column will allow null values to be stored. If not checked, a value will always be required.

Add the five column definitions to the table designer grid. The final result should look similar to the diagram below. If you make an error whilst adding the columns, you can modify values after clicking on them. You can also insert and delete items by right-clicking the small square at the left of the grid row and choosing the appropriate option from the context-sensitive menu.

Once your design matches that shown, the table must be saved. To do so, select "Save Customers" from the File menu, click the Save button on the toolbar or press Ctrl-S. The table will be committed to the database and will appear beneath the Tables branch of the Object Explorer. NB: If the table does not appear, right-click the Tables branch and select "Refresh".

The table designer may now be closed by clicking the X at the top-right of the tabbed window. If you need to edit the table again at a later date, the designer can be reopened by right-clicking the table name in the Object Explorer and selecting the "Design" option.

Congratulations, you have now created the first JoBS database table!

Limitations

There are some limitations that you should be aware of when creating database tables. The first of these is that there is a limit of two billion tables permitted per database. However, a database containing such a large number of tables would be unwieldy to say the least!

A single table may contain up to 1,024 columns. The total size of these columns must not exceed 8,060 bytes, excluding large object data. This is a limitation on the size of the actual information held in a row. It is, therefore, possible to create a table containing variable length columns with a maximum size greater than 8,060 bytes. However, if you try to insert a row with combined data exceeding this limit you will receive an error.